How to display the results of a many to many relationship in a specific order?

Lorne_Huston · ‎Sep 25, 2020

I have a base which relates artists and exhibitions. When I want to see all the exhibitions (over 50) in which an artist participated, I want to see them in chronological order. In the same way, when I want to see all the artists (more than 50) who participated in an exhibition, I want to see them in alphabetical order. For the time being, I can only see them in the order in which I entered the various records. Where can I learn more about this?

kuovonne · ‎Sep 25, 2020

Welcome to the Airtable community!

As you have noticed, linked records (and their related lookups and rollups) appear in the order that you entered them.

If you want them to appear in a different order, you can

manually re-order them
re-order them using the batch update app.
re-order them with a script

ScottWorld · ‎Sep 25, 2020

Everything that @kuovonne said, plus:

You can also view your records in your junction table, and sort/group/filter them there.

kuovonne · ‎Sep 25, 2020

Yes, if you have a junction table you can sort/group in the junction table. If you have a junction table, this is probably the best way to analyze the data.

However, this will not affect the order of the records in the linked record field, lookups, or rollups.

Also, depending on the base setup, there might not be a junction table. Unlink some other databases, Airtable allows many-to-many relationships without a junction table.

Lorne_Huston · ‎Sep 25, 2020

I am precisely at the stage where I am trying to decide the best way to design this database. Can you direct me to some information which would allow me to weigh the pros and the cons of adding a junction table or linking the two tables directly. I have a lot of data that I will eventually want to import from another source. This is an academic research project. I am willing to devote some time to getting a firm grasp of the fundamentals of Airtable.
Thank you.

Pablo_Rios · ‎Sep 25, 2020

Welcome to the community Lorne,

Kuovonne & Scottworld have both pointed you in the right direction.
Anytime you have a cardinality of many-to-many it is recommended to use a junction table. However this choice will be up to you. I use a junction table to join my tables when I can.
In your case, 3 tables: Artist, < Artist_Exibition, > Exhibition. Artist contains artist attributes, the Exhibition contains exhibtion attributes, the Artist_Exibition table will contain a combination of the Artist and Exhibition tables plus any new relations you want to add, this is where you would do all your views, filtering, grouping, etc.

Here is some info on many-to-many relationships.

All the Best!

Pablo :blush:

kuovonne · ‎Sep 25, 2020

Airtable is different from most other relational databases in that it does not require a junction table for a many-to-many relationship. If you don’t need a junction table, you can make data entry a little easier by not having one.

However, there are many good reasons why your data and/or reporting could benefit from a junction table. To expand on @Pablo_Rios’s comments, ask yourself some questions:

Do you want to keep track of anything unique to a specific artist/exhibition combination, such as
- the specific piece of artwork that an artist had at that exhibition
- the number of pieces of artwork the artist had at that exhibition
- any awards the artist received at that exhibition
Do you want to be able to group, filter, and sort exhibitions and artist in the user interface?

If you answered “yes” to any of the above questions, you should create a junction table.