Partially working relationship links
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.