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.