Implementing a stack rank function?
I need to implement a stack rank of a list of items in a quickbase table. Is there a way to write a formula to update all records stack ranks when the value is set for a new item?
Forum Discussion
I will try that, but I think that will take up a lot of room on the screen, and certainly not as compact as being able to identify a field on any record as a placeholder or rank. It just surprises me that as Quickbase already counts the number of records in a report (displays the number of records in the report) there isn't a checkbox or something on report set up that allows for row numbering.
- Did you come up with a solution in the end?
------------------------------
Nick Charalambous
nick@em-dev.co.uk
London, UK
------------------------------- 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
------------------------------- Elwyn. Thank you for this code. It almost worked right off the copy. Though I am getting several things ranked 10... even though they might have a score of 19.2 vs 19.4 to compare from. Maybe only integer comparison?
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 = 73; // the field id of the numeric field being ranked - CHANGE AS NEEDED
var number rankThis = [Average Total Score]; // the numeric field being ranked - CHANGE AS NEEDED
So is FieldId 73 is [Average Total Score] in my case. Should it be different?
var number rank = If($descOrder, Size(GetFieldValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
Size(GetFieldValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);
I notice it was throwing a soft error on "GetCellValues" so I switched it to GetFieldValues and seemed to work similarly.
If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))
I really appreciate the hard work and know that I need to put more work into learning language.
------------------------------
Michael Gibson
------------------------------
- MarkShnier__You
Qrew Legend
Not natively. Not sure if it could be done with Pipelines. Maybe try posting a new question and link to this one and ask if anyone thinks that this can be done with pipelines.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
