5 Replies Latest reply on Nov 24, 2008 2:36 PM by Orlando

    How to determine if the table contains records of set criteria?

    LeoCA

      Title

      How to determine if the table contains records of set criteria?

      Post

      Hi,

       

      I am new to FileMaker.  We are using FileMaker Pro 8.5.  I try to determine if records exist, such as:

       

         Select Count(*) from Table where date_field1 < SystemDate-7

       

      I tried create a layout and using Perform Find script, but it won't let me specify dynamic value for the WHERE criteria.

       

      Any idea?  Thanks for your help!

       

      LeoRA

        • 1. Re: How to determine if the table contains records of set criteria?
          Orlando
            

          Hi LeoRA

           

          Welcome to the forum. 

           

          If what you are trying to do is find all records where the date in date_field1 is older that seven days ago then you want to set the search criteria using the Enter Find Mode and Set Field steps, and not trying to specify the criteria in the Perform Find step, you need to create a script that does the following.

           

            #

            Set Error Capture [On] 

            # 

            Enter Find Mode [] // Make sure to deselect the Pause option here

            Set Field [TABLE::date_field1; "< " & Get ( CurrentDate ) - 7]

            Perform Find []

            # 

            If [Get ( FoundCount ) = 0]

                Show Custom Dialog [ "Message" ; "There were no records found." ]

            End If 

           

          This will perform a find for all records more than seven days old, and you will be left in a found set of those records. 

           

          I have also included the Set Error Capture step so you will not see an alert message popup asking to modify search or cancel, instead after the search there is a check and a message the user if no records were found.

           

          You can then perform any additional steps you require based on the result of the search.

           

          I hope this does the trick or points you in the right direction. 

          • 2. Re: How to determine if the table contains records of set criteria?
            LeoCA
              

            Thank you Orlando. Your suggestion works great for me.

             

            I hung when trying Set Field using > Get (CurrentDate) before.  Your right syntax does help me understanding better on FileMaker script syntax.

             

            I will follow up by a new question of  "How to use variables".  You can help me more from there.  Thanks again.

            • 3. Re: How to determine if the table contains records of set criteria?
              Orlando
                 Hi LeoCA, I am glad this worked for you.
              • 4. Re: How to determine if the table contains records of set criteria?
                LeoCA
                  

                Orlando,

                 

                How to I specify value in Set Field script to a Global Variable, such as $$UserRegion?

                Can I use Global Variable in IF script's condition? If yes, How?  I tried several ways and did not get it work.

                 

                BTW, my only source for finding script solution is HELP from menu.  Any other easy get access sources?

                 

                Thanks.

                 

                LeoCA

                • 5. Re: How to determine if the table contains records of set criteria?
                  Orlando
                    

                  Hi LeoCA

                   

                  You can set a Set Field script step to the value of a global Variable by simply putting the name of the variable as the Calculated result so using the variable you mentioned the set would look like this

                   

                    Set Field [TABLE::Field; $$UserRegion

                   

                  It would be the same in any calculation, just specify the variable by name. If you are having issues double check the variable is set and if so post and someone will be able to help.

                   

                  Book I always highly recommend are:

                   

                  FileMaker 9 Developer Reference: Great source of info of Calculation and Script functions with examples.

                   

                  FileMaker Pro 9 Bible: A must have, full of info to get you started and examples to follow.

                   

                  Other resources I use often are:

                   

                   

                   
                  Enjoy