Forum Discussion

JVonnahmeJVonna's avatar
JVonnahmeJVonna
Qrew Trainee
6 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
6 years ago
For a native solution to rank on just one attribute at a time, you can use a summary report in a particular way.  The results will be visible, but will not give a field on the record.

So what you do is to create a formula numeric field called

[Ranking]

with a formula of 1

Then make a Summary Report type to Group Rows by Item, and sort the report on the weight.  Then include the field ranking but set that field to use totals running down the page.

Once you make that report, then you will need to make two more reports to sort on he other two attributes.
  • JVonnahmeJVonna's avatar
    JVonnahmeJVonna
    Qrew Trainee
    6 years ago
    Hi! Thank you for the response. Would you be able to show me an example of this?
  • JVonnahmeJVonna's avatar
    JVonnahmeJVonna
    Qrew Trainee
    6 years ago
    Once you make all three, is there a way to combine the results to give an overall ranking?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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
      4 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
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Impressive!  is there a published list of the Transform functions like "Size"?

        ------------------------------
        Mark Shnier (YQC)
        [email protected]
        ------------------------------