1 2 Previous Next 16 Replies Latest reply on Jan 26, 2015 7:36 AM by chihuas

    Constrain found records question

    chihuas

      Hi everybody

       

      I have a script that has to find data based on criteria contained on several fields on a table of more than 400,000 records. I can't use a single find records request because it depends if the different fields are empty or not. What I did is to perform a find based on the first field which always has a value. For the next fields, I perform a constrain found records if the field has some values. I am having the results I need regarding the records I am looking for.

      When the script runs, it first finds for saying something 10,000 records on the find record request, then when it performs each constrain found records request, the script takes the same amount of time or more to show the new set of records. I supposed that all the following constrain record requests should search from the already found set, on my example, the next constrain should search from records on the 10,000 found set of records, not on the 400,000 records again.

      Can anybody explain me if my way of thinking is correct or not, or show me if there is a way to make this requests work run faster?

       

      Thank you in advance.

        • 1. Re: Constrain found records question
          mtwalker

          it depends if the different fields are empty or not

          Are you referring to the fields in the Find request of the fields in the found set?

          • 2. Re: Constrain found records question
            chihuas

            The script is something like this:

             

            Perform find (restore)

            If(field_1 <> "")

              Constrain found set (restore)

            Endif

            If(field_2 <> "")

              Constrain found set (restore)

            Endif

            and the same for the rest of the fields used as search criteria

            • 3. Re: Constrain found records question
              mtwalker

              If you are already using conditional branching, why not include all of the criteria in one find?:

               

              Perform find (restore)

              If(field_1 <> "")

                Perform find (restore) ( RequiredField = thecriteria and field1 = thecriteria )

              Endif

              If(field_2 <> "")

                  Perform find (restore) ( RequiredField = thecriteria and field2 = thecriteria )

              Endif

              • 4. Re: Constrain found records question
                chihuas

                My problem is that my final find may include criteria for any combination of fields, so I will have to create a script including all the different scenarios.

                • 5. Re: Constrain found records question
                  chihuas

                  The total number of fields used as search criteria is about 12.

                  • 6. Re: Constrain found records question
                    justinc

                    It seems like you could easily do this in a single find request record.  Since you are Constraining the found set on each time, then you are performing an "AND" style search.  That is, the records MUST have ALL of the criteria that the user entered to search for.  In FileMaker terms, all of the search terms will be on the same Find record.

                     

                    It also sounds like you are collecting the user's input all at once, in a single place; you are providing a layout with 12 input fields where the user types in what they want, and then you run a script to go find records based on those 12 inputs.

                     

                    Since that is the case, I would write the script thusly (I like explicit steps for setting the fields):

                    --------

                    Enter Find Mode

                    Set Field1 = criteria1

                    Set Field2 = criteria2

                    Set Field1 = criteria3

                    ...

                    Set Field10 = criteria10

                    Set Field11 = criteria11

                    Set Field12 = criteria12

                    Perform Find

                    ----------

                     

                    If the criteria are empty, it won't affect anything - you will set the search field to an empty value and it won't hurt/hinder/affect the results.  If the criteria has an input value, it will find for that value (and all the other ones, too).

                     

                    --  Justin

                    • 7. Re: Constrain found records question
                      mtwalker

                      I guess I don't understand your scenarios. Can you give some examples?

                      • 8. Re: Constrain found records question
                        justinc

                        Oh, to answer the more conceptual question:  Yes, using Constrain should operate only on the current found set.  When you do a regular "Perform Find" then it will operate on the entire set of data in the table.

                         

                        Why does it take so long on each Constrain?  I don't know, I would want to see the script.  But, keep in mind that it has to download the results that it is going to display for you (or at least 25 or 50? of them).  So, if they are different results each time, they won't be cached, and it has to download 50 new ones each time.

                         

                        Say your first find results in 10000 records (IDs 1 - 10,000), and FM downloads record IDs 1-50 to display on your layout.  The next Constrain results in only 5000 records, but none of those (perhaps) is ID 1-50, so it has to download record IDs 100-150 to display on the layout.  The next Constrain results in only 500 records, but perhaps none of them are from the IDs 100-150, so it has to download record #s 160-210.  Etc.  You should eventually see a speed up as it is more and more likely that the records being shown will be in the cache.  But it is always possible that they aren't.

                         

                        --  Justin

                        • 9. Re: Constrain found records question
                          chihuas

                          I think this is what I am looking for, only one more question, the fields are not on my the layout, the script you wrote still works this way, I am only showing the total number of records based on the criteria.

                          • 10. Re: Constrain found records question
                            chihuas

                            By different scenarios I meant all the different sets of variables that may have values at the same time, but I think the way Justinc explained is  the solution of my problem, I have never used the find on the way he presented. I am very new on Filemaker.

                             

                            Thank you for your help

                            • 11. Re: Constrain found records question
                              chihuas

                              I think this is what I am looking for, only one more question, the fields are not on my layout, only the global fields used as variables, the script you wrote still works this way?, I am only showing the total number of records based on the criteria. No fields from the file are used on the layout.

                              • 12. Re: Constrain found records question
                                justinc

                                The values used in the script can come from anywhere:  fields on a layout that the user fills in, global variables, etc.  Here's how you could use it with global variables:

                                 

                                --------

                                Enter Find Mode

                                Set Field1 = $$gvar1

                                Set Field2 = $$gvar2

                                Set Field1 = $$gvar3

                                ...

                                Set Field10 = $$gvar10

                                Set Field11 = $$gvar11

                                Set Field12 = $$gvar12

                                Perform Find

                                ----------

                                • 13. Re: Constrain found records question
                                  beverly

                                  "any combination" may imply an OR search not an AND search. If you need any OR searches include a New Record/Request before each of the Set Field steps while in  Find mode.

                                   

                                  It's difficult, but not impossible to combine AND with OR in a single search. Sometimes it's good to use the Constrain Found Set (after other scripted find steps).

                                   

                                  Think through it all.

                                   

                                  BTW, The "OMIT" is a way to also throw in a NOT, but all omits should occur after all AND/OR steps.

                                   

                                  Beverly

                                  • 14. Re: Constrain found records question
                                    Mike_Mitchell

                                    Now that your question is answered ...  

                                     

                                    I would suggest that a "slow" search is a result of searching on something that's not indexed. If you're searching 40,000 records and all the search fields are indexed, it should process very quickly, regardless of whether it's constrained or not. So what I would be looking for is a way to eliminate any unindexed fields (unstored calculations, related field) from the search array.

                                     

                                    Just my $0.02.

                                     

                                    Mike

                                    1 2 Previous Next