Is an editable Bi-directional relationship possible without Automation?

Dear @GJ_Roelofs

Did you check this post?

Creating a bi-directional row-level relationship between two tables

Coda’s approach is different from Notion and Airtable, but for sure it’s possible to get the same with the lookup function

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.

1 Like

Dear @GJ_Roelofs,

You are 100% right, let’s see if we will get more input from the Codan’s

@mallika it would be great to receive more input from you or a colleague, thank you :handshake:

1 Like

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.

8 Likes

@Al_Chen_Coda I will keep a keen eye on the update page then!
Thank you for the response.

2 Likes

@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.
  • See that you can’t add rows to Children Lookup
  • See that you can add rows to Children Formula
1 Like

Trying to switch over from Airtable, and this is a major deficiency of Coda. Are tables not relational?

1 Like

Simple use case:

  • Table of Projects
  • Table of People

People have a lookup so I can add them to projects.

Now when I’m look at a project there is no way to add someone to the project. I have to individually look up each person in the people table.

1 Like

Any update on this discussion @Al_Chen_Coda?

2 Likes

Dear @Ryan_Martens

I felt to share this post that might be of your interest:

1 Like

Thanks @Jean_Pierre_Traets. Looks like we are still waiting for that functionality.

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)

1 Like

I answered @ABp 's questions on this yesterday coincidentally: Connecting Lookup Tables via Join Row?

Note the use of a Button column to bypass the Detail View restriction (a bit).

As said before though:

  • These reverse lookups tend to be costly, so given performance considerations probably not the way forward.
  • It’s still not a true bi-directional editable relationship.
2 Likes

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?

You may find this usefull. It’s the best way I’ve found to add from either table

2 Likes

yes, thanks you @Saul_Garcia, in particular the use of the third table, as this is something I was trying to utilize here:

Connecting Lookup Tables via Join Row?

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!

Cheers!

1 Like

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:

  1. Create a new People table (or just use the existing one in my doc)
  2. Create a Cities table
  3. 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)
  4. 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.)
    image
  5. Create a Details view of the Cities table
  6. 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!

6 Likes

hey @Ryan_Martens great post!

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.

I wrote a post here:

Grouping, and adding to Groups - Guidelines request

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.)

Thanks again for the response!

Your welcome :slight_smile:

If you spent hours trying to figure out this functionality I think that’s good feedback for the team as an indication to document the functionality better in the help files.

But I think the real solution is yet to come, when we finally get a truly bidirectional lookup (without formulas and without automation) like exists in AirTable.

1 Like

@Ryan_Martens thanks!

Yes, this is a pain point with me since I started extensive use of Coda about a year ago. I am not a developer, but well-versed in most other productivity apps out there, and fairly proficient in Excel outside of formulas and functions. I have found that I will hit a roadblock between beginning something I am trying to set up in Coda, and getting to the end of making it work as I desire.

I am not sure how many users of my skillset are in Coda now, and if this is the target segment of the vision of Coda. I would say about 90% of what I read in the community is on a very advanced level, so if the sample of users here is where Coda is targeting moving forward, I am probably not an ideal user/Maker. In fact, I’ve started to think that Coda may head in the direction of multiple other frameworks out there that are basically platforms for development given the expertise needed to master it and the time necessary to gain that expertise, especially if you are not a developer.

Perhaps you’re familiar with the fact that nowadays there are dedicated teams, not just individual positions, within larger software companies for Jira admins. I find Jira actually less complex than Coda, but it’s complex enough all the same to require companies to hire specialists. I could see companies actually hiring “Coda admins” over time.

The bottom line for me is that I will spend a lot of time looking at things I am setting up and getting stuck in the “why isn’t this working as I think it should,” which usually leads to those hours of time spent trying to figure out what’s going on. Almost all the time these roadblocks wind up with me having to work through some formulas which challenge me if I’m trying to do anything with a level of sophistication such as here:

And just in case this is useful feedback for others in the forum, I can say that I had a lot of trouble for a while mastering the notion of “thisrow” in formulas. I think it would be great to see a dedicated section in the Coda Knowledge Base about thisrow, written for novice Makers. Thisrow is an essential in the Coda Formula Language, but not easy to master at all if you are not a developer used to formulas.

I hope all that is useful! Thanks again for responding!

1 Like