Many-to-many cross reference

Showcase Tips and Hacks
1

Conceptual Problem

I have a table that has a reference field to other rows in the same table. And when referencing those rows, the referenced row needs to then also reference back the one being edited.

Practical Problem

The case here is that I’m building a matrix of productivity apps to try and find the set that will completely cover my full business workflow. One of the columns is “Integrates with”, which just references other services in the same table.

Table: Services

Service Name |  Integrates with
_______________________________
Coda         |  Gmail, Gcalendar, intercom
Gmail        |  Coda, Gcalendar
Gcalendar    |  Gmail, Coda

So, right now, I have to enter the information twice to get it to work. I’d like to just enter it on one service and have at service add to it’s existing list the back-reference.

The “Integrates with” column is set up as a select list with the following formula for the options:

thisTable.[Service Name].Filter(CurrentValue.IsNotBlank() AND CurrentValue != thisRow.[Service Name])

I could change this to a Table Lookup column type, but I don’t see how that would allow me to enter the values on top of it.

The result looks like this:

20%20PM
Screen Shot 2019-05-21 at 12.54.20 PM.png999×886 43 KB

Solutions

It seems that I either need a separate pivot table along with a button to add new values that would then appear in the services table.

Or, I did try setting up an automation rule, but nothing I do makes it run correctly. It waits for the modified “integrates with” column, and then is supposed to update every referenced service by adding the name of the current row to the existing integration lists. But it errors out, and the error gives no feedback about what went wrong.

Any ideas on how to bring this together would be greatly appreciated.

1 Like
2

Did you ever figure this out? I’m in the same boat where I want two tables to have references to each other.

3

I think you need a separate table for connections: each row is just one single connection. Note that I’ve added conditional format to prevent user from enter wrong or already presented connection.

4

I missed this topic previously — here’s how I used to have a many-to-many connection in my Chat Bot experiment using a single table:

(see Dialog data tab)