Can you filter & sort linked records by a field on another table?

[AM858]

Hello,

I’m using Airtable as a CRM and I relate contacts to specific opportunities. I have a table I link a “pursuit” of a specific contact with different pipeline stages (Pre-Qual, Qual, Offer, Passed).

I want an easy way to log everyone who may be interested, and push them to try to offer on these deals.

I would really like to easily filter all the leads in Pre QUal or Qual so I can easily click into them and edit the record (taking notes on last conversation, etc). ANd it would be nice to automatically sort the field on the opportunity tab by these pipeline stages.

As you see in the screenshot, I have to manually drag these records to adjust the sorting… super annoying and when I have 80 linked contacts to one opportunity, it becomes a mess trying to scroll to find one particular record.

Is there a better way?

[Katerie]

It is not really possible to automatically filter the linked record cards (as shown in your screenshot). The only way to do this currently is with rollup or lookup fields, which can be configured to show you only a subset of the linked records based on criteria you choose.

What you would see in such a field would not be the card, but instead the values from other fields in the table where the linked records reside. You can create a formula field in that table to concatenate the values of the fields you care about and present them in a nice way (like, say, a bulleted list), and then use a rollup field to concatenate the values of that formula field.

If you do want to go this route, you can get a little creative with how you write the formula, and use things like emoji to create visual highlighting of the Stages values and include a clickable URL to a view where you can edit the linked records (by combining the link to the view where you want to be able to edit the record with the RECORD_ID() function).

The resulting user interface will be a lot less pretty and fluid than just using the cards, but perhaps it will serve some of the purposes you need.

Now, the next problem is sorting the linked records.

Automatically sorting linked records is currently only possible using two methods:

• The Batch Update app, which only allows you to sort by the title field; or

• Scripts, via either the Scripting app or a script that runs inside an automation.

It looks like for your use case you are going to need to sort the links with a script, and probably would want that script to run automatically whenever you link a new record or change the value of the Stages field in any of the linked records. This is quite tricky to set up, and requires a custom version of a sorting script similar to the one that’s discussed in this thread. The script would need to be modified to grab the value of the Stages field in the linked records and sort by that value. I could provide some guidance but would rather not get into it unless you really do decide to go down this particular rabbit hole…