Selecting right contact for an order
I have an orders table. It is linked to a companies table. Companies is then linked to Reps and Contacts. Sitting between companies and reps, and companies and contacts, are join tables. In the companies table I select the rep assigned to the company (note: I use a join table as there are many reps at a rep firm, but I need to select which rep is assigned to the company).
I have a portal on the companies table that shows all the contacts associated with the company. I use a join table because often the company (in this case, a radio station) is part of an owner group. The owner group has many stations, but the personnel from the owner -- sales, accounting, production, etc. -- are usually assigned to more than one, but not all, of the radio stations.
In orders I select the company. If the company has an outside rep firm, then the order has to go to the rep contact; if there is no rep, then the order is addressed to the sales contact. Here is my problem, the rep firm shows up fine, but the contact does not. I am assuming that is because while there is only one rep contact joined to a company, there are numerous contacts that are joined to each company. I tried creating a portal of contacts in the orders report and filtered it by the contact type field. That works for showing up the contact, but then the rep does not show up.
Here is a schematic of the joins: