Speeding up Summary fields

[jakeleveto]

In my database I have about 30,000 records, each related to about 3 line items. Each record has a calculation price field that is the sum of the price of the line item. I then have a layout that has a summary field that sums all of the prices for each record. This takes a very long time - Is there any alternative way to speed up summaries? I can't pre-calculate the prices because Filemaker won't let me store a calculation that relates to a different file.

Thanks

[Søren Dyhr]

Filemaker won't let me store a calculation that relates to a different file.

What about Evaluate( or Calc'ed Lookups???

--sd

[RalphL]

How do you update calculated lookup? Evaulate would also give unstored results. I have tried combining the 2, i.e., Evaluate ( "Lookup (TO::Field)" : [Trigger] ). This works if the trigger is in the current TO but then the problem is how to activate the trigger. I tried using Modification Time or TimeStamp as the trigger. It works when I manually make a change to the record.

[Ender]

jakeleveto,

You might try storing the total for each Invoice in the Invoice table. This would require a scripted update or a triggered calc as Soren and Ralph suggest (though for the trigger to work, the Invoice record itself would need to be modified--not just the line items.)

This at least gets you a stored total for each Invoice. If the summary total on this is still not fast enough, you might try storing and updating totals after each Invoice is completed or closed. This would require scripts to update data in a separate Summary table every time an invoice is changed.

[Søren Dyhr]

Well if make the primary key a compund one consisting of both a serialnumber as well as the clientID, does it take next to nothing to make a split of the foreign key so the reporting can take place in the related file instead. Since the relations now with 7.0 are bidirectional, is a scripted replace easily done once and for all, for then to let the future keys follow the new scheme. The foreign key is always stored and indexed to you hearts desires!

This is only a slight denormalization!!!

--sd