8 Replies Latest reply on Nov 22, 2011 6:43 AM by Beat

    Format of timestamp returned by list() and max()

    Beat

      Hi everybody

      In a script I'm using the two functions list() and max() on a timestamp field of a related table . This works fine locally. On the server it doesn't. After some debugging I found the problem: 'list' lists the timestamps in a local format whereas max returns the timestamp in the american date and time format. Is this a bug or are there any good reasons for this strange behavior? Is there a simple solution or is it necessary to walk through the list and 'manually' format all entries?

       

      Thanks for any suggestions.

       

      Beat

        • 1. Re: Format of timestamp returned by list() and max()
          Ramki

          How u are getting the timestamp? are u using Get(CurrentHostTimeStamp)?

          regards

          • 2. Re: Format of timestamp returned by list() and max()
            RayCologon

            Hi Beat,

             

            If I understand you correctly, the List( ) function is returning the timestamp values in the local format, and that is what you require, but Max( ) isn't respecting either the file or OS formats (you haven't said what they are) and is defaulting to US format for the date component.

             

            Without knowing what the embedded file formats are, or what the date formats for the computers you're seeing this behavior, it's hard to guess where the problem may lie, but I can say that I'm not seeing this behavior - ie using FMP 11.0v4 on a machine with local date and time settings (and in a file created on a local system) both List( ) and Max( ), when targeting a timestamp field return their results in the expected local date/time format.

             

            The first thing I suggest you do is check what setting you have in the Data Entry panel of the Text tab of the File Options dialog. Most likelty it is set to the default, which is "Always use current system settings" - in which case that may indicate that one of the systems you're using presently has US date/time formats configured at the system level. That being the case, you might try either 1) changing the Fle Options setting to "Always use file's saved settings", or 2) change the OS-level regional formats.

             

            Regardless, there should be no need to walk the list, as the List( ) function is already returning its results in localised format. At most, you may need to enclose the (single value) result of the Max( ) expression in some additional code to apply an explicit format. But hopefully that won't be needed and some invenstigation of the file and OS region settings may solve the issue for you.

             

            Regards,

            Ray

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

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            http://www.nightwingenterprises.com

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

            • 3. Re: Format of timestamp returned by list() and max()
              Beat

              Thank you, Ray, for this precise and detailed answer.

               

              Yes, the fields are originallly filled with get(CurrentHosttimeStamp) when some modification happens on different systems. Then a copy of the file is being saved (I have no idea about the systems this is running on). I get the copy by mail and import the records to our database. The problem shows up during this import procedure.

               

              The file has "Always use current system settings" set. On the local system where I have been testing the import script, I have for both date and time the Swiss(German) format set. On the server, I had first the US format set, then changed it to the Swiss format but with no effect. Locally it's correct, both functions returning the values in the expected format. On the server it is still mixed: List() retruns the values in the Swiss format, Max() in the US format.

               

              I'm not shure if it is sufficient to only control the Max() result as long as I don't know where the problem is coming from. It might happen, that with some records it's the other way round. Both functions are running on the same system, the same file, even the same field and same values. It's strange enough to have different results at all, no matter of all the different settings. It's no effort to supply some additional code to reformat the results, but it might become a matter of execution time sometimes.

               

              What would be the effect of changing to "Always use file's saved settings" when importing files from different systems and settings? - predictable?

               

              Thanks again.

              Beat

              • 4. Re: Format of timestamp returned by list() and max()
                CarlHenshall

                Which version of FileMaker server are you using?  There was a bug where server scripts always interpreted dates as US format no matter which of the settings Ray mentioned you used.  I believe (but have not tested) that the latest version fixes this bug.

                 

                Best regards

                Carl

                • 5. Re: Format of timestamp returned by list() and max()
                  Beat

                  We are using FileMaker Server 11.0.3.309 on Mac OS X 10.6.8. I'm not shure, if this is the last update.

                  Locally I am working on FileMaker Pro Advanced 11.0v2 on Mac OS X 10.6.8.

                   

                  Regards

                  Beat

                  • 6. Re: Format of timestamp returned by list() and max()
                    RayCologon

                    beat wrote:

                    What would be the effect of changing to "Always use file's saved settings" when importing files from different systems and settings? - predictable?

                     

                    Hi Beat,

                     

                    If the "Always use file's saved settings" option is enabled, then all inputs and file content are interpreted with respect to the regional settings that were in place at the OS level when the file was created or last cloned.

                     

                    In that respect, the behavior will be consistent (if that's what you mean by predictable) in the way it responds to incoming data from any given source, but it's no guarantee that data being brought across from applications or systems that operated in alternate or 'foreign' formats or modes will be represented ideally - ie without any requirement for adjustments.

                     

                    However, with respect to your current problem, adjusting the setting to see if it restores expected behavior in your current scenario is worth trying - and it's a simple matter to put it back the way it was if need be. However, given that I'm not able to reproduce the effect you're seeing when using a localised file on a localised (same locale) system, it seems there's a fair chance that it relates to a format conflict (an episode of 'lost in translation', if you will) between the file and the OS.

                     

                    If adjusting the File Options setting doesn't correct the problem, then it might be time to report the issue to FMI and then take a look at a more heavy handed (calc-based) solution for the short term.

                     

                    Regards,

                    Ray

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

                    R J Cologon, Ph.D.

                    FileMaker Certified Developer

                    Author, FileMaker Pro 10 Bible

                    NightWing Enterprises, Melbourne, Australia

                    http://www.nightwingenterprises.com

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

                    • 7. Re: Format of timestamp returned by list() and max()
                      databuzz

                      Hi Beat,

                       

                      There's definitely an issue that FMI have acknowledged which results in dates, when importing via a FileMaker Server Side script, having the wrong expected value regardless of the locale settings on the server. It appears that the local date format is ignored for server side scripts and it always uses the USA date format. There's a number of similar reports on the FMI forums including:

                       

                      http://forums.filemaker.com/posts/f31c78e9c8

                       

                      http://forums.filemaker.com/posts/3e527504c5

                       

                      http://forums.filemaker.com/posts/fa57eb65cd

                       

                      I've encountered this myself and had to work around it by separating out the date, month and year components before import then combine them together via calculation in the expected format post import.

                       

                      Hope this helps,

                      Andrew

                      • 8. Re: Format of timestamp returned by list() and max()
                        Beat

                        Hi Ray and Andrew

                         

                        thank you both for your answers.

                         

                        Ray, you say that the behavior is consistent as long as the data is entered from within Filemaker and not imported from another data source. I was just wondering what happens, when I'm gathering information entered on different systems with different settings (always originating from Filemaker, though) and importing them all into the same file. There might be some more or less dominant settings:).

                         

                        Anyway, I made some more tests. The setting "Always use file's saved settings" doesn't change anything. On the server, List() returns all dates in the expected format according to the settings whereas Max() allways returns the result in the US format whereas locally everything is ok.

                         

                        So I will move to some coded formatting now.

                         

                        Regards,

                        Beat