6 Replies Latest reply on Nov 24, 2015 1:15 PM by Appogee

    How to Create Multiple Relationships between Two Tables?

    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.  

       

      ChassisDataExtract.JPG

       

      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.  

        • 1. Re: How to Create Multiple Relationships between Two Tables?
          wimdecorte

          can you post a screenshot of your graph?

          • 2. Re: How to Create Multiple Relationships between Two Tables?
            Appogee

            wimdecorte Sure, here it is, as requested.

             

            Relationships.JPG

            • 3. Re: How to Create Multiple Relationships between Two Tables?
              jbrown

              Good morning.

              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.

              FileMaker is using the relationship from your MechsInventory table to the chassis table to pull an attribute. Since the relationship is by chassis only, FIleMaker pulls the first record's data (which is what you saw).

               

              So to allow FM to pull the correct information, set up a new relationship to that chassis table, relating from inventory both the chassis and the variant. That will all FM to 'see' the attributes of the record that has a chosen chassis and a chosen variant.

               

              Note: you can have multiple TOs of the same table, and relate each of them differently back to the same table. That is perfectly allowable, depending on the circumstance. In your case, you need two, one to show the variants of the chosen chassis, and one to show the attributes of a chosen chassis and variant.

              Just add that row to your relationship-setup and have two fields that are part of the relationship.

              • 4. Re: How to Create Multiple Relationships between Two Tables?
                Appogee

                Thanks jbrown, much appreciated.

                 

                Here's my new Relationship Graph.  Have I done it correctly?

                 

                I renamed the second TO as TblChassisDataAndVariant, even though it refers to the same database as TblChassisData.  I hope this wont matter.

                 

                Now, I assume, in my scripts where I want to take Variant-specific data, I just refer to those fields as TblChassisDataAndVariant::FieldName?

                 

                Thanks again for your help.

                 

                Relationships2.JPG

                • 5. Re: How to Create Multiple Relationships between Two Tables?
                  jbrown

                  Afternoon.

                  Having multiple TOs of the same table (with different names) is a fundamental part of db development. I am working on a system that shows the academic years of a student. I have one TO to show all of them, and one TO to show the current one. I believe this is quite common.

                   

                  Your relationship graph looks correct. Its good that you named the 2nd TO something easy to understand.

                   

                  And yes, your scripts / portals / fields will need to reference this TO. That way the correct data is pulled.

                   

                  SOMETHING TO CONSIDER:

                  The fact that you've chosen a chassis and a variant leads you to know the attributes. You could lean towards a normalized database by simply having the related fields (the attributes) through this second TO on the inventory layout rather than copying the data over into the inventory record.

                   

                  This would only be workable if you want to see an update made to one of the attributes ripple into all the inventory records that have that related chassis / variant.

                   

                  This is very common in an invoice database: a customer's address is found in the customer section. Oft times it gets placed on the invoice. Should that invoice show always the customer's current address, or should it show the address of the customer at the time of the order.

                  I consider this a lot as I develop systems: is this related data worth copying over into the new table OR can I just show the related field (through the proper TO), if applicable.

                  • 6. Re: How to Create Multiple Relationships between Two Tables?
                    Appogee

                    Thanks again jbrown both for your answer and your advice.

                     

                    I too was considering whether I needed to copy the data across to the main inventory, or whether I should just keep cross-referencing it.

                     

                    I decided I will copy it because:

                    • under some specific circumstances, users are able to modify some attributes of their Mechs (building fighting robots is a very complicated business LOL!)
                    • this is a "one database per user" kind of app, so there's no particular need to minimise storage space (even though I know that's what a good developer would do)
                    • When I start performing Reports from the data in the inventory, looking for Mechs which meet certain criteria, or which have certain attributes, it will be easier to construct the queries if every Mech has its own unique data in each field.
                    • I'm not yet confident in my own abilities to manage relational databases .

                     

                    I hope I'll never need to 'ripple' attribute changes through the entire inventory.  If I do, I guess I will need to do something inelegant like run a one-off script which updates the Inventory.

                     

                    Thanks again for your advice, I really appreciate it!