8 Replies Latest reply on Sep 2, 2016 2:11 PM by LaRettaK

    Merged fields/variables time format incorrect

    LaRettaK

      Product and version

      FileMaker versions 11 - 15

       

      OS and version

      Confirmed in El Capitan 10.11.6

       

      Description

      Time incorrectly adds decimal formatting when in same merge block as number formatted as decimal. 

       

      How to replicate

       

      1. Define a Number field and Time field, and populate them with some values, say 123.4 for the Number and 8:15:45 for the Time.

      2. Merge the two fields in the same text block (for example: "An amount of <<Numberfield>> is due at <<Timefield>> today.").

      3. Format the text block to display numbers as Currency or Decimal, Fixed number of decimals: [2], and to display times as [hhmmss].

       

      The result looks like this: "An amount of $123.40 is due at 8:15:45.00 today." with two decimal zeros appended to the merged time.  Then change the decimal number to 5 decimals.  The time changes to 8:15:45.00000.

       

      Workaround (if any)

      none

        • 1. Re: Merged fields/variables time format incorrect
          TSGal

          LaRettaK:

           

          Thank you for your post.

           

          I am able to replicate the issue.  Since Time fields are stored as numbers, I can understand why this issue occurs when seconds are displayed.  Regardless, I have sent your information to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

           

          As a workaround, create a Calculation (Text) field with the formula:    GetAsText ( <Time field> )

          You can then use the Calculation field in the merge field block.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Merged fields/variables time format incorrect
            LaRettaK

            Thank you for your prompt reply.  As I believe you realize, time should follow the time format as specified in Inspector so it should be corrected.

             

            Instead of creating a calculation, one might take advantage of *PLACEHOLDER text (single global text field from any table) and format the string within its calculation.  It is a shame to create a field ( adding to table width by increasing number of fields ) only to correct a format.

             

            Thanks again, TSGal.  I hope FMI fixes this issue.  :-)

             

            * will not work for printing, previewing or PDF.

            • 3. Re: Merged fields/variables time format incorrect
              LaRettaK

              TSGal said, "Since Time fields are stored as numbers, "

               

              As of when?  I do not believe this is correct.  Date fields aren't stored as number either (although they both should be, for sure). 

               

              It has nothing to do with this issue I've posted about, which is regarding time format, but I wanted to make it clear so that others reading this sentence would know.  :-)

              • 4. Re: Merged fields/variables time format incorrect
                TSGal

                LaRettaK:

                 

                For clarification, Date, Time, and Timestamp fields are indeed stored as numbers.

                 

                Date fields store the number of days beginning with 1/1/0001.  Time fields store the number of seconds since Midnight.  Timestamp fields store the number of seconds since Midnight 1/1/0001.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Merged fields/variables time format incorrect
                  LaRettaK

                  Sorry TSGal, you are incorrect. 

                   

                  It is true that FM calculates time according to number of seconds since midnight, as well as date since 1/1/0001 but that is not how it is stored in the file.

                   

                  If it were true that times, timestamps and dates were stored strictly by their numerical equivalent, there would be no need for FM to 'convert' them when it changes OS system settings.  When a file is not properly converted, we end up with broken dates etc.

                   

                  I really did not wish to muddy this thread - only mention that it was not accurate. 

                  • 6. Re: Merged fields/variables time format incorrect
                    Benjamin Fehr

                    To my experiences, it appears that FM stores a date not in a universal format. I know of a A::date = B::date relation breaking when system setting has changed?!

                    • 7. Re: Merged fields/variables time format incorrect
                      TSGal

                      LaRettaK:

                       

                      Testing and Development say this is the intended design as it allows control over the amount of fractional seconds to be displayed.

                       

                      TSGal

                      FileMaker, Inc.

                      • 8. Re: Merged fields/variables time format incorrect
                        LaRettaK

                        Hi TSGal,

                         

                        Wonderful - not a bad idea at all!  I really appreciate your prompt and courteous reply! 

                         

                        This bug, err feature exists clear back in version 11.  FMI sure must have planned ahead!  I wish FMI would have handled it differently, like in the Inspector's TIME format where it belongs.

                         

                        I wish it even worked.  ;-)  Maybe it just breaks on Mac?

                         

                        1. This is going to cause problems where merge fields and merge variables contain both number and time formats.  All time displays will inherit the decimals from the numbers within the same text block, whether we want it or not.
                        2. Even if FMI planned it that way (really?) it wasn't followed through since it only displays zeros even if there are fractional seconds in the field ( if number decimal is specified ).
                        3. This 'feature' breaks display of leading zeros on the seconds.  If we request a display of:  8:45:09 AM ... we get 8:45:9 AM ( if number decimal is specified ).

                         

                        Moreover, we should not be required to select NUMBER decimal options to control fractional seconds.  All of the TIME options should exist within Inspector's TIME format options.   This is a bug broken feature which has remained broken since 2010 (or before? ) and it needs to be fixed.  Don't you agree? 

                         

                        Also to clarify ... this breaks on regular time fields as well, if we have fractional seconds and wish to display 4 decimals and select the number format to 4 decimals ... we still get zeros when displayed.