10 Replies Latest reply on Feb 16, 2012 10:47 AM by AndresLP

    American date format to European ... mystery!

    AndresLP

      Hi,

       

      Not sure if I´m missing something obvious but I´m going crazy ... please see attached screenshot of Data Viewer (first two lines).

       

      Line 1 Data Viewer: I have a global date field where I enter a date. If I enter today's date in the global, it shows as "1/27/2012" (month first, day after).

      Line 2 Data Viewer: But if I enter "≤" & Global it shows as "≤27/01/2012" (day first, month after).

       

      Anybody has a clue of what´s going on?

       

      Thanks in advance!

       

      Andrés

        • 1. Re: American date format to European ... mystery!
          RayCologon

          Hi Andrés,

           

          When a database file is created under one set of regional settings and then opened on a workstation with different settings, there is a difference between how the raw data is represented, and how it's returned as text - eg when referenced in a calculation.

           

          I suspect that the anomaly you're seeing in the data viewer is an instance of this, although I haven't encountered that specific circumstance previously (though I've seen other instances of what I'd consider to be the same phenomenon).

           

          If it's a problem for you, you may need to either change the setting in File Options (Text tab) that controls the behavior when the file and regional settings differ, or create a clone of the file and open it on a system with the intended regional settings, then reimport the data into the clone (the format settings at file level in the clone will then conform to the regional settings).

           

          Regards,

          Ray

          ------------------------------------------------

          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia

          http://www.nightwingenterprises.com

          ------------------------------------------------

          1 of 1 people found this helpful
          • 2. Re: American date format to European ... mystery!
            AndresLP

            Hi Ray,

             

            Thank you first of all for replying to my message and for all your impressive contributions to this community for so many years!

             

            About this issue, I´m using the separation model, and tested it changing both file´s setting first to "Always use current system settings" and afterwards to "Always use file´s saving settings". I get the same result in the Data Viewer with both configurations.

             

            We are two developers working on this file, I use the US Mac OX opertating system and the other one uses Windows with settings from Spain. The file´s settings are most likely "European", but this is the first time I notice this issue.

             

            I even used FMDiff to check if the file is somehow corrupted, but didn´t show any warning. I´m lost ...

             

            Thanks again,

             

            Andrés

            • 3. Re: American date format to European ... mystery!

              Hi Ray,

               

              This is a frequently asked question!

               

              I've set up a web site especially on that subject some time ago:

              <http://fmdiff.com/fm/locale.html>

               

               

              Winfried

              1 of 1 people found this helpful
              • 4. Re: American date format to European ... mystery!
                AndresLP

                Hi Windried,

                 

                Thank you for your insight, as you can see I bought FMDIff, thank you for such a great product!

                 

                I had a look at your website, thank you. So does this mean that the scripts of a file created with European settings that involve dates won´t work if the file is opened from a computer with US settings??

                 

                Thanks,

                 

                Andrés

                • 5. Re: American date format to European ... mystery!
                  RayCologon

                  alopas wrote:

                  ...I even used FMDiff to check if the file is somehow corrupted, but didn´t show any warning. I´m lost ...

                   

                  Hi Andrés,

                   

                  I understand your concern, but believe it's unfounded. That is, I believe the issue relates to handling of dates when the system format and file embedded formats don't match, and is unlikely to result from any form of corruption. Your check using FMDiff adds further weight to this view.

                   

                  To further allay your fears, I have just confirmed that I can reproduce your initial issue with a new file created on a system that is configured for dd/mm/yyyy date format, and then opened on a system configured for mm/dd/yyy. Ie the Data Viewer returns the date in the current system (mm/dd/yyy) format when the field is referenced directly, but when returned as text as part of a compound expression ("≥" & DateField) it's returned in the file's original format.

                   

                  In my test, changing the FIle Options>Text>Data Entry setting to "Always use file's saved settings" causes the Data Viewer to bring the two values into line (ie both are returned as dd/mm/yyyy).

                   

                  In your case, bear in mind that you may need to change the File Options setting for either or both files (depending on how you're referencing the field) in order to see a change in the Data Viewer.

                   

                  Also, if you're going to be making ongoing use of the file with different formats, creating a new version (via the cloning procedure mentioned above) may be worth considering.

                   

                  Regards,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 6. Re: American date format to European ... mystery!
                    RayCologon

                    Winfried Huslik wrote:

                     

                    Hi Ray,

                     

                    This is a frequently asked question!

                     

                    I've set up a web site especially on that subject some time ago:

                    <http://fmdiff.com/fm/locale.html>

                     

                     

                    Winfried

                     

                    Hi Winfried,

                     

                    Thanks for chiming in - and for adding a link to the page on your site on this subject.  :)

                     

                    All the best,

                    Ray

                    ------------------------------------------------

                    R J Cologon, Ph.D.

                    FileMaker Certified Developer

                    Author, FileMaker Pro 10 Bible

                    NightWing Enterprises, Melbourne, Australia

                    http://www.nightwingenterprises.com

                    ------------------------------------------------

                    • 7. Re: American date format to European ... mystery!
                      comment

                      alopas wrote:

                       

                      tested it changing both file´s setting first to "Always use current system settings" and afterwards to "Always use file´s saving settings". I get the same result in the Data Viewer with both configurations.

                       

                      This is a bug in the way 'Always use current system settings' works: you will see the same thing if you enter GetAsText ( YourDate ) - the date will be shown using the file's saved settings.

                       

                      The bug is mostly harmless as long as you avoid using text functions on dates (which is bad practice anyway).

                      • 8. Re: American date format to European ... mystery!
                        AndresLP

                        OK I was just about to go crazy but I solved the mystery: if you save a clone of a file that doesn't have any tables, the clone will keep the system settings of the computer where it was created (the first time).

                         

                        That is, saving a clone won't get the file to change the system settings when that file doesn't have any tables.

                         

                        The problem I was having is I'm using the separation model, one file holds the data, and other one just the interface (it doesn't have any tables). I passed both files to a collegue to save a clone in order to get the same system settings for both files, and he returned them to me. I was assuming that they were both in European format after he saved the clones, but still didn't work well.

                         

                        After doing some tests I noticed this weird situation: the same exact script would work in the data file, but it didn't in the interface file. What this script basically does is entering find mode and performing a search for "≤" &  Global date (global field). This was the result:

                         

                        Global Date= 1/31/20012 (entered with the calendar pop up with American system settings)

                         

                        Data file: correct execution of script (European system settings)

                        Interface file: script gave an error trying to search for "≤31/1/20012" (American system settings, since 31 cannot be taken as a month)

                         

                        So the only way to fix this was creating a table in the interface file, with a date field (I didn't try by just creating a table with no fields). I passed the file to my collegue, he saved a clone, and FINALLY the interface file got the European system settings.

                         

                        Conclussion: by saving a clone the file won't get the systems settings if the file doesn't have any tables. In other words, if the file has no tables, saving a clone will keep the system settings of the computer where it was created originally.

                        • 9. Re: American date format to European ... mystery!
                          comment

                          alopas wrote:

                           

                           

                          Conclussion: by saving a clone the file won't get the systems settings if the file doesn't have any tables. In other words, if the file has no tables, saving a clone will keep the system settings of the computer where it was created originally.

                           

                          A clone inherits the settings of the system under which it is first opened. It doesn't matter if the file has tables or doesn't.

                          • 10. Re: American date format to European ... mystery!
                            AndresLP

                            You are right Michael, my colegue saved the clone but didn't open it ... doh!