Final Grade is an unstored calculation field. Sorting on an unstored field will take much longer than sorting on a stored field. In addition, the complex relationship with inequality operators is slowing down how quickly the final grade field evaluats. (The sort has to evaluate this field on every record in the found set before it can sort them.
I don't know how much it would speed things up, but one way to match by a date range without inequalities is very similar to your cDateList field.
A custom function (requires Filemaker Advanced to add it to your database file) or a script can replace the datestart and dateend fieilds with a single text field filled with a return separated lists of dates from date start to date end. That would make your relationship similar to this:
Employee-Stats (Same for Calls)
And it might be possible to set up DateRange so that cDateList is no longer needed by omitting those dates from the list in DateRange--further simplifying the relationship.
Phil! We meet again! This makes since because I recently moved out of testing 10-20 employees and now the sort is sorting 80. With 10-20, it took under a minute to sort. This was around the time we were working on the cDateList/DatesOmitted relationship. I think I'll look into changing the Start/EndDate to DateRange next week.
I do have FM Pro Advanced (12). I'm not sure if I have my head around the DateRange field just yet. Would this be similarly populated as cDateList. Where I create a loop that populates a date from start to end? I'll pick this up next week.
Would this be similarly populated as cDateList. Where I create a loop that populates a date from start to end? I'll pick this up next week.
You have the right idea. A custom function can use a recursive loop to populate the field. Without that function you can use a looping script as you describe to populate the field. In either method, the dates from table of omitted dates can be omitted from this list to simplify the relationship even further.
Solving Performance Emergencies with FileMaker Server
Performance Optimization of FileMaker Databases
How Can I improve performance of my databases?
The Marvelous Optimization Formula