I have been working on a project and have run into a problem involving many-to-many relationships. I have a PROJECTS table and a PARTY table. A party can be associated with one or more projects, and a project can be associated with one or more parties. I believe I will need to create a many-to-many relationship. I have read the Data Modeling 101 article which describes one-to-many relationships and many-to-one relationships, but I am struggling to implement a many-to-many relationship. Is there a way to make many-to-many relationships like the one I am describing work? If so, could you point me to any articles or discussions that describe such an implementation?
Here is what I have tried so far:
I have 3 tables; PROJECTS, PARTIES and PROJECT_PARTIES. My PROJECTS table has a Projects_Id column along with a few other descriptive columns. My PARTIES table has a Party_Id along with a few other descriptive columns. Then in my PROJECT_PARTIES table I have 4 columns, Project_Id, Party_id, Project_Connect (which contains a filter to the PROJECTS table) and Party_Connect ( which contains a RowLink to the PARTIES table.
The goal here is to allow the user to select a project and display in a column list the parties attached to that project so they can edit them. The filter I have created for my column list is:
(=FILTER(PARTIES, "PARTIES[Party_Id]=PROJECT_PARTIES[Party_Id] AND PROJECT_PARTIES[Party_Id]=$[InputRow]"))
However, it is not working as I would like when I pass my Project_Id to the filter in my column view.
I don't want to include a Project_Id in my PARTIES table, because I don't want to have to re-enter in the descriptive columns every time the same Party is associated with a new Project. I want to enter the details on a project only once and I want to enter the details of a party only once. I want to use the PROJECTS_PARTIES table to create a mapping between the two. Is this possible using HoneyCode?
I appreciate any advice anyone can give me on this.