Forum Discussion

ElizabethSchlag's avatar
ElizabethSchlag
Qrew Member
7 months ago

Rank Using Query

I am trying to build a query that ranks the weights from high to low in a 1-X format. It is working, except when two records have the same weight it ranks them with the same number. Essentially, I want it to uniquely rank them. Is this possible in Quickbase?

Current Formula:

var text QUERY = "{355.GTE.'" & [Weight] & "'}AND{17.XEX.'Cancelled'}AND{17.XEX.'Complete'}AND{17.XEX.'Hold'}AND{9.XEX.'Research'}";

If([State]="Complete",null,
If([State]="Cancelled",null,
If([State]="Hold",null,
If([Type of Contract]="Research",null,

Size(GetRecords($QUERY))))))



------------------------------
Elizabeth Schlagel
------------------------------

9 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    7 months ago

    Interesting question and one that I don't recall being satisfactorily answered before.

    Here is an example what worked for me to create an arbitrary tie breaker.  I created a new field which was fid 8 called [Value + Record ID# /10000]

    The formula as you might guess was 

    [Value] + [Record ID#] / 10000

    Then I ranked that field using this formula

    var text QUERY = "{8.LTE.'" & [Value + Record ID# /10000] & "'}";
     
    Size(GetRecords($QUERY))
    .
    .
    .
    Note that there were triplicate and duplicate ties for Values 23 and 100 which were broken by the Record ID# / 10000



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------

    • BobThomas1's avatar
      BobThomas1
      Qrew Cadet
      7 months ago

      I looked at this problem as an exercise in code for me. 

      I went and sorted Rank as Mark has from 'Lowest to Highest' and it is the same as above. BUT, one of the things I get frequently are the spaces between data. which I don't want most of the time? 



      ------------------------------
      Bob T.
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        7 months ago

        Your table report is sorting and grouping by rank. Just change it so it sort by rank. The spaces are because you have chosen to have groups by rank.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------