4 Replies Latest reply on Feb 10, 2010 1:15 PM by TSGal

    GetAsText(date) BUG



      GetAsText(date) BUG

      Description of the issue

      FMP 9 or FMP 10GetAsText(date) returns the date in the format when the file was created. It disregards the filesettings->Text-> "use system date format" How to reproduce:Close FMP-Set PC region date format to USA  (M/D/Y)- Open  FMP- Create new DB, add field1 of type date, add field2 as a calculated field "GetAsText(field1)"           Any date (M/D/Y) entered into field 1 will be displayed in field 2 (M/D/Y) using the calculation.-Set File Options->text->Use system settings so the file will use the PC date settings.-  Close FMP- Change PC region to be a country with (D/M/Y) , ie Australia- Open FMP, Open File- Enter a date into field1 (this time with order D/M/Y) It enters fine as D/M/Y, ie 20/1/2010, which would throw an error if the date region setting was wrong.Notice that field 2 shows the date as (M/D/Y)  This is NOT the same as some FMP functions that always return in the order M/D/Y. If you make a new file when the PC settings are D/M/Y the calculation will return D/M/Y It seems that GetAsText  uses the date format at file creation only and ignores the date settings in FileMaker. This is a problem since we use this function to make complex date range find requests.   

        • 1. Re: GetAsText(date) BUG

          I have managed to reproduce this using FMPA 10.0v3 under OS X 10.5.8. The strange thing is that the same formula produces the correct result when used in Data Viewer.


          KSP wrote:

          This is a problem since we use this function to make complex date range find requests. 

          Regardless of the bug, I'd advise you to always use Date functions for date calculations, and reserve the GetasText() function for those cases where you actually need a text result.

          • 2. Re: GetAsText(date) BUG

            I'm learned some tricks in FMP6 and keep using them - maybe there is a better way and will eliminate this bug...


            To find a date range from a script I do the script step in find mode of: "insert calculated result -GetAsText(Invoices_::G Start Date)&"..."&GetAsText(Invoices_::G Stop Date)"into the date field I want to preform the find on. At least n FMP6 I had to convert the date to text otherwise it wouldn't work since I am putting a text string into a date field.  I just kept doing it this way in newer version of FMP.


            So what is suggested in FMP10 for finding a date range in a script step? Is there a more modern way?




            • 3. Re: GetAsText(date) BUG

              You can use Set Field[] instead of Insert Calculated Result[], and you can set the searched field to =


              Invoices_::G Start Date & ".." & Invoices_::G Stop Date

              • 4. Re: GetAsText(date) BUG



                Thank you for your posts.


                This problem has been previously reported, and I have attached your post to the original case.


                The calculation provided by "comment" (Thank you!) should give you the desired results.



                FileMaker, Inc.