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.
- Building of lead contact
- Phone of lead contact
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
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.