Category: Visual Foxpro

DBI Calendar Control

If you attended Southwest Fox 2016, a free copy of DBI’s Calendar Control was part of your goodies bag. I had a user request come in recently that the calendar control was a perfect fit for so had a chance to work with it.

My application has an “Appointment Book” form that shows all of the appointments, events, etc that the client has to deal with. It’s not terribly sophisticated, just listing all the items in date/time order, but it got the job done:

appointmentbook

The problem the client ran into was that the “Intro” items frequently move around when people call to reschedule. They needed a quicker and easier way to change them as well as seeing what is going on over multiple days. This is exactly what the DBI Calendar Control does and I ended up with the form below. Again, not terribly sophisticated, not using a lot of the color/graphic capabilities of the control, but it gets the job done:

schedule

The advantage here for the user is that they can see the days graphically now, can see multiple days at once, and they can drag & drop the appointments to change the day/time of the appointment.

I was impressed with how easily the control was to work with and how smoothly it works with VFP. In 4 hours or so I went through DBI’s samples (in VFP!), created my own form, and did all the code for adding the appointments, allowing for drag & drop, and double-clicking to open an appointment for editing.

Here’s the 1 line of code for adding an appointment to the calendar. The calendar control works on minutes past midnight rather than a time format so the first parameter is converting my VFP time into minutes. The second parameter is end time which I don’t have for my appointments so I default to start time + 30 minutes. The third parameter is the date while the fourth is whatever text you want to show up.

lIndex = thisform.ctxCalendar1.addAppointment(HOUR(ApDate) * 60 + MINUTE(apDate),;
 (HOUR(apDate) + 1) * 60 + MINUTE(apDate) - 30,;
 TTOD(apDate),;
 lText)

Here’s the code to update my data when an appointment is dragged & dropped to a new date/time. This goes into the control’s AfterAppointmentChange event. The control keeps an index number for every added appointment so I just have to take the nIndex that is passed in, find that entry in my data, and then update my data with all of the other passed in parameters for the new date/time:

SELECT CurAppt
LOCATE FOR ctIndex = nIndex
IF !apx("Seek", CurAppt.apID)
 zmsg("o", "Error! Could not locate appointment to update date/time.", "Error!", "!")
else
 * Update the time of the appointment
 SELECT apAll
 LOCAL lDateTime
 lDateTime = dtDate + (nStartTime * 60)
 replace apDate WITH lDateTime
endif

This code adds or removes day columns when the user changes the form width:

 LOCAL lCol
 lCol = INT((thisform.ctxCalendar1.Width - 60) / 150)
 thisform.ctxCalendar1.DayColumns = lCol

 

Really straight-forward code and the control works really smoothly. In the future I might get fancy and add some color-coding for different appointment types or add some graphics.

The ease of use from DBI has me wondering what other controls I might want to incorporate as well.

Stripe: Working with Pagination

Stripe.com has an …interesting… feature where when you ask for a bunch of things – customers, recent charges, etc – it doesn’t return all of them to you, but instead gives you just the first 100. I’d guess it’s a performance issue, but it does mean you have to take a few steps and a few calls when you need all of something.

For example, you can call Stripe to get customers with a call like: /customers. This will only return the first 100 customers to you.

To get the next 100 customers, you have to do a call like /customers?starting_after=<TheIDOfTheLastCustomerYouGotInThePreviousCall>. (And, no, you can’t do customers?starting_after= and leave this blank. Stripe gives you an error for that.)

So to get all your customers, you have to do the first call and then repeatedly do the second call until Stripe tells you there are no more. How do you know when there are no more? One of the last fields Stripe returns to use is “has_more” which is true/false. If there are more records to return, it’ll be true.

And one final gotcha: Stripe doesn’t return a nice neat ID number for those calls. It returns a bunch of data about the customer, and data about their subscription, and data about their plan, and data about their recent charges. So if you’re searching that data looking for the customer ID, you have to make sure you’re looking in the right place and not getting a Subscription ID by mistake. Additionally, there can be multiple has_more entries in there for that extra stuff. Make sure you’re looking at the right one.

The CustomerList function of the StripeX wrapper is a good example of how to make all of this work.

 

Global hgignore

Rick Borup‘s excellent sessions at Southwest Fox has gotten me using Mercurial in my development. One thing Rick didn’t cover (or maybe I just missed it?) is that you can create a global hgignore file in Mercurial. If you’re working with multiple projects, that’s incredibly handy. You setup the global file just once and it’s used for any repository you have.

You’ll find the global file in My Documents and it’s named hgignore_global.txt. Put all of the standard stuff you want ignored – VFP binaries, *.zip, etc. – in this file and then you won’t have to set that up every time you create a new repository. (Rick has provided a good baseline hgignore in previous Mercurial sessions. Doug Hennig provided one as well in his ‘Lessons Learned with Version Control’ session in 2016.)

The global file works in conjunction with the local .hgignore file – Mercurial will ignore anything that is contained in either file when you attempt a commit. So if one project has some special things to ignore, you just add them to the local hgignore.

Lastly, if you’ve got one strange repository that needs to ignore the global hgignore entries? Mercurial can do that as well.

 

AppendXLSX

I’ve been using the AppendXLSX tool to create XLSX files from my Foxpro data. It works great for the most part, but occasionally I find that it builds the XLSX file but it will be a corrupt file with no data in it. After a lot of fiddling, I figure out that more than 1 date field in the file seems to cause the problem.

This quick bit of code will change the data fields in your table into character.

FOR x = 1 TO FCOUNT()
IF TYPE(FIELD(x)) = “D”
ALTER table (ALIAS()) Alter COLUMN (FIELD(x)) C(10)
ENDIF
NEXT

 

(Does anyone know where AppendXLSX comes from? There’s nothing in the comments saying who the author is and may googling didn’t turn up anything. Whoever your are author, thanks a ton for this tool.)

Word Merges

For years and years now, I’ve been merging Foxpro data in Microsoft Word. It doesn’t always work smoothly and I’ve spent probably more time than I should walking clients through ODBC driver installs, but its always work and, hey, if it ain’t broke – don’t fix it.

Word 2016 broke it. Stupid Word 2016.

Merging Foxpro to Word 2016 gets an “Could not find installable ISAM” error. Crap. I decided it was probably time I stopped trying to merge with Foxpro data (OK, first I tried to Google a solution but couldn’t find one, THEN I decided not to use Fox data anymore).

I decided I’d just convert the Fox data to Excel in my program and then merge that. Works great, except that Word 2016 pops up a “Select Table” dialogue box and asks you to select a worksheet. Stupid Work 2016. DisplayAlerts = .f. does not stop that behavior. Stupid, stupid Work 2016.

A bit of googling turned up that I should pass a select statement to bypass the dialogue box. It looks like this then:

this.o.ActiveDocument.MailMerge.OpenDataSource(lFile,,,,,,,,,,,,”SELECT * FROM ‘Sheet1$'”)

Still not working. Stupid Word 2016. Study the google result a little more carefully, tweak the statement to this:

this.o.ActiveDocument.MailMerge.OpenDataSource(lFile,,,,,,,,,,,,”SELECT * FROM `Sheet1$`”)

Voila! Works. You can totally see the difference right? The Sheet1$ has to have a backwards quote mark around it, not a regular quote mark. WTH is a backwards quote mark? Stupid Word 2016. But it works.

Alter Table Mystery Fail

So I’ve got a bit of code in one of my products that does about 30 Alter Table Add Column commands in a row. Has worked fine for 15+ years, but today it starts failing for one of my clients.

The failure is inconsistent – some fields won’t get added some times, other times other fields aren’t added. It was usually 2-4 fields out of the 30 that wouldn’t add. Even more bizarrely, there’s no error message. The command acts like it works, carries on merrily, but the field is not added to the table.

I know about the problem with Alter Table and Cursors, but this is a table. Tried a FLUSH. Tried a NOVALIDATE. Those didn’t help.

In the end, I simply had to add some code to double-check if the field was actually in the table (the Field() worked reliably). If the field did not exist, I paused for a second and then retried the Alter Table command. I maxed out the retry at 10 times, but I never saw it take more than 1 try to work.

This was a brand new laptop running Windows 10. No idea what the underlying cause is, but I’m thankful I do all my Alter Table commands through a generic utility rather than having to search through all my code, for all my clients, and fix all of them.