I don't have any great solutions.
I generally do what you're doing, I use a server side script that runs at night to update those fields. You could use script triggers so when fields are changed in one table the script updates the related table immediately.
You could try a script triggering plug in that allow you to add a trigger to a calculation. For example, a field validation calc so then you don't have to worry about updating the script triggers if you layout changes.
in combination with script trigger (David solution), every 30min, I use a script server like yours, but I run the sync only for changed records (date change), which are much less compared to the whole recordset.
My scenario works on tables with 500.000/600.000 records.
FileMaker could solve our problems and eliminate all the kludgey workarounds and millions of wasted cpu cycles by just allowing us the option of maintaining an index on an unstored calculation or the option to have a stored calculation auto-trigger on its dependency changes.
There are certain circumstances where using script triggers is enough and quite reliable, such as updating an invoice total (indexed) based on the sum of the line item records.
In our invoicing system, thel ine item records are created and edited solely through a portal, so the Invoice's subtotal, tax, and total can be set reliably with a script trigger OnRecordCommit. This has created huge time saving on invoice reports -- having the invoice figures fully-stored and indexed instead of unstored calc sums referencing the line items records.
In another case, we run a daily updater script each morning to calculate how long it has been since a client had orders, invoices, and contacts with us, to set the clients' status. All of these would be unstored calcs, but we have them set each morning via a looping script that stores and indexes the results so these system-wide calcs are never more than 1 day out of sync with the incoming data. It is realiable enough for our tracking needs, and a huge performance improrivement over the live unstored calcs. Especially since our sales team uses this info even when on the road, over WAN connections.
It really depends on what types of information you need to update, when you need to use it, and how the related records are edited.
Hi Stephen and David,
Script triggers, and Save buttons or other scripted options, are fine where the process can be easily pinpointed. For our solution, that is quite rare. It is a vast array of interwoven inputs and modifications, so to keep track of those specific actions which will affect the indexed values across the different tables, would be too cumbersome and open to failure as development is ongoing.
The problem with overnight updates, is that many of these field values can’t wait that long. We do adopt a range of different methods to take care of the critical values, but it would be good to have some simple mechanism in the Field Definition itself to have available as an option. Alas, it doesn’t appear to be, nor soon I suspect.
I just thought some clever developer might have found a neat solution.