AnsweredAssumed Answered

relation from any of several fields?

Question asked by fpreidel on Aug 20, 2018
Latest reply on Aug 20, 2018 by philmodjunk

I have a table "company" for my corporate contacts (architects, contractors, engineers), and a table "units' for addresses where work has been performed.

I need to accomplish the following:

1.  in "units" I would like drop-downs for the various trades...when entering an Architect for the work done, i want the dropdown to only list all the architects (not the contractors,...)

2. in "company" I need a portal to show all the "units" that the company has worked on, regardless of their role.


I created a workable solution for the first issue by creating unique field for each profession (fk_ArchitectID, fk_ContractorID,...) and creating unique relationships for each (Unit::fk_ArchitectID = Company::pk__CompanyID)



However, this creates a problem I have not been able to solve for second issue.

In order to create a portal for each "company" in which it shows each "unit" they worked on, the portal would need to matches any of multiple fields (Company::pk__CompanyID = Unit::fk_ArchitectID) or (Company::pk__CompanyID = Unit::fk_ContractorID)....

I have tried creating a Multi-Key field (Unit::fk_CompmanyID) that houses each fk_ID's for the varios trades, but that is where things go off the rails for me.


Any advise is much appreciated,

I am open to any suggestions, including restarting from scratch to clean this up.