Entering date in European format?

[comment]

The first rule of troubleshooting is isolate. You mention half a dozen of tests and experiments that enter MORE variables into the equation, instead of reducing it. There could be a problem with 10.4 - but we'll never know until you repeat EXACTLY what I have done in 10.3, and get different results.

So please, once again:

1. Set your system date format to yyyy-mm-dd;

2. Open Filemaker and create a NEW database. Define a date field.

3. Click into the date field and enter 2005-6-23. Click outside the field.

If you didn't get a message that the entry is invalid, proceed with the following steps:

4. Go to Select File -> File Options -> Text and select "Always use file's saved setting".

5. Close the file.

6. Change your system date format back to whatever you use normally.

7. Open the file and make sure "Use System Formats" under Format menu is NOT selected.

8. Create a new record. Click into the date field and enter 2005-6-23. Click outside the field.

The question is: will the file accept that as a valid date entry, or will you get a validation message? We will keep getting nowhere until we have an answer to that.

[PatriciaW]

Quite honestly I don't think you have ever specified this process (or else if you did it got lost in all the other messages) ... but I have followed your process and it does accept dates in the format I want.

So now the question is, why does it appear not to work for an existing database? or could it be that the format does not get changed for an existing database (or records).

I have to go out now but I'll do some more testing tomorrow to see if I can narrow it down further.

Thanks

[comment]

I did, perhaps not in the same level of detail.

Now that we have eliminated Tiger as a suspect, please do the following:

1. Set your system date format to yyyy-mm-dd.

2. Open Filemaker and your existing database. If asked, select "file's setting".

3. Go to File -> File Options -> Text and select "Always use file's saved setting". Make sure "Use System Formats" under Format menu is NOT selected.

4. Go to File -> Save a Copy As... At the bottom of the dialog box, select Type: clone (no records). Note the name of the file being saved.

5. Close your file.

6. Open the new, cloned, file. Once again, check the settings are the same as in step 3.

7. Close the new, cloned, file.

6. Change your system date format back to whatever you use normally.

8. Open the new, cloned, file and make sure "Use System Formats" under Format menu is NOT selected.

9. Create a new record. Click into a date field and enter 2005-6-23. Click outside the field. If the date is accepted, the war is won and we can all go home.

[PatriciaW]

Ok ... comment ... the battle is won but I'm not so sure about the war.

The battle is won because I can do this with a cloned copy ... but not otherwise. I have separated my UI and data so it isn't a major issue for me but 1) there is a documentation issue and 2) what would have happened had I had data in my file. I guess it isn't clear to me what caused the problem, because I followed your procedure without the clone option (which I was not familiar with) and it didn't seem to save the new format.

But for me it is academic and I really appreciate your help. I do have a workable solution and that will make my users happy.

Thanks a bunch.

[comment]

I am not sure what documentation has to do with this. If you had data in your file, you would have to import it into the clone. What will happen when the files in the actual solution get corrupted? You should always keep an empty clone of your file/s and have a script ready to import the data.

Cloning also compacts the file, purging it of its history. It's basically equivalent to creating a new, fresh, file. And only a new file inherits the system format - that's why it didn't work when you "followed your procedure without the clone option".

Anyway I'm glad we can finally cross this one off. Good luck with the rest of the war!

[PatriciaW]

Hi comment,

By documentation, I meant the online help. In the section that you referred to (Setting file options) there is no mention of cloning. And in the discussion I had read on cloning, there was no mention of the significance with respect to system formats etc. Mind you, even if there had been I might not have recognized the significane. In reviewing this thread, I see that from the first you mentioned cloning ... but I also talked of Saving and asked what was meant about saved file options ... so I was misreading your instructions. And since I have no data in my UI file, I did not see the significance of cloning.

Mea culpa. Thanks again, over and out.

[PatriciaW]

I know I concluded this thread a few days ago, but since then I discovered another anomalie. I was reloading data into the data files in this application. I had forgotten to examine the system date formats. I cloned the existing data files (previously I had been working with UI files (layouts only). After I imported data into the cloned files I noticed that the dates that were being displayed in the UI were incorrect. For example a date in the data file of 04/14/2001 was being displayed as June 30, 2009. There were various calculations for some of these fields that were working correctly before I did this cloning and picked up the format of yyyy-mm-dd. Eventually, I went back and re-cloned using the US date format and reimported the data. The dates were displayed correctly. Strange. It isn't what I expected.

[comment]

I would re-check all calculations involving dates to make sure they don't use any text parsing or concatenating routines, since these will automatically convert dates into text and vice versa. These conversions will use the current date format in force.

I trust you haven't used a text file as an itermediary to move data from one file to another?

[PatriciaW]

First the ones with the wrong dates: One calculation in particular is very complex. First it uses two other derived dates. They are calculated from a date field in a related table using the Min() and Max() functions. Then thse fields are used in a quite complex calculation using functions such as MonthName(), GetAsNumber(), Month().

But some date fields just showed up as "?" in the layouts. These fields had not been modified in any way. In one case the date format on the layout for one field was "December 25, 2003".

Once I restored the data files to use the correct date format all of these date fields displayed correctly.

[PatriciaW]

I did a further test. I saved a clone of a data file which contained dates. Before I did this, I set the system date format to yyyy-mm-dd. And the clone was set to use files saved settings.

I imported the data from a Merge file (which i had created from a file which was converted from FM6.)

When I browsed the data from the cloned file, the dates were displaying as "?" but when I clicked on them they showed dates in the MM/DD/YYYY format.

Do you think the problem is that I imported data from a Merge file created/saved in a different format?

[comment]

Yes, I do. If you open the .mer file, you will see that dates there are represented as MM/DD/YYYY.

A text file, such as .mer, is purely text data. "07/01/2005" is a meaningless text string, not necessarily date information. When you import text data into a date field, FMP tries to interpret the data according to the current date format in force for the importing file. If the importing file is set to DD/MM/YYYY, the string will be understood as January 1, 2005.

When you see a date displaying as "?", that is an indication of invalid date. What you see when you click in the field is NOT a date - it is the imported text string that FMP cannot translate to date.

[PatriciaW]

Do you know if there is an import option that will permit dates stored in a different format to be imported? And is there a way to determine which date format a database has been saved with?

[comment]

I am not sure I understand your questions. There's a big difference between importing from a Filemaker file vs. a text file. I believe (haven't tested this) that if you import from one FMP date field into another FMP date field, it doesn't matter which format any of the two files use. Importing from a text file is another story since, as I have explained above, you're importing text - and the importing file needs to interpret the text as date. A text date such as 01/07/05 is ambiguous - it could be Jan 7, 2005 or Jul 1, 1905, or Jul 5, 2001 etc.

I don't know of a 100% fool-proof method to determine which date format a database has been saved with.

In any case, if you know the format of the text file to be imported, you can switch your OS to use the same format, and switch the importing file to temporarily use system formats (in Browse mode).

[PatriciaW]

I'm probably confusing things myself ... here is my question from another perspective:

If I have a table in a database (in FM6) which uses the US date format and I want to move it to a database which uses a custom date format, how do I do that? I assumed that exporting from the first database in Merge format and then importing it into the second one would do that ... but it didn't. How should i have done it?

[comment]

There are several possible ways. I think the simplest one would be to convert the source file to FMP7 and import directly from the converted file. I don't think you need to worry about the formats being different in this scenario.

Another simple way would be to switch both files to use system formats, do the export to a text file, and import from the text file. It doesn't matter what the system format is, as long as it is the same one for both export and import, and both files are told to use it for the duration.

[PatriciaW]

I did not know that it was possible to import directly from a database. Somewhere along the line I got the impression that it was only possible via an export file. My mistake.

But your last statement confuses me, because I also got the impression (via earlier in this thread) that the only way to convert a database to a different format was via cloning which automatically deletes the data. Hence my use of export/import.

[PatriciaW]

I think I've got it ... I clone my database using the new system format, and then import directly from the uncloned version. I'm a bit slow sometimes.