Partially Valid Dates

Questions
1

Just ran into a weird bug. One of our clients has been entering date fields as MM.DD.YYYY (instead of MM/DD/YYYY). FileMaker accepts this, and seems to interpret the date correctly.

However, we ran into a bug where using ESS to send data to a Microsoft SQL Derver database (via ODBC) we'd get a 729 error (which typically suggests field validation issues).

After some digging, I was able to figure out the issue: Microsoft SQL Server is not happy with "MM.DD.YYYY" format, which makes sense.

Questions:

  • how do we find the historical bad fields? I can't seem to search for "." or "[backslash]." inside a date field. Do I need to create a new calc field = GetAsText(DateField) and then do the search inside that field?
  • how do we prevent this going forward, ideally at the data entry validation stage?
2

For data entry validation:

  • AutoEnter = GetAsDate(Self) // does not fix the problem, strangely
  • AutoEnter = Substitute(GetAsDate(Self); "." ; "/") // does fix the problem
1 Like
3

I ran into that issue, too. Fixed it with an ‘OnObjectExit’ triggered script that substitutes ‘.’ for ‘/‘.

4

I'm afraid to wonder - what other non "/" characters might FileMaker accept silently? And does it depend on the date-format for your locale?

5

Good question. I haven’t checked this yet.

6

The documentation say that it does depend on your system locale.

use the GetAsDate or Date function to enter a date constant into a formula. The format of text date must be the same as the numeric date format on the system where the file was created.

Using the list of tests that you posted in GetAsText ( “” ) - #11 by xochi I was able to get valid dates from all the strings except the ones using spaces as delimiters. One of the strings you provided was "09_11_2001." In my locale Day("09_11_2001") returns 9.

What I have found in my experience with dates is that the safest way to generate a date is to use the Date function but you always have to be aware that the system locale will determine the output :

$date = "09_11_2001"
Date( Month ( $date ) ; Day ( $date ) ; Year ( $date ) )
-- depending on system locale the output will be
-- 9th November 2001
-- 11th September 2001
2 Likes
7

That code works as long as $date is an expression that FileMaker considers to be a date.

1 Like
8

Re: "how do we find the historical bad fields?"
A useful method of examining the contents of any field is to click inside the field and choose Insert —> from Index (best to do this in Find mode to avoid accidental data entry). This will bring up a list of all unique field content, enabling a quick visual check to see the extent of the problem. If you are in Find mode you can then select bad entries, find them and correct them. If the problem is not too extensive that might be as fast as any other way.
Re: "how do we prevent this"
My preferred method is to set up all date fields with dropdown calendar, and select entire contents on entry option checked. This pretty much guarantees that dates will be entered in a consistent format.

4 Likes
9

+ 1 for the insert from index method of clean-up. It's easy to do and not a demanding task. It can dramatically improve search and reporting.

10

I have millions of rows, over a dozen date fields, and thousands of dates, so I think the "insert from index" method would not be practical.

Instead, I created a new, unstored calc field which finds the bad dates and prefixes with a code so I know which field is bad.

Let(
  d = "H:" & GetAsText ( HireDate ) & 
      "¶C:" & GetAsText ( CancelDate ) & 
      "¶J:" & GetAsText ( JoinDate ) & 
      "¶R:" & GetAsText ( RetiredDate ) & 
      "¶D:" & GetAsText ( DeceasedDate ) & 
      [... etc ...] 
;

If(
  PatternCount ( d ; "?" ) > 0 or
  PatternCount ( d ; "." ) > 0;
  d; "")

)

Then I just searched this field for "*" and hand-edited the few I found. If it had been more than a few dozen, I would have written a script to do the fixing for me.

3 Likes