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.'" & [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------------------------------

MarkShnierYou · 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. 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
------------------------------

Sign up

Login to the community