AnsweredAssumed Answered

How to Create Multiple Relationships between Two Tables?

Question asked by Appogee on Nov 24, 2015
Latest reply on Nov 24, 2015 by Appogee

Hello Filemaker Gurus.   I have returned to your font of wisdom with what I am sure is a noob question, to which I've been unable to find what is likely a simple answer.  


I am creating an app which will record, sort and report my collection of "Mechs" (robots that I use in an online computer game).  


Each Mech has various attributes, based on two basic classes that they belong to.  

  • "Chassis" is their main class, and
  • within "Chassis" there is a sub-class called "Variant".  

There are about 150 Chassis, and each Chassis has been three and seven specific variants.  Each Mech also has 20 or so other attributes, many of which are determined by the Chassis and Variant.


To make the task of entering the data about the Mechs easier, I want my Filemaker app to lookup each Mech's attributes from a table, once I have selected the Mech's Chassis and Variant from drop down lists.   I then want Filemaker to auto-populate the fields in each item in my database inventory based on this selection of a Chassis and Variant.


So, the workflow for entering data about each Mech looks like this:

  1. Open a new record in the TblMyMechInventory.
  2. In the Chassis Field, the user selects the appropriate Chassis from a drop down list showing all Chassis.
  3. In the Variant Field, the user selects the appropriate Variant from a drop down list showing only the Variants available for the Chassis they selected.
  4. Filemaker then auto-populates all the other fields for the record, based on the user's selection of Chassis and Variant.


I have successfully got steps 1, 2 and 3 working.  I did this by creating a relationship between TblMyMechInventory and TblChassisData, using the Chassis field in each as the inter-related field.  


However, I can't get step 4 to work.  I auto-populate the other fields in TblMyMechInventory using a script.  However, the value that Filemaker retrieves from my TblChassisData in that script is incorrect.  It seems to always be the value for the first Variant in the list, not the Variant that the user selected.   Put another way, Filemaker successfully retrieves both the Chassis and the Variant from the related database.  However, when I try to retrieve an adjacent attribute from the correct row table of the table for the selected Variant (for example, trying to retrieve TblChassisData:Special) it incorrectly retrieves the attribute from the row relating to the first instance of the Chassis, not the row relating to the specific selected Variant.


Here's a screenshot of TblChassisData, which shows the data relating to each Chassis and Variant.  




I applaud you for reading this far down .  


I'd really appreciate your advice on how to have Filemaker retrieve the correct attributes for each selected Variant.