Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 13, 2023 09:50 AM
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.
Jun 13, 2023 10:40 AM
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-r...
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.
Jun 13, 2023 10:53 AM
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?
Jun 13, 2023 04:57 PM
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).
Jun 13, 2023 06:41 PM
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;
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.
Jun 14, 2023 12:55 PM
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!
Jun 15, 2023 05:00 AM
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.
Jun 15, 2023 05:13 PM
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!
Aug 07, 2024 02:13 PM
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
Aug 22, 2024 03:07 AM
Hi @Wayne_Merlino ,
I've just had the same challenge - and found a simpler option using Interfaces to show linked records in alphabetical order:
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.