I’m trying to figure out a way to update a record in a related table when I change a value in another table.
Here’s my scenario: I have a table called Products, another table called Entries, and a third table called Sets. Sets are collections of entries (grouped together for testing purposes), with each entry corresponding to a single product. Each product can exist in multiple sets, and therefore have multiple entries.
What I want to be able to do is to allow users to nominate any Entry for advancement to the next stage in our testing. If a user nominate an Entry, that should also nominate the corresponding product. So, if I have a Toggle field in my Entries table, I would like a change in that field to potentially trigger a change to a corresponding nomination toggle in the Products table. So far, I have been unable to figure out a solution (I am able to update a toggle in the Entries table based on a change in the Products table, but I need it to go the other way).
Alternatively (and I suppose preferably for revision tracking purposes) would be to have another table called Nominations, where the toggle field in the Entries table would create or update records in the Nominations table storing each nomination plus additional data such as the timestamp of the nomination (or de-nomination). But I’m not sure how to set that up either so that a toggle field would be able to trigger the creation/update of those records.
Thanks in advance for any help.