Recently, Doug de Stwolinska and I been trying several methods to update a field in another table. Before now, we have been adding a trigger on the field on the layout which runs a script that updates the stored field in the other table. It works nicely, but if we add the field somewhere else we’re likely forget the trigger. Also, we might add a field to a layout by duplicating the field with the trigger which then causes an incorrect values to be updated. On top of that, we end up with a bit of screen flicker as we open a window off screen.

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!

View On YouTube

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