FileMaker Related Field Updater / Storing Unstored Fields | Blog | CampSoftware

FileMaker Related Field Updater / Storing Unstored Fields

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.





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.

RelatedFieldUpdater-CalculatedValue11RelatedFieldUpdater-CalculatedValue2

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