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

Page 1 / 1

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:

Use an automation with a script (see example from the podcast linked below)

"S09-E07] Full Podcast for 10-26-2021 1RAW 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) {



        groupspgroupIndex].recordIds.push(record.id)



    } else {



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



    }



    let rank = (



        groupIndex >= 0 ?



        groupspgroupIndex].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);



}

Use this app (manual):

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:

Use an automation with a script (see example from the podcast linked below)

"S09-E07] Full Podcast for 10-26-2021 1RAW 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) {



        groupspgroupIndex].recordIds.push(record.id)



    } else {



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



    }



    let rank = (



        groupIndex >= 0 ?



        groupspgroupIndex].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);



}

Use this app (manual):

Thanks alot will try it out

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

}

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

}

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

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded