I want to have two tables with columns referring to each other that I can easily edit from both perspectives. As far as I know, this is only do-able by setting up automation that updates the other perspective if I edit one side. Given both scaling and the fact that automation is delayed, I would prefer a solution that isn’t relying on it.
So, formally, is there a way to define a Lookup From Table Column on two tables that reference each other (E.g.: Parent, Children; so for both One-X and Many-X relationships), that is assignable on both without using Automation?
As an example, see the following embed where I use a Lookup Table column to define Parent & Children relationships, but a formula to populate one end.
The issue however is that I can only easily change the “Parent”, through the “Assign Parent to Subtasks” view. However, I cannot change the “Children” through the “Assign Children of Subtasks” view.
Yes, I previously read the post and it does not solve the issue.
The issue is that any column driven by a formula (which this requires) is uneditable.
So any view which normally would allow you to easily change things (like drag & drop a card) don’t work. See the last table in the example in my opening post.
To be clear, to have a relationship that is auto-updated on both sides, only two options are available as far as I know:
(The examples use a Children column in table Task, and a Parent column in table Subtasks)
Solution 1: Lookup Column & Formula (see example in first post)
Create Lookup Column in Task, name it Children, point it at Subtasks.
Create a Lookup Column in Subtasks, name it Parent, point it at Tasks.
Formula: Lookup(Tasks, Children, thisRow)
Solution 2: Automation
Create the same lookup columns in Task & Subtasks.
Create Automation that listens for changes on these columns. These automations are relatively complex (need to detect removes, adds).
The problem I am describing is that for Solution 1, the Subtasks are now un-editable as they are driven by a formula (to keep the values in sync).
Solution 2 is untenable for a lot of different reasons: they are error prone, slow (performance tanks) and need to be setup for each relationship while being very complex.
Hi @GJ_Roelofs, assigning values in the method you described is not possible right now in Coda, but it is a feature we are working on as part of a broader story around being able to interact with relational data. We have discussed this feature internally quite a bit and have a team working on this, stay tuned for updates.
@Al_Chen_Coda By accident I discovered that the Detail View has an unintended side-effect in that it does give you the functionality that you want: Being able to adhocly assign to columns that are driven by a formula.
This caught me completely by surprise, as it’s not really supported through any expected means.
E.g.: Dynamically add Subtasks from the perspective of the Task
In the below embed, I can open up the Detail View of a Task, and create new rows in the “Displayed as Table” Children Formula column, which is driven by a formula. It properly creates the rows in the Subtasks table and assigns them to the parent.
This specifically only works for the Text column. Getting the lookup column to display as a Table only works sometimes, and when it does, you can’t add children. (see Children Lookup)
In the below table, select the A row in Tasks-2 and expand into the detail view.
So I was playing around with this, and it looks like a good deal of the functionality that we want is actually already present, without automation rules. And it doesn’t even feel hacky, I think Coda’s designed to work like this on purpose.
What we can do
If you have a table with a lookup column (not formula), and then in that table being referenced, create a column with a formula that filters the rows referencing it (see embed below), then you add, remove, edit, delete, and create records from both tables from the perspective of either table.
Limitations
You just need, as others have mentioned, the details view. And it seems the formula must be =Filter(something) (ie, FormulaMap(), Lookup(), and probably other methods of generating rows work don’t work as well – either failing to display as tables in the view or unable to add rows). Therefore, it’s still not a truly bi-directional relationship, because one of them is still technically an uneditable formula.
I’ve written more notes about the possiblities and limitations of this kind of linking below.
UPDATE
Here’s an alternative method using multiple selects and buttons. I expected it to be pretty simple to set up, but my formulas ended up getting surprisingly convoluted… therefore it’s likely too much work / too complicated to be useful for most applications. But I’ll put it here anyway in case someone likes it. (same document, duplicated tables)
And I wanted to jump in here too and comment that this is really great work guys @GJ_Roelofs and @Ryan_Martens. Pretty advanced though, and I know I could not do most of this on my own - I wonder how many non-devs could. Ryan, curious why you think this is done by design? It took me a while to figure out when and when you cannot access the subtable in Details view.
If you have any more time, would love to get your take on whether you think this stuff could be presented in the Coda UX, perhaps via simple wizards, to allow for non-devs to implement this?
to associate the “adjoining” tables’ data via the “middle” table.
I have to say though as much as I appreciate these solutions, way over my head. Most of you here in the community are probably crack engineers in your day jobs, respect! But unfortunately for me as somebody trying to build the basics of my team app without the use of a developer, this doesn’t really make me think I will be that successful with what I want to accomplish without a Herculean effort to master all the subtleties of Coda!
It looks like @Saul_Garcia has designed a clever solution with this functionality to make things more straightforward for the end user, but you’re right @ABp, it’s not necessarily straightforward for the maker with little database experience.
However @ABp, the basic version of this really is simple to create in Coda. Take a look at this example (actually same example but I got rid of some of the extra fluff):
Go ahead and try out the following steps to see how simple it is to create your own two-way table link:
Create a new People table (or just use the existing one in my doc)
Create a Cities table
Create a new column in the People table that is type “Lookup” to the Cities table (using the blue plus button, if you search column type “Cities” it will suggest a lookup to the Cities table)
Using the same blue plus button method, create a new column in the Cities table that is a lookup to the People. (If you choose the option with the blue dot beside it as in the screenshot below, it will automatically write the correct formula for you.)
Create a Details view of the Cities table
Customize the layout, and fully editable People subtable will automatically appear.
And there you have it! The main limitation still applies, of course, that you can only edit the People subtable in the detail view and not in other views, but at least it’s there!
What I’d really like to see is this functionality better presented somewhere - in the Coda Intercom pages, here in the community, even the YouTube channel. I spent hours upon hours trying to figure this out, and I’m afraid other non-db experts will stumble on this without this being laid out more.
asking for some guidance as well. I would hope that the Coda team might be able to start building out some documentation, how to’s, other stuff that would explain the workings of Coda that are known, but very hard to figure out if you are not a developer. Such as in this case the fact that you have to have those multi-select options on both the “allow multiple selections” and “allow quick adding of items” under Item Settings, at least I think.
The fact that bi-directional linking itself is not possible also tripped me up. Much of the time I spent trying to figure this out was not being able to get a grip on the lack of the bi-directional capability, something I assumed - and I think it was a justified assumption - would be a built-in feature of LookUps.
Expanding the Coda Knowledge Base with this material doesn’t require any development bandwidth, so I hope the team can prioritize this without the need to have any features put on backlog while this is accomplished. My own experience in Coda is compromised frequently by not being able to figure out what’s going on in things like lookups, grouping, and layouts (as discussed here.)