I have a data table of around 20,000 records which stores sales data for several hundred customers. Each customer has around 100 rows of sales data. There are various criteria which are needed as well in order to allow summing and sorting on a row by row basis.
I need to be able to show the customer sales data for say product XYZ against the national average. So far, this has involved me doing the following:
- Search for customer in question by their ID and return only their records, creating a 200 row found set
- Use native Filemaker Case and Summary commands to split out and summarise the data on a row by row basis
- For the national averages and querying the 19,800 records remaining, I've needed to use ExecuteSQL as this is not limited by what's in the found set, but it's amazingly slow.
Is there any way that I can use Case and Summary fields to search and return figures on the remaining 19,800 records so the results can appear alongside the customer's own results? As these are outside the found set for this customer, it appears I cannot reach them to calculate on them.
Thanks in advance