5 Replies Latest reply on May 31, 2017 4:00 PM by erolst

    Find Records using a Script

    clayhendrix

      I need to run a script upon opening a layout to find records that meets certain criteria. I am having a brain block and cannot figure out this particular situation, although I have done this many times before.

       

      I need records found that have one of two values in a field and one value in another field.

       

      In the script, I have used the Perform Find command and then specified the fields and values in the Specify find requests dialogue box. But something is incorrect about as the records that I wish to be found are not the ones that I wish to be found.

       

      What is the best practice for Find in a script. Field A: value 1 or 2 AND Field B Value 3.

       

      I think I should have multiple command lines in the script because what I am doing is not working. If I perform the search manually on the layout, I find Field A: value 1 extend found set to Field A: value 2, restrain found set for Field B Value 3 and that works perfectly.

       

      I'm assuming that I should not try to perform a find of this complexity with one line in the script. I attached a screenshot from the script workplace so you might have a better idea of what I am trying to accomplish.

        • 1. Re: Find Records using a Script
          erolst

          I'd do it like so:

           

          Enter Find Mode

          Go to Layout [ Table ( Table ) ]

          Set Field [ Table::a ; 1 ]

          Set Field [ Table::b ; 3 ]

          Duplicate Record/Request

          Set Field [ Table::a ; 2 ]

          Set Error Capture [ on ]

          Perform Find [ restore: no ]

           

          Note the use of Enter Find Mode and Set Field.

           

          Makes the script longer, but the logic is clearer and can be understood without opening the Perform Find step.

           

          Also, if you are looking for contiguous values in a field, there is the range operator - which are three (or two) periods, rather than a dash/hyphen:

           

          Enter Find Mode

          Go to Layout [ Table ( Table ) ]

          Set Field [ Table::a ; 1 ... 2 ]

          Set Field [ Table::b ; 3 ]

          Set Error Capture [ on ]

          Perform Find [ restore: no ]

          • 2. Re: Find Records using a Script
            clayhendrix

            I do not see Extend Found Set and or Restrict Found Set as steps in your script. When creating the script, as you have, do you NOT need to use extend/restrict found set?

            • 3. Re: Find Records using a Script
              erolst

              It's "Constrain" - and while there are requests that can only be realized using it and the "Extend" step, this seems not to be one of them:

               

              a = 1 OR a = 2 AND b = 3

               

              is equal to

               

              ( a = 1 AND b = 3 ) OR ( a = 2 AND b = 3 )

               

              Each of the terms in parentheses corresponds with one query from the first step - where you have two queries because first one is created with "Enter Find Mode", the second via duplication (and adjustment).

               

              When you define a query, all the criteria from one request are evaluated as AND; if you need OR criteria,  create a new request.

               

              In the second script, the range is one field is the OR, and together both fields constitute the AND, so you only need a single query.

               

              Questions?

              • 4. Re: Find Records using a Script
                clayhendrix

                Also, I am unable to modify the script for ANOTHER situation.

                 

                Find Field 1 is value 1 or value 2 AND Field 2 is NOT value 3, value  4, or value 5.

                 

                The values are actually text, not numbers.

                • 5. Re: Find Records using a Script
                  erolst

                  clayhendrix wrote:

                  Find Field 1 is value 1 or value 2 AND Field 2 is NOT value 3, value 4, or value 5.

                  Stripped of all the paraphernalia and as pseudo-code, that would be

                   

                  Set Field 1 to 1

                  new request

                  Set field 1 to 2

                  new request

                  omit record

                  set field 2 to 3

                  duplicate request

                  set field 2 to 4

                  duplicate request

                  set field 2 to 5

                  perform find

                   

                  Omit Record in Find Mode means "Exclude records matching these criteria" -same as clicking Omit in a manual Find.

                   

                  clayhendrix wrote:

                  The values are actually text, not numbers.

                  There goes the range operator ... * otherwise the data type makes no difference to the logic.

                   

                  *You can use the range oprator with text, but in real life it is rarely needed.