Is an editable Bi-directional relationship possible without Automation?

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.

4 Likes

+1 on this request – I’m trying to figure this out!

1 Like

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.

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.

4 Likes

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

1 Like

@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