6 Replies Latest reply on Jul 25, 2015 11:56 AM by philmodjunk

    3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.

    Kaorin

      Title

      3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.

      Post

      Apologise if my title didn't make any sense.

      The aim is to create script(s) which will find all the values ticked from 3 different check boxes.

      Let’s say I want to find the records matching to the following by clicking the values in check boxes:

      Vegetable” and “Fruits” from Produce category and

       “UK” and “France” of origins and

      Fresh” and “Frozen from State of the products.

      to show the record(s)match to above criteria.

      However, I may only want to tick just 1 value from 1 checkbox, to just find "apples" only from Produce category.

       

      These are the fields I have so far:

      GROCERIES::Produce (apple, peas, pear, basil, potato, etc…)

      GROCERIES:: Type (Fruits, Vegetable or Herbs)

      GROCERIES::Origin (UK, France, Spain, etc..)

      GROCERIES::State (Fresh, Frozen or Dried)

       

      I also created 3 global fields, which are the checkboxes listing all the values from the appropriate fields:

       

      GROCERIES::g_finf_produce,              all values from  the field GROCERIES::Produce

      GROCERIES::g_find_fuits,                    all values from GROCERIES:: Type

      GROCERIES::g_find_origin,                  all values from GROCERIES::Origin

       

      How do I create a script that finds everything that I tick from each of the 3 check boxes and which field should I attach the script to?

      Should I make 3 different scripts and apply to each global fields or should I make just 1 script and apply to all 3 global fields?

       

      Thanks.

      Screen_Shot_2015-07-03_at_17.56.58.png

        • 1. Re: 3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.
          philmodjunk

          An OnObjectModify script trigger can be put on all three global fields to run the same script. That script will need a series of nested loops in order to correctly find the desired records due to the combination of "or" and "and" search logic implied by this format. Here's a rough example of how that script would work:

          Say you selected, Fresh, Frozen, vegetables, herbs, UK, France

          Your looping script would create:

          1 Find request specifying Fresh, vegetables, UK
          1 FR specifying Fresh, vegetables, France
          Fresh, Herbs, UK
          Fresh, Herbs, France
          Frozen, vegetables, UK
          Frozen, vegetables, France
          Frozen, herbs, UK
          Frozen, herbs, France

          That's 8 requests for a single find.

          You can find some bits and pieces of such scripted methods in the scripted find examples found here: Scripted Find Examples

          • 2. Re: 3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.
            Kaorin

            Thanks Phill for the links.

            I created my own versions (attached the screenshot) as a test.

            The one on Right is attached to global field Groceries::g_find_Type (checkbox) and it finds everything that gets a tick as I tick the boxes. The Left one is attached to the global Groceries::g_find_Origin and finds as above.

            I combined both of above to create 1 script (the very bottom screenshot) to find matching values from Groceries::g_find_Type and global Groceries::g_find_Origin.

            Unfortunately, this script only finds 1 value from each global field. (am I making sense?)

            Basically when I tick in the following order:

            “Herb” and then

            “Fruits”

            and in the following order was ticked:

            “UK”  and then

            “SPAIN”

            the script only finds the fist values only, meaning “Herb” and “UK”, rather than all as below:

            “Herb” “UK”

            “Herb” “Spain”

            “Fruits” “UK”

            :Fruits” “SPAIN”

             

            Where should I make an adjustment on my script?

            Thanks.

            • 3. Re: 3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.
              Kaorin
              /files/71913c6a58/Screen_Shot_2015-07-07_at_19.27.53.png 615x622
              • 4. Re: 3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.
                philmodjunk

                If you run your script and then select "modify find" from the records menu, you'll find that your script did not succeed in creating all the needed find requests. (You can click through different find requests by clicking the book control while in find mode.)

                You have to use three nested loops. The outer loop would loop through the values of the first check box. The next loop in would loop through the values of the second check box, creating one request for each value of the second check box, but since this loop is "inside" the first loop, you this creates a set of such requests for each individual value in the first check box. You then continue this process by adding the inside loop nested inside the middle loop. And you have to set up the exit loop logic in a way that ensures that each looped code executes at least once even if that particular check box field has no values selected.

                The tedious complexity in typing this script is why I only listed the results such a script should produce.

                There is a simpler scripted approach that you might consider. It's main draw back is that it locks the user into a rigid left to right order when selecting categories in the check boxes.

                In this approach the first check does a typical find by looping through just the values of the first check box and performing a find. The second and third check boxes do not, however perform finds. Instead, they enter find mode, create find requests just using the values of their associated check box field, but then constrain the current found set. With this method, changing the check box selections for the first field should also clear the other two check box fields and changing the middle check box selections would clear the last check box fields.

                • 5. Re: 3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.
                  Kaorin

                  Hi Phil,

                  I'm not familiar with having loops within loop (nor beyond basic script steps), but I started working on what you've said....

                  Obviously below doesn't work....I'm not sure if I have created the script steps in right order.

                  Where have I got it wrong?

                  Kaori

                   

                   

                  Set Variable [Type; Value:GROCERIES::g_find_type]

                  Set Variable [Origin; Value:GROCERIES::g_find_origin]

                  Enter Find Mode []

                  Loop

                  Set Valuriable [$K; Value:£K + 1]

                  Exit Loop If [$K ≥ ValueCount ($Type)]

                  Set Field [Groceries::type; Get Values ($Type ; $K)]

                  Loop

                  New Record / Request

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

                  Set Field [groceries::origin; GetValue ($Origin)]

                  Exit Loop If [$K ≥ ValueCount ($Origin)]

                  End Loop

                  End Loop

                  Set Error Capture [On]

                  Perform Find []

                  • 6. Re: 3 Checkboxes with scripts to find 2 or more matching values from 3 different fields.
                    philmodjunk

                    When setting a variable the first variable of its name should be a $ or you won't be able to use them in a calculation as you will need to do here. (but maybe you have a typo leaving that ouf of your first two script steps.) and since you have global fields--if they have truly set up in field options to be global, you don't need the variables at all as you can refer to the value of a global field while in find mode.

                    But your script doesn't create find requests correctly and the values specified in each won't work for what you want here. Keep in ind that each new request set's up an "OR" for the results produced. "Find all records matching request 1 or request 2 or ..."

                    I suggest running your script and then using Modify last Find from the records menu to return to find mode so that you can click through your requests ( or use table view to see them all at once) to see how criteria is currently being set up by your script.