3 Replies Latest reply on Apr 23, 2012 2:43 PM by philmodjunk

    Many to many relationships

    RogerKnapman

      Title

      Many to many relationships

      Post

       Hi all,

      Total noob here.  I'm having a problem setting up a many to many relationship. Basically my organization has many departments at many campuses.  There are more than one department(customer) per campus, and any particular customer can have assets at several campuses.  There's no real unit numbers per address, as we are all one larger organization.

      I assume I need a join table but have yet to make this work.

      Can anybody point me to an working example?

        • 1. Re: Many to many relationships
          philmodjunk

          Are customers and departments one and the same thing? That's how I read:

          "There are more than one department(customer) per campus."

          • 2. Re: Many to many relationships
            RogerKnapman

            I am at odds on how to define entities.  Our whole organization is one or two departments. Seems to breakdown:

            Department/division/section

            However there are even smaller sub-sections that I call customers as I perform services for them. Basically,

            Location a customer a

            Location a customer b

            Location b customer a

            Location b customer c

            Location c customer a

            Location c customer c

             

            Something like that. I could fake a room number, but would likely not be able to get users to pick as it's meaningless. 

            I need to choose both customer and location at the same time like you can do with value lists, but then populate two fields at the same time. 

            • 3. Re: Many to many relationships
              philmodjunk

              This isn't something that has a clear cut answer based on what little you've shared so far. At the very least, you seem  to need

              Departments----<Customers-------<Customer_Location>-----Locations

              I'm using location as my term for "campus". Whether you also need tables for division and section is something I don't know from the info shared here.

              Set up your match fields like this, if you haven't already done so:

              Departments::DepID = Customers::DeptID
              Customers::CustID = Customer_Location::CustID
              Locations::LocID = Customer_Location::LocID

              With this setup, you can put a portal to Customer_Location on a customer layout to list all the locations for that customer. Fields from Locations can be added to the row of such a portal to supply additional info about the location.

              In similar fashion, a portal to Customer_Location can be placed on the locations layout to list all customers with that location.

              If you entable "allow creation of records via this relationship" for Customer_Location (called a join table), you can set up Customer_Location::LocID with a value list of Locations and you can then select an existing location in order to assign it to the current customer.

              Here's a demo file you can look at: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              What I have described here is called the "basic setup" in the join table.