8 Replies Latest reply on Dec 17, 2014 7:49 AM by Stu412

    Many addresses, one customer



      Many addresses, one customer


      Hi there, 

      I'm working on displaying many addresses for a single customer.  A lot of our customers have multiple sites.

      I could create AddressX, CountyX, CityX all in one table where X is a number being the customer site but this would lead to a massive number of columns.

      I'd rather create a table where I have columns for clientID and addressID, address details, address type and enter address type as Primary Address, Billing Address, Depot Address etc.

      I'd then like to display this information on my customer layout with the Primary details showing by default and perhaps a popover for the other details.

      Any help appreciated, thanks.


        • 1. Re: Many addresses, one customer

          Primary details? Does that mean default/primary address?

          • 2. Re: Many addresses, one customer

            That's correct yes.  Customers have a primary/default contact address and thereafter several other addresses we need the details of.



            • 3. Re: Many addresses, one customer

              Sounds like you need a related table off addresses. These addresses can still be displayed in column format even if you use a related table by setting up the appropriate portals to your address table.

              • 4. Re: Many addresses, one customer

                I agree with PhilModJunk that you need an Address table related to your Customer table. For each type of address you have — Primary, Billing, Depot, etc. — is each customer limited to one address of that type, or can they have many? In situations where a customer only has one address of a certain type — usually Primary or Billing — I add a foreign key to the Customer table that can only point back at one Address record at a time, rather than setting that as a type field in the Address record.

                • 5. Re: Many addresses, one customer

                  I've seen it done both ways, but using different foreign keys is definitely a better option if you need to enforce a "link to only one" policy.

                  • 6. Re: Many addresses, one customer

                    Hi there

                    Many customers will have multiple depots, but only one default and/or billing address.  What I'm thinking is to use foreign keys and have something (I've not decided quite what) along the lines of Depot 1, Depot 2, Depot 3 etc...

                    • 7. Re: Many addresses, one customer

                      I would use one filtered portal to all "depot" addresses if at all possible as that makes for a much simpler and more flexible way to handle this data. What will you do if you set up foreign key links to Depot 1, Depot 2, Depot 3 and you get a client with 4 depot addresses? With your approach, you'd have to add another foreign key field and modify layouts to use it. With a portal to all Depot addresses, you just add another record to appear in the portal.

                      • 8. Re: Many addresses, one customer

                        Hi Phil

                        This looks like the approach I'm taking just at this moment.  I've tweaked things so I have two address types only, a primary (or default) and "additional" addresses.  I have these on a custom list which I don't anticipate will grow much, if at all.  I've created a portal on the customer layout based on the addresses table which has a join table back to customers.  A dropdown based on the custom list allows the end user to add the address type.  

                        Thanks for your help on this:)