2 Replies Latest reply on Aug 16, 2017 9:02 AM by EV

    Scripting a Find Request with Both And and Or

    EV

      I want to script the following find request.

       

      Some background information:

       

      Each of my records all have a calculated most recent testing date. There is multiple testing dates for each record, and I wrote a script to determine the most recent one. I want to be able to create a script that will find records within a certain date range and if they have one of two values in another field.

       

      More information:

       

      Right now, to aid in the date range find, I have a layout where there are two date fields--one for either end of a range. I call them start date and end date. Users enter the start and end date of the range they want to search. Then, the trigger a script by clicking a button on the layout. However, I can't seem to mix the two operators.

       

      My script right now looks like this:

      Enter Find Mode

      Set Field [Most Recent Test Date; Start Date & "..." & End Date]

      New Record/Request

      Set Field [Table: Field; "Value A"]

      New Record/Request

      Set Field [Table: Field; "Value B"]

      Perform Find

       

      I know this is wrong. The records it finds are all sorts of screwy. It provides records that are not within the date range, however when I change the date range, the number of records it finds changes.

       

      I want it to find all the records that are within the date range (start-end dates) AND have either Value A or Value B in Field.

        • 1. Re: Scripting a Find Request with Both And and Or
          philmodjunk

          The first set field step will not work unless start date and end date have global storage specified.

           

          I think that you are trying to find all records with a date in the specified range and that have either value "A" or Value "B".

           

          IF that is correct, the key is to manage what criteria you put into each find request. (that's what New record/request creates when the window is in find mode.)

           

          For "And" criteria, you have to specify that criteria in each request. "or" criteria should be specified in just one of multiple requests.

           

          That means that there are two ways to script this possible:

          Enter Find Mode

          Set Field [Most Recent Test Date; Start Date & "..." & End Date]

          Set Field [Table: Field; "Value A"]

          New Record/Request

          Set Field [Most Recent Test Date; Start Date & "..." & End Date]

          Set Field [Table: Field; "Value B"]

          Set Error Capture [on]

          Perform Find

           

          or you can use Duplicate Record/Request. This can be helpful if you have a lot of different fields getting "and" criteria. When you duplicate the request, the criteria entered into the first request is now also entered into the duplicate, you just update the criteria for fields where it's "or" criteria. I am assuming that the Value A and Value B criteria are being entered into the same field of different requests here.

           

          That means that there are two ways to script this possible:

          Enter Find Mode

          Set Field [Most Recent Test Date; Start Date & "..." & End Date]

          Set Field [Table: Field; "Value A"]

          Duplicate Record/Request

          Set Field [Table: Field; "Value B"]

          Set Error Capture [on]

          Perform Find

           

          For more examples of scripted finds, you might read the first part of this discussion as it has a number of different script examples:

           

          Scripted Find Examples

          1 of 1 people found this helpful
          • 2. Re: Scripting a Find Request with Both And and Or
            EV

            This made so much sense once you explained it. I integrated your solution, and it worked perfectly. Thanks.