8 Replies Latest reply on Apr 22, 2015 3:49 PM by philmodjunk

    Selecting right contact for an order

    KurtSnow

      Title

      Selecting right contact for an order

      Post

      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:

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

        • 1. Re: Selecting right contact for an order
          philmodjunk

          What I see as a data model and what I read in your description seems to contradict each other.

          For a given company, is there ever more than one associated Rep?

          You show a relationship with a join table that would indicate that a company can be linked to more than one rep and that a rep can be linked to more than one company, but then in your text, you state:

          In the companies table I select the rep assigned to the company

          Which would seem to indicate that a rep might represent more than one company, but that there is only one rep associated with any given company???

          • 2. Re: Selecting right contact for an order
            KurtSnow

            Sorry for the confusion. There is never more than one associated rep...there is only one rep associated with a company.

            • 3. Re: Selecting right contact for an order
              philmodjunk

              Then reps should be linked directly to companies without the use of a join table. A join table would allow you to link multiple reps to the same company while still being able to link multiple companies to the same rep.

              • 4. Re: Selecting right contact for an order
                KurtSnow

                Then how would I select the correct sales rep from the Rep company? There are many reps that work at the rep company but I have to select only one.

                • 5. Re: Selecting right contact for an order
                  KurtSnow

                  Any thoughts on this? A big chunk of my database is not working properly because of this problem.

                  • 6. Re: Selecting right contact for an order
                    philmodjunk

                    Select the Rep in a record from which table?

                    If there is only one Rep to link to that record you need only link an occurrence of the Reps table to the first table by a RepID--what I typically label as __pkRepID.

                    Keep in mind that you can create multiple Tutorial: What are Table Occurrences? of the same table in a FileMaker relationships graph as a way to define the needed relationships.

                    • 7. Re: Selecting right contact for an order
                      KurtSnow

                      There are many reps who work at one rep firm. That is why I have created a join table. Within my media company database I have to select the correct rep (from the join table) from the rep firm that works with the media company. For example, radio station KFBK in Sacramento is represented by Clear Channel. So, I have selected Jean Mihalek from three others at the rep firm Clear Channel as she is my rep. However, for this station I deal directly with the sales rep Joel Mordhorst. So, to indicate that I work direct, the boolean field "Rep" is not checked. See attached screen shot of my media company table.

                      • 8. Re: Selecting right contact for an order
                        philmodjunk

                        There are many reps who work at one rep firm. That is why I have created a join table

                        But this does not require a joint table--as I have previously stated here. You only need the join table if you have both of the following situations: 1 rep can work for/represent multiple companies AND one company can have multiple reps working for/representing one company.

                        I think that you are confusing the relationship needed for a conditional value list--a list that can limit the list of reps in a value list to just those from a single company with the relationship needed to show that a specific rep has been selected for a particular record in the order table. Same table, two occurrences and two different relationships.

                        Reps|Selected-----<Orders-----<Reps|byCompany

                        Reps|Selected::__pkRepID = Orders::_fkRepID

                        Orders::_fkCompanyID = Reps|byCompany::_fkCompanyID

                        Your value list of Reps would be based on Rpes|ByCompany with "include only related values starting from orders" specified. The _fkRepID field in Orders would be set up with that value list. When you select that Rep ID from the value list, you then link that record in Orders to just one record in Reps|Selected.