Entering date in European format?

[PatriciaW]

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?

[comment]

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.

[PatriciaW]

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?

[comment]

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

[PatriciaW]

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.

[PatriciaW]

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

[comment]

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.

[PatriciaW]

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

[BobWeaver]

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.

[PatriciaW]

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.

[comment]

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.

[PatriciaW]

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?

[-Queue-]

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.

[comment]

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

ISOdate.fp7.zip

[PatriciaW]

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.

[comment]

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.

[PatriciaW]

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

[PatriciaW]

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.

[comment]

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.

[Lee Smith]

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

Lee

[PatriciaW]

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.

[Lee Smith]

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

Lee

[comment]

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?

[PatriciaW]

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.

[comment]

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

[BobWeaver]

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.

[comment]

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

[PatriciaW]

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.

[BobWeaver]

I have attached my test file.

DateFormat.fp7.zip

[comment]

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.

[BobWeaver]

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.

[comment]

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

[PatriciaW]

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.

[comment]

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.

[PatriciaW]

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.