8 Replies Latest reply on May 31, 2011 1:58 PM by margotjacqz

    Use of date fields and variables in finds

    PecCars

      Title

      Use of date fields and variables in finds

      Post

      1. I have a find that looks to omit empty records with a date field. If I use =, I get the error message that is not a valid date format. What is the correct criteria for omiting an empty date field ?

      2. The same find uses a comparison in another date field in order to filter records, I am using a date variable > $$ThisMonth and compare it against the date field ThisRecordsDate. I get also the same error message for an invalid date format. What is the correct way of comparing a date field with a date variable in a find dialog ?

      ScreenShotScript.JPG

        • 1. Re: Use of date fields and variables in finds
          philmodjunk

          1. I have a find that looks to omit empty records with a date field. If I use =, I get the error message that is not a valid date format. What is the correct criteria for omiting an empty date field ?

          Make sure that you first enter find mode. In find mode, you should not get that error message.

          2. The same find uses a comparison in another date field in order to filter records, I am using a date variable > $$ThisMonth and compare it against the date field ThisRecordsDate. I get also the same error message for an invalid date format. What is the correct way of comparing a date field with a date variable in a find dialog ?

          The same is true for a global variable.

          What do you mean by "find dialog"? In fileMaker you can simply enter find mode and enter criteria directly in to the fields of the layout. This is true for specifying criteria manually or when using a script.

          If you have a script for performing the find, you may need to post that script so that we can see it.

          • 2. Re: Use of date fields and variables in finds
            PecCars

            thanks Phil,

            on 1., here the screenshot

            on 2, here the script. What I called find dialog is the find mode. I have actually tried both ways: entering the criteria in find mode and doing it via a script. In both cases it doesn't work. Attached the script as well.

            The content of GB_GlobalVariables::ThisMonth = 01.03.2011.

            I suspected this had to to with the local settings of my computer but it seems it isn't either. Background: FM is running on Win XP. I have set the locale to German where a correct date is 01.03.2011 for today. I tried both with the German date format as well as with 03/01/2011 to no avail.

            However when I enter directly >=01.03.2011 in the search field in find mode it filters correctly the records.

            • 3. Re: Use of date fields and variables in finds
              philmodjunk

              I don't see any screenshots. You can click Edit on your first post to get to a screen where you can upload a screen shot. You can also upload screenshots to a file sharing site and post the download link in any message you post to this thread.

              Methods for posting screenshots here without having to type them in line by line.

              1. You can upload a screen shot by clicking the edit link to your original message and uploading it there
              2. You can upload a screen shot to a file share site and post the download link to a new response you post here.
              3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
              4. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.

               

              Here's the script I'd use to find records where a field is empty:

              Enter Find Mode[]
              Set Field [Table::possiblyblankfield ; "="]
              Set Error capture[on]
              Perform Find[]

              Here's the script I'd use to find records where a field of type date is > than a date in a variable:

              Enter Find Mode[]
              Set Field [Table::possiblyblankfield ; ">" & $Variable]
              Set Error capture[on]
              Perform Find[]

              • 4. Re: Use of date fields and variables in finds
                PecCars

                Phil,

                I have uploaded the screenshot with the different messages and I believe it is self explanatory. I see that in your example you are using Set Field. Now what happens if I want to combine to search fields and make use of Set Field ? As you see from my screenshot, the approach I have taken is to include the criteria in the Enter Find Mode and the Perform Find lines, which allows my to AND both search fields.

                • 5. Re: Use of date fields and variables in finds
                  philmodjunk

                  To specify criteria in more than one field, you just add more set field steps. I prefer using set field to specify the criteria as it produces a script where I can see what criteria is being used without having to open up the stored find in a separate dialog.

                  To avoid the error message shown in your screen shot, I think you'll need to use / instead of . as the delimitter between day, month and year in your date.

                  • 6. Re: Use of date fields and variables in finds
                    PecCars

                    I found the error: "/ instead of ." is not relevant. / is a date separator in American English and . is a date separator in German, both work correctly. The error is actually that the table where the current date is GB_GlobalVariables::ThisMonth is actually an unrelated table. So, Set Variable to that field prior to performing the find solves everything.

                    • 7. Re: Use of date fields and variables in finds
                      margotjacqz

                      Hope I can pick this up here as I am having the same or similar experience. Trying to create and save a Find including [<= a week from today]. Obviously easy to do individually but ... how to use a variable for a regular and more complex find request?  I'd thought using (current date + 7), or creating a global variable < gWeek = Get(CurrentDate)+7 > would work, but no. I am getting the non-valid message pictured above.

                      Phil, I understand the script approach, but it shouldn't be necessary. There are a couple of Saved Finds I would find very helpful to have, more easily modified than Scripts. Frustrated I can't use a date variable in a date field. Am I missing something?

                      • 8. Re: Use of date fields and variables in finds
                        margotjacqz

                        Expereimenting with a script but same thing, the perform find [] step runs into these same limitations in Specify Find Requests. The Find I am testing is two parts [ (StatusX AND Datevariable) OR (CategoryY AND DateVariable) ]