Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Help having the Autonumber adjust to filter/sort

Solved
Jump to Solution
1862 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Thomas_Coiner
4 - Data Explorer
4 - Data Explorer

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

Screen Shot 2020-12-14 at 9.39.36 AM

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!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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 = ["1st field to group by", "2nd field to group by"]

const rankFieldName = "Rank"

const batches = []
let updates = []

records.forEach(record => {
    let group
    groupPath.forEach(x => group += record.getCellValueAsString(x))
    const batchIndex = batches.findIndex(x => x.batch === group)

    if(batchIndex >= 0) {
        batches[batchIndex].recordIds.push(record.id)
    } else {
        batches.push({batch: group, recordIds: [record.id]})
    }

    const rank = 
        batchIndex >=0 
        ? batches[batchIndex].recordIds.length
        : 1
    
    updates.push({id: record.id, fields: {[rankFieldName]: 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 = ["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.

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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 = ["1st field to group by", "2nd field to group by"]

const rankFieldName = "Rank"

const batches = []
let updates = []

records.forEach(record => {
    let group
    groupPath.forEach(x => group += record.getCellValueAsString(x))
    const batchIndex = batches.findIndex(x => x.batch === group)

    if(batchIndex >= 0) {
        batches[batchIndex].recordIds.push(record.id)
    } else {
        batches.push({batch: group, recordIds: [record.id]})
    }

    const rank = 
        batchIndex >=0 
        ? batches[batchIndex].recordIds.length
        : 1
    
    updates.push({id: record.id, fields: {[rankFieldName]: 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 = ["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? :rofl:
The solution worked perfectly!

Screen Shot 2020-12-15 at 1.20.19 AM

THANK YOU.