4 Replies Latest reply on Aug 20, 2013 2:09 PM by philmodjunk

    perform "OR" logic in search for particular field



      perform "OR" logic in search for particular field


           I have a form which I use in find mode to search for particular sets of records. It has several fields and one of them is a checkbox set (3 checkboxes to choose).

           I need to perform search using OR logic while checking these checkboxes.

           E.g. I want to check first and third boxes and find all records which have first OR third options.

           For now when I check first and third boxes it looks for records that have first AND third options simultaneously.


        • 1. Re: perform "OR" logic in search for particular field

               I'm not an expert, but I would create a calculation field, using Cases, with each case defined by which of the boxes are "empty" or "not empty", returning text results that describe what you are looking for. eg "One and Five only". Be sure to select TEXT as the Calculation result type.

               Then make a value list of these returned values and assign it to the field in Field Format. Then search on that field.

               I do this a lot for date or progress status finds, based on which date fields have values (empty or not empty).

          • 2. Re: perform "OR" logic in search for particular field

                 Enter find mode

                 Click the first check box

                 Select "New Request" from the "Requests" menu

                 Click the third check box

                 Perform the find.

                 Entering criteria in different requests like this sets up your OR search. This can also be scripted. See the looping script near the end of this thread: Scripted Find Examples

                 Extend found set can also be used.

            • 3. Re: perform "OR" logic in search for particular field

                   Yes, thanks! I tried extend found set method and it seems to be working, however now it searches only by check boxes fields. And ignores parameters for search from the others fields. Can you please check my script?

                   I'm not too much familiar with coding, so I just little edited and inserted in my script the piece of code from Scripted Find Examples . Now it looks like this:


                        Set Error Capture [On]


                        Perform Find []






                        Set Variable [$rooms; Value:household:room_number]


                        Enter Find Mode []


                        Set Variable [$K; Value 1]


                        Set Field [household::room_number; GetValue ( $rooms ; 1)]




                           Set Variable [$K; Value: $K + 1]


                           Exit Loop If [$K > ValueCount ( $rooms ; $K )]


                           Extend Found Set []


                        End Loop




                        If [Get (LastError) > 0]


                           Modify Last Find


                           Show Custom Dialogue ["Error"; "No records found."]


                           Exit Script []


                        End If


                        New Window []


                        Go to Layout ["household" (household)]


                        ... next go new window parameters

                   Where "household" is my db, and "room_number" is my checkbox field

              • 4. Re: perform "OR" logic in search for particular field

                     To combine "and" criteria with "or" criteria, specify the "and" criteria each time you extend the found set along with the "or" criteria that is different each time.

                     And keep in mind that extend Found set adds the records found to those already in the found set so your first find has to use Perform FInd instead of extend found set or you use other steps to create a found set of zero records before you start extending the found set.