Multiple Conditional Multiple Choice from a table to another table.

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

Hello all....

Can someone explain me, how can I have different fields on a table related to the same multiple choice field of other table. Basically:

Three tables - Site, Main Contractor and Sub Contracto.

Each Site have one Main Contractor and may have many different Subcontractors for each of its tasks.

Main Contractor 1-to-Many to Sub Contractors ( Each main contractor have its own subcontractors)

Site many-to-1 Main Contractors (many sites belong to one Main Contractor) and Site Many-to-1 Sub Contractors.

I can have the correct lookup field in sites from the main contractor - OK

The issue is that I need to have the possibility to choose different subcontractors (related with the One Main Contractor) for the diverse tasks of site.

Main Contractor Table
Main Contract 1
Main Contract 2
Main Contract 3
Main Contract n

SubContractor Table
Main Sub
Main Contract 1 SubContract ALPHA
Main Contract 1 SubContract BETA
Main Contract 1 SubContract CHARLIE
Main Contract 2 SubContract ALPHA
Main Contract 2 SubContract TANGO

I would like to have my Site Table as the follow:

SIte - Main Contractor - Sub Task1 - Sub Task2 - Sub Task3 ..... Sub Task n
Site1 - Main Contract 1 - ALPHA - BETA - ALPHA - Charlie
Site2 - Main Contract 1 - BETA - BETA - BETA - BETA
Site3 - Main Contract 2 - ALPHA - TANGO - ALPHA - ALPHA

I hope that I was clear.....

Thanks for the help and ideas.....

Photo of Joao


  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of Leanne


  • 354 Points 250 badge 2x thumb
Hi Joao,

I'm having the same problem. My work around right now is creating multiple versions of the same relationship with the reference material filling the new field. for example:

Relationship = Main contract to Sub Task 1

Relationship = Main contract to Sub Task 2

Etc. The look up field is the same but the reference is now populating a field called SubTask 1, the second relationship populates the SubTask 2.

Don't know if this is the best way to go about it (because it really creates a lot of relationships) but the need to have additional fields in a table populate with the same information from a separate table seems like a pretty important action that I have yet to see a good answer for! Let me know if there is a better solution!

Photo of Joao


  • 0 Points
Hi Leanne,

Although not the best solution, was the only one that I also found.

Thank you