Using a Calculation to fill in foreign key field
An apologetic preface: this is my first attempt working with FMP and I haven't done any kind of coding in about a decade so I'm sorry if this comes across as stupid but here goes...
I have a "child" table which lists different transactions. I need to use an identifying string of numbers (14 characters) from this table to be able to automatically link it to the corresponding record in the "parent" table (I want the calculation to spit out the proper value into the foreign key field). The field that I want to compare to in the "parent" is 10 characters. I already figured out how to truncate the last 4 characters from the child record that I don't need so both values are now 10 characters.
Is there a way to compare the "child" value to every record in the "parent" and come back with key value to link them?
If that's a little confusing maybe this is easier to see:
If ChildRecord = ParentRecord Than ForeignKeyField = x
...I just thought of this... Can I just set up a new relationship between the two tables using the fields I'm trying to compare? That would probably remove the need for this calculation at all eh??
Thank you to anyone in advance for taking the time to help!