I understand that the date format used for entering dates is the US format. I have changed the date format for displaying date on a field (I thought) and all date fields changed to the same format ... as far as I could determine.

My main question is ... given that other countries use different date formats wouldn't it make sense to allow for other entry formats ... for example in the Preferences? Why should other users have to use complex calculations to do this ... or is there some plug-in or function that will do this?

I am afraid your basic premise is incorrect. Filemaker uses the operating system's date format to decide what is a valid date entry and how to interpret it. These setting are stored with the file when it is first created (or cloned later).

Select File -> File Options -> Text to see how contradictions between file's saved setting and the current operating system can be handled.

Read also this thread.

Thanks for correcting me, although I was just going by the documentation (and google searches) I had read. (I can't see any reference in FM's Help either.) I'm happy to hear that it is otherwise. I do have a question related to that. I'm have developing this application (actually enhancing it) on "OS X" and will be deploying it on "Windows XP". Will that affect this?

No. The only thing that matters is the operating system's date format - not which OS it is.

In general, if you set File Options to always use current OS formats, your file will be universally functional on any OS anywhere on the planet - unless you have written some explicit routines to translate text to dates and/or vice versa. Such routines can easily be made universal too, as I pointed out in the other thread.

---

Oh, and it's in Help > Contents > FileMaker Pro basics > Managing files > Opening files with foreign system formats

I do have some more questions.

I tried doing this and chose to Ask whenever settings were different. I changed my system settings to yyyy-mm-dd but even though I got prompted when I started up the application (and chose system settings), I got an error message when I attempted to enter data in European format.

I then changed the option to use System settings and that worked correctly.

Using saved settings did not work. so that raises the question: what is meant by file's saved settings? In my case, I have two separate files ... the UI and data. I have been making the changes in the UI and as I said the use current settings does appear to work, but the other two options do not ... at least in my testing.

As you will see in my other post, I cannot get the date settings to be saved with the file so that presents the problem that this customer only needs/wants to use European date formats for this application ... not necessarily with their entire system.

Thanks for pointing out the Help .. I just did a search on Date ...

Then you need to either create or clone the file while your system format is set to the "European date format" (I believe there are differences between the various European countries, so there really is no such thing as a European format, but it doesn't really matter here).

Then set File Options to always use the file's saved settings.

I still cannot get that to work ... I'm sure I am doing something wrong ... I tried to use the Format option "Use System Formats", and also set the Always use File's Saved Settings ... then I closed the file. The File Option was unchanged but the Use system formats was no longer checked, and I get error messages when I attempt to use the European format (actually I customized it to accept yyyy-mm-dd.

The only combo that I can get to work is to use the File Options to Use current system settings and also click on the Use System Formats in the Format menu (although that is not visible in Layout mode.)

I'm a bit confused ...

If you want to use system settings on OSX, you need to specify the short date format. That's the one Filemaker uses when you choose to use the system default. For some reason, when you select Canada in the international settings control panel, it adjusts the long date format correctly, but keeps the American style m/d/y for the short format. So, you have to set the short date format separately. After that, Filemaker should work correctly.

What I did was use the Customize option which adjusted the short date format ... it looks fine when it works but I cannot get it to save so that I can use it independently of the system settings.

I don't think I can add anything new to what I said before, but let me try and summarize it into a step-by-step:

1. Set your system date format to dd/mm/yy;

2. Uncheck the "Use System Formats" option under Format menu;

3. Save a clone of your file;

4. Open the clone, go to Select File -> File Options -> Text and select "Always use file's saved setting".

Now you will have a file that expects dates to be input as dd/mm/yy, regardless of the system's setting. If some users prefer to use their own system's format, they can always override this by checking the "Use System Formats" option under Format menu. Hopefully this makes it clear.

I've done all of this except that I am trying to use a format of yyyy-mm-dd. When I follow your instructions and then attempt to enter 2005-01-01 I get an error message that the date is in the wrong format. If I tab into the date field it displays existing dates as mm/yy/dd.

My question to you is: Have you been able to get this to work? and if so, what version of FM and OS?

You may be able to display dates as yyyy-mm-dd, but you cannot enter them that way unless you use a calculation (perhaps auto-enter) to convert the entered text into a valid FM date field format.

I think there is some confusion here over 'European format' also. This is, AFAICT, not what Comment sees as European format. It is merely SQL format. dd/mm/yyyy is European format.

Yes, I have - see attached (FMD 7.0v3, OS X 10.3.6).

ISOdate.fp7.zip

Hi,

I definitely see that this works ... but I can't see the difference. One thing that I do notice is that then entire Format menu is greyed out in Browse mode. The other difference is that you have specified the format as to be left as entered. I had entered a custom format. But when I change to leave date format as entered, it doesn't do that for me ... it always goes to the format I specified in my system preferences.

How did you define your date format in the System Preferences? (I am using 10.4 in case that makes any difference.)

Thanks for the example, I'll try to find out the difference.

1. Whatever you do in Layout mode, affects only the display - not data entry. You can format the display as "Wednesday, June 22, 2005", and still, when you click into the field it will show the data as entered.

2. My Format menu is not greyed out in Browse mode. All options, except "Use System Formats" are greyed out until you click into a field. "Use System Formats" will be greyed out when file formats are the same as system formats.

3. See attached pic.

The layout of the preference is different in Tiger, but I think the information is essentially the same. See attached layout.

I think one difference between us is that in Tiger, I don't see any standard short formats. I have to customize them. I'm not sure if that is the issue ... I haven't identified a pattern yet ... but that is my suspicion.

The image you've attached is too small to see anything (try command-option-shift-4 to grab a screenshot, then save as gif). By all means, customize the format, if that's what it takes.

Or use Grab [color:"blue"] Applications >> Utilities >> Grab

Lee

I rushed too fast ... I had created a tiff file (via Grab) but in my haste to convert it to a jpeg (when i discovered I couldn't attach a tiff) I did not notice I had reduced the file size so much that it was unreadable.

If you zip the file, you should not need to change its type.

Lee

That seems OK. Now, when you have it like that (2005-1-5) AND the file is set to use system's format, does it recognize entry of 2005-1-5 as Jan 5, 2005?

Yes ... it does ... but it does not seem capable of saving it with the file. But I am not sure what you mean bu recognizing 2003-1-5 as Jan 5, 2005. II have the format as yyyy/mm/dd and it displays like that, but when I click on it, it shows yyyy-mm-dd ... the same applies to any other FM date formats.

BTW, in a test, I set the OS X preference custom format to yyyy-mm-dd/ (trailing / deliberate) but when it was displayed in FileMaker it dropped the trailing / ... interesting but not a problem.

I am losing the focus of the problem. If you have entered 2005-1-5, you will see 2005-1-5 when you click into the field. That's normal and expected.

Can you do this step by step and pin-point where the problem starts? If you now have the file accepting 2005-1-5 as valid entry, clone the file, open the clone and set the clone to "Always use file's saved setting" under File Options (and uncheck "Use System Formats" under Format).

I just partially confirmed Patricia's problem. I was able to get a file to use the saved formats sometimes, but not always. In my test, it appeared that if the system date format matched the saved date format, then it would use the both the system date and time format even if the saved time format was different from the current system setting. If the system date format was different from the saved date format, then the file would use both the saved date and time format.

I set up my OS date and time format to yyyy-mm-dd and 24 hour time (no AM/PM indication) and saved a clone with the file option set to use the saved file formats. I then changed the OS date and time to mm/dd/yyyy and 12 hour time with AM/PM display. When I opened the saved clone, it did use the format saved with the file; both date entry and display was in yyyy-mm-dd format and time was 24 hour. But, here is the interesting part. I closed the file, changed the OS date format back to yyyy-mm-dd but left the system time in 12 hour format. When I re-opened the file, the time format was now 12 hour format. So, there is definitely a problem of some kind.

Tested with FM7.0v3 on OSX Panther.

<edit:

Regarding the trailing slash on the date:

You can't set trailing characters in the system *short* date format in the international settings control panel, and short date format appears to be what Filemaker uses, so that part, at least, would not be possible.

Have you tested this with two fields for date and time, or with a timestamp?

I always had the file accepting that as a valid format. (Reported that earlier in the thread although I completely understand the loss of focus ... I am having the same problem.)

But I didn't want that. I wanted "Always use file's saved settings." But if I follow your instructions and then change the system settings back to the US standard, then for data entry it reverts to US format.

I have attached my test file.

DateFormat.fp7.zip

Your file arrived using yyyy-mm-dd for date and 24-hour format as time. This sticks no matter what my system settings are. Some of the time entries include an AM suffix, which remains when I click into the field - indicating that it was entered this way (which shouldn't be necessary with 24-hrs format, but it is allowed).

I am unable to duplicate your result with a new file. I did exactly what you described and I am not getting the same result - once the file was set, it didn't change.

Here are some points which may be relevant:

1. When you clone a file, you have to open it for the formats to register;

2. If you format a time field to display "as entered" you may - under certain conditions - see the actual entry made, no matter what the system/file settings are;

3. The settings in File Options are concerned with DATA ENTRY - NOT DISPLAY (and I cannot stress this one enough);

4. A time field is more flexible concerning data entry than a date field. Even with 24-hours format, an entry of 10pm is accepted and recognized as 22:00. Heck, you can even enter 25 and it will be accepted and understood. And as I have already mentioned, if you leave the DISPLAY "as entered", you will see "10pm" and "25" as entries in the time field, even though the file is set to 24-hours.

In any case, time is not the problem here, and I feel this is only clouding the issue.

I think everyone is aware that this is strictly a data entry issue, since the display format is easily configured in the layout options.

I should also mention that, except for the last record, when I entered time and date into the fields, I used the command hyphen and command semicolon to enter the current time and date. The differences you see in the different records are because I changed the OS date/time formats between the different record entries. So, Filemaker obviously changed its format based on system settings rather than file settings.

So, Filemaker obviously changed its format based on system settings rather than file settings.

I wouldn't go as far as to conclude that. I can confirm one thing - when the file's saved date setting matches the current system's date setting - Filemaker considers that a match, although the time settings may be different. You don't even get the option to use the system format.

That may be a bug, or it may be on purpose (given the tolerance in time entry I mentioned earlier). In either case I cannot see how this would relate to the current problem. Although in fairness I should point out that by now I am not sure what the current problem is, or even if there is one...

I think this thread has gone on far too long ...

The problem is (as far as I am concerned - and I started the thread) that the users of the system I am working on want to be able to enter dates in yyyy-mm-dd format, but they do not want to have to set the system date format permanently to this as this would affect other applications,

All I would like to know is how to do this.

I agree (too long). I have posted a file that does it, and a step-by-step description how I made it. I cannot understand from you at which step you start getting different results from mine.

I thought I made it clear that when I specified a custom date format, it did not get saved with my application. I did try your application and while it worked initially, I was unable to resave a clone of it which saved changed date preferences.

Here's what I did:

I started a copy of your ISOdate program ..

I had previously saved it with always use current system settings. So when I looked at it, the dates were in m/d/yyyy format (my current setting.)

i went into the system preferences, and changed the short form date to yyyym/d (not very standard I agree but this was just a test.)

Then I changed the setting on the ISOdate to use saved settings, and saved it as a different file. When I then launched this copy it displayed the data as yyyy-m-d which which was how you originally sent it.

As far as I can see, FM does not attempt to save my settings with the clone. Now, perhaps I have misunderstood your instructions for creating a clone, and conceivably I have done things in the wrong order ... but I think this is some by-product of the fact that Apple has changed the way of defining custom dates in Tiger.

Anyway, in case I haven't made it clear, I appreciate very much your attempt to follow this thread and to assist me. It isn't a critical problem and it might not behave this way on the target platform (Windows XP), but I would really like to get it to work so I can test it out for the end users.

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.

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

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.

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.

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!

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.

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.

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?

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.

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?

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.

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?

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).

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?

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.