Skip to main content

Can you sort multiple linked records inside an individual cell?

W

Hi Airtable Community,

I have an interesting problem I did not forsee when we made a base to track equipment we rent to other contractors from time to time. We have a job (record) where we linked in individual records for different kinds of equipment and now that it has been returned, we need to be able to track and validate it is here. I have the individual records that were added inside an individual cell and it went in how we did it - meaning in no numerical order (we just grabbed a piece of equipment off the shelf, scanned it into the customers record and repeated. The issue with that is the individual records didnt go in in any discernable order. They each have a number, but I can't seem to sort by the record by that number. This creates an issue becuase in order to check the items back in (or rather validate that they made it back here) we have to visually scan through every item in the record looking for the right number. This won't work as it will take too much time as there are hundreds of records in these cells to hafve to look through.

Is there a way to sort the records inside these individual cells? I have tried to do it using the sort feature but it treats the entire cell like one value. I have also tried using the search feature for the specific number of equipment I am looking for and it finds it but doesnt allow to click and open it as it is still seeing it as a total cell value. So am a bit lost. 

I have attached two screenshots that show what I am talkign about as far as the numbers of each equipment piece and how they are inside the cell. 

Thanks in advance for any assitance. 

2 Attachments

9 replies

S

You can manually drag and drop the sort order of linked record field values but keeping it sorted on field update will require a script.

This sounds like a potential use case for a junction table where you have a unique list of jobs and a unique list of equipment and you want to itemize/track details about each equipment per job - such as sort by serial and "checked back in status" etc. https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable#manytomany-relationships-and-junction-tables

In your second screenshot, are you asking about how to search for a value that is not yet inserted in this field? If a linked record has already been added, this record won't show up when you go to add more linked records to this value. If you're looking to find records that have a specific field value, you can use filters, views, cmd/ctrl+f to find these.

W
Stephen_Orr1 wrote:

You can manually drag and drop the sort order of linked record field values but keeping it sorted on field update will require a script.

This sounds like a potential use case for a junction table where you have a unique list of jobs and a unique list of equipment and you want to itemize/track details about each equipment per job - such as sort by serial and "checked back in status" etc. https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable#manytomany-relationships-and-junction-tables

In your second screenshot, are you asking about how to search for a value that is not yet inserted in this field? If a linked record has already been added, this record won't show up when you go to add more linked records to this value. If you're looking to find records that have a specific field value, you can use filters, views, cmd/ctrl+f to find these.


Hi 

Thanks for replying. As far as the 2nd screenshot - I was just showing that there is no way to search this list is all. I could probably have just included the 1st screenshot. 

I will look at the link you sent me about junction tables - is that separate from a scrip solution that you reference?

S

Yes, creating a junction table does not require scripting. 

You would have these three tables:
Jobs (unique) <-linked-> Job Equipment (junction) <-linked-> Equipment (unique)

In your junction table, you could have the primary (left most) field be a formula field that combines the values in the two linked record fields to Jobs and Equipment like, {Job} & ' | ' & {Equipment}

Also, your linked record fields in the junction table should not have the ability to link to multiple records (untoggle this option in both of their settings).

Now you can store details about equipment per job in a record here. You might want to keep track of return damage, return status, rental rate, etc. (ie, info that varies between jobs for the same equipment or varies for equipment for the same job).

Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 14, 2023

Short answer - Yes, using either the Scripting App, an Automation Script, or (heavens forbid) manually sorting the countless records one by one.

Long answer - If you're sorting the linked record by its primary field (such as the four digit numerical field I can see), then the script is a little easier to tackle.

If however, you're sorting the linked records via another field (aka lookup) other than the primary field, you'll need to crack your knuckles and learn how to deal with this layered process programmatically.

A couple of questions;

  1. Have you written Airtable scripts before?
  2. Are you familiar with JavaScript, JSON and object manipulation using expressions such as .forEach(), .map(), .filter() and .reduce()?

I've confidence that you'll get your solution working - this is the starting point of what you need to understand to address it.

As a side note - it's worth poking Airtable to upgrade the feature set within the Linked Field to support a Sort functionality out-of-the-box. Not having it is a right pain - and which is why at least I can help you with crafting a scripted solution.

L
1 person likes this
  • L
    Leslie_Kane
W
Karlstens wrote:

Short answer - Yes, using either the Scripting App, an Automation Script, or (heavens forbid) manually sorting the countless records one by one.

Long answer - If you're sorting the linked record by its primary field (such as the four digit numerical field I can see), then the script is a little easier to tackle.

If however, you're sorting the linked records via another field (aka lookup) other than the primary field, you'll need to crack your knuckles and learn how to deal with this layered process programmatically.

A couple of questions;

  1. Have you written Airtable scripts before?
  2. Are you familiar with JavaScript, JSON and object manipulation using expressions such as .forEach(), .map(), .filter() and .reduce()?

I've confidence that you'll get your solution working - this is the starting point of what you need to understand to address it.

As a side note - it's worth poking Airtable to upgrade the feature set within the Linked Field to support a Sort functionality out-of-the-box. Not having it is a right pain - and which is why at least I can help you with crafting a scripted solution.


Hi

Sorry for my late response. I do not have any skills on any scripts, Java, JSON, etc.If this is something you are avaialble to help with I would be very interested in that. Please let me know as moving them by hand is not a good solution for me!

Karlstens
  • Karlstens
  • Inspiring
  • 601 replies
  • June 15, 2023
Wayne_Merlino wrote:

Hi

Sorry for my late response. I do not have any skills on any scripts, Java, JSON, etc.If this is something you are avaialble to help with I would be very interested in that. Please let me know as moving them by hand is not a good solution for me!


All good, I've written an example Scripting App Script for you, which will work on a base structured as per my screenshots below - where the table "Main Table" has a field that links to the "Linked Records" table. And within that 'Linked Records' field, you'd like to sort the records via a field found within the "Linked Records" table.

 

So for example, each Linked Record has a lookup of a Number, or a Letter. Note how they're ordered differently - my script will let you choose which field to sort with and then will update the main linked record with that sorted order.

 

let mainTable = base.getTable("Main Table");
let record = await input.recordAsync("Select a record to use", mainTable);

if (!record) {
output.text("No record was selected");
// @ts-ignore
return;
}
let linkedRecordsTable = base.getTable("Linked Records");

const shuffleArray = (array) => [...array].sort(() => Math.random() - 0.5);

if (!record.getCellValue("Linked Records")) {
let fillWithRandomRecords = await input.buttonsAsync(
"There are no linked-records to sort in the selected record, would you like to fill it with random records?",
["No", "Yes"]
);

switch (fillWithRandomRecords) {
case "Yes": {
console.warn("Adding a max of 5 Random Records");
// Get all records and shuffle them
let allLinkedRecordsQuery = await linkedRecordsTable.selectRecordsAsync({
fields: ["Name", "Numbers", "Letters"],
});

let allRecords = shuffleArray(allLinkedRecordsQuery.records);

// Take the first 5 or fewer
let randomRecords = allRecords.slice(0, 5);

const randomRecordsMapped = {
"Linked Records": randomRecords.map((record) => ({
id: record.id,
})),
};

console.info(JSON.stringify(randomRecordsMapped, null, 2));
await mainTable.updateRecordAsync(record.id, randomRecordsMapped);
// @ts-ignore
return;
}

default: {
console.warn("No Linked Records to Sort");
// @ts-ignore
return;
}
}
}
output.text(`You selected this record: ${record.name}`);

const linkedRecordsQuery = await linkedRecordsTable.selectRecordsAsync({
fields : ["Name", "Numbers", "Letters"],
recordIds : record.getCellValue("Linked Records").map(record => record.id)
});
let linkedRecordsObject = linkedRecordsQuery.records.map((record) => ({
RecordID: record.id,
Numbers: record.getCellValue("Numbers"),
Letters: record.getCellValue("Letters"),
}));

let sortingField = await input.buttonsAsync("Sort via Letters or Numbers?", [
"Letters",
"Numbers",
"Random",
]);
let sortedRecords = [];

switch (sortingField) {
case "Letters":
output.text(`Sorting Record:${record.id} by letters 🔠`);

sortedRecords = [...linkedRecordsObject].sort((a, b) =>
a.Letters.localeCompare(b.Letters)
);

break;
case "Numbers":
output.text(`Sorting Record: ${record.id} by numbers 🔢`);
sortedRecords = [...linkedRecordsObject].sort(
(a, b) => a.Numbers - b.Numbers
);
break;
default:
output.text(`Sorting Record: ${record.id} randomly 🎲`);
sortedRecords = [...linkedRecordsObject].sort(
() => Math.random() - 0.5
);
}

const sortedRecordsMapped = {
"Linked Records": sortedRecords.map((record) => ({ id: record.RecordID })),
};

await mainTable.updateRecordAsync(record, sortedRecordsMapped);

Here we can see, the with the script executed for each of the three main records, that they're linked field is sorted alphabetically.

 

It's worth noting - that this is the tip of the iceberg - things will get more complex as your real-world requirements stack up, and then edge-cases start to creep in.

The script I've written itself is arguable too, in that perhaps I could have written or structured things differently. There's a few different ways to approach this Linked Field sorting requirement - However, I only had a spare hour this evening to help - and for someone who's not familiar with JavaScript, understanding this script is a BIG ask - but hopefully with a bit of tinkering you'll be able to get it to do what you need.

M
1 person likes this
  • M
    Menashkes
W
Karlstens wrote:

All good, I've written an example Scripting App Script for you, which will work on a base structured as per my screenshots below - where the table "Main Table" has a field that links to the "Linked Records" table. And within that 'Linked Records' field, you'd like to sort the records via a field found within the "Linked Records" table.

 

So for example, each Linked Record has a lookup of a Number, or a Letter. Note how they're ordered differently - my script will let you choose which field to sort with and then will update the main linked record with that sorted order.

 

let mainTable = base.getTable("Main Table");
let record = await input.recordAsync("Select a record to use", mainTable);

if (!record) {
output.text("No record was selected");
// @ts-ignore
return;
}
let linkedRecordsTable = base.getTable("Linked Records");

const shuffleArray = (array) => [...array].sort(() => Math.random() - 0.5);

if (!record.getCellValue("Linked Records")) {
let fillWithRandomRecords = await input.buttonsAsync(
"There are no linked-records to sort in the selected record, would you like to fill it with random records?",
["No", "Yes"]
);

switch (fillWithRandomRecords) {
case "Yes": {
console.warn("Adding a max of 5 Random Records");
// Get all records and shuffle them
let allLinkedRecordsQuery = await linkedRecordsTable.selectRecordsAsync({
fields: ["Name", "Numbers", "Letters"],
});

let allRecords = shuffleArray(allLinkedRecordsQuery.records);

// Take the first 5 or fewer
let randomRecords = allRecords.slice(0, 5);

const randomRecordsMapped = {
"Linked Records": randomRecords.map((record) => ({
id: record.id,
})),
};

console.info(JSON.stringify(randomRecordsMapped, null, 2));
await mainTable.updateRecordAsync(record.id, randomRecordsMapped);
// @ts-ignore
return;
}

default: {
console.warn("No Linked Records to Sort");
// @ts-ignore
return;
}
}
}
output.text(`You selected this record: ${record.name}`);

const linkedRecordsQuery = await linkedRecordsTable.selectRecordsAsync({
fields : ["Name", "Numbers", "Letters"],
recordIds : record.getCellValue("Linked Records").map(record => record.id)
});
let linkedRecordsObject = linkedRecordsQuery.records.map((record) => ({
RecordID: record.id,
Numbers: record.getCellValue("Numbers"),
Letters: record.getCellValue("Letters"),
}));

let sortingField = await input.buttonsAsync("Sort via Letters or Numbers?", [
"Letters",
"Numbers",
"Random",
]);
let sortedRecords = [];

switch (sortingField) {
case "Letters":
output.text(`Sorting Record:${record.id} by letters 🔠`);

sortedRecords = [...linkedRecordsObject].sort((a, b) =>
a.Letters.localeCompare(b.Letters)
);

break;
case "Numbers":
output.text(`Sorting Record: ${record.id} by numbers 🔢`);
sortedRecords = [...linkedRecordsObject].sort(
(a, b) => a.Numbers - b.Numbers
);
break;
default:
output.text(`Sorting Record: ${record.id} randomly 🎲`);
sortedRecords = [...linkedRecordsObject].sort(
() => Math.random() - 0.5
);
}

const sortedRecordsMapped = {
"Linked Records": sortedRecords.map((record) => ({ id: record.RecordID })),
};

await mainTable.updateRecordAsync(record, sortedRecordsMapped);

Here we can see, the with the script executed for each of the three main records, that they're linked field is sorted alphabetically.

 

It's worth noting - that this is the tip of the iceberg - things will get more complex as your real-world requirements stack up, and then edge-cases start to creep in.

The script I've written itself is arguable too, in that perhaps I could have written or structured things differently. There's a few different ways to approach this Linked Field sorting requirement - However, I only had a spare hour this evening to help - and for someone who's not familiar with JavaScript, understanding this script is a BIG ask - but hopefully with a bit of tinkering you'll be able to get it to do what you need.


Thank you so much for that work! I will study it and I am sure this will get me where I need to go. Thank you again for taking time to help me!

Karlstens
1 person likes this
  • Karlstens
    Karlstens
J
  • James_TCA
  • Participating Frequently
  • 5 replies
  • August 7, 2024

Looks like this topic is over a year old, but had this issue and another solution I found is the https://support.airtable.com/docs/batch-update-extension This will let you select a table and then multiple fields and an action you wish to take. It does not continually update, so if you are making constant changes that require sort order to be rerun you would need to access the extension via automation. I haven't set it up yet, but here are instruction to do so https://support.airtable.com/docs/when-record-is-created-trigger 

H
  • hydephillips
  • New Participant
  • 1 reply
  • August 22, 2024

Hi @Wayne_Merlino ,

I've just had the same challenge - and found a simpler option using Interfaces to show linked records in alphabetical order:

  • using the 'Record review' layout in Interfaces
  • add the field element showing the linked records
  • click on the element and under appearance select 'view' (instead of 'field')
  • click on the list of linked records within the element, and you can then sort by whichever field you want

Here's a quick video showing how it worked in my context: 

https://www.loom.com/share/81aec584b95843ab8f34ac3fbb614613?sid=a1235510-2273-4b26-8cf4-50ff87ab7092 

Not sure if that will work for you if you need to operate from within the back-end table, but could provide a way through.

 

Reply


Most helpful members this week

Terms & Conditions