Ability To Select Multiple Choices From One Related Table Field

Question

I have two tables: Extracts and Vendors with a relationship that allows me to select a vendor for each extract (field Vendor Name). I have been asked to modify this so that up to three vendors can be selected for each extract record and I'm not sure how to make this work.

I made copies of each of the Vendor fields I needed to replicate but when I select a vendor name for the first one, the fields for vendor 2 and vendor 3 are automatically being populated with the information for vendor 1. except for the actual vendor name. I also tried creating the fields for vendor 2 and vendor 3 individually but if I do that, I don't have the ability to add all of them as look up fields in my relationship because I can only have 10 and I would end up with 18 - 6 fields for each vendor.

I've attached a doc showing my table relationship and a screenshot of the form showing what I am trying to accomplish. I want to be able to select a new vendor name for each one and have their related information populate.

------------------------------
Rachel Miller
------------------------------

evanmartinez · Answer

Hi Rachel,When two tables need to be able to have multiple relationships between them, in your example when many Vendors could be related to many Extracts, this can be accomplished using a many to many relationship. Instead of directly relating the two tables you create a third table that sits between the two tables (we call it a join table and often name it something like Assignment). Then every time an Extract is related to a Vendor you create a record that tracks the connection, then you could have any number of instances where a Vendor or an Extract could be connected. You can even include an embedded report right on your Extracts table from the new table that sits in the middle so that a user can go in and select which Vendors should be connected to the Extract. It takes a little set up but allows consistent reporting and more flexibility in how those two tables relate to eachother.------------------------------Evan Martinez------------------------------

Forum Discussion

Ability To Select Multiple Choices From One Related Table Field