Discussions

 View Only
Expand all | Collapse all

Convert Combined Text into a list?

  • 1.  Convert Combined Text into a list?

    Posted 07-10-2020 12:42
    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?

    ------------------------------
    Paul Peterson
    ------------------------------


  • 2.  RE: Convert Combined Text into a list?

    Posted 07-10-2020 15:28
    To convert a combined text summary field into a vertical list, you can use this syntax in a formula text field.

    SearchAndReplace([My Combined Text Summary field], " ; ", "\n")

    The best way to get the min max and count would be to make new summary fields on the same relationships where the Combined Text Summary field was made.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Convert Combined Text into a list?

    Posted 07-17-2020 15:22
    This method doesn't actually generate a list.  Is there any way that will create an actual list rather than something that looks like one?

    ------------------------------
    Paul Peterson
    ------------------------------



  • 4.  RE: Convert Combined Text into a list?

    Posted 07-17-2020 15:25
    What do you mean by "an actual list".  Can you attach an image of what you want it to look like?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Convert Combined Text into a list?

    Posted 07-17-2020 16:14
    It's not a matter of what I want it to look like, it's more a matter of how I can manipulate the data.  I need to get a count of the actual items in the "list", get max and min values over that same list.  I am able to extract some of the data into a summary field, but not for all fields needed.

    ------------------------------
    Paul Peterson
    ------------------------------



  • 6.  RE: Convert Combined Text into a list?

    Posted 07-17-2020 16:49
    I'm still not understanding. 
    I previously posted this above.

    The best way to get the min max and count would be to make new summary fields on the same relationships where the Combined Text Summary field was made.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Convert Combined Text into a list?

    Posted 07-20-2020 12:16
    I had issues getting the summary fields right.  I think I'm finally there.

    ------------------------------
    Paul Peterson
    ------------------------------



  • 8.  RE: Convert Combined Text into a list?

    Posted 08-01-2022 15:22
    Hi,

    I have 2 questions on the Combined Text field.

    1. I am looking into making it a list. I tried the above, which works great. Is there a way to add a character or bullet point before each line?
    2. How can I sort the combined field, based on a specific date field in my child table? 


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



  • 9.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 10:31
    There are 2 ways to make bullets

    https://www.quickbasejunkie.com/blog/multi-select-bullet

    and a formula rich text field like this.

    var text CombinedText = ToText([My Text Concatenation Field for Child names]);

    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


    as for getting the list to sort by date





    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 10.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 14:25
    Thank you very much, that worked perfectly!

    Anything on getting the list to sort by date?

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



  • 11.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 16:34
    Try this trick

    Create a formula text field on the child

    Child formula
    var text RollupText = ToText([Session Date in Words]);
    var text ZeroPaddedDaysfromFiveYearsSAheadFromToday=

    PadLeft(ToText(100000 - ToDays((AdjustYear(Today(),5) - [Date]))),8,"0");


    "<div style=" & $ZeroPaddedDaysfromFiveYearsSAheadFromToday & ">"& $RollupText & "</div>"


    Then make a combined text summary of that field called something like

    [Combined Text Session Text for Combined Text Summary sorted]

    Then make this formula on the Parent.

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



  • 12.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 16:48
    =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
    ------------------------------



  • 13.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 17:06
    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
    ------------------------------



  • 14.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 17:09
    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
    ------------------------------



  • 15.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 17:16
    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
    ------------------------------



  • 16.  RE: Convert Combined Text into a list?

    Posted 08-02-2022 23:39
    I will have to find time to do some testing.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 17.  RE: Convert Combined Text into a list?

    Posted 08-03-2022 09:21
    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
    ------------------------------



  • 18.  RE: Convert Combined Text into a list?

    Posted 08-03-2022 11:35
    Thank you!!
    I'm getting the below error:


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



  • 19.  RE: Convert Combined Text into a list?

    Posted 08-03-2022 12:55
    change 
    var text RawStepOne =

     to 

    var text Raw=

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 20.  RE: Convert Combined Text into a list?

    Posted 08-09-2022 13:13
    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
    ------------------------------



  • 21.  RE: Convert Combined Text into a list?

    Posted 08-09-2022 15:40
    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
    ------------------------------



  • 22.  RE: Convert Combined Text into a list?

    Posted 08-09-2022 16:57


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



  • 23.  RE: Convert Combined Text into a list?

    Posted 08-09-2022 17:03
    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
    ------------------------------



  • 24.  RE: Convert Combined Text into a list?

    Posted 08-09-2022 17:08
    That worked, thank you!!

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