AnsweredAssumed Answered

a secnario and your calculation suggestions

Question asked by isamudysan on Apr 15, 2014
Latest reply on Apr 17, 2014 by isamudysan

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):



Unit Type

Module ID


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 IDUnit TypeVendorModule ID
TEST1Type 1Vendor 1mID-01
TEST2Type 2Vendor 1mID-02
TEST3Type 3Vendor 2mID-03
TEST4Type 4Vendor 3mID-04
TEST5Type 5Vendor 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.