Record Serial ID to link Related Records
(Running FMP10 on OS X.6)
What I have is three tables (Raw Materials, Master Formulas, & Master Inventory). Inventory Records are related to one Formula, Formulas are related to up to 40 Materials. Right now, the related field is the Part # (for both relationships). The problem with this set up is that if a part # changes, the related record in the other table is lost.
What I am thinking of changing to is having a RecordID (serial on creation) for Materials & Formulas. Then, the relationship will have a corresponding field in the next table to be linked to. I can then pull the Part # field from the RecordID field. This is all working fine and I am comfortable up to this point.
From a user interface standpoint, the RecordID should not be seen, or even known. Just the Part # (mimicking the original relationship). Where I am getting stuck is setting up the RecordID to change accordingly when the Part # is entered or changed. I was thinking about a RecordCommit Script Trigger, but was wondering if there were a easier, more efficient way to do this.
Say if the Material Part # changes, that change should be reflected on all the Formulas it is tied to, so the Part # should be a auto-enter calculation? But it needs to be modifiable in the Formula.
Any ideas or thoughts would be greatly appreciated.