Forum Discussion
MarkShnier__You
Qrew Champion
I believe that I made a formula on field to do this.
This will sort normally with the oldest at the top. I believe what I wanted here was to sort dates and have them appear as words like
May 27, 2022
you may be OK with dates like 05-27-2022.
The actual date field is called [date] in my example.
The cheat here is to preface the date with a number to control the sort as combined text summary fields sort alpha and have that inside some kind of delimiters which can then be removed by formula.
In my case my dates were never more than 5 years n the future.
Child formula to be created.
var text RollupText = ToText([Date in Text format]);
var text ZeroPaddedDaysfromFiveYearsSAheadFromToday=
PadLeft(ToText(100000 - ToDays((AdjustYear(Today(),5) - [Date]))),8,"0");
"<div style=" & $ZeroPaddedDaysfromFiveYearsSAheadFromToday & ">"& $RollupText & "</div>"
Then roll that up in combined text summary field called [Combined Text Session Text for Combined Text Summary sorted]
Then make a formula field to strip away the extraneous character that were just used to force the sort.
Parent formula
var text Raw =
SearchAndReplace(ToText([Combined Text Session Text for Combined Text Summary sorted]),"</div>","|");
List("\n",
Right(Part($Raw,1,"|"),">"),
Right(Part($Raw,2,"|"),">"),
Right(Part($Raw,3,"|"),">"),
Right(Part($Raw,4,"|"),">"),
Right(Part($Raw,5,"|"),">"),
Right(Part($Raw,6,"|"),">"),
Right(Part($Raw,7,"|"),">"),
Right(Part($Raw,8,"|"),">"),
Right(Part($Raw,9,"|"),">"),
Right(Part($Raw,10,"|"),">"),
Right(Part($Raw,11,"|"),">"),
Right(Part($Raw,12,"|"),">"),
Right(Part($Raw,13,"|"),">"),
Right(Part($Raw,14,"|"),">"),
Right(Part($Raw,15,"|"),">"),
Right(Part($Raw,16,"|"),">"),
Right(Part($Raw,17,"|"),">"),
Right(Part($Raw,18,"|"),">"),
Right(Part($Raw,19,"|"),">"),
Right(Part($Raw,20,"|"),">"),
Right(Part($Raw,10,"|"),">"),
Right(Part($Raw,21,"|"),">"),
Right(Part($Raw,22,"|"),">"),
Right(Part($Raw,23,"|"),">"),
Right(Part($Raw,24,"|"),">"),
Right(Part($Raw,25,"|"),">"),
Right(Part($Raw,26,"|"),">"),
Right(Part($Raw,27,"|"),">"),
Right(Part($Raw,28,"|"),">"),
Right(Part($Raw,29,"|"),">"),
Right(Part($Raw,30,"|"),">"),
Right(Part($Raw,31,"|"),">"),
Right(Part($Raw,32,"|"),">"),
Right(Part($Raw,33,"|"),">"),
Right(Part($Raw,34,"|"),">"),
Right(Part($Raw,35,"|"),">"),
Right(Part($Raw,36,"|"),">"),
Right(Part($Raw,37,"|"),">"),
Right(Part($Raw,38,"|"),">"),
Right(Part($Raw,39,"|"),">"),
Right(Part($Raw,40,"|"),">"),
Right(Part($Raw,41,"|"),">"),
Right(Part($Raw,42,"|"),">"),
Right(Part($Raw,43,"|"),">"),
Right(Part($Raw,44,"|"),">"),
Right(Part($Raw,45,"|"),">"),
Right(Part($Raw,46,"|"),">"),
Right(Part($Raw,47,"|"),">"),
Right(Part($Raw,48,"|"),">"),
Right(Part($Raw,49,"|"),">"),
Right(Part($Raw,50,"|"),">"),
Right(Part($Raw,51,"|"),">"),
Right(Part($Raw,52,"|"),">"),
Right(Part($Raw,53,"|"),">"),
Right(Part($Raw,54,"|"),">"),
Right(Part($Raw,55,"|"),">"),
Right(Part($Raw,56,"|"),">"),
Right(Part($Raw,57,"|"),">"),
Right(Part($Raw,58,"|"),">"),
Right(Part($Raw,59,"|"),">"),
Right(Part($Raw,60,"|"),">"))
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
This will sort normally with the oldest at the top. I believe what I wanted here was to sort dates and have them appear as words like
May 27, 2022
you may be OK with dates like 05-27-2022.
The actual date field is called [date] in my example.
The cheat here is to preface the date with a number to control the sort as combined text summary fields sort alpha and have that inside some kind of delimiters which can then be removed by formula.
In my case my dates were never more than 5 years n the future.
Child formula to be created.
var text RollupText = ToText([Date in Text format]);
var text ZeroPaddedDaysfromFiveYearsSAheadFromToday=
PadLeft(ToText(100000 - ToDays((AdjustYear(Today(),5) - [Date]))),8,"0");
"<div style=" & $ZeroPaddedDaysfromFiveYearsSAheadFromToday & ">"& $RollupText & "</div>"
Then roll that up in combined text summary field called [Combined Text Session Text for Combined Text Summary sorted]
Then make a formula field to strip away the extraneous character that were just used to force the sort.
Parent formula
var text Raw =
SearchAndReplace(ToText([Combined Text Session Text for Combined Text Summary sorted]),"</div>","|");
List("\n",
Right(Part($Raw,1,"|"),">"),
Right(Part($Raw,2,"|"),">"),
Right(Part($Raw,3,"|"),">"),
Right(Part($Raw,4,"|"),">"),
Right(Part($Raw,5,"|"),">"),
Right(Part($Raw,6,"|"),">"),
Right(Part($Raw,7,"|"),">"),
Right(Part($Raw,8,"|"),">"),
Right(Part($Raw,9,"|"),">"),
Right(Part($Raw,10,"|"),">"),
Right(Part($Raw,11,"|"),">"),
Right(Part($Raw,12,"|"),">"),
Right(Part($Raw,13,"|"),">"),
Right(Part($Raw,14,"|"),">"),
Right(Part($Raw,15,"|"),">"),
Right(Part($Raw,16,"|"),">"),
Right(Part($Raw,17,"|"),">"),
Right(Part($Raw,18,"|"),">"),
Right(Part($Raw,19,"|"),">"),
Right(Part($Raw,20,"|"),">"),
Right(Part($Raw,10,"|"),">"),
Right(Part($Raw,21,"|"),">"),
Right(Part($Raw,22,"|"),">"),
Right(Part($Raw,23,"|"),">"),
Right(Part($Raw,24,"|"),">"),
Right(Part($Raw,25,"|"),">"),
Right(Part($Raw,26,"|"),">"),
Right(Part($Raw,27,"|"),">"),
Right(Part($Raw,28,"|"),">"),
Right(Part($Raw,29,"|"),">"),
Right(Part($Raw,30,"|"),">"),
Right(Part($Raw,31,"|"),">"),
Right(Part($Raw,32,"|"),">"),
Right(Part($Raw,33,"|"),">"),
Right(Part($Raw,34,"|"),">"),
Right(Part($Raw,35,"|"),">"),
Right(Part($Raw,36,"|"),">"),
Right(Part($Raw,37,"|"),">"),
Right(Part($Raw,38,"|"),">"),
Right(Part($Raw,39,"|"),">"),
Right(Part($Raw,40,"|"),">"),
Right(Part($Raw,41,"|"),">"),
Right(Part($Raw,42,"|"),">"),
Right(Part($Raw,43,"|"),">"),
Right(Part($Raw,44,"|"),">"),
Right(Part($Raw,45,"|"),">"),
Right(Part($Raw,46,"|"),">"),
Right(Part($Raw,47,"|"),">"),
Right(Part($Raw,48,"|"),">"),
Right(Part($Raw,49,"|"),">"),
Right(Part($Raw,50,"|"),">"),
Right(Part($Raw,51,"|"),">"),
Right(Part($Raw,52,"|"),">"),
Right(Part($Raw,53,"|"),">"),
Right(Part($Raw,54,"|"),">"),
Right(Part($Raw,55,"|"),">"),
Right(Part($Raw,56,"|"),">"),
Right(Part($Raw,57,"|"),">"),
Right(Part($Raw,58,"|"),">"),
Right(Part($Raw,59,"|"),">"),
Right(Part($Raw,60,"|"),">"))
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
MelissaFreel
2 years agoQrew Member
Thanks Mark! Works just as intended!
------------------------------
Melissa Freel
------------------------------
------------------------------
Melissa Freel
------------------------------