Re: Can you sort multiple linked records inside an individual cell?
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.
