6 Replies Latest reply on Aug 14, 2013 2:18 PM by ToddGetz

    Script to find records older than 30 days

    ToddGetz

      Title

      Script to find records older than 30 days

      Post

           I am trying to write a script to perform a quick find for all records 30 days or older from the date of creation, and exclude those marked as previously found.  These are my fields:

           DatePulled = date the record is created
           Destroyed = previously found, is a checkbox where checked is equal to 1

           I tried to first right the find records older or equal to 30 days as:  
           DatePulled ≤ (Get ( CurrentDate )-30)

           I just cannot get this to work. Until this works I cannot even begin to figure out how to exclude the records with the checkbox marked.

        • 1. Re: Script to find records older than 30 days
          philmodjunk

               Why use quick find? quick find searches across multiple fields in each record and this seems like a find where you would just search against the dates in a single field.

               Try this script:

               Enter FInd Mode[] --> Clear the pause check box
               Set FIeld [YourTable::DatePulled ; "<" & Get ( CurrentDate ) - 30]
               New Record/Request
               Set FIeld [YourTable::Destroyed ; 1 ]
               Omit Records
               Set Error Capture [on]
               Perform Find []

               For more examples of scripted finds, see this thread: Scripted Find Examples

          • 2. Re: Script to find records older than 30 days
            ToddGetz

                 I cannot get the set field parts to work.  I get An operator (e.g. +,-,*,...) is expected here, when I try to enter the calculation.

                  

                 FYI this is Filemaker Pro 12.0v3

            • 3. Re: Script to find records older than 30 days
              philmodjunk

                   When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: Script to find records older than 30 days
                ToddGetz

                     Thanks for that.  I was going in circles.

                      

                     One last puzzel for this setup.  If I want the checkbox to trigger the current date to be filled into another field what would the calculation for that look like.

                     the checkbox field is called Destroyed

                     the date field I want auto entered when the box is checked is DestroyedDate

                • 5. Re: Script to find records older than 30 days
                  philmodjunk

                       As an auto-enter calculation and assuming that the check box field is of type number:

                       If ( Destroyed ; Get ( CurrentDate ) ; self )

                       And you'll probably want to clear the "do not replace existing value..." check box, but you can experiment with it set either way to figure out the best option for yourself.

                  • 6. Re: Script to find records older than 30 days
                    ToddGetz

                         Thanks so much.  You have been excellent.