Forum Discussion

PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
6 years ago

Convert Combined Text into a list?

What is the best way to convert a combined text summary field to a list?  Also, is it possible to find the highest value, lowest value and total items in the field? ------------------------------ ...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 years ago
=Change the [session date in words] field to the text field that you are trying to float up to the parent record ... what you are doing the combined text on now, but don't like the alpha sort.

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
EstherSchonfeld's avatar
EstherSchonfeld
Qrew Member
3 years ago
Thank you so much for your help!
One more final question. How do I combine the 2 fields? I want the RTF field so I have the bullets and I want the sort order. 

Parent table:
Field 1: EOD Status List:

var text CombinedText = ToText([EOD Status Combined Text]);

var text ConvertToVerticalListWithBullets=
Trim(SearchAndReplace($CombinedText, " ; ","<br>&#8226")); // separate entries with a new line and a bullet

If($ConvertToVerticalListWithBullets<>"", "&#8226") // this is a bullet for the first entry
&
$ConvertToVerticalListWithBullets




Field 2: EOD Status List:
var text Raw =

SearchAndReplace(ToText([EOD 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,"|"),">"))

------------------------------
Esther Schonfeld
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    If your question is that the child table has a pair of fields that you want rolled up together then need to make a formula text field which combines those two fields into one field and then roll up that one field.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • EstherSchonfeld's avatar
    EstherSchonfeld
    Qrew Member
    3 years ago
    No, I want to combine the formatting, not the text
    It's the same text. One formats it with bullets and one sorts it

    ------------------------------
    Esther Schonfeld
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    I will have to find time to do some testing.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    OK, not tested but try this as a formula Rich Text field

    var text RawStepOne =

    SearchAndReplace(ToText([EOD Combined Text Session Text for Combined Text Summary sorted]),"</div>","|");

    var text RawStepTwo=
    List("<br>&#8226",
    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,"|"),">"));

    IF($RawStepTwo<>"",
    "#8226" & $RawStepTwo)


    LMK if it works!


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • EstherSchonfeld's avatar
    EstherSchonfeld
    Qrew Member
    3 years ago
    Thank you!!
    I'm getting the below error:


    ------------------------------
    Esther Schonfeld
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    change 
    var text RawStepOne =

     to 

    var text Raw=

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • EstherSchonfeld's avatar
    EstherSchonfeld
    Qrew Member
    3 years ago
    Sorry for the bother, I have one final question. How can I get the first line to display a bullet?

    var text Raw =

    SearchAndReplace(ToText([EOD Combined Text Session Text for Combined Text Summary sorted]),"</div>","|");

    var text RawStepTwo=
    List("<br>&#8226",
    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,"|"),">"));

    If($RawStepTwo<>"",
    "#8226" & $RawStepTwo)

    ------------------------------
    Esther Schonfeld
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    I'm surprised that the formula above does not start with a bullet. Can you post a screenshot of what you are seeing as a result of the formula above?

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • EstherSchonfeld's avatar
    EstherSchonfeld
    Qrew Member
    3 years ago


    ------------------------------
    Esther Schonfeld
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Thx for the screen shot.  I had an "aha" moment seeing it.

    Try changing this line to this. I was missing an &

    IF($RawStepTwo<>"",
    "&#8226" & $RawStepTwo)

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • EstherSchonfeld's avatar
    EstherSchonfeld
    Qrew Member
    3 years ago
    That worked, thank you!!

    ------------------------------
    Esther Schonfeld
    ------------------------------