FileMaker Related Field Updater / Storing Unstored Fields
NOTE: We really like this method, but it won't work in FileMaker Go since it requires an Extension. If the ExecuteSQL function had INSERT, UPDATE, and DELETE or if we could run a script in the calculation engine, this would work.
For years, we’ve had problems with Filemaker unstored Fields. Don’t get me wrong, unstored fields are great for having related data available in related FileMaker tables. However, unstored fields don’t work so well for Finds, Sorts, or Relationships.
Yesterday, Doug and I found a way to do this with no triggers and no flicker but it does require a FileMaker Extension to be able to use FileMaker SQL INSERT, UPDATE, and DELETE statements. We use the BaseElements Extension which does a ton including FileMaker SQL!
All we needed to do is add a Calculated value to the field within Manage Database, allowing the value to be replaced.
So, if we needed to update a First Name to another table, we could type into the field and the Calculated value calculation would run. Initially, we created the calculation within the Calculated value, but after we found all the ins and outs, the calculation became very long. Since it was so complex, we generalized the calc and converted it to a Custom Function. This how we can update the stored field in the related table:
First & Extensions.BaseElements.RelatedFieldUpdater ( First ; CONTACT ID ; GetFieldName ( ContactsRelated::NameFirst ) ; GetFieldName ( ContactsRelated::CONTACT ID FK) ; "update insert delete" )
These are the parameters:
Extensions.BaseElements.RelatedFieldUpdater ( thisFieldValue ; thisRelationshipKey ; relatedFieldName ; relatedRelationshipKeyFieldName ; action )
The "First &" simply places the field back in the field. The remaining part is the custom function which receives the edited value, keys, field name, and what it should do. The custom function looks to see if the related record exists. If it does, it uses FileMaker SQL to UPDATE the field. If the record doesn’t exist, it INSERTS the record, if you add 'insert' to the action. If the field is cleared and you add 'delete' to the action, it will delete the related record. If you clear the field and don’t allow deletes, it will clear the field on the related table.
Download the Example Database with the Custom Function