Many to many lookups and keeping it all in sync question

[Aaron_Westberg]

Hi All,

Forgive what maybe a very simple question but I can not figure out how to create a many to many relationship between two tables.

I have two tables - Tasks and Projects. Both are many to many relationship.
Can i create a lookup column in each table or would I need to have two lookup columns to make this work. It seems when I update one lookup it does not update on the other table. How do i keep it all in sync with using lookups and many to many relationships?

[Jean_Pierre_Traets]

Dear @Aaron_Westberg,

Rather sure that his post gives you the right direction:

Reverse Lookup to Table

Of course, you can do the one way table, which works just fine: [image] This is done like so: [image] [image]

[Aaron_Westberg]

Thanks for the response. I would also like the [Items Bought] column in the customers table to be a lookup to the [purchases] table and not using formula referance because formula referance is read only.
So whether I add [items bought] to the customer table or add [customers] to the purchases table it all stays in sync. My thinking is that I need another table in between both tables as the intermediate table to establish the many to many relationship and have it all stay in sync. Am i correct in this assumption?

[Jean_Pierre_Traets]

You’re welcome,

Yes you are right, you could call it transaction table.

I suggest to use a transaction ID where one or more customers can purchase one or more items on a repeatedly base.

And accordingly you can make a summary table to get the data needed accordingly your business logic.

Start with a small fictive simple setup to play and get the logic.
In case you have more questions popping up, feel free to share a copy of the doc and explain your expectations for the outcome.

Success

[Aaron_Westberg]

Thanks again Jean,

I am struggling with how to setup the table lookups and formulas. I have three tables ‘task’ ‘projects’ ‘link’
my transition table is the link table. How would i setup lookups between the tables so that I can add many tasks to one project and also add many projects to one task while keeping it all in sync?

[Jean_Pierre_Traets]

Dear @Aaron_Westberg,

I hope that the below explains better how it works
Feel free to play around and experience how the updates are immediately visible

You can add as many projects and tasks as you require, no need for more tables

[Aaron_Westberg]

Thanks again Jean for all your help! This community is amazing I see what you are doing with the example you shared with me but am still puzzled on how to add tasks to the all_task table directly and be able to select the related_project at the same time while adding a task. The related_project field is formula based and does not let me select from a list all the projects that relate to this new task. it appears as read only because of formula being used. Can i use a formula but still allow users to select from list of options? Thanks again !

[Jean_Pierre_Traets]

Dear @Aaron_Westberg

I have added buttons to add projects and tasks.
I configured it in this way, so you can assign immediately the related tasks to the project.

[Aaron_Westberg]

Jean,

Once again thank you for your help! This is not quite what I was after however. While in the all_projects table I want to be able to add tasks within all_projects using lookup function. This part is good and works. But then I also want to open up a individual task from all_tasks and add an additional project to it and have it show up in the all_projects related_tasks column. Im not sure how to do this. HOpe I am explaining this well as I am new to code and Coda.

Thanks again!

[Jean_Pierre_Traets]

Dear @Aaron_Westberg,

For the moment I don’t see a solution, maybe because I am not able to focus for this moment on the subject.

Let’s see if @BenLee / @mallika or @anybody else is able to give a helping hand

[Aaron_Westberg]

I believe the solution is using a third transaction table but I cant figure out how to set it up in coda.

[Dalmo_Mendonca]

Hi Aaron, I believe this scenario is quite similar to what you’re looking for:

Grouping and Select Lists

Just did some playing around with this idea. The problem lies in that Coda is unable to show the same row twice in a view. So to achieve this: You really have to have two rows for item 1, two rows for item 3 and so on. In short, one row for each combination movie-tag. So in theory the best solution is to have a big table for all combinations and then just make views from it. The caveat here is you lose many features like drag-and-dropping or using select lists. I was able to create a semi-d…

[Aaron_Westberg]

Dalmo,

Thanks for sharing this semi work around. I see this as one of the biggest new features that Coda could do. Airtable allows for many to many using a third table. If Coda could create a reverselookup this would really make the product much more powerfull! I hope this is on the roadmap ?

[Benjamin_Shepherd1]

I completely agree with this. I have asked for it countless times. There is a similar product called Notion that does this, the issue is that Notion does not have all of the other features notion provides.