Date field format

[andrewkw]

I'm working on a file to be used with FMTouch on an iTouch. Since this app doesn't effectively let you choose a year when entering a date field that's set up as a calendar, I need to set it up as a regular text edit field.

This is all fine so far, but I also need to format the date as DD/MM/YYYY. I can do this manually under Format-->Date, but the program still only allows me to enter the date as MM/DD/YY. If I try to put in 23/12/2003, for example, I get an error message. If I enter 12/23/2003, it accepts and displays it as 23/12/2003.

This would still be OK, but I want to avoid confusion about entering values like 4/3/2003. If someone mistakenly enters March 4 as this (DD/MM), it will accept the value and display it as 3/4/2003, or April 3.

The database will be used in a country where DD/MM/YYYY is standard, so I'd prefer to only deal with that format. Thoughts?

[comment]

If you change your OS short date setting to DD/MM/YYYY, then clone the file, the clone will interpret entered dates as DD/MM/YYYY. Also check out the options under File > File Options… > Text > Data Entry.

[andrewkw]

Thanks for the advice.

I'd prefer not to have to change the system settings for good, so I've been playing around with this a little bit. Also, the file may eventually be used on other computers, so it wouldn't be great to have to go through and adjust everyone's settings before being able to use it properly.

Simply put, it looks like working through the File Options should do the trick if I turn off the custom date formatting. When I choose system settings, the field listens to the format I have saved for the OS (but this would require permanently changing my settings). When I tell it to listen to the file settings, however, it ALWAYS makes me enter it as MM/DD/YYYY (even when custom formatting is back on).

I've tried cloning (save a copy as?) the file, but this doesn't seem to make a difference in terms of the settings. Is there somewhere else I should be looking to find out where the File Options menu is finding the "file's saved settings"?

[comment]

I'd prefer not to have to change the system settings for good

You only need to change it for the duration of cloning the file - then you can change it back.

Leaving the file's native format as MM/DD/YYYY and setting file options to always use system setting should also work in most circumstances. However, knowing that the user's format is DD/MM/YYYY, I'd clone the file to their format before delivery.

Formatting the DISPLAY of a field on a layout has nothing to do with the issue.

[andrewkw]

You only need to change it for the duration of cloning the file - then you can change it back.

Leaving the file's native format as MM/DD/YYYY and setting file options to always use system setting should also work in most circumstances. However, knowing that the user's format is DD/MM/YYYY, I'd clone the file to their format before delivery.

Formatting the DISPLAY of a field on a layout has nothing to do with the issue.

To be clear, the information is being transcribed from a paper record where the format is always DD/MM/YYYY.

The electronic database will be used by 2 computers (including mine) and an iTouch, all of which have the American setting MM/DD/YYYY. So it's not that the user's computer format is necessarily different...it's the format of the data in general. If I tell the file to read from the system settings of the computer, it would always read the wrong format. I don't want to have to ask the other person to change their date formatting indefinitely.

If there isn't a way around this, I guess we just have to risk some mistakes, but I thought it couldn't hurt to ask. I can also just take the time to make individual fields for each component of the date...but by that time FMTouch will have probably fixed the year problem.

[comment]

The electronic database will be used by 2 computers (including mine) and an iTouch, all of which have the American setting MM/DD/YYYY.

OK, I understood differently from what you said earlier:

The database will be used in a country where DD/MM/YYYY is standard

But the answer is still the same, even more so:

If your computer is set to MM/DD/YYYY, you cannot enter dates in DD/MM/YYYY format directly into a Date field, unless:

1. The file's native format is DD/MM/YYYY

AND

2. The file is set to use its own saved settings.

The only other option is to enter the date into a Text field, and use a calculation or a script to convert it to MM/DD/YYYY.

[andrewkw]

If your computer is set to MM/DD/YYYY, you cannot enter dates in DD/MM/YYYY format directly into a Date field, unless:

1. The file's native format is DD/MM/YYYY

AND

2. The file is set to use its own saved settings.

The only other option is to enter the date into a Text field, and use a calculation or a script to convert it to MM/DD/YYYY.

Again, thanks so much for your help. Unless I'm doing something wrong, following the 2 steps you listed does not work.

To be clear, I changed my computer's settings to DD/MM/YYYY. Next, I saved a copy of the database. Finally, I opened the new copy and selected "always use file's saved settings."

I went to a field formatted as a date and set to display an edit box. When I enter "25/12/2009," I get the error message that it needs to be entered in MM/DD/YYYY. If I enter this before changing the file options, there's no error message, but the display changes when I change my computer settings back.

Is this process what you're thinking of when you say "cloning" or "native format"?

[comment]

By cloning I mean select Save a Copy As... from the File menu, then choose "clone (no records)" as the Type.

It's best to follow this order:

1. Close all files;

2. Change your OS to DD/MM/YYYY;

3. Open the original file;

4. Save a copy as clone;

5. Open the clone and set it to use saved settings;

6. Close all files;

7. Change your OS back to original.

[Squeaky Data]

Wouldn't it be great if there was a preference where you could actually see and change what the files date settings are without jumping through the: Set OS/Save Clone Mystery Dance!

Maybe FileMaker V17?

[andrewkw]

Thanks so much!

That seems to have done the trick. I also figured out that if you set the date fields to display as simple edit boxes rather than calendars, FMTouch gives the option of selecting a year--thus solving the original problem.

Still, it's great to know how to do this. Thanks so much for being patient and spelling it out...I'm relatively new to all this.

[ouioui]

By cloning I mean select Save a Copy As... from the File menu, then choose "clone (no records)" as the Type.

It's best to follow this order:

1. Close all files;

2. Change your OS to DD/MM/YYYY;

3. Open the original file;

4. Save a copy as clone;

5. Open the clone and set it to use saved settings;

6. Close all files;

7. Change your OS back to original.

I'm having a similar problem, but this didn't work for me. Unless I misunderstood the process. In my case, I want MonthName(Date) to return "janvier" in French instead of January in English. The only way I seem to be able to do this is if I change my Regional Settings (PC - WinXP) to French, then create a NEW d-base. But I can't seem to get an already existing d-base to switch to French even if I clone while my RegSettings are set to French and check off "Always use file's saved settings" in FileOptions.

To complicate things, ideally I'd like to use French dates (1 janvier) on some layouts and English (January 1) on others. Is this even possible?

Thanks for your help.

Louise

[comment]

I don't know - it works for me. The only thing I can think of is that you are saving a copy, not a clone.

ideally I'd like to use French dates (1 janvier) on some layouts and English (January 1) on others.

That's not possible, at least not using date formatting. But you can use a calculation field with a Text result to display the date.