Hi - struggling with a lookup capability and hoping the community can help. I have two DB tables (well, more, but for this purpose 2): DB Companies and DB Contacts. I have lookups in the tables to each database. Thus in DB Contacts → DB Companies and in DB Companies → DB Contacts.
From one table, I can perform a look up search in the row, and if the value is not found, I can add a row to the second table:
Click Lookup row “DB Contacts” in “DB Companies”.
Type search criteria; when not found option to + text.
New row is added to DB Contacts.
However, what is missing is the “DB Companies” reference in the new row. Meaning, when I browse to the new row in DC Contacts, there is not a reference back to the DB Companies row that originated the new row.
What I would like is to have the new row in DB contacts be automatically populated with the company from DB Companies. That seems to work seamlessly in Notion, but I can’t figure out how to do that in Coda.
You can then use this column like a normal lookup. In the demo below I have used it to pull the company location from DB Companies as a related column.
Option 2: Automation
The best workaround is Automation.
It is set to trigger when the DB Contact Lookup column in DB Companies is changed, either by setting to an existing contact or when a new entry is created and set.
The formula for the IF section is as follows:
This will stop the automation replacing existing links although this might be something you want to happen in which case just leave it out.
The formula that runs when the trigger happens and the conditions are met looks like:
Wow @Dale_Cowling, that is a fantastic reply - super helpful, thank you.
In Option 1, as I interpret it, while the referenced-by column type auto-populates the column from the lookup, we have a second column that is not auto-populated. Is that accurate (that’s what my testing seems to show)?
For Option 2, this does seem to be the best option, although more complicated. I’m going to experiment with that a bit to see if I can make it work.
You are absolutely correct with your interpretation. Every link to another table would need two columns in each table. One is a manually set Lookup and the other is the Reference that populates based on the lookup.
table1
Name, Lookup ([table2]) , Reference ([table2] where Lookup column is set to thisRow in this table)
table2
Name, Lookup ([table1]), Reference ([table1] where Lookup column is set to thisRow in this table)
The issue with this method is that if you set a Lookup in table1’s Lookup column it will not populate the Lookup column in table2 but will show in the reference on table2. The same in reverse, adding a row from table1 in table2’s Lookup column will show in the Reference on table1 but not the lookup.
Using this method you will end up with some empty rows in the Lookup column on both tables but the combination of both columns means that you will also have a link to the other, albeit in a not so great way.
It all depends on what you are trying to do with these links and how far you want to go.
Every other option I can think of involves manual steps and would require a change to the way you work.
I agree that Automation would be the best route to go if you have the automation quota to spare.
Let us know if you are able to get Automation to do what you need it to do.
