Comparing records in different fields in the same table

[Charles_Dorante]

Hi, I have 3 Fields “Name” “Results” “Position”

I will like to compare the values in “Results” Field and position it from ‘1st - last row’

(Field “Position”)
If “Field Results” Row 1 or Record 1 is > Row 2, then Row 1 = “1st”
else If
“Results” Record 2 > Record 3, then Row 2 = “2nd”
else if
“Results” Record 2 = Record 1, then Row 2 = “1st”

What I want the “Position” Field to do is, compare if Results Field Row 1 is Greater than Row 2 then Position Field Row 1 is equal to “1st”

And if Results Field Row 2 is equal to Row 1 then Position Row 2 will be “1st”
Or if
Row 2 > row 3 then Position row 3 = “3rd”

Finally if “Results” Row 1 = Row 2 then both "Position Row 1 and Row 2 will be “1st”
But “Position” Row 3 will be “3rd”

What formula do I use to achieve this? Help me people

This is what I want to Achieve

[Kamille_Parks]

This can’t be done with a formula alone. Formulas can only reference data in the record to which that formula is applied. You could do one of several things including:

1. Use an automation with a script (see example from the podcast linked below)
   [S09-E07] Full Podcast for 10-26-2021 [RAW ORIGINAL VIDEO] - YouTube

The improved version of the script discussed in that episode is copied below. Since you seem to want absolute ranking (i.e. no grouping), you can set your let groupPath = []

/*

    SCRIPT SETTINGS

        Edit the values in quotes below to match your base's structure

*/

// table containing the records to be ranked

let tableName = "Table name"

// view which groups the records to be ranked, this view should be in the desired sort order

let viewName = "Name of sorted view"

// names of each field by which the view is grouped ~in the correct order~

let groupPath = ["Grouped field 1", "Grouped field 2"]

// Number-type field that contains the ranking

let rankFieldName = "Position"

/*

    QUERIES

        Do not edit anything below this line

*/

let table = base.getTable(tableName)

let view = table.getView(viewName)

let fieldsToQuery = [...groupPath, rankFieldName]

let query = await view.selectRecordsAsync({fields: fieldsToQuery})

let records = query.records

let groups = []

let updates = []

records.forEach(record => {

    let groupName = groupPath.map(fieldName => record.getCellValueAsString(fieldName)).join("")

    let groupIndex = groups.findIndex(x => x.groupName === groupName)

    if(groupIndex >= 0) {

        groups[groupIndex].recordIds.push(record.id)

    } else {

        groups.push({groupName: groupName, recordIds: [record.id]})

    }

    let rank = (

        groupIndex >= 0 ?

        groups[groupIndex].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);

}

2. Use this app (manual):

Airtable

Auto Ranker - Apps - Airtable Marketplace

Generate rank numbers automatically for your records on Airtable with the Auto Ranker app.

[Charles_Dorante]

Thanks alot will try it out

[Simon_Damborg1]

Thanks for the script! I've updated it so that it properly handles ties as well. 

/*

    SCRIPT SETTINGS

        Edit the values in quotes below to match your base's structure

*/

// Table containing the records to be ranked
let tableName = "Table name";

// View which groups the records to be ranked, this view should be in the desired sort order
let viewName = "Name of sorted view";

// Names of each field by which the view is grouped ~in the correct order~
let groupPath = ["Grouped field 1", "Grouped field 2"];

// Field used to determine ties
let tieField = "Tie determining field";

// Number-type field that contains the ranking
let rankFieldName = "Position";

/*

    QUERIES

        Do not edit anything below this line

*/

let table = base.getTable(tableName);
let view = table.getView(viewName);
let fieldsToQuery = [...groupPath, rankFieldName, tieField];

let query = await view.selectRecordsAsync({fields: fieldsToQuery});
let records = query.records;

let groups = [];
let updates = [];
let lastTieValue = null;
let lastRank = 0;

records.forEach(record => {
    let groupName = groupPath.map(fieldName => record.getCellValueAsString(fieldName)).join("");
    let groupIndex = groups.findIndex(x => x.groupName === groupName);

    if(groupIndex >= 0) {
        groups[groupIndex].recordIds.push(record.id);
    } else {
        groups.push({groupName: groupName, recordIds: [record.id]});
        lastTieValue = null;
        lastRank = 0;
    }

    let currentTieValue = record.getCellValueAsString(tieField);
    let rank;

    if(currentTieValue === lastTieValue) {
        rank = lastRank;
    } else {
        rank = groups[groupIndex >= 0 ? groupIndex : groups.length - 1].recordIds.length;
        lastRank = rank;
        lastTieValue = currentTieValue;
    }

    updates.push({id: record.id, fields: {[rankFieldName]: rank}});
});

while(updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

[E77en]

This is what I was looking for! Thank you ^^