Forum Discussion

JVonnahmeJVonna's avatar
JVonnahmeJVonna
Qrew Trainee
5 years ago

Is there a way to get a value based on comparing 3 different fields across all records and then combining the results for a total ranking?

For each record, I have a field for the bottle weight, closure weight, and label weight. I was wondering if there was a way to have each field compared to all the other record's corresponding field to...
App builders
Formulas and functions
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
No.  This low tech method only allows for a visual of the ranking, but these are not actually fields which can be used in formuals.

But you could for example have a helper table for rankings and use the more .. button on that report to export to another table and then  you would have the values where you could use them in formulas.  But that would be a manual "from time to time" type process to purge the target table and then  re-export into it.
ElwynDow1's avatar
ElwynDow1
Qrew Trainee
3 years ago
Ranking formula using the new Query functions. Will rank records based on numeric field.  Ranks top 10, top 100, etc – or every value…

var bool descOrder = true;            // true if ranking from high to low, otherwise false
var number maxrank = 0;             // 0 to rank every number, else 10 to rank 1 through 10, 100 to rank 1 through 100, etc
var number fieldId = 17;              // the field id of the numeric field being ranked - CHANGE AS NEEDED
var number rankThis = [number field];  // the numeric field being ranked - CHANGE AS NEEDED
var number rank    = If($descOrder, Size(GetCellValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
                                    Size(GetCellValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);
If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))

------------------------------
Elwyn Dow
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    Impressive!  is there a published list of the Transform functions like "Size"?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------