Help

Re: Can you sort multiple linked records inside an individual cell?

2145 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Wayne_Merlino
6 - Interface Innovator
6 - Interface Innovator

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. 

7 Replies 7

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.

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?

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).

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!

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.

Karlstens_0-1686829872885.png

 

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.

Karlstens_1-1686829901107.png

 

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.

Karlstens_2-1686830134828.png

 

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!