Selecting Correct Contact
I have an orders table. It is linked to a companies table. Companies is then linked to Reps and Contacts (however, sitting between companies and both reps and contacts is a join table, as one rep and contact can be assigned to more than one company). 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. I have a number of "if" calculated fields to pull up the proper 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, for example, accounting, sales, operations, etc. I have tried a couple of solutions to get the correct company contact to show up:
1) I created a portal of contacts in the orders table and filtered it by the contact type field. That works for showing up the contact, but then the rep does not show up.
2) In contacts I created an if calculation: if the contact type is "sales" then the contact field, otherwise blank. This does not work either.