1 2 Previous Next 17 Replies Latest reply on Apr 19, 2013 9:06 AM by philmodjunk

    Problems with a a "find" script

    hanstrager

      Title

      Problems with a a "find" script

      Post

           I've had some help in here before to create a script that would make find based om different global search fields and check boxes.

           I basically have 2 different search fields - "date"

           a drop down menu - "Seller"

           and 2 different check box fields - "Category" and "Payer"

           Before "Payer" was only a drop down menu, but when i change this around to a check box field like the "category" i kind of dosen't really work anymore.

           Basically i want every record found must match at least one of those values in Category and payer, but necessarily not all of them, but must match the criteria in "Seller" and "date"

           So far I got the following code that almost does what i want, but dosen't work when I uncheck any of the check boxes:

           Go to Layout [ “Accountant” (Zips) ]
           Set Variable [ $CategoryList; Value:Globals::Category ]
           Set Variable [ $PayerList; Value:Globals::Payer ]
           Set Variable [ $P; Value:1 ]
           Set Variable [ $K; Value:1 ]
           Enter Find Mode [  ]
           Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
           Set Field [ Zips::Seller; Globals::Seller ]
           Set Field [ Zips::Category; GetValue($CategoryList ; 1) ]
           Set Field [ Zips::PAYER; GetValue($PayerList ; 1) ]
           Set Error Capture [ On ]
           Perform Find [  ]
           Loop
           Set Variable [ $K; Value:$K + 1 ]
           Set Variable [ $P; Value:$P + 1 ]
           Exit Loop If [ $K > ValueCount($CategoryList) ]
           Exit Loop If [ $P > ValueCount($PayerList) ]
           Enter Find Mode [  ]
           Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
           Set Field [ Zips::Seller; Globals::Seller ]
           Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]
           Set Field [ Zips::PAYER; GetValue($PayerList ; $P) ]
           Extend Found Set [  ]
           End Loop
            

            

        • 1. Re: Problems with a a "find" script
          davidanders

               Going to Find Mode and setting Name to George and Color to Red and performing the Find will only find Red Georges. [Red AND George]

               Going to Find Mode setting Name to George, then Add New Request and set Color to Red  and performing the find will find Reds and or Georges.[Red OR George]

          http://help.filemaker.com/app/answers/detail/a_id/5210/~/multiple-find-requests

          • 2. Re: Problems with a a "find" script
            hanstrager

                 Im not entirely sure how this is going to help with my script..?

                 Sorry im quite new to Filemaker scripting...

            • 3. Re: Problems with a a "find" script
              philmodjunk

                   I'd use multiple requests instead of multiple extend found sets, but either approach can work.

                   But I see some  more fundamental issues here:

                   Each time through the loop, you use one value from Category and One value from Payer as the criteria. So this does a find for Category = X AND Payer= Y. If you want to find all records where Category = X but payer may = any one of the values selected for it, this script won't find them.

                   The script stops when it reaches the end of the list of Category or Payer values--whichever is the shortest list. If there are remaining values in the longer list, they aren't used in the script to find records.

                   If you are trying to do what I think you are, you need two looping scripts, one inside the other in order to perform a find with every possible pairing of Category and Payer values.

              • 4. Re: Problems with a a "find" script
                hanstrager

                     I changed my script to the one underneath.  The Payer and category dosen’t seem to work together. This is what I try to have the to do:

                      

                Primary filter boxes:

                     Date: needs to be able to put a two dates date  and fine every record within that date range.

                     Payer (check boxes): within that date range I need to only show certain payers

                Secondary filter boxes:

                     Category (Check boxes): Needs to filter further what has been filtered by the primary filter boxes.

                     Seller (Drop down list): Needs to filter further what has been filtered by the primary filter boxes.

                      

                Go to Layout [ “Accountant” (Zips) ]

                Set Variable [ $CategoryList; Value:Globals::Category ]

                Set Variable [ $PayerList; Value:Globals::Payer ]

                Set Variable [ $P; Value:1 ]

                Set Variable [ $K; Value:1 ]

                Enter Find Mode [  ]

                Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]

                Set Field [ Zips::Seller; Globals::Seller ]

                Set Field [ Zips::Category; GetValue($CategoryList ; 1) ]

                Set Field [ Zips::PAYER; GetValue($PayerList ; 1) ]

                Set Error Capture [ On ]

                Perform Find [  ]

                Loop

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

                Exit Loop If [ $K > ValueCount($CategoryList) ]

                Enter Find Mode [  ]

                Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]

                Set Field [ Zips::Seller; Globals::Seller ]

                Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]

                Extend Found Set [  ]

                End Loop

                Loop

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

                Exit Loop If [ $P > ValueCount($PayerList) ]

                Enter Find Mode [  ]

                Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]

                Set Field [ Zips::Seller; Globals::Seller ]

                Set Field [ Zips::PAYER; GetValue($PayerList ; $P) ]

                Extend Found Set [  ]

                End Loop

                      

                     Hopes this makes more sense

                • 5. Re: Problems with a a "find" script
                  philmodjunk

                       It's not what I suggested as you need to place one loop inside the other loop-- what we call "nested loops" that way the script takes one value for Payer in the outside loop and the inner loop then matches that up with a different category value in turn--looping through each category. Then control passes to the outer loop and it advances to the next payer in the list and loops through the categories all over again. If you have 3 payers and 2 categories, that produces 6, (3 x 2 ) pairings of payer and category.

                       But that may not be the only approach that works here. It's the "old school" method that I would have used before constrain and extend found set were added. I don't have more time to spend on this one right now, but I'm mulling over whether your script could find all records for date range, category and seller--using either multiple requests for each category or a series of "extend found set" steps. Then return to find mode and loop through the payers generating separate requests (can't use extend here) and then constrain the found set with those additional requests to filter the result down to just those records on the list of selected payers.

                       But I'd have to test that idea to see if it will work or not.

                  • 6. Re: Problems with a a "find" script
                    hanstrager

                         I tried to nest my script, but dosen't seem to work...?

                         Go to Layout [ “Accountant” (Zips) ]
                         Set Variable [ $CategoryList; Value:Globals::Category ]
                         Set Variable [ $PayerList; Value:Globals::Payer ]
                         Set Variable [ $P; Value:1 ]
                         Set Variable [ $K; Value:1 ]
                         Enter Find Mode [  ]
                         Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                         Set Field [ Zips::Seller; Globals::Seller ]
                         Set Field [ Zips::Category; GetValue($CategoryList ; 1) ]
                         Set Field [ Zips::PAYER; GetValue($PayerList ; 1) ]
                         Set Error Capture [ On ]
                         Perform Find [  ]
                         Loop
                         Set Variable [ $K; Value:$K + 1 ]
                         Exit Loop If [ $K > ValueCount($CategoryList) ]
                         Enter Find Mode [  ]
                         Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                         Set Field [ Zips::Seller; Globals::Seller ]
                         Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]
                         Extend Found Set [  ]
                         Loop
                         Set Variable [ $P; Value:$P + 1 ]
                         Exit Loop If [ $P > ValueCount($PayerList) ]
                         Enter Find Mode [  ]
                         Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                         Set Field [ Zips::Seller; Globals::Seller ]
                         Set Field [ Zips::PAYER; GetValue($PayerList ; $P) ]
                         Extend Found Set [  ]
                         End Loop
                         End Loop
                          

                    • 7. Re: Problems with a a "find" script
                      philmodjunk

                           I see two issues:

                           The variable $P needs to be set back to 0 after every loop so you need to insert: Set Variable [$P ; value: 0 } Just before the Loop step.

                           The other issue is really a question: Do you want to find all records that have a specified payer or a specified category? Or do you want to find all records with any possible pairings of the listed payer and category?

                           Example: if categories are Entertainment and Food And the Payers are Walmart and Costco, do you want to find:

                           all records with the other criteria AND Walmart And Entertainment Or

                           other criteria And Walmart And food OR

                           Other citeria and Costco and Entertainment Or

                           Other critera and Costco and Food

                           ?

                           If so, you can't use Extend Found set this way, you'll need to use multiple find requests. Your current script, once the loop variable issue is corrected would find all records with:

                           The other criteria and Walmart, Or  Other criteria and Costco, or the other criteria and Food or the other critera and Entertainment.

                           Thus, an entry that matches "other criteria" but has category: "food", Payer: SaveMart will be found by your script, but not if we modify the script to produce the results described in the first example.

                      • 8. Re: Problems with a a "find" script
                        hanstrager

                             I want to find records with: Date AND payers

                             "Category" and "seller" is only supposed to exten the search.

                             so if i have payer (A), payer (B) & payer (C). and in category i have: milk, onions, and candy

                             if we then have 6 records that's constructed like this:

                             1) 5 October, Payer (A), Milk

                             2) 7 October, Payer (B), Milk

                             3) 10 October, Payer (C), Onions

                             4) 13 October, Payer (C), Milk

                             5) 1 October, Payer (A), Candy

                             6) 1 November, Payer (C), Milk

                              

                             when I then in my date fields has a date range from 1 October - 20 October, and in my Payer check boxes I have Payer (A) and Payer (C) selcte, then only record 1), 3), 4) & 5)

                             If I then further more select Milk & onions in my category check boxes, I should only be left with 3 records: 1), 3) & 4).

                             Does this illustrate what I want to try to do..?

                             The Script sofar:

                             Go to Layout [ “Accountant” (Zips) ]
                             Set Variable [ $CategoryList; Value:Globals::Category ]
                             Set Variable [ $PayerList; Value:Globals::Payer ]
                             Set Variable [ $P; Value:1 ]
                             Set Variable [ $K; Value:1 ]
                             Enter Find Mode [  ]
                             Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                             Set Field [ Zips::Seller; Globals::Seller ]
                             Set Field [ Zips::Category; GetValue($CategoryList ; 1) ]
                             Set Field [ Zips::PAYER; GetValue($PayerList ; 1) ]
                             Set Error Capture [ On ]
                             Perform Find [  ]
                             Set Variable [ $P; Value:0 ]
                             Loop
                             Set Variable [ $K; Value:$K + 1 ]
                             Exit Loop If [ $K > ValueCount($CategoryList) ]
                             Enter Find Mode [  ]
                             Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                             Set Field [ Zips::Seller; Globals::Seller ]
                             Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]
                             Extend Found Set [  ]
                             Loop
                             Set Variable [ $P; Value:$P + 1 ]
                             Exit Loop If [ $P > ValueCount($PayerList) ]
                             Enter Find Mode [  ]
                             Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                             Set Field [ Zips::Seller; Globals::Seller ]
                             Set Field [ Zips::PAYER; GetValue($PayerList ; $P) ]
                             Extend Found Set [  ]
                             End Loop
                             End Loop
                              

                              

                              

                              

                        • 9. Re: Problems with a a "find" script
                          philmodjunk

                               In your example, you are constraining the search, not extending it. And we have a "payer", but where did "seller" come from? Leaving out seller for a moment,

                               You would first have

                               payer A onions

                               Payer A watermelon

                               but once you have specified your categories, Payer A, Watermelon is excluded, you have constrained your found set to a shorter list of records.

                          • 10. Re: Problems with a a "find" script
                            hanstrager

                                 in my script do I then only need to change  "Extend Found Set []"  into "Constrain Found Set []"..?

                            • 11. Re: Problems with a a "find" script
                              philmodjunk

                                   It's not that simple.

                                   After performing the original find where you specify all the criteria that all records must match, you need a loop that generates a series of requests and then does a single Constrain Found set to reduce the original found set to just those records that match one of the selected categories.

                              • 12. Re: Problems with a a "find" script
                                hanstrager

                                     No sure if I got it right I tried the underneath script, but think I failed miserably, since it dosen't really work. I tried to specify in the beginning that all found records must match Payer and Date, and then the rest to come after in the loop.

                                     Go to Layout [ “Accountant” (Zips) ]
                                     Set Variable [ $CategoryList; Value:Globals::Category ]
                                     Set Variable [ $PayerList; Value:Globals::Payer ]
                                     Set Variable [ $P; Value:1 ]
                                     Set Variable [ $K; Value:1 ]
                                     Enter Find Mode [  ]
                                     Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                                     Set Field [ Zips::PAYER; GetValue($PayerList ; 1) ]
                                     Set Error Capture [ On ]
                                     Perform Find [  ]
                                     Set Variable [ $P; Value:0 ]
                                     Loop
                                     Set Variable [ $K; Value:$K + 1 ]
                                     Exit Loop If [ $K > ValueCount($CategoryList) ]
                                     Enter Find Mode [  ]
                                     Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                                     Set Field [ Zips::Seller; Globals::Seller ]
                                     Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]
                                     Constrain Found Set [  ]
                                     End Loop
                                      

                                • 13. Re: Problems with a a "find" script
                                  philmodjunk

                                       You have multiple constrain found sets, once per loop. I am recommending that you use just one, but with multiple requests.

                                       Go to Layout [ “Accountant” (Zips) ]
                                       Set Variable [ $CategoryList; Value:Globals::Category ]
                                       Set Variable [ $PayerList; Value:Globals::Payer ]
                                       Set Variable [ $P; Value:1 ]
                                       Set Variable [ $K; Value:1 ]
                                  # All records must meet this criteria: Date, Seller and one of the listed Payers
                                       Enter Find Mode [  ]
                                       Loop
                                          Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]
                                          Set Field [ Zips::PAYER; GetValue($PayerList ; $P) ]
                                          Set Field [ Zips::Seller; Globals::Seller ]
                                          Set Variable [ $P; Value:$P + 1 ]
                                          Exit Loop If [ $P > ValueCount ( $PayerList ) ]
                                          New Record/Request
                                       End Loop
                                       Set Error Capture [ On ]
                                       Perform Find [  ]
                                  # Constrain result to be only records that match one of the listed categories
                                       Enter Find Mode [  ]
                                       Loop
                                           Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]
                                           Set Variable [ $K; Value:$K + 1 ]
                                           Exit Loop If [ $K > ValueCount($CategoryList) ]
                                           New Record/Request
                                       End Loop
                                       Constrain Found Set[]


                                       I am assuming that since you are only specifying a single seller, that all records found must have the specified seller.

                                  • 14. Re: Problems with a a "find" script
                                    hanstrager

                                         I’ve tried the script, but im not sure whats happening it just continues to find records and first when I press esc it stops and then it have found thousands of records (I only have 25 sofar) with the same payer and same date, but nothing else…

                                         I’ve taken the seller option out since it becomes too complicated so now it’s only date, payer and category.

                                         I’ve used the following script which seems pretty identical to what you suggested above..?

                                          

                                    Go to Layout [ “Accountant” (Zips) ]

                                    Set Variable [ $CategoryList; Value:Globals::Category ]

                                    Set Variable [ $PayerList; Value:Globals::Payer ]

                                    Set Variable [ $P; Value:1 ]

                                    Set Variable [ $K; Value:1 ]

                                    Enter Find Mode [  ]

                                    Loop

                                    Set Field [ Zips::DATE; Globals::Date1 & "..." & Globals::Date2 ]

                                    Set Field [ Zips::PAYER; GetValue($PayerList ; $P) ]

                                    //  Set Field [ Zips::Seller; Globals::Seller ]

                                    New Record/Request

                                    End Loop

                                    Set Error Capture [ On ]

                                    Perform Find [  ]

                                    Enter Find Mode [  ]

                                    Loop

                                    Set Field [ Zips::Category; GetValue($CategoryList ; $K) ]

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

                                    Exit Loop If [ $K > ValueCount($CategoryList) ]

                                    New Record/Request

                                    End Loop

                                    Constrain Found Set [  ]

                                          

                                    1 2 Previous Next