5 Replies Latest reply on May 7, 2012 9:09 PM by MichaelMaher

    Unstored and indexed versions of a field


      Over the years we have experimented with various ways to address this problem, but to date we are still dissatisfied with the solutions we are using.


      The problem: often a field in a table, that is a value from a related table, needs to be indexed for various reasons, eg. for relationships, searching.


      The way we currentky deal with this is to have two fields:

      RelatedValue_c (an unstored calculation from a related table).

      Relatedvalue_set (an indexed stored field, with Auto-calc, on Calculation or Lookup, to populate the field on record creation).


      Then overnight, or before a relevant script, we update the _set field with the _c just in case they have got out of synch since record creation.


      This is a very time consuming process, especially as the database grows into a leviathan, and the number of fields to update across all tables escalates.

      Typically, 'normalising' means one stored value per database, but FileMaker is so slow, it's unpractical to always use unstored related values, plus they don't work for right-side relationships.


      We experimented with ways to have an indexed field continually update, like using Let and triggers in the Auto-calc area, but eventually we had to abandon most of those as they caused the overnight update scripts to run too long (there were dependancies that ran on endlessly), or they simply didn't work reliably.


      Has anyone else solved this issue?


      I want a field value to immediatly update via calculation involving a related table, and yet still be indexable. It may be impossible, but it is sorely needed.



        • 1. Re: Unstored and indexed versions of a field

          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.

          • 2. Re: Unstored and indexed versions of a field

            Hi Michael,


                 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.


            Best regards.

            • 3. Re: Unstored and indexed versions of a field

              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.

              • 4. Re: Unstored and indexed versions of a field
                Stephen Huston

                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.

                • 5. Re: Unstored and indexed versions of a field

                  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.


                  Michael Maher