By most modern standards 25k records isn't a huge amount. Fields which cannot be indexed are slower to search on than indexable fields, and fields that rely on a calculation involving related values cannot be indexed, so are slower. But not that much slower.
Is your ranking dependent upon a sort order? If the calculation uses something that is dependent upon a sort order then you are likely to have problems as the Find Mode step will remove that found set, and sort order.
Thanks for the answer. There is no sort for the ranking. I did wait to see how long it would take and it was about 10 minutes. Could it be because it has to calculate every record?
That is a very long time. What is the calculation? Does it used values that are from related fields?
Yes, it is a complex calculation using nested IF/THAN statements across 3 fields. I make a calculation in one field, carry the result to a second field where it is "cleaned up" and parameters are added, then follow the process again to a 3rd field for additional parameters. The resulting number is "cleaned up" one last time in the 4th field and is displayed.
But are the fields in the calculation linked by relationships? (Or are they all in the same record?)
You could 'comment out' increasing steps of the calculation and see where the time-delay is coming from. Might be another way to effect that part by a different method.
The calculations are all in the same record. I split them into 3 calculations just so my mind could work around the process of how the calc would work.
I like the idea of "commenting out" the calculation. I will work on that to see if I can identify the problem.
Examine all three field definitions in Manage | Database | Fields. See if any of them show as "unstored".
Open the specify calculation dialogs and click storage options. See if any show indexing turned off. Try to turn indexing on--but Filemaker usually manages this for you unless you specifically disabled it.
OK, all of the field definitions are "unstored"
Whenever I try to uncheck the indexing checkbox a message comes up saying " The calculation (calc name) cannot be stored or indexed because it references a related field, a summary field, an unstored calculation, or a field with global storage". which of course it does. Even the first field in the calculation references other fields. Is there any work around?
Possibly, but we'd need to know those calculations in order to suggest anything.
OK, Here goes:
first we start with a number (10) then add or subtract based on IF/THAN statements...
Responses to a survey (+1 or -1)
Location (+1 or -1)
Products bought (+1 or -1)
Product subsets (+1 or -1)
If new customer assign a number without using calc #1 else use calc #1
Trim the result of calc #2 (If above X use X if below Y use Y, else use number) then...
If part of loyalty program add or subtract 1,2, or 3
Final Calc #4
Trim the result of calc #3 (If above X use X if below Y use Y, else use number) then...
Display resulting number.
Kind of convoluted I know, but the calculation is important to the customer.
Please post the exact calculations. You can copy and paste them from Specify Calculation into Post A Answer.