Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
cvbdn

Create trigger to automatically add new rows in related table

Recommended Posts

cvbdn

I am hoping to get help creating a script or button that will trigger the creation of several rows in a related table.

 

I have 3 tables, a participant table, a studies table, and a join table between the two so I can have a many-to-many relationship between participant and studies (many participants can be in one study and one participant can be in many studies). When I add a new participants, I want to be able to click a button that automatically creates all the join table rows for every study listed in the studies table.

 

What is the best way to accomplish this? I am not sure where to even begin!

 

Thanks for your help!

 

Christina

Share this post


Link to post
Share on other sites
eos

The usual method is to read in the ID of the new participant, then capture a list of all studies and use a looping script to create the join table records.

 

From FM12 onwards, ExecuteSQL() is a handy method to capture that list, but seeing that you have FM9, you can do the same thing at the expense of an additional table occurrence.

 

Create a new table occurrence of Studies, and relate it to Participants using the x (aka Cartesian) operator; this means you have access to all Studies records from every Participant record, and you'll use this with the List() function.

 

Then on creation of a new participant, use a script like the following:

 

Set Variable [ $participantID ; Participants::primaryKey ) ]
Set Variable [ $allStudies ; List ( Studies_cartesian::primaryKey ) ]
Set Variable [ $alreadyAssigned ; List ( JoinTable::study_foreignKey ) ]
If [ ValueCount ( FilterValues ( $allStudies ; $alreadyAssigned ) ) = ValueCount ( $allStudies ) ]
 Exit Script
End If
Go to Layout [ JoinTable ( JoinTable ) ]
Loop
 Exit Loop If [ Let ( $i = $i + 1 ; $i > ValueCount ( $allStudies ) ) ]
 Set Variable [ $curStudyID ; GetValue ( $allStudies ; $i ) ]
 If [ IsEmpty ( FilterValues ( $curStudyID ; $alreadyAssigned ) ) ]
   New Record/Request
   Set Field [ JoinTable::participant_foreignKey ; $participantID ]
   Set Field [ JoinTable::study_foreignKey ; $curStudyID ]
 End If
End Loop
Go to Layout [ original layout ]

 

Note that this includes code to check if a ) all studies already have been assigned to this participant (and exiting the script if this is found to be the case), and b ) check on the individual IDs, and only creating a new record if that study is not yet assigned to this participant.

 

There are other ways to do this, e.g. using another loop before leaving the layout by filtering $allStudies one by one through $existing and building a new list – so we wouldn't need the If[] check later, because this new list only has unassigned IDs.

 

And on a final note: this entire scheme assumes that you are using unique primary keys in your tables.

Share this post


Link to post
Share on other sites
cvbdn

Wow! This worked like a charm. Just one more question for clarification. If I add studies to my table will I have to duplicate the studies table again in my relationship view or will it automatically update it?

 

Thanks again for your quick response!

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use