AnsweredAssumed Answered

Record Serial ID to link Related Records

Question asked by BatchMaker on Feb 1, 2010
Latest reply on Feb 1, 2010 by ninja

Title

Record Serial ID to link Related Records

Post

(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.

Outcomes