One shortcoming of FileMaker compared to SQL databases the lack of built in Atomicity–the ability to insure that all records in a transaction are updated successfully, or no changes are applied if the update fails. This shortcoming has been addressed by FileMaker developers in the past by cycling through records in a portal without committing data until all records have been updated, then committing the changes all at once. And it works pretty well. But it’s kind of a clunky work around.
Jan Zelenka of ClickWorks shows off a way to update records by making a temporary relationship to a global variable on the fly, and gets the same results:
The idea is actually rather simple. When woking in a context of a certain Table Occurence, use a global field in that table and link it with a unique ID in a table you want to modify transactionally. Every time the Global Field gets set to a value of existing ID in the related table, that record becomes available through the relation and can be modified. Then when the value in global foreign key is changed, another record is available for modifications.
At the end, when the Commit command gets executed, all the changes to all the related records we modified get saved together. Likewise, upon Revert, all the changes get dropped and the database is safely in the state it was before we started the modifications.
Jan addresses a couple of issues that could cause problems, but his approach is simple to implement and has some advantages, as well. And be sure to read the comments to learn about deleting related records in this setup. And be sure to download the linked example file, as well.