hello everyone! . greatly appreaciate your thoughts and calculation suggestions in this scenario of mine.
in my FMP file, my main table, i have an S/N field, the unit type, vendor, and module ID (in my main table):
the S/N is always a 17 alphnumeric S/N: ABC123456789TEST1K.
here's the breakdown:
- the "TEST1" of said S/N indicates the unit type, the vendor, and the module ID. in a separate table called "family," i have 4 fields respectively named (of course, the first field is the unique ID): indicator ID, unit type, vendor, and module ID.
|Indicator ID||Unit Type||Vendor||Module ID|
|TEST1||Type 1||Vendor 1||mID-01|
|TEST2||Type 2||Vendor 1||mID-02|
|TEST3||Type 3||Vendor 2||mID-03|
|TEST4||Type 4||Vendor 3||mID-04|
|TEST5||Type 5||Vendor 4|
- i need to come up with a calculation and/or script that will lookup the "TEST1" part of the S/N in the "family" table and auto-enter the necessary info in their respective fields in the main table.
as for the relationship graph, not sure if a primary key and foreign key is useful in this scenario (please correct me if i am mistakened) so i related the field "Indicator ID" of the "family" table to "Unit Type" of my main table.
i've been messing with Middle(text;start;numberOfCharacters) and Lookup but not sure if that's even a good place to begin to try and come up with some sort of calculation and/or script. i think a script trigger of OnObjectModify on the S/N field is probably better? i'm still very new to FMP and still learning it's nuances in particular it's scripting, and calculations (two areas that i want to be consitently good at).
thx in advance, and i greatfully appreciate all your assistance.