You learn something new every day

Today I attempted to correct a problem in a FileMaker database - and screwed it up badly. (Thank goodness for back-ups.)

The problem concerned some data that had not transferred properly during a change-over between two systems - some dates in the old system had appeared in the new as plain integers instead of dates.

A quick experiment showed me that the GetAsDate() function in FileMaker Pro would convert the number to the correct date - simple. So I duly went ahead and made the change to the data in-place. I know better than to do this, of course, but I was in a hurry and didn't want to take the time to export out the data, convert it and import it back in. So you can imagine my expression when those numbers turned not into the dates I was expecting, but instead into question marks, Filemaker's way of indicating that the data was now invalid.

Grabbed a back-up and retrieved the numbers, but what went wrong?

Doing what I should have done first time, and examining matters more thoroughly, I discovered something that I would not have expected. My initial experiment involved taking a number from a number field and converting it using GetAsDate(). But in the actual database, I was taking numbers from a date field and trying to convert them using GetAsDate(), and this doesn't work. It seems that retrieving a number (invalid data) from a date field does not return the number, and GetAsDate() consequently fails.

It became more interesting when I tried (on a separate file derived from the back-up) to use GetAsNumber() on those numbers in the date field - they all came back as 0. Okay, that doesn't work.

So, one more try - what does GetAsText() return? Aha! I get the number - as a text string. But now I have my answer - GetAsDate(GetAsNumber(GetAsText(theDate))). Problem solved.


You learn something new every day. And sometimes you get to learn an old lesson all over again.