Comparing records in different fields in the same table

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

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

}
  1. Use this app (manual):

Thanks alot will try it out

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.