2 Replies Latest reply on Feb 28, 2011 3:04 PM by Kat11_1

    Looking up and connecting service location to a customer

    Kat11_1

      Title

      Looking up and connecting service location to a customer

      Post

      I have two tables. Customers and Service Locations with a unique id field in each. I created a table occurrence for each (CustomerLocations) and (ServiceLocationCustomers). A Customer can have one or more service locations.

      I would like to be create a layout in whicch I would find an existing customer (or add a new one)  and be able to look up and associate service location(s) with this customer.

      I am not quite sure how to go about it. What kind of relationships I set up between the table occurances? (Do I just need one Table Occurance Customer Locations?

      Do I use a portal to show all the possible locations? If I can show the possible locations, how do I then "select" or assign the location to the customer?

      Or do I use Value List based on a field in the Service Location Customers table? How do I then "select" or assign the location to the customer?

      I am a novice user so this is quite a problem for me. Thanks for your suggestions and help.

        • 1. Re: Looking up and connecting service location to a customer
          philmodjunk

          I believe you have encountered the classic "many to many" relationship challenge. Any different customer can have multiple service locations and any given service location can be linked to multiple customers.

          If this is correct, you need a third "join" table so that you can link to many records when starting from either CustomerLocations or ServiceLocations.

          CustomerLocations----<Customer_ServiceLoc>-----ServiceLocations

          CustomerLocations::CustID = Customer_ServiceLoc::CustID
          ServiceLocations::SvcLocID = Customer_ServiceLoc::SvcLocID

          You can place a portal to Customer_ServiceLoc on a customerLocations layout and include fields from ServiceLocations inside the portal row to list service locations for that customer. You can place a portal to Customer_ServiceLoc on a serviceLocations layout, include fields from CustomerLocations and use it to list customers for that service location.

          Here's a demo file you may want to down load and examine. It matches Companies to Contracts, but if you renamed the tables to match what I posted up above, it would work for your as a starting point for this concept.

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: Looking up and connecting service location to a customer
            Kat11_1

            Thank you Phil. After a lot of scratching my had I got it to work in a Customer -----<Customer Service Locations ----------- Service Locations type of relationship.

            But I think that many to many relationship is a better way to go (sometimes one customer pays for one service and another for other and it is the same location)

            Thanks for the link. I will have a look.