9 Replies Latest reply on May 16, 2012 10:35 PM by PeterThorton

    Searching through join tables

    PeterThorton

      Title

      Searching through join tables

      Post

      Hello,

      I have a realtively simple problem. I have a table of customers. Each customer can have multiple addresses assigned to them. When the user is entering an order, they pick one or two addresses (billing and shipping address if they're not the same) and put them on the order form. So far it's easy.

      But some of our customers are actually dealers, and they have a set of customers to whom they sell our products. Sometimes the order has to be made to the dealer, with the dealer's data and billing address, but with their customer's address as the delivery address.

      I've prepared the data structure that supports this, it's in the picture. BusinessPartner is the actual table that stores my customers (and vendors, therefore the vague name) CustomerDealer is, as you can see, a join table. Turns out more than one dealer can sell to the same customer (and of course one dealer has multiple customers) so it's a many-to-many relationship. RelatedCustomer is just another table occurence of BusinessPartner (since the dealers' customers are also our customers)

      What I don't know is how to perform a search that would return the required records. I start with an ID (the ID of the dealer whose customers' addresses I want to list). I want to find all the addresses of all the customers related to this dealer (an entry in the CustomerDealer exists for the customer-dealer pair). How can I do it?

      CustomerDealer.png

        • 1. Re: Searching through join tables
          GuyStevens

          How would you like to display these related customers? In a portal or in a list view?

          In a layout based on your BusinessPartner table you could make a portal that is based on "CustomerDealer" that shows the CustomerId's for that particular DealerId.
          Add to that layout the BusinessName from the RelatedCustomer table and you should have something.

          Otherwise you might make a list view based on "CustomerDealer" that contains the CustomerID from the CustomerDealer table but the BusinessName from the RelatedCustomer Table.

          In a list view you might have to do a find for the "BusinessPartnerId" from the BusinessPartner table in the DealerId in the CustomerDealer table. This to display only one dealer and to see his related customers.

           

          I hope this makes sense, it sounds a little funky, I know. :)

           

          • 2. Re: Searching through join tables
            philmodjunk

            I'd be inclined to use one table for Contacts (dealers, customers etc.) for name, web site etc and a related table for addresses. And with yet another table for phone numbers and email addresses. This can be more flexible when a given contact might have many different phone numbers and more than two addresses.

            Working with what you have, a portal to customerDealer can be placed on a BusinessPartner layout. Fields from RelatedCustomer can be included inside the portal row to list addresses and other data.

            You can also create a list view layout based on CustomerDealer and perform a find on it for a specified DealerID (or Use Go to related records from the BusinessPartner layout). You can include fields from RelatedCustomer on this layout to list the addresses.

            • 3. Re: Searching through join tables
              PeterThorton

              Thanks DaSaint, I'll try it. But I don't want to list just the dealer's customers. I need to list all their addresses. And as you know, they are in a different table. (I made a mistake of not including the Address table in the screenshot)

              • 4. Re: Searching through join tables
                GuyStevens

                Sounds to me like you need to use a second TO of the adress table as the basis for your portal, and maybe relate the board member table to this secont Table Occurrence's BusinessPartnerId.

                The idea being that it's your adress table that holds every adress ad has a link to every Business Partner.
                So yo would get a list where you see the same business Partner multiple times, each time with a different adress.

                I'm not at home right now so I can't work this out in more detail, I'll look in to this a little more later.

                • 5. Re: Searching through join tables
                  GuyStevens

                  I made a little trial.

                  Could something like this work?

                  I tried it with portals to be as compact as possible.

                  http://dl.dropbox.com/u/18099008/Demo_Files/TwoPortalsWithSetField_V3.fp7

                  In the second tab you can both:

                  - Link contacts (or add linked contacts)
                  - Select contacts to see their adresses

                  Then you can set the button to do something with the adress.

                  • 6. Re: Searching through join tables
                    PeterThorton

                    Hi,

                    thanks for the demo. This approach seems to work, I've already solved it by using something similar. However what I originally had in mind, (and what I may still be asked to do) is list all the relevant addresses in one long list. The names of the related customers should be displayed along with the addresses in a special column. Ideally they should only have the customer name on the first address line belonging to that customer, but that's not a requirement. I think the list should contain both billing and shipping addresses, since the customer is likely to only have one address, listed as billing, but serving for both billing and shipping. I've attached a picture of what I mean, made by mashing up all the addresses from your example. I'm not sure I'll even need this, but as part of my FileMaker training I'd really like to know how to do this, for future reference.

                    • 7. Re: Searching through join tables
                      GuyStevens

                      I think we should be able to come up with something like that.

                      The way I did it now was to keep it as compact as possible.

                      I'll have another look at it tonight.

                      But that example of you looks like it needs a list view with a sub summary part.

                      • 8. Re: Searching through join tables
                        GuyStevens

                        Allright, this was a little bit of a tricky one, but I managed to find a way.

                        It required making a find based on a value list from another table. But it seems to work nicely.

                        The way it works is:
                        On a layout based on the table contact, when you click the orange button. A variable is created that is a list of all the linked contacts of the contact that is currently the active record.

                        A new find is created and in this find a new record is made for every contact in the list.

                        Then the find performs and shows you a list of all the adresses of all the contacts that are linked to the contact you came from.

                        It's a list with a subsummary part.

                        You could then use the select button to perform whatever script you want.

                        I should have renamed the file, but I forgot to.

                        http://dl.dropbox.com/u/18099008/Demo_Files/TwoPortalsWithSetField_V3.fp7

                        • 9. Re: Searching through join tables
                          PeterThorton

                          Thanks, I'll study it and try to emulate it, just to learn how it's done.