AutoSort Linked records based on criteria in the linked table.

[shmuelpalgon]

Hey-

I will spell this out as simply as I can!

I've got 2 tables.

• Event Requests
• Hosts

Table 1 has a linked field titled Possible Hosts

The Possible Hosts field contains records that are automatically linked via automation. [the automation basically finds potential hosts for the event request based on certain criteria such as guest count, amenities, etc and the automation will then add those records as linked records] SO FAR SO GOOD!

My question is: I'd like to now sort the linked records so that the records [aka Hosts] with the highest number of 5 star reviews show up first. We have that data point in the linked table of Hosts. 

The reason why I need this is because we want to then automatically send the top five Possible Hosts [based on number of 5 star reviews located in the field titled Host Score] to the guest via automation so that they can choose from the best hosts. There will be times when there are dozens of linked records from the Host table but ultimately we only want to send the top 5 options to the guest. I understand that a script can help with this. 

Here's what I have so far:

-------------------------------------

let table = base.getTable("Event Requests");

let result = await table.selectRecordsAsync();

let linkedRecords = result.records.map((record) => record.getCellValue("Possible Hosts"));

for (let i = 0; i < linkedRecords.length; i++) {

let linkedTable = base.getTable("Hosts");

let linkedRecordsResult = await linkedTable.selectRecordsAsync({

filterByFormula: `FIND('${result.records[i].id}', {Possible Hosts})`

});

let sortedLinkedRecords = linkedRecordsResult.records.sort((a, b) => {

return b.getCellValue("Host Score") - a.getCellValue("Host Score");

});

let updatedRecord = {

id: result.records[i].id,

fields: { "Possible Hosts": sortedLinkedRecords.map((record) => { return { id: record.id }; }) }

};

await table.updateRecordAsync(updatedRecord);

}

-------------------------------------

The ERROR message I get is: 

TypeError: Cannot assign to read only property '0' of object '[object Array]'

at main on line 10

I'd TRULY appreciate your help with this. Thanks in advance 🙂 

[Karlstens]

Which scripting environment are you using this code in? I'm assuming an Automation Script action?

Reason I ask, filterByFormula isn't available for use within either the Scripting App Extension or the Automation Script action. Instead, you'll need to run a .filter() method on the returned base object.

You'll need something like;

const table = base.getTable("Event Requests");
const query = await table.selectRecordsAsync(
  {
    fields:["Possible Hosts", "Host Score"], 
    sorts: [ {field: "Host Score", direction :"asc"}]});

const filteredQuery = query.records.filter(( record => 
  record.getCellValueAsString('Possible Hosts') === {Possible Hosts}
));

Typically, (but maybe not always) I do the following order.

1. Get the table.
2. Query the table and the needed fields. You can also sort at this stage OR query selected recordIds from your table (that unfortunately return unsorted if you do use this method).
3. Filter the query with .filter() to remove the undesired records.
4. Sort the filtered result if the original query was unsorted.
5. Map the data into various formats typically for use with updating records.
6. Update records.