I think what you need to do is create a relationship between the main table and the family table using all three fields (Unit Type, Vendor and Module ID) as the key.
To do this, go to the relationship graph and edit your existing relationship - you will probably have the MAIN table on the left and the FAMILY table on the right. The MAIN table has "Unit Type" highlighted and the FAMILY table has "Indicator ID" highlighted - change that to be "Unit Type" on both sides, then click the "Change" button. Now you're ready to add the next set of fields - select "Vendor" in each list and then click the "Add" button (you'll see another row below the list boxes with the new predicate displayed). Then select "Module ID" in each list and click the "Add" button again.
At the bottom of the dialog you'll see options for creating new records, deleting related records and sorting records. You want to allow records in the FAMILY table to be created, so check the top box under that table name and then click OK.
Click OK to close the Manage Database dialog box to save the changes.
Now you're ready to display the related FAMILY records on a layout based on your MAIN table. Go into layout mode and draw a portal on the layout - a wizard will then appear and guide you through selecting a relationship, displaying fields, etc. When you're happy with the portal, save the layout and go into Browse mode.
Click on a field in the first empty row of the portal - you'll pretty much only get something useful if you type in the Indicator ID field. FileMaker will automatically create the related record and store the new value there. You will notice that FileMaker has automatically entered values in the Unit Type, Vendor and Module ID fields; it does this to make sure the relationship evaluates to something that will be displayed in the portal (if it didn't, as soon as you exited the first field, the portal row would "disappear" and you would not be able to view the related record).
This is a bit rushed; if you need further explanation please ask.
thank you for replying, and i appreciate it very much
hmmm...portal does sound pretty interesting. hadn't thought of it. i wonder if i can use a script trigger in the S/N field, something like, e.g., set field [ main table::Unit Type; Middle(main table::S/N ; 12 ;4) ] to auto-enter the info in the Unit Type field of the portal. i'm gonna have to give this a shot when i'm back at work tomorrow as all the necessary info is there, LOL. i'll let you know how it's going.
Ah, OK, I misunderstood. You want a different setup, one that uses lookups:
Create a text field, gIndicatorID, in the MAIN table. Turn it into a global using the "Options..." button - navigate to the Storage tab. Make a relationship between MAIN and FAMILY where MAIN::gIndicatorID = FAMILY::Indicator ID (no need to create related records on either side).
Back in the MAIN table, set the Unit Type field options to lookup data from FAMILY::Unit Type (it's in the Auto-Enter tab of the dialog). For Vendor, set the lookup to be FAMILY::Vendor, and so on. Save the changes.
Now, on your MAIN layout, add a field object for the gIndicatorID. In Browse mode, type a valid value into this field; as soon as you exit the object the other fields will have their values set automatically.
NB: I have assumed that you don't want to keep the Indicator ID value in the MAIN table, so I specified a global field (one that keeps the same value no matter which record is active). If that's incorrect, turn off global storage.
this is getting better and better. however, is it necessary to have the gIndicatorID field in the Main table? i mean, will it be possible to have it work in the background seamlessly with a script trigger to auto-enter the indicator ID from serial number field to the gIndicatorID field? this is exciting! gonna have to give it a shot (since i have a test FMP file at home lol).
Wow, I really didn't read your original post properly, did I?
Rather than a global, make the Indicator ID field in the MAIN table be a calculated field with a text result (even after all these years I sometimes forget to change the result from the default type of number).
The calculation you want is:
Middle ( S/N ; 13 ; 5 )
This translates to "Starting with the 13th character in the 'S/N' field, get five characters" which will give you the Indicator ID.
Note that your S/N field name is likely to cause errors with the calculation engine since it includes the division symbol - renaming it to "SerialNum" is going to fix that issue.
Hope this helps,
If you want to do it with a script trigger, you would have the script take the same formula as above and store the result in a variable, then switch to a layout based on the FAMILY table occurrence, enter find mode, set the Indicator ID field to the value stored in the variable, perform find, set a variable for each of the Unit Type, Vendor and Module ID field values, go to the original layout and set field for each of the MAIN table fields to the appropriate variable.
Given the error checking you would need to add to this process, I think you'll find that the lookups solution is the easier and faster method.
hmmm....i guess my sample serial number is not 17 characters, lol. so, lets see....
set the relationship as such:
Main Table Relationship Direction Family Table Indicator ID < ------ > Indicator ID Unit Type < ------ > Unit Type Vendor < ------ > Vendor Module ID < ------ > Module ID
set the Indicator ID field, in the Main Table, as calculation -- Middle ( SerialNum ; 12 ; 4 ) -- for a 17 alphanumeric serial number
and then, set field options for Unit Type, Vendor, and Module ID in the Main Table to look up data from their respective fields in the Family Table.
lol, yeah, that script trigger does not sound appealing after reading your last post LOL
Depending on the version of FM that you are using, you could avoid the relationship graph entirely and use ExecuteSQL to retrieve the data from family.
i have FMP13 trial at home, while at work we're using FMP12 Advanced. you know, i've been thinking about executesql, just really don't know where to begine, LOL.
thank you stephen, i have downloaded it and will check it out either tonight or tomorrow
well, this worked out for me greatly; and, i appreciate yours and everyone's collective replies. i set Indicator ID as a calculation using Middle(text;start;numberOfCharacters). as for the fields of Unit Type, Vendor, and Module ID, i also set them as calculations by using the Lookup feature. somehow, it wouldn't lookup by using the "Options" as you have stated above daniel. in the relationships graph, i have the Indicator ID going from my Main Table to the Indicator ID of the Family Table. gonna be running tests on this solution for a while to make sure that it's supposed to do what it was designed to do, LOL.
again, my many thanks.