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

[Lorne_Huston]

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]

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]

Everything that @kuovonne said, plus:

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

[kuovonne]

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]

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]

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.

Airtable - Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

All the Best!

Pablo :blush:

[kuovonne]

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.