7 Replies Latest reply on Apr 6, 2011 8:18 AM by philmodjunk

    Partially working relationship links

    miw

      Title

      Partially working relationship links

      Post

      I am stuck on a more complicated series of relationships amongst tables.  I have tried to get a better grasp of the issues in the FM Training Series, especially Module 3, but remain stuck.  I have attached a graph of my relationship tables but also let me try to explain what I am trying to accomplish:

      I have a series of tables and join tables all culminating with information related to a Product.  Each product belongs to a Collection so many products can be in one Collection record.  A Collection is defined by three main components:  a Country, Designer and Suppliers.  The Country and Designer combinations are unique and thus there is a join table for them, Join_CountryDesigner.  The Join_CountryDesigner table is linked to the Collection table.  Multiple Suppliers can be linked to multiple Countries and are managed through a join table, Join_CountrySupplier.  In the Collection layout, I first establish a link between the CountryDesigner combo and then select a valid Supplier, related to the Country selected in the CountryDesigner combo, to create a unique Collection representing Country, Designer and Supplier.  These relationships work fine and I have it well controlled.  On both my Collection and Product layouts, I can put a non-enterable edit box for Country::CountryName, Designer::DesignerName and Supplier::Supplier name and get the proper names for each of those respective record IDs.

      I also have multiple Facilities in a Country and they are managed through another join table, Join_CountryFacility.  A Product is specific to a Facility and not a Collection.  I can manage this easily in the Product layout whereby I can drill down to a Facility through the Country as defined in the Collection a Product belongs to.  These relationships also work well; I have no problem in the Product layout with an non-enterable edit box that can show me Facility::FacilityName based on the FacilityID chosen that is relevant to a Product's chosen Country.

      Each Supplier may have a sub-vendor or each Supplier may be its own sole vendor.  I have created a Vendor table where there may be multiple Vendors (or none) per Supplier.  The Supplier / Vendor relationships are managed through a join table, Join_SupplierVendor.  My issue is that a Product can be assigned to a Vendor and I only want it to be linked to an eligible Vendor as per a Supplier as defined in the Product layout (although Suppliers are first defined in the Collection layout which is in turn linked to a Product.  I do not want to define a Vendor in the Collection records).  My current Relationship links seem to work – I can get a Product assigned to an eligible Vendor and record the ID number for that Vendor in Product record through a ValueList with the first value field Join_SupplierVendorID::VendorIDLink; second value field Vendor::VendorName and for related values from Vendor_VendorbySupplier.  However, even though it records the VendorID properly in the Product record, I cannot seem to drill down for more information relating to that Vendor:  for example, in my Product layout, if I put an non-enterable edit box with the field Vendor::VendorName, I only get the first Vendor name linked to that Supplier and not the actual Vendor name corresponding to the VendorID that was chosen and recorded.

      What am I doing wrong in establishing my relationship between Product and Vendor whereby I can choose a Vendor in my Product layout based on the Supplier (note that the Supplier is chosen in the Collection record which is passed through to the Product record)?  It seems odd that I can properly select the Vendor ID but cannot get the proper information for that Vendor ID to pass through to my Product layout.

      DatabaseLinks1024.jpg

        • 1. Re: Partially working relationship links
          philmodjunk

          Can't see your relationships. Click the edit post link for your original message and try uploading your screenshot again so we can see it. You've got way too many join table controlled relationships to try to reconstruct from just the text.

           

          • 2. Re: Partially working relationship links
            miw

            New screenshot has been uploaded...

            • 3. Re: Partially working relationship links
              philmodjunk

              I don't think you need all these join tables. It doesn't seem that you have many to many relationships in every case judging from your description of what you are trying to set up here. If you can record the vendor ID directly in the Product table, there's no join table controlled many to many relationship between the vendor and the product. That you can enter a single vendor ID, implies that there is only one vendor possible for any given product record--a one to one relationship.

              If you can simplify your system structure to remove join tables except where you truly have many to many relationships, you'll get a much easier system to work with.

              Given a vendor ID value entered directly in the product table, add a table occurrence to Vendor and link it directly to the product table by VendorID. Now you can refer to this table occurrence to access the vendor data for that product.

              Another detail spotted that should be corrected:

              The Join_CountryDesigner table occurrence is not correctly linked to the Collection and Country table occurrences as needed to function as a join table. You should have these relationships:

              Collection::CollectionID = Join_CountryDesigner::CollectionID
              Country::CountryID = Join_CountryDesigner::CountryID

              • 4. Re: Partially working relationship links
                miw

                All the join tables do actually represent a many-to-many relationship.  And, as you note, the Product to Vendor tables are not many to many, rather it is a one-to-one relationship, with the Vendor selected for any one product being one of potentially many vendors which is dependent on the Supplier relatoinship for that Product from the Supplier table.  Your third paragraph is spot on: I would like to choose a VendorID directly in the Product table but how do I limit the selection to only those eligible Vendors that are linked to the Supplier for that ProductID?

                • 5. Re: Partially working relationship links
                  philmodjunk

                  You can set up a conditional value list for this.

                  Set up your value list to list vendor ID's from Vendor_VendorSameSupplier

                  Specify "include only related values starting from Product."

                  If conditional value lists are a new concept: 

                  Custom Value List? (forum tutorial)

                  http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (Knowledgebase Article)

                  • 6. Re: Partially working relationship links
                    miw

                    I have linked my Vendor table directly to the Product table (Vendor::VendorID = Product::VendorIDLink).  I did not break any of the other links in my relationship graph, merely added the forementioned one-to-one as you suggested.  I also set up the conditional value list as suggested.  However, I get a double listing of the eligible Vendor IDs. Also, when I make a selection, the VendorID doesn't always match to the VendorName.  Other than establishing the one-to-one as recommended, do I need to rejuggle the relationship chart?

                    • 7. Re: Partially working relationship links
                      philmodjunk

                      You may have issues with the data already entered into your tables.

                      However, I get a double listing of the eligible Vendor IDs. 

                      A double listing in what? The conditional value list? Are both the vendor ID's and vendor names fully identical? Such a value list should drop out duplicates from the value list.

                      Also, when I make a selection, the VendorID doesn't always match to the VendorName.  Other than establishing the one-to-one as recommended, do I need to rejuggle the relationship chart?

                      If the vendorName is from the new table occurrence I recommended and you aren't getting the expected VendorName, you'd better check the data in this table to see if you have more than one record with the same vendor ID.

                      Did you correct the other error I pointed out with the Join_CountryDesigner table occurrence? I'm concerned that you may have records in join tables that do not correctly link records like they should here.