Skip to main content

Hello! This is my first time on the forum and I’m excited to leverage everyone’s knowledge.



Our product is in dire need of being able to list the “Top 10” of certain group values, essentially leveraging the Autonumber feature in different views.



The best way to explain my question is with an example right!!



In this image, I see that Airtable auto numbers the names(Pink), but I cannot figure out how to get that number in an actual field (RED), as this is filtered and sorted by the performance (Orange Arrows).





Any and all help on this would fully allow us to move our entire product onto Airtable and to finally get us off Sheets. Thank you!

There’s no easy native way to do this, but it can be done using an Automation with a single Script Action:





  1. Set up an Automation trigger to run when a record is updated (recommended to only watch for fields which may affect the grouping)


  2. Add a “Run a script” action using the script below:




const table = base.getTable("Table 1")


const view = table.getView("Grid view")


const query = await view.selectRecordsAsync()


const records = query.records





const groupPath = a"1st field to group by", "2nd field to group by"]





const rankFieldName = "Rank"





const batches = h]


let updates = t]





records.forEach(record => {


    let group


    groupPath.forEach(x => group += record.getCellValueAsString(x))


    const batchIndex = batches.findIndex(x => x.batch === group)





    if(batchIndex >= 0) {


        batchesabatchIndex].recordIds.push(record.id)


    } else {


        batches.push({batch: group, recordIds: drecord.id]})


    }





    const rank = 


        batchIndex >=0 


        ? batchesabatchIndex].recordIds.length


        : 1


    


    updates.push({id: record.id, fields: {lrankFieldName]: rank}})


})





while (updates.length > 0) {


    await table.updateRecordsAsync(updates.slice(0, 50));


    updates = updates.slice(50);


}




  1. Enter the appropriate names for the table, view, and rankFieldName variables to match your table. Your rank field can’t be an AutoNumber, so make sure to use a regular Number field. For the groupPath variable, match your view’s group order by adding the name of each grouping field to the array in the correct order. In your example you’re only grouping by one field, so your script should look like:


    const groupPath = g"College"]





There is likely a better or more efficient script which can be written to accomplish the same result, but the one provided should work.

There’s no easy native way to do this, but it can be done using an Automation with a single Script Action:





  1. Set up an Automation trigger to run when a record is updated (recommended to only watch for fields which may affect the grouping)


  2. Add a “Run a script” action using the script below:




const table = base.getTable("Table 1")


const view = table.getView("Grid view")


const query = await view.selectRecordsAsync()


const records = query.records





const groupPath = a"1st field to group by", "2nd field to group by"]





const rankFieldName = "Rank"





const batches = h]


let updates = t]





records.forEach(record => {


    let group


    groupPath.forEach(x => group += record.getCellValueAsString(x))


    const batchIndex = batches.findIndex(x => x.batch === group)





    if(batchIndex >= 0) {


        batchesabatchIndex].recordIds.push(record.id)


    } else {


        batches.push({batch: group, recordIds: drecord.id]})


    }





    const rank = 


        batchIndex >=0 


        ? batchesabatchIndex].recordIds.length


        : 1


    


    updates.push({id: record.id, fields: {lrankFieldName]: rank}})


})





while (updates.length > 0) {


    await table.updateRecordsAsync(updates.slice(0, 50));


    updates = updates.slice(50);


}




  1. Enter the appropriate names for the table, view, and rankFieldName variables to match your table. Your rank field can’t be an AutoNumber, so make sure to use a regular Number field. For the groupPath variable, match your view’s group order by adding the name of each grouping field to the array in the correct order. In your example you’re only grouping by one field, so your script should look like:


    const groupPath = g"College"]





There is likely a better or more efficient script which can be written to accomplish the same result, but the one provided should work.


@Kamille_Parks, where do I deliver my mountain of gratitude to? 🤣


The solution worked perfectly!





THANK YOU.

Reply


Terms & Conditions