My Filemaker solution has, at its core, a fairly straightforward invoicing structure. There is an Order table, and an Order_Item table for the line items.

The Order table has an Order_Total calculation. At the moment this has a very simple definition: Sum(Order_Item::Cost)

The calculation cannot be stored as it references a related field. This arrangement seems to be affecting the speed of the database as there are lots of other calculation and summary fields which all use Order_Total and therefore, in turn, have to be unstored.

We now have ~22,000 records in the Order table and ~85,000 in Order_Item so I imagine there's a lot of recalculating going on in the background.

Is there a sensible / recommended way to make this calculation storable? I know I could use a script to insert the total, but that would mean a lot of script triggers etc. needing scattered throughout the solution. The total needs to update fairly quickly whilst staff are adding items and building an order, but after that it tends to stay unchanged apart from the occasional amendment.

I've been using auto-enter calculations for some other fields, would that be an option here?

First, are you sure you have the right cause of the slowdown? 22k/85k records is not a lot; it would really take a lot of related calculations involving all or most of these records to make an impact.

Perhaps you are using some sort of a dashboard to display aggregated data. In such case you may consider generating the data on-demand only - or even storing some of it (the parts that are not going to change, e.g. past years summaries) in a dedicated table.

8 hours ago, Angus McKinnon said:

I've been using auto-enter calculations for some other fields, would that be an option here?

Not really. Auto-enter calculations re-evaluate only when a local field is modified. So your sum would not update when a child item is modified, added or deleted.

If you really want the sum to be stored you will have to script this, with all the caveats you correctly noted.

15 hours ago, comment said:

Perhaps you are using some sort of a dashboard to display aggregated data.

Bingo! There's a dashboard which is the main layout that staff use to access the system. (A couple of main portals for items coming in and out, along with some other displays which are essentially small, single-row portals.)

The problem seems to be that there are a lot of cascading unstored calculations coming off of the Order_Total field. I can trace five levels of calculations working back to it. In theory if I can devise a way of making Order_Total stored then a lot of these other calculation fields could become stored too, or at the very least it's one less level in the cascade.

16 hours ago, comment said:

Not really. Auto-enter calculations re-evaluate only when a local field is modified. So your sum would not update when a child item is modified, added or deleted.

That makes perfect sense. What I've been using auto-enter for so far is things like creating a log of the changes to another field. But that's all within the same table.

Here's a potentially silly suggestion: could I create another field in the Order table (let's call it Order_Total_Unstored) which has the same Sum calculation. Then use an auto-enter calculation so that Order_Total updates when that unstored field changes?

(Or would it simply mean that the auto-enter gets updated every time the unstored calc is re-evaluated, leaving me no better off?)

3 hours ago, Angus McKinnon said:

use an auto-enter calculation so that Order_Total updates when that unstored field changes?

No. An unstored calculation field cannot be "modified". It holds no value (hence "unstored"). It recalculates on-demand (usually on window refresh).

You need to script either the dashboard or the creation/deletion/modification of order items. To avoid "a lot of script triggers etc. needing scattered throughout the solution" if you decide on the latter, consider eliminating any option for users to act on the items other than through a scripted procedure.

All makes sense, thanks. I'll start by trying to figure out where all the places/layouts are where users currently interact with it.