Rank Using Query

Question

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.'" &amp; [Weight] &amp; "'}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------------------------------

markshnier__you · Answer

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.&nbsp; I created a new field which was fid 8 called [Value + Record ID# /10000]
The formula as you might guess was&nbsp;
[Value] + [Record ID#] / 10000

Then I ranked that field using this formula
var text QUERY = "{8.LTE.'" &amp; [Value + Record ID# /10000] &amp; "'}";
&nbsp;
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 · Answer

I looked at this problem as an exercise in code for me.&nbsp;
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?&nbsp;------------------------------Bob T.------------------------------

markshnier__you · Answer

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

bobthomas1 · Answer

Thanks Mike. I see that when I sorted on the column Rank it was also grouping them. Customizing the sort to rank from low to high in the report settings worked.------------------------------Bob T.------------------------------

bobthomas1 · Answer

Learning the query formula methods from Community board is very helpful to me. I have been cleaning up old forms and using new forms, though as I understand the new forms don't embed reports, though maybe that is being worked on?&nbsp;------------------------------Bob T.------------------------------

Forum Discussion

Rank Using Query