Date Format Issue in Report Script

[JLC]

Hi All,

I've just started having some trouble with date formats.

I start off in a Search Layout/Table where I enter a Start Date and an End Date into Global Fields from that Table.

From there I trigger the following script:

Go to Layout ["Table1" (Table1)]

Enter Find Mode []

Insert Calculated Result [select; Table1::Date; "Search::Start Date" & "..." & Search::End Date]

Perform Find []

This script has been working for over a year, and today I got the following error:

The value of this field must be a valid date in the range of years 1 to 4000 and should look like "2003/12/25".

Options given are "Revert Field" and "OK"

All of the date data in Table1 are formatted yyyy-mm-dd (I'm not talking about formatting a date field on a layout, but th actual data in the table).

Even though they are formatted this way the script inserts them into the Table1::Date field like this dd/mm/yyyy…dd/mm/yyyy

I'm on Mac OS 10.6.4 and the date format by default is yyyy-mm-dd (the same as the date data in the Table) which I checked because I read somewhere that affects the way filemaker handles dates. I'm running Filemaker Pro 11 Advanced.

So in summary:

Filemaker Error Message = yyyy/mm/dd

Database Date Data = yyyy-mm-dd

Insert Calculated Result = dd/mm/yyyy

Can any one help me with what is happening and how to fix it/protect against it in the future.

Thanks,

Jen

[comment]

Can you reproduce the problem in a new file? If yes, please post it here.

[JLC]

I've attached 2 files - "Original File" (account name "test", no password) is the solution I'm having problems with, stripped down to just the fields in question and as you will see when you hit the run script button, the error persists.

The "New Test" file (no account or password) is rebuilding just these fields from scratch and there is no error.

Unfortunately the original database is huge and I can't rebuild/rewire the whole thing.

thx

Jen

Can you reproduce the problem in a new file? If yes, please post it here.

Original File.fp7.zip

New Test.fp7.zip

[comment]

All of the date data in Table1 are formatted yyyy-mm-dd (I'm not talking about formatting a date field on a layout, but th actual data in the table).

I am afraid you are mistaken about this. This file was created under an OS whose short date format was dd/mm/yyyy. This format is a part of the "file's saved settings" - see File > File Options… > Text > Data Entry.

The file is set to 'Always use current system setting' so it is using yyyy/mm/dd when you open it on your system - except there is a bug with the Insert Calculated Result[] script step.

Short Fix:

Use Set Field[] instead of Insert Calculated Result[]. This is something you should do anyway, since Set Field[] works directly on the data level and does require the field to be on the layout.

Proper Fix:

Clone your file under your current OS and import the data from the existing file into the clone.

[JLC]

I am afraid you are mistaken about this. This file was created under an OS whose short date format was dd/mm/yyyy. This format is a part of the "file's saved settings" - see File > File Options… > Text > Data Entry.

The file is set to 'Always use current system setting' so it is using yyyy/mm/dd when you open it on your system - except there is a bug with the Insert Calculated Result[] script step.

Short Fix:

Use Set Field[] instead of Insert Calculated Result[]. This is something you should do anyway, since Set Field[] works directly on the data level and does require the field to be on the layout.

Proper Fix:

Clone your file under your current OS and import the data from the existing file into the clone.

Thanks for all your help. I just tried your suggestion on the demo file, and of course it works. However I'm not quite sure what the best practice for handling this in the future is. When I create a database that will be remotely accessed by various users / platforms / computers, what should the "file's saved settings" - see File > File Options… > Text > Data Entry be set to to avoid this type of problem?

[comment]

When I create a database that will be remotely accessed by various users / platforms / computers...

Well, then you have no choice but to set the file to 'Always use current system setting' (unless you want to force all your users to use a uniform date format). This works quite well with files sent back and forth between users, and I would assume sharing would be the same - though I have not tried it myself.

There are a few gotchas to watch for, such as the one you have encountered. Another one I happen to remember: when exporting, you should make sure the 'Apply current layout's data formatting...' option is checked, otherwise the data will be exported in the file's original format.

You can probably find more of these if you search these forums for "clone".

[JLC]

Thanks so much for all your help

Jen