Question Regarding Many to Many Relationships

Coll-ad2f · August 11, 2021, 5:32pm

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.

Thanks!

Collin

Alyssa · August 12, 2021, 8:58pm

Hi @Coll-ad2f, nice to see you here! I'm happy to show you how to set this up.

First, here's a Project_Parties table, which has two columns that are rowlinks to two different tables (a Parties table and a Projects table). This allows for multiple projects to be associated with multiple parties:

For the Project table, there's a column for the name and a column with a FILTER formula which, returns all of the parties associated with the project. For my example, the formula is: =FILTER(Project_Parties,"Project_Parties[Project]=[Project]")

I did a similar set up for the Parties table, where I have the name and a column that filters the associated projects, in case that is helpful elsewhere in your app:

In Builder, I set up a screen with a field to allow a user to select a project and a list of the Parties table. For the source of that list, I point to the filter in the table we created above (which displays all of the parties). In my example, the variable is $[Project], however yours may be $[InputRow] from what you described. The formula in this example to call the column with the filter is: =$[Project][All Parties]

Now you have a screen where a project can be selected, and it will list the associated parties

Hope this helps! Let us know if you have any further questions.