New Field with the top 3 values of other X fields

[mariana-ggg]

Hi

I have 8 fields with numerical values and I want to create a "summary" in a new field, which would show per each record which of those 8 are the 3 top ranked fields (name_field&value). Is that possible?

Thanks

[Stephen_Orr1]

The easiest way to do this is with an Airtable script. Your trigger could be when any of the number fields are updated (ie, "When a record is updated") and your action could be to run a script that reads all of the number fields into an array, then sorts the array, then extracts the first 3 elements using slice, and finally outputs this to a field.

[mariana-ggg]

Thanks @Stephen_Orr1 

Unfortunately a rookie with scripts, but will try to learn 🙂 

by the way, you are not the Stephen Orr who worked in Zurich, right? 🤗

[ScottWorld]

@mariana-ggg The non-scripting, no-code way of doing this easily & quickly would be to use Make, which lets you search for the first X number of records sorted in a specific way.  This is one of the key reasons that people use Make instead of other automation tools… because it offers this functionality built into the product. 

However, there is also a convoluted way of doing this natively in Airtable using the trick that I describe in this podcast episode, but I only show how to do it for one field. You’d have to do a lot of extra work to extend this trick out to 3 different fields.

p.s. If you go down the Make path, there can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. 

[Stephen_Orr1]

@mariana-ggg ah I'm a different Stephen Orr 🙂 Strangely similar backgrounds though if I'm looking at the person you're thinking of on linkedIn...

This script should work for you:

//Automation script: Sort numeric field values of the trigger record and output top n values
//Input variables: "url", {Airtable record URL}
//by Stephen_Orr1, June 12 2023

//declare config variables
let trigRecUrl = await input.config().url
let trigBaseId = trigRecUrl.match(/(app[^\/]+)/)[0]
let trigTableId = trigRecUrl.match(/(tbl[^\/]+)/)[0]
let trigRecId = trigRecUrl.match(/(rec[^\/]+)/)[0]

//define numeric fields to capture
let trigRecFieldNames = [
    'num1',
    'num2',
    'num3',
    'num4',
    'num5',
    'num6',
    'num7',
    'num8',
]

//define number of sorted fields to ouput (n)
let n = 3

//query the record
let table = base.getTable(trigTableId)
let queryResult = await table.selectRecordsAsync({fields: trigRecFieldNames})
let record = queryResult.getRecord(trigRecId)

//create array of field values
let trigRecValues = trigRecFieldNames.map(f => parseFloat(record.getCellValue(f)) || parseFloat(record.getCellValue(f).name))

//sort array of field values descending
trigRecValues.sort((a, b) => b - a)

//output top n values, reference topValues in next automation step
output.set('topValues', trigRecValues.slice(0, n))

To set it up, first create a new automation with the trigger step as "when a record is updated" and then select all of the number fields to watch for changes.

Then add a "Run a script" action and paste the code above. Create an input variable on the left side of the code editor that looks like this (name it "url" and click the blue + icon to select "Airtable record URL"):

Finally, add another action to "Update record" and populate a field of your choice with the output of the script called "topValues". Turn on the automation and try updating a number to see it in action.

Hope that helps!
-Stephen

[Stephen_Orr1 · Accepted solution]

In my previous post, I did not include the name of the field in the output, only the values. Here is the updated version that includes the name of the field:

//Automation script: Sort numeric field values of the trigger record and output top n values
//Input variables: "url", {Airtable record URL}
//by Stephen_Orr1, June 12 2023

//declare config variables
let trigRecUrl = await input.config().url
let trigBaseId = trigRecUrl.match(/(app[^\/]+)/)[0]
let trigTableId = trigRecUrl.match(/(tbl[^\/]+)/)[0]
let trigRecId = trigRecUrl.match(/(rec[^\/]+)/)[0]

//define numeric fields to capture
let trigRecFieldNames = [
    'num1',
    'num2',
    'num3',
    'num4',
    'num5',
    'num6',
    'num7',
    'num8',
]

//define number of sorted fields to ouput (n)
let n = 3

//query the record
let table = base.getTable(trigTableId)
let queryResult = await table.selectRecordsAsync({fields: trigRecFieldNames})
let record = queryResult.getRecord(trigRecId)

//create array of field value objects
let trigRecValues = trigRecFieldNames.map(f => ({field: f, val: parseFloat(record.getCellValue(f)) || parseFloat(record.getCellValue(f).name)}))

//sort array of field value objects descending
trigRecValues.sort((a, b) => b.val - a.val)

//convert array of field value objects to array of strings
let trigRecValuesStr = trigRecValues.map(f => `${f.field}: ${f.val}`)

//output top n values, reference topValues in next automation step
output.set('topValues', trigRecValuesStr.slice(0, n))

[mariana-ggg]

@Stephen_Orr1 you are a star! thank you so much - it worked