3 Replies Latest reply on Dec 6, 2016 7:25 AM by philmodjunk

    Retrieving data through a many to many relationship

    sophiegarrett

      I’m relatively new to Filemaker and I’m struggling with many to many relationships.  I’m running Filemaker Pro (not Advanced).  I have the three tables below, which I think are correctly set up and which have lots of data loaded and various portals that all look correct.

      There is a many to many relationship between Projects and Contacts, so I’ve created a join table called Involvement.  On Involvement, as well as the IDs for the projects and contacts table, there are several variables relating to the involvement, a key one being to identify the lead contact for the project. 

      Projects

      • ID
      • Name
      • Building of lead contact
      • Phone of lead contact

      Involvement

      • ID
      • ProjectIDfk
      • ContactIDfk
      • Lead

      Contacts

      • ID
      • Building
      • Phone

      On the Projects table and forms I would like to have the Building of the Lead Contact.  I’ve realised that if I simply link the tables together, Filemaker gives me a building, but if could be the building for any of contacts linked to that project (I can’t work what the logic is). Similarly, I want the Phone Number of the Lead contact, but again, Filemaker gives me the phone number for one of contacts linked to the projects, and not necessarily the same contact as the Building came from.

       

      I can see that I’m missing some logic to define this need to select the Lead Involvement for the Project. But I can’t work out a way to do this. I tried fiddling with the set up of the relationship in Manage Databases, but couldn’t see anything sensible.  I tried putting a calculation on the Projects/ Building of lead contact field and couldn’t immediately see a way of setting this up, so I tried using ExecuteSQL, with the following command:
       

      ExecuteSQL ("SELECT Contacts.Building

      FROM Contacts

      JOIN Involvement ON Contacts.ID = Involvement.ContactIDfk

      JOIN Projects ON Projects.ID = Involvement.ProjectIDfk

      WHERE Involvement.Lead = Yes";

      "" ; ""; Involvement::Lead)

       

      But I just get question marks in the field.   My SQL is very rusty and not helped by having no access to SQL outside of Filemaker to try it out on.  I’m far from sure that this is even the right way to solve the problem.

       

      Any pointers would be gratefully received.

        • 1. Re: Retrieving data through a many to many relationship
          David Moyer

          Hi,

          I'm no SQL expert either.  This is probably a typo ... I see that you added a variable to your ExecuteSQL statement - there should be a corresponding ? in the sql that is replaced with your variable.

          • 2. Re: Retrieving data through a many to many relationship
            erolst

            The string you're trying to pass isn't quoted; either use single quotes (SQL syntax), like

             

            WHERE Involvement.Lead = 'Yes'

             

            or make use of the optional parameter and pass a string in FM syntax

             

            sophiegarrett wrote::

             

            ExecuteSQL ("

              SELECT Contacts.Building

              FROM Contacts

              JOIN Involvement ON Contacts.ID = Involvement.ContactIDfk

              JOIN Projects ON Projects.ID = Involvement.ProjectIDfk

              WHERE Involvement.Lead = ?

              "; "" ; ""; "Yes"

            )

            With the above formatting, I find it easier to distinguish the SQL code from the wrapiing FileMaker function syntax.

            • 3. Re: Retrieving data through a many to many relationship
              philmodjunk

              A NonSQL approach would be to add more Table Occurrences of Involvement and Contacts to your relationships graph so that you can match to Involvement by ProjectID and Lead, then match to another TO of Contacts to show the building or other contact info. You can set up a calculation field with a text result that always has the value "Yes" to match to the Lead field in Involvement.