Rank using a query
Hi everyone, I have a list of records that I need to rank, first by the expected start date, then by the final date required, then by the build priority, and last using the record ID. One thing t...
Forum Discussion
For my suggestion the components of your concatenated field do not have to be date fields. I was just pointing out that a date field needs to be in the format YYYYMMDD in order to sort correctly.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Thank you @Mark Shnier (Your Quickbase Coach) I did end up using your solution and added a field that changed the format of the date field and appended the build priority to the end of it with a dash.
If(IsNull([Expected Start Date])=false,(ToFormattedText([Expected Start Date],"YYYYMMDD")& "-" & [Build Priority]),
IsNull([Expected Start Date])=true,(ToFormattedText([Final Date Required],"YYYYMMDD")& "-" & [Build Priority]))
I then used the new field in a formula to "rank" the records using the ranking option shared by @Quick Base Junkie in her blog post, which allows me to use the record ID as the differentiator when 2 records are otherwise the same (a tie), in the ranking.
var text QZER = "{289.EX.'true'}AND{58.EX.'"&[Region]&"'}AND{45.EX.'"&[Data Center Name]&"'}AND{291.LT.'"&[For Build Order Combined]&"'}";
var text QONE = "{289.EX.'true'}AND{58.EX.'"&[Region]&"'}AND{45.EX.'"&[Data Center Name]&"'}AND{291.EX.'"&[For Build Order Combined]&"'}AND{10.LTE.'"&[Record ID]&"'}";
Size(GetRecords($QZER))+Size(GetRecords($QONE))
​​
------------------------------
Emma Finger
------------------------------
If(IsNull([Expected Start Date])=false,(ToFormattedText([Expected Start Date],"YYYYMMDD")& "-" & [Build Priority]),
IsNull([Expected Start Date])=true,(ToFormattedText([Final Date Required],"YYYYMMDD")& "-" & [Build Priority]))
I then used the new field in a formula to "rank" the records using the ranking option shared by @Quick Base Junkie in her blog post, which allows me to use the record ID as the differentiator when 2 records are otherwise the same (a tie), in the ranking.
var text QZER = "{289.EX.'true'}AND{58.EX.'"&[Region]&"'}AND{45.EX.'"&[Data Center Name]&"'}AND{291.LT.'"&[For Build Order Combined]&"'}";
var text QONE = "{289.EX.'true'}AND{58.EX.'"&[Region]&"'}AND{45.EX.'"&[Data Center Name]&"'}AND{291.EX.'"&[For Build Order Combined]&"'}AND{10.LTE.'"&[Record ID]&"'}";
Size(GetRecords($QZER))+Size(GetRecords($QONE))
​​
------------------------------
Emma Finger
------------------------------
