Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Comparing records in different fields in the same table

Topic Labels: Formulas
4684 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Charles_Dorante
4 - Data Explorer
4 - Data Explorer

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

2

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
3

4 Replies 4

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

Simon_Damborg1
4 - Data Explorer
4 - Data Explorer

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 ^^