Skip to main content

Comparing records in different fields in the same table

C

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_Parks11
Forum|alt.badge.img+15

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):
E
1 person likes this
  • E
    edad
C
Kamille_Parks11 wrote:

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

S

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

 

 
E
E
2 people like this
  • E
    E77en
  • E
    edad
E
  • E77en
  • New Participant
  • March 17, 2024
Simon_Damborg1 wrote:

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


Most helpful members this week

Terms & Conditions