2 Replies Latest reply on Nov 26, 2016 1:24 PM by philmodjunk

    How to store/cache a unstored calculated field

    samgiertz

      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?

        • 1. Re: How to store/cache a unstored calculated field
          coherentkris

          change your last modified by user to a calculation that is attached to the modified timestamp field and does nothing when a server account is performing an action.

          eg

          Let (

          _ts = auto enter modified timestamp field;

           

          if ( user account = server ; your mod history timestamp field ; _ts)

          )

          • 2. Re: How to store/cache a unstored calculated field
            philmodjunk

            Usually a point in time comes when an invoice is no longer subject to modification because it has been submitted to the customer. At that point, it is usually the case that a new invoice has to be created should a problem be encountered at that point. So one way to save that calculated value is to make it part of a script that changes a status field in the parent record--the Jobs record to which these items are linked. Record Level Access can then be used to prevent any future modification to this set of records. So you might not need to save any value until this point in the process is reached.

             

            Another thing you can do is modify that update script that runs each night so that it only modifies data should the unstored calculation not match the stored value. You might consider setting up the script to log each time the update is needed in order to figure out what action got through your existing process.