Let’s say I have two tables:
Customers
Products
In products table I have a lookup field to customers for selecting which customers bought that product.
Now, in customers table I want to see which products they have bought. How can I do that?
1 Like
Do you have a sample document you can link? I wasn’t sure about several points, so I made some assumptions for this solution. My two tables look like this:
I setup the Purchasers column in the Products table as a Select List formatted column, and allowed for multiple selections and to use the Names column of the Customers table as the list of options to select:
After selecting some names for each product, I then added the following formula to the Products Bought column of the Customers table:
Basically, this formula looks at the the Purchasers column of the Products table, and filters the column to only show rows where the Customer’s name is contained.
Hope this helps.
2 Likes
Thank you, @Joseph_B
The solution you have provided is almost close to what I need.
From the above solution, I want to select products bought from Customers table as well.
For example:
In Products Table, if I select, Kevin and Jane for Wrench Product then it should be automatically get updated at Customers table.
and in Customers table, I should be able to select the products bought and it should be automatically be updated at Products table as well.
Got it. The approach I have for this makes some assumptions and might not be as clean as you want. Others may have alternate or better solutions.
First, I updated the above tables by adding a row (screwdriver) to Products to highlight some of the changes, and added two columns to the Customers table.
The ManualInput column is a Select List formatted column that operates the same way as the Puchasers column in the Products table, but in reverse:
The AllProducts Column looks at both the ManualInput column and the Products Bought column and combines them into a single list limited to unique values:
The idea behind this is if you want to manually input a purchased product, you can do that in the ManualInput column. If you input the product on the Products table instead, they will show up on the Products Bought column. The AllProducts column looks at both these columns and combines them into a single list using the ListCombine() function, and removes duplicate values using the Unique() function.
You can repeat and reverse this process on the Products table to have a column that automatically looks up the information and a place to manually input the information.
If you use this approach, you should be able to hide the automatic column (e.g. “Products Bought”) and just leave the ManualInput column and the All Products column. You can also use the Views if these additional columns are bothersome.
If you need to account for quantities, that should be workable too, just need some additional modifications.
Be careful of a potential pitfall we noticed on another thread - trying to add the formula from the Products Bought column as the default value on the ManualInput column. This formula in a Select List-formatted column will work for existing values but will not continue to update, so it is not a good approach for data that may grow or change.
1 Like
Thank you for the detailed response.
However, I think it’ll be quite clumsy to work this way. I was looking for functionality similar to AirTable which by default allow to do what I am looking for.
2 Likes
Pawan_Agrawal, This is actually a really good question, and was valuable for me to put time into figuring out, so thank you for asking. I learned about one way to do it, but as of yet it does not seem to be bidirectional.
What you’re looking for is this:
Where if you click on either the Items Bought column you get a selection pop up, and the same for the Customers column.
But changing the contents of the Customers.Items Bought column doesn’t change the Purchases table unfortunately.
I can only get it to work unidirectionally, let me try something else though and I’ll report back (if I’ve learned anything with Coda, it’s that if you can’t get something to work with two tables, it’s probably because you should use one).
1 Like
1 Like
Hey CODA team, any insights about a “reverse lookup column” coming up in a next update of CODA ?
Airtable has it at its core and it’s really powerful to have … as well as having the possibility not to have it too (like it is now). Ideally, both should be available. Don’t know if it’s technically possible tough.
3 Likes
I have been struggling with many many relationships and I think the reverse lookup would solve everything! I too was using airtable and could keep it all in sync with many to many relationships but
coda seems more limited this way. Does anyone know of a hack using automations to do this?
Hello everyone,
You may want to consider this schema when working with this problem, thanks!