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.