AnsweredAssumed Answered

Update portal records in related table if primary key changes

Question asked by msei on Jul 11, 2018
Latest reply on Jul 12, 2018 by jbrown


I'm using FMP 17 Advanced.  I plan to deploy this application using Filemaker WebDirect once I complete development.


I'm looking for a way to modify related records in a table (Workflow_Procedures), whose records are created from a portal located in a data entry table (Workflows).  My problem is this:  I can create new records in the related table via the portal without issue.  However, if the user modifies the primary key field (Workflow_Num) in the Workflows table, the relationship is broken and the related fields in the Workflow_Procedures table disappear from the portal.  They still exist in the related table of course, but their foreign key no longer has a match to a primary key; rendering them nomad records...


I'd like to create a script that gets triggered if the user tries to change the value in the primary key Workflow_Num field of the Workflow table.  It looks like OnObjectCommit could be an option, but that function does not show up in my scripting step list?  The script needs to trigger BEFORE the data is committed, but once the user has begun to edit the Workflow_Num field.  The script then needs to:


1. Obtain the original primary key value (Workflow table)

2. Obtain the modified primary key value (Workflow table)

3. Perform a find in the related table for the original primary key value (Workflow_Procedures table)

4. Replace the foreign key field values for all records in the found set


I understand that I will likely need to use a few global variables to accomplish my objective, but that exceeds my knowledge of FMP...  Any help will be appreciated.