5 Replies Latest reply on Jul 31, 2014 1:05 PM by philmodjunk

    Perform Find Script Help with Dates

    RyanSearle

      Title

      Perform Find Script Help with Dates

      Post

           Hello, 

           I am using FileMaker Pro 12.0v5. One field in my database is an "Expiring Lease" field where I enter a date using the calendar function. I would like to be able create a Perform Find Script that searches for all the leases that will expire in the next 60 days by looking at the "Expiring Lease" field and comparing it with "today's date". Any suggestions on how to set up this script? 

           Thank you, 

           Ryan 

        • 1. Re: Perform Find Script Help with Dates
          philmodjunk

               If you use a find script patterned after those shown here: Scripted Find Examples

               You can use:

               Set Field [YourTable::ExpiringLease ; "<" & Get ( CurrentDate ) + 60 ]

               as one of the set field script steps in that type of script.

          • 2. Re: Perform Find Script Help with Dates
            RyanSearle

                 Thank you PhilModJunk for your comment. I'm not sure if I am fully understanding. Doesn't the "Set Field" function actually change the value in the field. I want to "Preform Find" so all the leases expiring with 60 days are found. I am fairly new at this and apologize if I am not understanding correctly. 

                  

                 Thank you, 

                 Ryan 

            • 3. Re: Perform Find Script Help with Dates
              philmodjunk

                   Please look at the examples in the link. Set Field does not change the value of a field when the window is in find mode. The example scripts enter find mode, use set field steps to specify search criteria and then perform a find.

              • 4. Re: Perform Find Script Help with Dates
                RyanSearle

                     PhilModJunk, 

                     Thank you for your help with this. I am still having trouble. I am fairly new to FileMaker and can not get this find to work. The "Primary Lease Expiration" field is an Indexed field and not Global. Is there a way to click the "Find" link at the top and enter this formula in the "Primary Lease Expiration" field to find all entries that have expired or are expiring within 60 days? 

                     I've uploaded a photo to help you understand what I am seeing. 

                     Thanks! 

                     Ryan 

                • 5. Re: Perform Find Script Help with Dates
                  philmodjunk
                       

                            The "Primary Lease Expiration" field is an Indexed field and not Global.

                       And that is exactly how it should be defined. The scripted find examples use global fields as a way for user to enter find criteria while in Browse mode so that the scripts can take the data in the global fields and use them to generate find criteria with set fields steps. The fields into which the script specifies find criteria cannot be global. You don't need a global field here as the criteria is being calculated from the system clock's current date--no criteria is being entered by a user.

                       You cannot manually enter find mode and enter this formula. You have to create a script to do this.

                       The script would look like this and not use any global fields.

                       Enter Find Mode []  ---> Clear the "pause" check box
                       Set Field [YourTable::ExpiringLease ; "<" & Get ( CurrentDate ) + 60 ]
                       Set Error Capture [on] ----> keeps an error dialog from interrupting your script when no records are found
                       Perform Find []

                       This script would be created in the script editor opened from Manage | Scripts.