Feedback requested: How would you build reverse lookups in Coda?

1

This week is Coda’s quarterly Hackathon!

We’re exploring different ways lookup columns and reverse lookups could work in Coda.

To describe the scenario, a canonical example of Lookups and Reverse Lookups currently work is shown here:

image.png
image.png781×467 160 KB

Notice how the Tasks table has a lookup to the Project table. And the Project table has a “reverse lookup” to the Tasks table.

A few observations of the current pattern:

  • The reverse lookup is defined as a formula
  • The reverse lookup column does not allow edits, so you have to go to the Task table to change which Project it is in—something you can’t do from the Projects table

We’re thinking of 2 different approaches, and would love your feedback.

  1. Paired Lookups - When a lookup column is created from Table A to Table B, we also create a column in Table B that reflects the reverse relationship. We can then make it editable on both sides.
  2. Multiple Reverse Lookup columns - When a lookup column is created from Table A to Table B, the user can add multiple columns in Table B that reflect the reverse relationship, perhaps with different filters applied (e.g. All Tasks for this Project and All Active Tasks for this Project) These could each be made editable.

A few open questions:

  • Which of these 2 approaches do you find more intuitive? Are there other approaches that make more sense?
  • What might you call the Column Type of a Reverse Lookup? Is this a type of Lookup to you or a new Column Type?
  • Some of you may be familiar with this concept from other products. We’d love to hear comparisons if you’d like.
28 Likes
2

@Ben_Huynh

So:
Paired Lookups - spawns two columns, one in Table A and one in Table B, which can both be edited.
Multiple Reverse Lookup Columns - allows creation of multiple columns in Table B that look up Table A. All contents can be edited (both the one in Table A and those in Table B).

Do I have this right?

Question on Multiple Reverse Lookup Columns

Let’s say I have [Tasks] and [Projects] table.
[Tasks] has columns Name (text), Project (lookup), and Priority (checkbox)
[Projects] has columns Name (text) and Tasks (lookup)

It’s pretty clear what each of these would do. But let’s say I add to [Projects] the column Priority Tasks (lookup), which is governed by the filter:

Priority == true

The Tasks lookup, meanwhile, only shows tasks like

Priority == false

What happens when I check a Priority checkbox in Tasks? (Does it switch the task to the Priority Tasks column and remove it from the normal Tasks column?)

Or, am I thinking incorrectly about Multiple Reverse Lookup Columns? Could you give a more fleshed out example of how it would work and what would not be possible under Paired Lookups by comparison?

RE Open Questions

  1. I’m intuitively understanding Paired Lookups as they’ve been described. But in practice using multiple lookups might be more understandable if I could get my head around a more concrete usecase.
  2. Why isn’t this just a type of Lookup? It’s basically a lookup with default values that get updated if the other table changes.

I’m imagining a section under Item Settings:

image
image734×1194 35 KB

that says something like, “Automatically Select Rows If” and then allows me to select a filter (or divine my own). By default, if I select the Projects column from [Tasks] to be the column I reverse lookup from, it might generate this filter:

CurrentValue.[Projects].Contains(thisRow)

which is equivalent to this formula:

[Tasks].Filter([Projects].Contains(thisRow))

And it would be just like the Item settings Filter. It sits in the background relatively unseen, but autoselects matching rows as they are updated.

Is this somewhat what you meant by Multiple Reverse Lookup Columns?

  1. I used Airtable’s solution and found it enormously unintuitive. But I didn’t invest much time into really figuring it out.

Edit:
Also, please don’t tell me this is an April Fools joke, I want this feature too badly!

4 Likes
3

Checking in tomorrow for confirmation :sweat_smile:

2 Likes
4

OH YES, I’ve waited so long for this! Please make it come to life as soon as possible. It’s coda’s main limitation for my use cases.

I’ve built pretty and powerful relations using Paired Lookups with other products. I’d be completely satisfied by it. Nonetheless, there’s a tricky point about Paired Lookups when the table references itself (instead of another table). Notion offers two options when you try to add a Paired Lookup to the same table: “one way” (one row is related to another without the other knowing about it) or “both ways” (both rows get related to each other and are editable from both sides). If coda can build it just like that it would work greatly. If coda has to chose between one of the two, please pick “both ways”.

About the multiple reverse lookup columns it sounds way more powerful, but I honestly didn’t visualize how it would work (and I consider myself somewhere in between an avarge to an advanced coda user). Examples would be very helpful. Anyway I’d be the first in line to test it out when it came to life.

[Out of the topic] Since I’m already writing, please take note that the second greatest feature lacking in coda in my opinion is the ability to have each row as a “fully funcional” page when expanded, just like notions. It opens a whole universe of possibilities!

1 Like
5

Which of these 2 approaches do you find more intuitive?

`1. Paired Lookups: while simpler, it feels more automated to me, which is attractive

Are there other approaches that make more sense?

I envision transclusion via a brackets{ } keyboard shortcut where the word(s) is both editable & has the option to display/hide a tooltip# which counts references/rows (clicking the tooltip hyperlinks to the underlying table)

Typing {sample} would create a 3x1 table. Column 1 is the short word (i.e. sample). Column 2 is the full text (i.e. this sample is the entire text and includes a picture :framed_picture:). Column 3 is the URL linking to the first use. Typing {sample} for subsequent times creates 1 new row the existing table each time the quick word (i.e. sample) is used.

What might you call the Column Type of a Reverse Lookup?

Transclusion

Is this a type of Lookup to you or a new Column Type?

Type of lookup

1 Like
6

Ok, if I understand this correctly :innocent: : I think I would go with Option 2 : Multiple Reverse Lookup columns for a very simple reason the word can (also because it just seems like to work like the lookup we already have with the related columns, filter items… :blush: ).

When I use lookups in tables, 75% of the time, I don’t really need a reverse lookup from the Table B in my Table A :blush:.
But the fact that I could have a two way lookup (Display column from Table A ↔ Display Column Table B) if I need one is neat :grin: !

The auto-pairing seems easier but it might unnecessarily clutter tables with a lot of potentially hidden columns.

But, just in case I’m completely wrong in my understanding of the Multiple Reverse Lookup columns :

When I think about reverse lookup I visualize this :

  • Table A → Table B and Table B → Table A
    (at the level of the display columns but without the auto-pairing/creation)
  • If I change a value in Table B, the change is reflected in Table A and of course vice-versa :blush: .
  • In the reverse lookup in Table B the ability to filter the items, add related columns from Table A is a nice touch, like we already can in lookups.

Edit: Maybe the “auto-pairing” could be optional by using a toggle like for the single/multi select option when creating a lookup from Table A to Table B, that could give flexibility :blush: …

Type of Lookup

I’m not sure this is not a complete soup as I didn’t have enough coffee :coffee: yet :innocent: !
Sorry if that’s the case :sweat_smile: .

2 Likes
7

Hmm, I hadn’t thought about this.

@Daniel_Velho, why is it more difficult to do paired lookup from a column within the same table? Seems like it would pretty seamlessly follow the same rules.

Also, I’ve been thinking more about this. To me, it’s become very easy to think about “Reverse Lookups” as regular Lookups that simply update their contents based on another column. (I could be missing something here, I’m open to hearing that.)

I imagine it’s much more complicated than this in the background, and perhaps not possible. But if this were technically feasible it’d be quite elegant.

Example

If you have Column A and Column B.

If you want Column A to lookup from Column B, you’ll just use a regular Lookup column.

If you want to “Reverse Lookup” where Column B 's contents change as Column A is updated then I propose it works this way:

  • Fill in a field under Lookup options that says “Update Contents Based On” (in my prior post I called this “Automatically Select Rows If” but it amounts to the same thing).
  • You then choose Column A from the filter list.

You’re done! Now when you change Column A, Column B will also update.

8

Short answer: I would very much argue that we actually need it. I believe the existing approach (relationship editable only from one place but not the other) is actually good. It makes one think better about how to schema out their relationships and leaves less room for human error.

E.g. there’s no practical reason to be able to select Tasks from a Projects table row.

But let’s assume that there is. There’s a few challenges that I see here:

  1. If you select a Task for a Project through a project row, what should happen? Should that task be reassigned to that project, or should that task be now assigned to both projects? We could assume that the behavior should depend on whether Tasks.Project is a single-select or a multi-select lookup, but — 1. should this be the detrimental factor? (i.e. what if it’s a multi-select but we want to un-assign the task from some other project? should we go to that other project and unselect the task there?), and 2. let’s be frank, many Coda makers don’t bother to set that “Allow multiple values” toggle when making their lookup formula columns.

  2. What should happen if the filter is not your generic Tasks.Filter(Project = thisRow), but a more complicated one? E.g., what if there’s a “Is hidden” flag on tasks and those are explicitly removed from all lookups like Tasks.Filter(Project = thisRow AND Is hidden = false)? How would Coda assume which fields to set when one tries to make a connection through the Projects row?

    Simply updating Project to thisRow. Resolving the rest of the fields that participate in the filter is another.

I only read the rest of replies after I typed out the above. This made me think a bit more.

What is the use case that you’re ultimately trying to solve here? I.e. when would these 2-way lookups be useful?

2 Likes
9

I won’t argue against this, as I agree :blush: !

Like I said, 75% of the time, I don’t need the counter-part of a lookup in the second table (I don’t even use the related columns, I still create them myself instead when I need one :laughing: ), so I’m not sure I would use a reverse lookup that much.

It’s because I agree with you that I didn’t go for the “auto pairing” (or suggested to make it optional).

As it has been asked quite some time now, I guess others might find it useful… and I don’t know, if this comes to life, maybe I could too :woman_shrugging: :blush: .
There are few cases when I was using Airtable where this was/might have been useful, but I didn’t had the time to dig up a real use case when I gave my feedback :innocent: (without forgetting the fact that I would probably build my doc completely differently this time, as I’ve advanced on my own learning curve).

Maybe I’m wrong, but I’ve always seen this request as a way a gaining some time when you need to make a change in a referenced table without having to go back to the source table (which might be buried somewhere in the doc) :woman_shrugging: .

As the question was, how would you envision reverse lookups, well, I just gave my two cents, that’s it, nothing more :wink: .

10

An example that comes to mind:

This packages table allows me to add one component at a time and group them as part of one package:

image
image1906×934 76.4 KB

I want the ability to view these as collective types:

image
image1702×980 105 KB

For my user, this distinction between Package and Package Type is nebulous. It’s weird to them that they need to add a Package Type to [Package Types], then select the Package Type in the [Packages] table and add the components.

Instead, they would prefer to do all of this in one table:

  1. Add the new Package Type
  2. Select its components

This could be done with Paired Lookup columns.

But maybe you’re right, @Paul_Danyliuk. This could be solved with better implementation of grouped columns.

11

How about this as a quiz: Which table should have the Lookup column, [Activity] or [Strategy]?

image
image736×1820 115 KB

12

There’s two options depending on whether Trips table describes:
a) “template” trips, e.g. it says that Multinational Ski Trip usually includes those four activities, or
b) concrete trips, i.e. each row has a start and end dates, and a similar trip with the same activities would be a separate row on this table or not.

I assume it’s a), so the answer would be that Trips should have a manual multi-select lookup to Activities, and Activities.Trips should be a formula. This is because it’s more logical to select for each trip which activities it normally includes rather than for each activity select in which trips they are included. Whenever you add a new trip package, you want to be able to select right in place which activities are included — NOT go to the Activities table and go through each Activity to find whose “Trips” column to update.

In case it’s b) though, then the schema should be this:

  • Activity → renamed to Activity types. Ski, Climb, Go to Peru etc are activity types (i.e. can be repeated on many concrete trips) but not actual activities that belong to a specific trip.
  • Activities → new table that should describe actual activities: have an Activity type and a concrete trip linked (single-select lookups to Activity types and Trips respectively)
1 Like
13

Very exciting!

Paired Lookups:

  1. I like the idea of Coda making this easy to set up if you want it, but not required. I.e., I should be able to do something simple like check a box to “Create reverse lookup from the other table.”
  2. If I don’t check the box, it should be easy to add the reverse lookup later. “Add column > Lookup” with easy access (perhaps highlighted) to lookups from another table.

Multiple Reverse Lookup columns

  1. This seems like the most powerful approach, so it’s appealing for the long term.
  2. I’d say that this can be achieved by making the setup of simple paired lookups easy as described above. Creating multiple lookups should also be possible as described above by adding a second lookup column to the other table. There would be nothing special about any lookup except that the first reverse lookup has the checkbox shortcut to make it easier to set up.
  3. You should be able to apply filters to any lookup to limit the values that can be selected.

Open questions

  • I’d say both approaches could be intuitive, though I agree with some posts here that the reverse lookup should be optional and easy to set up.
  • I’d say a reverse lookup column is just a lookup column. Who is to say which is the reverse? :slight_smile:
  • I’ve seen it done the “Paired Lookups” way where it was required to be bi-directional. I found it could lead to clutter. It was additionally limited by not being able to filter values on the lookups.

Thanks for sharing this and requesting feedback. Really looking forward to seeing this capability!

1 Like
14

I think a really good point you make is that looking up the external data is only 1 part of the process.

Equally important is the additional data you input in the process of adding the row: and that is schema dependent (and mental model dependent).

In the case of Packages I’ll want the user to indicate the quantity.

In the case of Trips it’s the date of the trip.

These are important metadata without which the row is incomplete.

However, I believe the purpose of the reverse lookup is not schematic. It’s not to enable the maker to shirk the mandatory process of thinking about data and its relations. Instead, the reverse lookup is to simplify user experience so users can ignore some of the complexity of the underlying doc. Reverse lookups are more like buttons in this way.

This is perfectly clear in the Tasks example. The thought is, “A user might want to remove a task when looking at a Project.” Really, makers are just trying to make it easier for their users. I have been asked, “Why can’t I change it here?”.

Perhaps this means it could be solved with a different data input experience, like some sort of unified view input mode.

Alternatively, creating a row via reverse lookup | lookup could trigger an automatic Activate() on the row so users can input the relevant data.

15

If the “reverse lookup” column(s) will be functionally the same as the initial lookup column, then the most intuitive for me is to call them both “lookup” columns. And even present them both as if they were the original; kinda how tables and views in coda all just look like tables, so lookups and reverse lookups should just look like lookups.

I would also cast my vote for “paired lookups” over “multiple reverse lookups” … but only as a default; if possible, also give the maker the option to delete one of the paired lookups (effectively returning to a boring single lookup, which is desirable in many cases) and allow the maker to create more editable (reverse) lookup columns with different filters, like what is already possible in the detail/modal view.

In the case of a table looking up rows in itself, I think the maker must be given the choice about whether the reverse lookup column should be a separate column or the same column. (And if a different column, the maker still has the choice of deleting it if not needed.) Ie, if the lookup is mutual or not.

Example:
In a [People] table, I might make a column Buddy that is a lookup to [People], and when I set Ryan.Buddy to Connor, then I want to see Connor.Buddy immediately set to Ryan (Or [Ryan, Paul] if Connor and Paul were already buddies).

By the way @Paul_Danyliuk, this may be the example you were looking for where not having an editable reverse-lookup doesn’t make sense.

Then I may also want to make a Coach column that is not reciprocal; rather it should create a Coachee column as the editable reverse lookup.

2 Likes
16

Paul, can you elaborate on this? I can see how perhaps in the majority of cases, bi-directional editing is not strictly necessary, but is it harmful? Because I feel like there are many cases where, although one direction is more logical than the other, it is convenient to be able to do it both ways.

17

People.Friends is actually a good example where it would make sense to have editable reverse-lookup in the single column of the same table, yeah, but only from the “ease of use” perspective.

What would be normally done in traditional databases is a separate table of Friendships with columns Person A, Person B, and any extra column that one would potentially need, e.g. the date when the friendship started or some other friendship-level metadata. Then People.Friends would be a non-editable lookup formula from that other table.

Yeah, you wouldn’t be able to add a row through “add a row” button, or directly edit values in those cells. But I’m advocating against that anyway, and suggest using buttons and helper tables instead (see Part 3 here from ~40:00, and sorry for broken audio). This approach gives much greater flexibility that you’ll most likely need down the road. Besides, the more friendships you add, the slower the formula will get, so you’d most likely want to just stamp values instead (i.e. use an input column instead of a formula column) and you’ll need a single trusted place where on-demand recalculation happens (again, a helper table that updates only relevant people whenever any friendship changes, as a part of the “save friendship” button action perhaps.)

Every time you present a different way to do the same thing, you introduce one more risk to mess up the data.

  • You add an “Estimate pricing” task to Project A, but turns out it’s already a task in Project B. You weren’t looking at the Tasks table and couldn’t tell that it was the wrong one — all you saw was a display column value (most likely task name). You just wrongly moved the task or made a single task assigned to both projects.
  • On Employees table, you select one more Task to assign to them. Do you know immediately what happened? Was the task unassigned from any other employee and reassigned to this one? Or is it now assigned to both? You wouldn’t know for sure without checking on the Task row; you’d have to examine it anyway. You could just change the assignee from there after all.

You personally would bother to double-check what have changed. Many people in your team wouldn’t.

P.S. Why do you think I’m building all these workarounds and button-based systems for real clients out there and have this consulting practice even though “everyone can be a maker”? Because that’s exactly what I have to deal with: making docs that are as human error safe as possible, as intuitive and bulletproof to use as possible, by making all these design decisions.

5 Likes
18

Hey Paul, thanks for the clarification. I see now how the traditional database way holds many advantages.

This is a golden nugget :slightly_smiling_face:. And your examples help me to better understand the risk of such “ease of use” type features.

19

I wouldn’t want another column showing up automatically in table B, as that would make it more cumbersome for me to manage all my columns.

In contrast, if I’m in table B and I were to make a column that connects to Table A, I would expect to be able to edit the already made connections from B → A by default.

This is the most intuitive scheme for me because it limits the work I have to do

2 Likes
20

I thought I understood this question, but while reading all the replies, I am not so sure anymore. Looking at the very basic example with the projects and tasks, we are talking about assigning projects to tasks and tasks to projects, not altering the tasks them selves (so not making ‘task3’ into ‘taskA’)?
My preference would be, but only as an option, to allow editing the rows with a formula.
I would envision this as follows (based on your tiny example):
With the option turned on, you would go a field, say column 2, row 2 in the Projects table. You woud be allowed to add or delete tasks (even though it is a formula column) and these additions are reflected immediately in the Tasks table (added or deleted fields (and if necessary additional rows - I would be reluctant to delete rows upon this type of editing, but I guess it all depends on your table design).
I can see how it can nice to ‘edit’ your tasks table form the projects table, but tables are hardly ever as simple as in your example, so I can see a lot of havoc being done in a hurry.
That said, with this approach, you don’t have to rename the column type.

1 Like