6 Replies Latest reply on Jan 26, 2012 11:20 PM by catinthehat

    perform find script step EXTREMELY slow

    catinthehat

      Title

      perform find script step EXTREMELY slow

      Post

      I am running a script that searches about 17,000 records. It works fine and then sometimes it will go excruciatingly slow to the point where I will cancel the find. Its a pretty straightforward script that sets variables of Table::global_billed from value list:

      "yes"

      "no"

      "maybe".  (I have them as a checkbox and more than one can be selected.)

      then enters find mode, set field (field::billed;global_billed) and performs find.

      It goes slow specifically on the perfor find step.

      Its weird because it goes slow sometimes. It might go slow when "yes" and "no" are selected or maybe just when "no" is selected.

      All fields are indexed.

      Do you recommend archiving most of the old records? would that make a difference, since it does work some of the time.

       

      thanks!

        • 1. Re: perform find script step EXTREMELY slow
          philmodjunk

          Are you sure that all fields receiving criteria are indexed? What indexing options do you find specified in Manage | Database | Fields?

          This description:

          Its weird because it goes slow sometimes. It might go slow when "yes" and "no" are selected or maybe just when "no" is selected.

          Could indicate that FileMaker is updating the indexing on the specified field. In which case, the first find may be slow and subsequent finds may be faster.

          • 2. Re: perform find script step EXTREMELY slow
            catinthehat

            Thanks Phil,

             

            I archived a bunch of records and switched to a different layout while the script is running. One with only the fields necessary for the search. I did run into a different issue however with this solution.

            I want to show all the records designated by the script but if the user checks "yes" and "No" in the ::g_search_billed field, I want it to show records that have been billed as well as records that have not been billed. I tried several different ways and none seem to work.

            As you can see i created a field called ::billed_search_no because I thought I could search that field and it woud show both yes and no records. Not the case. Any help would be greatly appreciated.

            I hope this is clear

             

             

             

            • 3. Re: perform find script step EXTREMELY slow
              philmodjunk

              With your script, if "no" is selected, both find requests will specify "no" in their search criteria if I am reading your script correctly. You also are stepping through a list of clients from a value list definition. Why do you need to do that? This may or may not be necessary.

              If you are going to find records where multiple check boxes are selected from the same field and you want all records that match any one of the selected values, you'd normally create a separate find request for each value selected where all specified creteria except for the check box value is the same, but with yes/no as the only check box  values we can use a simpler approach:

              #inside the loop...
              If [ValueCount ( Tracking::g_Billing = 1 ) ]
                  Set Field [Tracking::Billed ; Tracking::g_Billing ]
              End IF

              In otherwords, if both values are selected, treat it as though NO values were selected and don't specify any criteria in the billed field as either set of criteria will produce the same found set.

              • 4. Re: perform find script step EXTREMELY slow
                catinthehat

                Thanks Phil,

                 

                There is another value that could be chosen. the value list of GLOBAL::g_billed consists of YES, NO, LATER. What would I do if i wanted to show all the records that have yes or no but not Later and not empty?

                 

                You also are stepping through a list of clients from a value list definition. Why do you need to do that?

                There are 5 global fields that that have the same list of clients so the script runs through and creates a find request for each.

                 

                thanks!

                • 5. Re: perform find script step EXTREMELY slow
                  philmodjunk

                  There are 5 global fields that have the same list of clients...

                  Sorry, misread that script step as something very different.

                  Note: if all fields in your script that start with g_ have global storage specified you do not need to copy that single value into a variable, you can just refer to the global field when specifying criteria. (Using list to put the values from your global client fields makes sense though.)

                  You'll need a nested loop. The outer loop steps through your list of clients. Your inner loop steps through your list of "yes, no.." values.

                  Enter Find Mode[]
                  #OuterLoop
                  Loop
                     set Variable [$K ; value: $K + 1]
                     Set field [tracking::Startdate ; Tracking::g_date_start & "..." & Tracking::g_date_end ]
                     Set Field [tracking::Summary ; Get Value ( $_clients ; $K ) ]
                     Set Field [tracking::Billed ; getvalue ( Tracking::g_billing ; 1 ) ]
                     Set Variable [$J ; value: 1 ]
                     #Inner loop
                     Loop
                         Set Field [Tracking::billing ; GetValue ( Tracking::g_Billing ; $J ) ]
                         Exit Loop If [$J > valuecount ( tracking::g_billing) ]
                         Set variable [$J ; value: $J + 1 ]
                         Duplicate Record/Request
                     End Loop
                     Exit Loop if [ValueCount ( $_Clients ) < $K ]
                  End Loop
                  Set Error capture [on]
                  Perform Find []

                  • 6. Re: perform find script step EXTREMELY slow
                    catinthehat

                    Sweet stuff Phil. Thanks!