How to store/cache a unstored calculated field

Discussion created by samgiertz on Nov 26, 2016
Latest reply on Jan 24, 2018 by PuraVida

Hi everyone!


We have a filemaker system (V15) accessed over WAN from several offices. We have table with Jobs and a related table with Charge Items. There can be many Charge Items to an Order. These Charge Items later also acts as Invoice lines when we invoice.


In the Jobs table I have a calculated field "total charge amount" for the job. It cannot be stored as it is a sum of records in a related table.


The system becomes very slow when we want to later search on, or aggregate, this unstored total charge value, for natural reasons. We have about 200k jobs and about 600k of charge itmes.


I have now implemented a "stored" total charge amount. I copy the unstored calculated value to this static number field after any charge items have been modified. I have to make this copy when the user adds, deletes or changes the value on any charge items in the relevant layout. I have to make sure to catch this with script triggers on the objects that deal with the charge items. I think I have caught all possibilities but it does not seem very graceful to me to handle it manually with script triggers. There should be no way of deleting or changing charge items unless through the Jobs layout.


I also run a script every night on the server which copies the calculated value to the stored static value, for fail safe. This unfortunately loses the functionality of "last modified by user" field as the server has updated every record every night.


Invoices still use the individual charge items so I am not so concerned if the static stored value is "off" at some point, it would only have implications on statistics and search for a limited time.


Is there a smarter way?