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

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

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

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.

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

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.

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

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.

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

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

This is exactly aligned with my mental model as well!

1 Like

Hi @Ben_Huynh ,

I’ve given this more thought and I think I now understand the heart of what prompted this request. I’ve hinted at it in previous comments but those were a bit garbled since I was still getting my bearings.

The main behavior I want out of reverse lookups is for my user to be able to violate the table hierarchy in the name of user experience.

A great example of this can be found in this Sales Pipeline doc. It is a Codafied version of the Monday.com template.

Here’s the key difference between the two:

Monday

My user can take two approaches:

  1. Add a deal and then add contacts related to that deal.
  2. Add a contact and then add deals related to that contact.

There is no violation of the data structure, instead, Monday uses a clever “link to deal” selector which pulls up deals from another page. Then, when you select the deal for this contact it adds this contact to that deal’s Contacts column.

Coda

In Coda, this is not possible. As a builder, you can choose whether your user can add deals to contacts OR contacts to deals, but not both.

In this case, I opted to structure it so that Deals have Contacts.

This is unfortunate, because of course users will ask, "Why do I have to go back to the Sales Pipeline page to add a Deal? Why can’t I just add it here in the Contacts page?.

It’s not better to reverse the relationship and have each Contact have a Deal, since then people will ask exactly the opposite, "But why can’t I just add a contact to this deal? Do I really need to go to the Contacts page?


Workarounds

Of course, there are workarounds. One strategy might be to create a column called Add Deal in the [Contacts] table and then you can add a button called Add Deal. But then you’re basically rebuilding the functionality of reverse lookups and you’re doing it in a bad way, since you’ll also have to implement updating and deleting using your column button system as well.

Plus, if I’m your user I’ll take extra coaching. I might ask, "So I have to push the button to add it? Why can’t I just add it?"

@Paul_Danyliuk, does this make sense and help clear up your concerns that the data hierarchy?

I think this is a strong example of the need for this behavior. It also clarifies that this behavior is for the doc user, not the doc maker.

2 Likes

Hi,

Before any other consideration, I think coda is totally amazing… And so, as this feature is one basic feature, I simply don’t understand why coda doesn’t have it ?.. Every single other tool (notion, fibery, airtable, etc…) has it, so why ?.. For my usage, Coda is the one and only as it allows to do everything you can have in mind BUT this missing feature is a total game breaker…

So I wonder : you post your message 2 months ago, do you now more about when this feature will be release ? @Ben_Huynh

And my opinion about your question is : second option, we need to be able to refer to any field of the reverse lookup element and to filter, sum, formula, etc…

Thank you for your answer and for your incredible tool !

First of all, I’m super excited. Not having reverse lookups was the reason that I stopped using coda.
What I love about coda is it’s endless possibilities with formulas.
For this reason I suggest to go with most flexible approach for the user. We should be able to choose how many reverse lookups we want to have and also have the option to have none.
Just as a quick input: I like how relations (lookups) are setup in Fibery. May get some inspirations there :slight_smile:

And while we are at it. Consider renaming it (relationships?) and be very clear about implied hierarchy / direction. This can be one of coda’s most powerful features if implemented with enough flexibility.

1 Like

I am very late to this, but wanted to add my two cents!

(1) My experience has been the following: (almost) every time I thought I wanted reverse look-up functionality, it has turned out that that the reason I thought I needed it was because I hadn’t really thought through my data structures, and once I re-thought things and got the relationships right the need went away. Not every single time, but most times for sure.

(2) Permissions: I really want users to have the clearest and easiest path to doing what they want/need, BUT I am also really concerned about giving users the power to screw things up. So when I look at the use cases described above, I am always thinking “who should have the ability to make changes to this other table, especially without having to look at that table first?”
What I really want is an easy-to-use permissions tool that can say: these users can make changes on these columns on these tables, can pick from select lists but not add new items to that list, and so on. My worry about reverse-look-ups is that I will accidentally give people more power than I meant to, and base data will end up editable in ways I didn’t want. That’s why I end up with lots of buttons - not because they are necessarily the best interface element for the purpose, but because they are controllable.

(3) I REALLY like the Monday.com solution @Connor_McCormick described. IF a user has permission to mess with the relevant table, then have the table details pop up and make the edits appropriately in the place they should be made.

(4) It would be good to have a better way to talk about relations. Lookup/select lists are clear, until you start adding formulas. Then it becomes much more complex to figure out what is really going on. I go back to my older docs and get lost as to what I meant and why. [COMMENTS IN FORMULAS PLEASE!!!]

1 Like

Sorry for the deviation of the recent discussion in this thread; I’d just like to connect solely to the project/task issue once more. Sorry also if what I describe is quite low level; I just hooked up to Coda some weeks ago and am still in the process of learning different concepts and solutions.

What I have some trouble with in my project/task setup is the idea (or necessity) of maintaining a separate table where projects live and having to interconnect to the task table by lookups.

This way the projects don’t live in my master task table and I don’t have the possibility to treat them similarly like tasks.
What I mean is that I’d like to have a project show up in my task table, in the corresponding area if I filter accordingly or at the right time when the project has a due date (and perhaps a start date as well).

My current approach is for a project in fact to be a task but defining it a project by giving it a special status “Project”.
I have set up a lookup column named “Parent” where I can define any existing task (which obviously has a “Project” status) as the current task’s parent.
And a Project gets a status “Sub-Project” instead if it has a project as parent.

This way projects are technically able to have any number of sub-levels and everything, projects, sub-projects and tasks, lives in my master task table. Conditional formatting helps in distinguishing the different entities.

By setting up a separate view where I filter all the tasks having “Project” or “Sub-Project” as a status and grouping accordingly, I get an overview of all my projects and their corresponding children, open and closed.

This is an example of the above, respectively the separate view for the projects I’ve described, where children are simply any tasks that have a parent defined.

If anyone would like to give any opinion of this approach, be it bad or good, I’d appreciate that very much.

1 Like

@Otto_Kiefer This approach exactly. What a people want/need sometimes is parent/child relationships, and those can be done with lookups WITHIN a table (or by using a support table that codes all the relationships, and gets read back into the master table).

I wish some of these relationships/dependencies were primitives (like in Fibery) and you didn’t have to reinvent the wheel every time…

1 Like

Yes, @Andrew_Milne , Fibery has a smart option to connect databases, while some of their other structural concepts are rather mind-bending, at least to me.

Personally, I just don’t recognize any advantage in separating projects and tasks into different tables as long as the lookup procedure in Coda appears too clumsy to me to feel intuitive.

This isn’t the real feature release, but it is a way to get linked columns:

I would prefer Multiple Reverse Lookup columns for additional flexibility, but with a twist.

Can’t it be both?

By default Coda can create Paired Lookups.

If a user needs additional Reverse Lookup columns, these columns can be created by:
right click on the Paired Lookup column header → select something like “Insert related Reverse Lookup column”.
Same as you can do with “Insert related column” for a Lookup column now.

And I strongly disagree with @Paul_Danyliuk that Coda doesn’t need this feature. I think it’s one of the top missing features that hold Coda back. Paired Lookups will make Coda so much easier to use, and docs will feel much more like real apps.

I greatly appreciate Paul’s examples and help in this community, but much of the functionality in some of his examples (like Best Practices Showcase (8 hours of videos & a doc)) is “hardcoded” and relies on buttons. And often you can’t drag and drop rows. Maybe sometime it’s good. Paul wrote it’s intentional:

If that’s what some docs benefit from, people can continue creating them the old way.

But many of us and our docs will benefit greatly from bidirectional Lookup columns.

Do it, @Ben_Huynh! :blush:

1 Like

Thanks for reviving this thread, @Andrei_Kharlanov , and for disagreeing with me too :slight_smile:

After the hackathon demo I actually sorta started liking the idea a tiny bit more.

Here’s what I think would make the most sense:

  • Add new column → Lookup → these top options should insert not an autoguessed “reverse lookup” formula but this new kind of an editable bidirectional lookup (a multi-select input column with a twist):

  • There will be these settings added to a Lookup column without a formula:

(it probably won’t be a formula but a column selection but you get an idea; could also just use blank selection instead of a separate toggle).

This way you’ll have the full flexibility of:

  • Having regular editable lookup columns
  • Having formula-based (non-editable) lookup columns
  • Having regular editable lookup columns that are linked to a corresponding reverse-lookup column on that another table. Editing a value in one would automatically immediately edit a value on the linked one.
  • And easy switching between the modes by adding/removing formula and/or selecting/deselecting the “Linked column” property in the settings.
5 Likes