Forum Discussion

RachelMiller's avatar
Qrew Cadet
3 years ago

Ability To Select Multiple Choices From One Related Table Field

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

1 Reply

  • 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