1 Reply Latest reply on Oct 16, 2014 9:44 AM by philmodjunk

    Many-to-many relationship in same table?

    JeffMurchison

      Title

      Many-to-many relationship in same table?

      Post

      I'm wondering if this is possible. I currently have 3 tables - Quotes, Invoices, and Projects. They're all related via a join table like so:

      This allows me to link quotes to invoices, quotes to projects, invoices to projects, etc. It works very well. However, sometimes we have multiple quotes or invoices for a project. Right now, the only way to tell is to go to the related Project record and look at some portals I put there which show the related Invoices and Quotes.

      I'm looking for a way to see related items from the same table. So for example, if I have 2 Quotes for one Project, I'd love it if when I was browsing one of those Quote records, if I could see the other related quote in a portal or something.

      Is there an easy way to do this?

        • 1. Re: Many-to-many relationship in same table?
          philmodjunk

          Your different Quotes records for the same project don't have any match field values in common.

          You can link a new occurrence of quotes to the current Quotes table occurrence using the cartesian join operator and then filter your portal by the Project ID value in the join table...

          Or you can make a new occurrence of the join table, link it to the original by project ID Match Field and then link another occurrence of quotes to the new occurrence of the join table...

          Or you can set up an ExecuteSQL calculation in Quotes to pull up a return separated list of QuoteID's from the Join table, using a Where clause to specify the Project ID, then use this list of ID's as a match field to a new occurrence of Quotes....