Junction Tables: More Intuitive Record Relationship UI/UX


#1

AirTable could benefit from making junction tables more of a feature than a workaround for bases requiring for many-to-many relationships. I’ll be using this mockup base (https://airtable.com/shrkWoMwtNLYAVvoH) as an example to explain these requested features. It has 3 tables: Projects, Firms, and Project Roles (the junction table). Each project will have a prime consultant, and may or may not have sub-consultants performing any of 20 or so roles (i.e. landscape architect, economist, contractor, cost estimator, etc.).

1. Junction record name “smart display” based on active table

This example base follows a common junction table entry naming convention: the primary field is a Formula that concatenates the records to which it is linked (i.e. {Project Name}: {Role Type}: {Firm Name})

When I’m viewing the Projects table, it would be most useful if entries in the Team column (which links to the Project Roles junction) displayed like “{Role Type}: {Firm Name}”. But in the Firms table, it would be most useful if entries in the Project Roles column (which is the corresponding link to the Project Roles junction) displayed like “{Role Type}: {Project Name}”

In other words, AirTable should understand which portion of the formulated entry name is implied by context (what is the active table) and hide/omit it to avoid redundancy. Alternatively there could be a new UI in the Customize Field Options that allows users to write a new formula/logic for a linked record’s display name for that column in that particular table to avoid repetitive info being displayed.

2. “Smart” linked record expand
02%20PM

Since the junction table has to act as a middle man, I can’t just click on the linked team member from the Projects table to view their contact info, I’m instead taken to the Project Roles entry and from there I have to click on the linked Firm record to view more of their details. One could, in theory, add a Lookup Field for absolutely every column of every table to which the junction is joined, but the issue with such a workaround is obviously tedious and inopportune.

3. Assume “Add New Record” when clicking the “+” icon for a Linked Record Field pointing to a junction table

37%20PM

Like many junction tables made in Airtable, the entries in the Project Roles junction table will only ever be linked to one Project and one Firm each.

It is more intuitive for a person to make new entries in this case within the Projects table as the junction entry will automatically be linked to at least one of its major components. It would be ideal if the “+” icon to Add a Linked Record skipped the “select from existing records” dialogue and went straight to the “add new record” dialogue when linking to a junction table.

4. Hide a table

Ideally, I will never have to even look at the Project Roles junction table once its set up. I’ll be adding all the new relationships from what is ostensibly the “main table”, in this case Projects. If I could have the option to hide the Project Roles table out of sight to avoid confusion, that’d be great.


#2

Hi @Kamille_Parks

I partially agree with you on this one - but I’m not sure necessarily about the ‘Smart’ option (unless you could override it). I would like to be able to specify the display value for each ‘end’ of the join myself.

Again, here I’m not so sure - although I can see how it could help in simple cases. In general Join tables often have many fields - for example there may be an hourly rate for a person on a project - in other words, join tables generally have useful information that the user would want to see.

Totally agree with you here!! I have seen lots of cases in Airtable with data incorrectly ‘joined’ - this needs to be sorted out.

This is not specific to Join tables - in particular, it would be nice to be able to remove look up tables and any others you need from the menu (still allowing them to be accessed of course). And maybe only accessible to some categories of user.


#3

Agreed, if suggestion 1 (smart display name) is implemented it should have a toggle in the link field options dropdown so its not forced on all users. But I think it would be incredibly useful because tables would become much more legible. Or as you say, allow users to specify the display value for each end of the join

For suggestion 2 (smart link) it sort of connects to suggestion 4 (hide tables). I just don’t want the end user to have to interact with the junction table. I don’t want to make junctions more complicated than they need to be, but maybe if there was a way to specify a new table as a “Junction” table, and junctions get a special options dialogue where users can specify display names in each linked table, and link routing? (i.e. tell Airtable to open the Firm record retails when clicking the linked junction record in the Project table)

^ might be a bit much, but its still on my wish list!