5 Replies Latest reply on Mar 9, 2017 9:37 AM by beverly

    Find Year Script

    rtolliver

      I'm trying to script a find for the previous year.

       

      Here's what I have so far;

       

      Go to Layout[ "Purchases" ( Purchase ) ]

      Enter Find Mode [ ]

      Set field [Purchase::PurchaseYr ; Purchase::PreviousPurchaseYr ]

      Perform Find [ ]

       

      PurchaseYr, is a Date filed. /Puchase table

      PreviousPurchaseYr, is a Calculation field "Year( Get( CurrentDate) ) -1   /Puchase table

       

      When I run this script I get an error message; "The provided find criteria is not valid. Enter a valid request before proceeding."

        • 1. Re: Find Year Script
          coherentkris

          you get an error because when in find mode all fields are empty until you put something in them. try set var to the field that has the value that you want to find THEN go into find mode, set field to the variable, and perform find.

           

          Go to Layout[ "Purchases" ( Purchase ) ]

          set var $criteria = Purchase::PreviousPurchaseYr

          Enter Find Mode [ ]

          Set field [Purchase::PurchaseYr ; $criteria ]

          Perform Find [ ]

          • 2. Re: Find Year Script
            philmodjunk

            Take a layout with fields and data visible and enter find mode. Notice how all the data in the fields disappear unless the data is in a global field?

             

            At the time your set field step executes, the Purchase::PreviousPurchaseYr field will be empty due to the window being in find mode. Set a variable to this field's value first, then enter find mode and then refer to the variable in your set field step instead of the field.

            • 3. Re: Find Year Script
              beverly

              good advice on the variable set before any switching from where you are! I might just use this:

              Set Variable ( $prevYr ; "//" & Year(Purchase::PurchaseYr) - 1)

              Then just use that. No need to have the calculation field (?) "Purchase::PreviousPurchasYr".

               

              the "*" is the 'anything' wildcard, so:

              //2016

              finds all from last year

               

              The reason to be flexible on the variable "calc" in the find, is that you might want 2 years ago, or just this year, for example.

               

              beverly

              • 4. Re: Find Year Script
                philmodjunk

                You don't need the wild card.

                 

                If you enter 2017 as find criteria in a field of type date, it will find all records with a 2017 date. FileMaker basically adds the wild cards for you.

                 

                You can also enter 3/2017 to find all records in March 2017

                • 5. Re: Find Year Script
                  beverly

                  I'd never tried that in a scripted find request!