Help

Calculate record number in particular view

Topic Labels: Formulas
Solved
Jump to Solution
2388 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Nat_Wiseman
5 - Automation Enthusiast
5 - Automation Enthusiast

I would like to capture the record number that appears on the LHS of each record in a particular view in a field (when you hover over the record this number turns into a check box).

I want to print labels for each record, and I want to have a field which shows what number the label is out of the total records in that view (i.e. “3 of 10”).

Or is there another way to calculate the order of records using formulas?

Any help would be much appreciated!

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Nat_Wiseman! :grinning_face_with_big_eyes: Record order is stored internally by Airtable on a view-by-view basis, but that order isn’t made available to either formulas or scripts (the former largely because formulas operate at the record level, not the table/view level).

However, when using a script—either in the scripting block or an automation—to collect the full list of records in a specific view (i.e. not just the table as a whole), they are collected in the same order that they appear in the view. With that in mind, the script below will add an order label to each record in a specific view.

Add a single line text field to your table to contain the label. Add a Scripting block, paste the script into the editor, tweak the three variable values at the top, and run it to label your records. Just keep in mind that if the record order changes for any reason—whether it be sorting or manual dragging—you’ll have to run the script again to re-label all records.

// Customize these values
let tableName = "My Table";
let viewName = "My View";
let labelField = "View Order";

// Main script starts here
let table = base.getTable(tableName);
let view = table.getView(viewName);
let query = await view.selectRecordsAsync();

let labels = [];
query.records.forEach(record => {
    let index = query.records.indexOf(record) + 1;
    labels.push({
        id: record.id,
        fields: {
            [labelField]: `${index} of ${query.records.length}`
        }
    })
});

// Update all records
while (labels.length) {
    await table.updateRecordsAsync(labels.slice(0, 50));
    labels = labels.slice(50);
}

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Nat_Wiseman! :grinning_face_with_big_eyes: Record order is stored internally by Airtable on a view-by-view basis, but that order isn’t made available to either formulas or scripts (the former largely because formulas operate at the record level, not the table/view level).

However, when using a script—either in the scripting block or an automation—to collect the full list of records in a specific view (i.e. not just the table as a whole), they are collected in the same order that they appear in the view. With that in mind, the script below will add an order label to each record in a specific view.

Add a single line text field to your table to contain the label. Add a Scripting block, paste the script into the editor, tweak the three variable values at the top, and run it to label your records. Just keep in mind that if the record order changes for any reason—whether it be sorting or manual dragging—you’ll have to run the script again to re-label all records.

// Customize these values
let tableName = "My Table";
let viewName = "My View";
let labelField = "View Order";

// Main script starts here
let table = base.getTable(tableName);
let view = table.getView(viewName);
let query = await view.selectRecordsAsync();

let labels = [];
query.records.forEach(record => {
    let index = query.records.indexOf(record) + 1;
    labels.push({
        id: record.id,
        fields: {
            [labelField]: `${index} of ${query.records.length}`
        }
    })
});

// Update all records
while (labels.length) {
    await table.updateRecordsAsync(labels.slice(0, 50));
    labels = labels.slice(50);
}

Thanks so much Justin! Worked like a dream, thanks for making it so easy to implement from a non-scripting newbie :slightly_smiling_face: