Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

perform find script step EXTREMELY slow

Question asked by catinthehat on Jan 16, 2012
Latest reply on Jan 26, 2012 by catinthehat

Like • Show 0 Likes0
Comment • 6

Title

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!

No one else has this question

Outcomes

6 Replies

philmodjunk Jan 17, 2012 10:44 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
catinthehat Jan 22, 2012 9:01 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
philmodjunk Jan 23, 2012 10:04 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
catinthehat Jan 23, 2012 9:11 PM
Mark Correct
Correct Answer
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!
Like • Show 0 Likes0
Actions
philmodjunk Jan 24, 2012 10:03 AM
Mark Correct
Correct Answer
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 []
Like • Show 0 Likes0
Actions
catinthehat Jan 26, 2012 11:20 PM
Mark Correct
Correct Answer
Sweet stuff Phil. Thanks!
Like • Show 0 Likes0
Actions

perform find script step EXTREMELY slow

Title

Post

Outcomes

Related Content

Recommended Content