Rank using a query

Question

Hi everyone,&nbsp;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.&nbsp; One thing to note is that the expected start date field is usually blank.This is the formula I am using currently, and it is working with the exception that if a record has the expected start date populated then it numbers those and then starts the numbering over again on the records that do not have a date entered in the expected start date.&nbsp; For example if 2 of my records have an expected start date they would be ranked 1 &amp; 2, then the next record (without an expected start date but with the earliest final date required) would again be ranked 1.&nbsp;I have referenced Quickbase Junkies videos which is how I got this far.&nbsp; Any help/suggestions would be appreciated.
var text QZER = "{289.EX.'true'}AND{58.EX.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{148.BF.'"&amp;[Expected Start Date]&amp;"'}";
var text QONE = "{289.EX.'true'}AND{58.EX.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{148.EX.'"&amp;[Expected Start Date]&amp;"'}AND{129.BF.'"&amp;[Final Date Required]&amp;"'}";
var text QTWO = "{289.EX.'true'}AND{58.EX.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{148.EX.'"&amp;[Expected Start Date]&amp;"'}AND{129.EX.'"&amp;[Final Date Required]&amp;"'}AND{47.LT.'"&amp;[Build Priority]&amp;"'}";
var text QTHR = "{289.EX.'true'}AND{58.EX.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{148.EX.'"&amp;[Expected Start Date]&amp;"'}AND{129.EX.'"&amp;[Final Date Required]&amp;"'}AND{47.EX.'"&amp;[Build Priority]&amp;"'}AND{10.LTE.'"&amp;[Record ID]&amp;"'}";
&nbsp;
&nbsp;
Size(GetRecords($QZER))+Size(GetRecords($QONE))+Size(GetRecords($QTWO))+Size(GetRecords($QTHR))------------------------------Emma Finger------------------------------

markshnier__you · Answer

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

emmafinger1 · Answer

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

markshnier__you · Answer

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

quickbasejunkie · Answer

Hi @Emma FingerI like the suggestion from @Mark Shnier (Your Quickbase Coach). It may be cleaner than my solution below if you can get it to work.Looking at the way you have it set up now....Because the blank start dates won't be ranked in your first queries, I'd suggest setting up something like this where you first handle the scenarios where they are not blank, then create rankings for when they are blank (only start the ranking after the total that have start dates).If(not isnull([Expected Start Date]),Size(GetRecords($QZER))+Size(GetRecords($QONE))+Size(GetRecords($QTWO))+Size(GetRecords($QTHR)),Size(GetRecords("{289.EX.'true'}AND{58.EX.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{148.XEX.''}")) + Size(GetRecords($QONE))+Size(GetRecords($QTWO))+Size(GetRecords($QTHR))&nbsp;)​​Let me know if that does the trick.-Sharon​------------------------------Quick Base JunkieQuick Base Junkiehttps://quickbasejunkie.com------------------------------

emmafinger1 · Answer

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.&nbsp; If(IsNull([Expected Start Date])=false,(ToFormattedText([Expected Start Date],"YYYYMMDD")&amp; "-" &amp; [Build Priority]),IsNull([Expected Start Date])=true,(ToFormattedText([Final Date Required],"YYYYMMDD")&amp; "-" &amp; [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.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{291.LT.'"&amp;[For Build Order Combined]&amp;"'}";var text QONE = "{289.EX.'true'}AND{58.EX.'"&amp;[Region]&amp;"'}AND{45.EX.'"&amp;[Data Center Name]&amp;"'}AND{291.EX.'"&amp;[For Build Order Combined]&amp;"'}AND{10.LTE.'"&amp;[Record ID]&amp;"'}";Size(GetRecords($QZER))+Size(GetRecords($QONE))​​------------------------------Emma Finger------------------------------

Forum Discussion

Rank using a query