1 2 Previous Next 29 Replies Latest reply on Mar 28, 2013 9:25 PM by simmka

    Multiple Find Criteria Script not Filtering multiple records?

    simmka

      Title

      Multiple Find Criteria Script not Filtering multiple records?

      Post

           I've created two layouts. One has several global fields that a user can enter information into any of the fields and then click a "find" button to take them to another printer friendly layout with all of the criteria that they inputted into the previous layout's global fields. On the printer friendly layout is where the fields that store the original data are located. The script in question goes to the printable layout and enters find mode and sets the print layout's fields with the global field search criteria respectively and performs a find on the matching gField to textField data. So the data that the user is putting in the global fields on the one layout is found and shown on the printable layout. I'm concerned that I haven't executed this correctly, though. because I want this script to perform a find on any and all of the criteria that is inputted into these global fields and have it populate on the other layout.  I want it to work on any and all criteria (but ONLY the criteria) that's entered in the global field. For example, right now if I put in "5" in the quantity field, it looks like it is finding any record that has a "5" in it at all. If that is too confusing, has anyone successfully done something like this? With multiple global fields going into a find script and populating on another layout?

           The script is:

           Set error capture on

           Go to layout [printable layout]

           enter find mode

           set field [table::field1;table::gfield1]

           new record/request

           set field [table::field2;table::gfield2]

           (several more of those repetitions)

           Perform find

            

           I do notice that the new record request step is actually temporarily creating a new record.

            

           Anyways, if anyone can help I greatly appreciate it. Hopefully I've explained well enough :).

        • 1. Re: Multiple Find Criteria Script not Filtering multiple records?
          philmodjunk

               It sounds like your quantity field is a field of type text instead of number. Putting 5 as criteria in a text field will find:

               5
               52
               500

               etc.

               Put 5 in a number field and that won't happen.

               You can also use ==5 to find exactly 5 in a text field, but a value like quantity really aught to be in a number field.

          • 2. Re: Multiple Find Criteria Script not Filtering multiple records?
            simmka

                 Thank you, you were absolutely right about the Quantity field. I changed it to a number field and now it's finding just "5" for the quantity. However, I still need some suggestions for how to filter the records. I also need a suggestion for the new record/request script step that is populating my print/found set layout with empty "new" records. My questions are...Is there a better way to perform this script without having to use the new record/request script step. I'm using it currently because (I believe) I need this script to go through all of the set field global fields in one script and "find" the records with those values on the print layout. Can I use another script step to accomplish this just as well without those fake records being populated on the other layout and if not, should I write a script that "hides" those new fake records? (even though that doesn't sound like a really great idea).

                 Second question is, I need these fields to essentially "filter" my records. So if someone puts in (for example) "Apples" for a description of the order (text field) and "5" for the quantity (number field) I want the script to only find orders that have "5" "apples"? Should I write another script that constrains the records somehow? Or can I accomplish that same effect with some other step in my original script (that would be ideal).

                 Thank you!

            • 3. Re: Multiple Find Criteria Script not Filtering multiple records?
              philmodjunk
                   

                        I also need a suggestion for the new record/request script step that is populating my print/found set layout with empty "new" records.

                   Better check your script.

                   new Record/Request

                   can only create a new record if you are in browse mode.

                   In find mode, it creates a new request. A request only exists in find mode, when you perform fhe find or return to browse mode, it will disappear.

                   

                        So if someone puts in (for example) "Apples" for a description of the order (text field) and "5" for the quantity (number field) I want the script to only find orders that have "5" "apples"?

                   From a logical stand point, there are two basic types of "finds" or "queries" that you can do. "OR" finds and "AND" finds. Your script is performing an "OR" find by using a separate request for each specified criterion. What you describe (qty = 5 AND description = "Apples"), is an "AND" find. To get an "and" result, remove the New Record/Request steps so that all the criteria is entered into fields of the same find request.

                   Note there are two other ways to get "AND" or "OR" find results:

                   Or: Perform a find, then perform successive finds with different criteria, but use Extend Found Set instead of Perform Find.

                   And: Perform a find, then perform successive finds using Constrain Found Set instead of Perform Find.

                   Sometimes it's easier to figure out what to do to get the desired results if you use one of these alternative methods.

              • 4. Re: Multiple Find Criteria Script not Filtering multiple records?
                simmka

                     Sounds good, I will work through a couple senarios based on that info and doing an "AND" find. Sounds like adding "enter browse mode" after the "perform find" will take care of the "new" records (if I keep the new records/request script step). Thanks for your help, I'll post back with additional information.

                • 5. Re: Multiple Find Criteria Script not Filtering multiple records?
                  philmodjunk

                       Sounds like adding "enter browse mode" after the "perform find" will take care of the "new" records

                       It will not. Perform Find already has returned you to browse mode.

                       If all else fails, post the actual script instead of a general description of it. Often very small details are what keeps a script from working as intended.

                       To post a script to the forum:

                         
                  1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                  2.      
                  3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                  4.      
                  5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                  6.      
                  7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                  • 6. Re: Multiple Find Criteria Script not Filtering multiple records?
                    simmka

                         You're right, found that out when I tried it for myself. Turns out that the New record/request step was what was messing it all up. I took it out and ran a few finds on just the Description field and Quantity field and got the found set that I needed. No constrain or extend needed. For some reason when I originally wrote this script and tested it without the new record/request step in it was not returning anything, like no matter what I searched for...which is why I put it there to begin with. I'll put a custom dialog step in the script when no results are returned so the users know they need to modify their search terms. I also think the changing the Quantity field to number field helped as well. So I'm all sorted out now as far as I can tell! As I'm not the original developer of the database I'm working with records and fields from, I think it was just me trying to work through new stuff based on old database quirks as well. 

                          

                         Thanks for the quick and helpful advice!! I really appreciate it!

                    • 7. Re: Multiple Find Criteria Script not Filtering multiple records?
                      simmka

                           I do have one more question. Is there a way that I can get the script to only evaluate the values if there are any in the global fields? For example, I'm searching for "Apples" in the description field but it returns no results because it's trying to search in both the description field and quantity field and since I haven't declared a quantity it's saying there are no results. I can't seem to figure out how to include a "do not evalute the quantity field if it's blank". How would I do that in my current script?

                      • 8. Re: Multiple Find Criteria Script not Filtering multiple records?
                        philmodjunk

                             That shouldn't be a factor.

                             If the global field is empty, no criteria is entered into the field unless you are using an expression to include an operator such as =.

                             IsEMpty ( global field here )

                             can then be used to detect if the global field is empty.

                        • 9. Re: Multiple Find Criteria Script not Filtering multiple records?
                          simmka

                               Okay, I've had some time to play with this a bit, sorry for the late response.

                               I did get all of the fields that I'm doing the find on with the script to work EXCEPT the ones that need to go through multiple fields searching for one value. 

                               So, I have several fields that are being used to record orders for a company. I've created a seperate file that works off of those orders to create reports. The one I'm having issues with is the one that is doing the reports. As I've stated, I've set up 2 layouts, one to capture the search terms in globals, and another to display the found set that has all of the fields from the original records on it in the same file.

                               I don't have a problem with the script finding a value in a single field using the global but I do have a problem when I try to search for one value in multiple fields.

                                

                               Here is a snippet of the one value in multiple fields script:
                               Set Error Capture [On]
                               Go to Layout [Found Set Layout]
                               Enter Find Mode []
                          Set Field [Orders::Description 1[]; Reports::gDescription]
                          Set Field [Orders::Description 2[]; Reports::gDescription]
                          Set Field [Orders::Description 3[]; Reports::gDescription]
                               (This same thing through all 10 fields)
                               and then I have about 10 other fields that is just pulling one value for one field using the same Set Field format, all in the same script.
                               Perform Find[]
                                
                               this script does populate the Found Set Layout BUT, of course, it finds only the ones that have the value I'm looking for in ALL of the fields so if I type in "apple" for the search it's finding only the orders where someone ordered apples in all 10 of the description fields.
                                
                               Now, one would think (well, I would anyways) that using the New Record/Request step between each of the set fields (Logical OR) for these description fields would fix this but it doesn't work at all.
                                
                               I need the global to find the one value I enter into the global field on ANY of the description fields. So If 1 of them has the value I'm looking for I want it to show that order on the Found Set Layout. If Description 1 has a value of "apple" but none of the others do, I still want to see that order in my results.
                                
                               I hope that makes sense and I'm explaining well enough :). I'm wondering if I need to concatenate these fields and it would work or if I need to rework the script somehow to make this work.
                                
                               Thank you!
                                
                                
                          • 10. Re: Multiple Find Criteria Script not Filtering multiple records?
                            philmodjunk
                                 

                                      So, I have several fields that are being used to record orders for a company.

                                 And that's a significant problem. You shouldn't have several fields used in this fashion. Define a related table of records so that you only have one such field, but in multiple related records. This solves a lot more problems than just this find. It also can greatly simplify your reporting tasks as you can generate your reports from a layout based on this newly added related table.

                                 In fileMaker 11, this related table is called the Line Items table in the invoices starter solution. In FileMaker 12, it's called the InvoiceData table. You can look at either starter solution for an example of how to set this up.

                                 

                                      Now, one would think (well, I would anyways) that using the New Record/Request step between each of the set fields (Logical OR) for these description fields would fix this but it doesn't work at all.

                                 Better look at your script again. New Record/Request is exactly what you should use in such a script, but if you are also specifying additional criteria in any other field, that data must be specified identically in each and every find request that you create with the new record/Request step.

                                 Set Error Capture [On]
                                 Go to Layout [Found Set Layout]
                                 Enter Find Mode []
                            Set Field [Orders::Description 1[]; Reports::gDescription]
                            New Record/Request
                                 Set Field [Orders::Description 2[]; Reports::gDescription]
                            New Record/Request
                                 Set Field [Orders::Description 3[]; Reports::gDescription]
                                 (This same thing through all 10 fields)
                                 and then I have about 10 other fields that is just pulling one value for one field using the same Set Field format, all in the same script.
                                 Perform Find[]
                                  
                                 But keep in mind that you shouldn't have the fields Description 1, Description 2, Description 3... in your orders table at all.
                            • 11. Re: Multiple Find Criteria Script not Filtering multiple records?
                              simmka

                                   I had a feeling that you might say that. I'm working with a DB that was developed almost 10 years ago and updated every so often (not by me) and I've only just begun working with it in the past 4 months. I am fairly new at filemaker but I've been learning pretty fast. Is there a way to get this at least functional for now or should the entire DB be overhauled before I go any further to prevent further problems such as this one?

                                    

                              You said: "Better look at your script again. New Record/Request is exactly what you should use in such a script, but if you are also specifying additional criteria in any other field, that data must be specified identically in each and every find request that you create with the new record/Request step."

                              Except I need to do both AND and OR finds in the one script, apparently. So, if by identical you mean adding in New Record/Request between all of the Set Field steps then I've just gone back to square one (being, the reason that I started this thread to being with ;)). So, can you expound on that some more, or am I right?

                                    

                              EDIT: I'd also just like to say how much I appreciate you helping me with this as I have been searching the internet endlessly it seems to see if I can find a similar problem/solution but with no avail, thank you!

                              • 12. Re: Multiple Find Criteria Script not Filtering multiple records?
                                philmodjunk
                                     

                                          Is there a way to get this at least functional for now or should the entire DB be overhauled before I go any further to prevent further problems such as this one?

                                     That's why I suggested taking another look at your script. You can fix the immediate issue and then work towards a long term goal of improving the structure of your database.

                                     What I am recommending here is that the "and" criteria needs to be present in every request while the "or" criteria is only in one of the requests.

                                     Say you specify that all records must have the date found in gDate but also with description text found in one of the 10 description fields:

                                     Enter Find Mode []
                                     Set Field [Orders::Date ; Reports::gDate ]
                                     Set Field [Orders::Description 1 ; Reports::gDescription ]
                                     New Record / Request
                                     Set Field [Orders::Date ; Reports::gDate ]
                                     Set Field [Orders::Description 2 ; Reports::gDescription ]
                                     New Record / Request
                                     Set Field [Orders::Date ; Reports::gDate ]
                                     Set Field [Orders::Description 3 ; Reports::gDescription ]
                                     And so forth for each Description field

                                     Set Error Capture [on]
                                     Perform Find []

                                     See how the date needs to be specified in each and every request as it represents "AND" criteria?

                                     One way to simplify your script slightly if you have multiple And Criteria is to set those fields for the first request and then use Duplicate Record/Request instead of New Record/Request. But if you do that, you need to use a set field step to clear the Description field into which you specified criteria in the previous request.

                                • 13. Re: Multiple Find Criteria Script not Filtering multiple records?
                                  simmka

                                       Okay! I will try implementing the above example and see where I'm at. Will post with any info! :)

                                  • 14. Re: Multiple Find Criteria Script not Filtering multiple records?
                                    simmka

                                         It worked! I tested about 4 or 5 permutations of the search to test it out and it seemed to run like a champ so far! Thank you :)! The script is about 10 pages long though (lol) but it's functional and working!

                                    1 2 Previous Next