5 Replies Latest reply on Sep 2, 2014 4:19 PM by ytrejbal

    Scripted find on date field using the format DD/MM/YYYY

    ytrejbal

      Summary

      Scripted find on date field using the format DD/MM/YYYY

      Product

      FileMaker Pro

      Version

      13.0v3.0

      Operating system version

      OSX 10.9.4

      Description of the issue

      Trying to perform a find on a date field using the following script steps:

      Enter Find Mode []
      Set Field [Tablename::Datefield; "28/03/2013"]
      Perform Find []


      I have also tried to use

      Set Field [Tablename::Datefield; GetAsDate("28/03/2013")]

      And it returned the dame error as described below.


      This error is also occurring in FileMaker 12.0v5

      Steps to reproduce the problem

      To recreate the issue create a script with the lines about and a table with an indexed date field. Populate the table with a number of records with varying date ranges but make sure that some have the date 28/03/2013

      Expected result

      Return found set of records with date 28/03/2013

      Actual result

      Error 500 on setting of date field to 28/03/2013 and an error message asking to provide valid criteria.

      Exact text of any error message(s) that appear

      The provided find criteria is not valid. ether a valid request before proceeding.

      Configuration information

      FielMaker is installed on a Apple MacBook Pro running OSX 10.9.4.
      Data Entry setting is set to "Always use current system settings".
      Date field is indexed

      Mac OSX Language & Region setting is:
      Region Australia,
      First day of Week Sunday
      Calendar Gregorian
      Date format is set to DD MM YYYY

      Workaround

      The work around is to use American date formatting. But our business works on Australian/European date formatting. So this is not a solution for us going forward.

        • 1. Re: Scripted find on date field using the format DD/MM/YYYY
          Fred(CH)

          Hi Yvan,

          Assuming you already checked your system preferences formats...

          This might occur if the system settings used when the file was created were different as your actual system settings.

          You can set your file behavior in this case on File menu > File Options… and click Text tab.

          More info here.

          Bye, Fred

          • 2. Re: Scripted find on date field using the format DD/MM/YYYY
            ytrejbal

            Hi Fred,

            Yep I have already checked that. If I type 28/03/2013 manually into the field when in find mode it returns all records with that date. If I script it as above I get an error 500. When I step through the script in script debugger I get an error 500 when I get to the script step Set Field [Tablename::Datefield; "28/03/2013"]. So I am assuming that there is a problem with using European date formatting when performing a find and setting a date field with the european date format (DD/MM/YYYY). 

            Thanks for your speedy response.

            • 3. Re: Scripted find on date field using the format DD/MM/YYYY
              Fred(CH)
              Sorry Yvan, i red your post too speedy indeed blush.
               
              And I am now definitely able to replicate. The steps i took :
               
                     
              1. Restarted my MacBook Pro (10.9.4) after changing system preferences as US, so MM/DD/YY date formatting
              2.      
              3. Created a brand new file with a date field, formatted "as entered" on my layout.
              4.      
              5. Entered some record with dates including 03/28/2013
              6.      
              7. Restarted my MacBook Pro (10.9.4) after changing system preferences as Australian, so DD/MM/YY date formatting
              8.      
              9. Created and performed your script

               

              Even the dates were immediately displayed as expected (DD/MM/YY) and a regular, manual search worked, the script get the 500 error and, of course, failed.
               
              If i then create a clone of the file, open the clone and import all datas from the original file, the script is now working on the brand new file.
               
              Bon courage,

              Fred

              • 4. Re: Scripted find on date field using the format DD/MM/YYYY
                Fred(CH)

                As an additional note, if i opened the "U.S. native file" in Australia the 28 March 2013, the Get (CurrentDate) fonction returned "28/03/2013" on Data viewer, although GetAsDate ( "28/03/2013" ) returned "?".

                Thus, if i used this function Get (CurrentDate) on the Set Field step, instead of GetAsDate ( "28/03/2013" ) the script worked correctly.

                Please note I did all theses testings locally, without any sharing. Even if I can perfectly understand the actual behavior, since a file  (and its scripts!) can be accessed by different clients with different regional settings, my conclusion is : Sometimes, FileMaker is incredibly clever, whereas other times, he's just an honest citizen wink.

                Fred

                 

                • 5. Re: Scripted find on date field using the format DD/MM/YYYY
                  ytrejbal

                  Hi Fred,

                  Thanks for your speedy reply. You are 100% correct. Our problem was with our interface file being created on a system with a different date format. Cloning the interface file has fixed the issue. Luckily we use the data separation model and all our data files were created with Australian formatting. I am very happy that we don't have to go down the path of importing all our data into clone files, that would have been a really big pain. Thanks Fred!

                  Yvan