2 Replies Latest reply on Mar 25, 2015 9:27 AM by KurtSnow

    Selecting Correct Contact

    KurtSnow

      Title

      Selecting Correct Contact

      Post

           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.

        • 1. Re: Selecting Correct Contact
          philmodjunk

               I have a number of "if" calculated fields to pull up the proper contact.

               I don't see how that would work. Calculations that refer to fields in a related record only reference the "first" related record. If you first contact is an "accounting" contact and your If calculation is: If ( Contacts::ContactType = "Sales" ;    then this will evaluate as false even though the second related record is a sales contact. The calculation can't "see" the data in that related record.

               I'm not quite sure what result you are trying to produce here. I get that you are selecting a company and then this makes it possible to see the Rep as this is apparently a many to one relationship (but then you don't need the join table)

               But I don't see how what you describe for 1) should have any effect on what Rep appears.

               What I think that I can deduce from your post in terms of relationships:

               Orders>------Companies--------<Join>--------Contacts
                                       |
                                       ^
                                    Join>------Reps

               Is that what you have? Let me know and then we can dig deeper here.

               But a few thoughts on those join tables. If there is never more than one rep for a company, there's no need for the join table. It's only needed if a company can link to more than one record in Reps while a Reps record can link to more than one company. And if a contact is never linked to more than one company there's no need for that join table either and for the same reasons. Of course, it can be possible for a contact to be linked to more than one company. A person can hold two jobs, or own two companies or be a "broker" that represents multiple companies... That all depends on your business model.

          • 2. Re: Selecting Correct Contact
            KurtSnow

            I apologize for the tardy reply, my work is seasonal which sometimes prevents me from working on my FileMaker projects.

            Here is a more complete chart of the relationships:

               Orders>------Companies--------<Join>--------Contacts
                                          |
                                          ^
            Rep Contacts---<Join>------Reps

            I have a join table between Reps and Rep Contacts as there is often many reps at a rep firm and I have to select which rep represents the particular media company.