Loading sorted records from server

[nexgen]

I have some sub-summary layouts which needs sorted records. Since, my file is hosted in filemaker server in cloud, all the records are fetched first before sorting.

This is causing some delays and I'm imagining what will happen if there will be lots of records.

Is there anyway to speed it up?

[Wim Decorte]

Collect the data in a scripted fashion and dump it in a scratch table in a way that saves you from sorting.

The extra effort needs to be weighed vs. how long it takes to do what you do now.

[nexgen]

This will still need the records to be sorted since I'm using sub-summary.

[Wim Decorte]

No, they don't have to be; if you loop through the records and collect the data and put them into the right 'summary' buckets then you can create the records in the scratch table in the order you want them to appear on the report.  No sorting needed.

[nexgen]

Can you please elaborate more on right 'summary' buckets? The only way I know to create the summary is by defining sub-summary part.

[Wim Decorte]

Say that you have these records with 3 columns (car make and how many sold in what month):

Chrysler  March 100

Mazda February 50

Chrysler February 200

Honda January 90

Mazda January 40

If you want to show the totals per car make then you can use your subsummary by sorting the records by car make and defining a summary field for the total sales.

But you can also loop through the records (physically or using GetNthRecord) or use another aggregating technique to load the data you need in a variable to loop through (HyperList has a very interesting readup on the pros and cons of all of them: http://www.modularfilemaker.org/module/hyperlist/)

And as you go through your list you can keep track of 'buckets' with their totals, all in variables

$sales[ code("chrysler")] would be the Chrysler bucket for instance, just keep track of the names of each bucket in a separate list, for every record, add the sales value to the sales bucket for that car make.

Once you are done looping through your records / list, go to your scratch table and create one record for each bucket item (car make) and set the field to the what is in the $sales bucket for that car make.