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
MarkShnier__You
Qrew Legend
Qrew LegendAn alternative would be to create a concatenated field which would combine all 4 fields and will sort correctly and then just run the Formula Query against that single field. You would need to convert the dates to strings like
20220719 (ie July 19, 2022)
You will need to control where fields with missing dates sort, at the top or the bottom. You can do that with the formula which makes the YYYYMMDD portion of the concatenated field.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
20220719 (ie July 19, 2022)
You will need to control where fields with missing dates sort, at the top or the bottom. You can do that with the formula which makes the YYYYMMDD portion of the concatenated field.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Thank you for the suggestion Mark, I should have specified that only 3 of the 4 fields are date fields. The Build priority is a numerical ranking 1, 2, 3. I believe what you are suggesting would only work if all 4 of the fields are date fields, but perhaps I can append the build priority to the end of the date fields to still make this work. I will give that a try.
------------------------------
Emma Finger
------------------------------
------------------------------
Emma Finger
------------------------------
- MarkShnier__YouFor 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
------------------------------- 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
------------------------------