Date value lists broken for non-US users

Discussion created by eduncle on May 9, 2012
Latest reply on Jun 13, 2012 by crusecontrol

I have encountered a bug that completey breaks using value lists with dates in FMP12.


I have tested this on migrated FP7 and newly created FMP12 databases on Mac OS Lion and Windows 7.


Steps to recreate

Install FMP12 on Mac OS 10.7.3 or Windows 7 set to UK system format (DD/MM/YYYY).

  1. Create a new database
  2. Add a date field (which it automatically sets to UK format)
  3. Add a few records with dates in format DD/MM/YYYY
  4. Create a value list, source - date field
  5. Assign the value list as a drop down on a test field
  6. Dates in drop down value list will be MM/DD/YYYY and will throw an error when selecting due to invalid date format.



Create two new fields: -

DateAsText = Calculation, result=Text, Formula: date (gives us the date in UK format, but is treated as text, not a date)

DateAsNumber = Calculation, result=Number, Formula: date (will return the day number since the year 0000 - we use this to sort the records in the right order)

Now edit your value list which references the date field and change it to reference the DateAsText field, and add secondary values from DateAsNumber, sort by second field, do NOT hide first field.

Now your drop down will show: -

  • 30/04/2012 734624
  • 07/05/2012 734630
  • etc...


Its fugly but it works and will just drop the actual date (in the correct format) in to the field.

FMP12v2 where are you? Any eta?