Discussions

Expand all | Collapse all

Formula to turn a Combined Text Summary field into a vertical list

  • 1.  Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-24-2019 17:22
    Sometimes you may not want the "bubble" appearance of the native Combined Text Summary field on a report.  Here is a fun formula to change the appearance of a Combined Text Summary field into a vertical list.  

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

    var text RemoveSpaceAfterSemiColon = SearchAndReplace($CombinedText, "; ",";");

    // substitute new line for semicolon
    SearchAndReplace($RemoveSpaceAfterSemiColon,";","\n")




  • 2.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-25-2019 12:54
    Nice. I haven't had a chance to try out the new formula functions yet, but they seem useful...


  • 3.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 12-20-2019 08:58
    Edited by Michael Zeppieri 12-20-2019 09:00
    Thank you for sharing this. I am trying to do something similar, but can't seem to figure out where to insert your formula. I have a combined text list that I am pulling from a child table as a consolidated list in the parent table through the table relationship. When referencing this combined text field as a column in a report, it renders as a continuous list, with each item surrounded by a bubble. I want to:

    - Render the text as a vertical stack
    - Remove the bubbles
    - Render a bullet in front of each line item

    Do I insert your formula into the formula section at the report level, and is it possible to modify the formula to add bullets and remove the bubbles? Thank you!




  • 4.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 12-20-2019 09:23
    Edited by Mark Shnier (YQC) 02-06-2020 13:17
    This would be a new text Rich Text formula field.  

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

    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


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



  • 5.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 09:45
    I used this 2nd formula and it works great.
    However, there are times when my combined text field is blank because our users will be answering that question at a later time.
    In those cases, a bullet point still appears. How can I remove those lonely black dots?

    ------------------------------
    Jennifer Schafer
    ------------------------------



  • 6.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 10:07
    Hmmmm, that is peculiar.  Is there a chance that our source data for the text field on the child record that is being summary has an entry with spaces as the entry.

    Can you try making a child on the child table 
    Trim([my text field to be summarized])

    but, D'oh,  actually I have a way better idea as I type.

    You are telling me that you do not want to include blank children in your Combined Text Summary field, so change the summary field to filter out the blank children.  Probably still a good idea to make the TRIM (  ) field and summarize that field and filter where that TRIM ( ) is not blank

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



  • 7.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 10:29
      |   view attached
    My parent table is Participants and child is Enrollments.
    I attached a report that shows numeric summaries of how many programs they are actively enrolled in and have exited. Also, combined text summary fields of what those programs are. As you can see if they don't have an exit date yet there is no program in the exit column just black dots. I included screenshots of the summary fields. How would I filter out the blank children?

    ------------------------------
    Jennifer Schafer
    ------------------------------

    Attachment(s)

    docx
    bulleted list.docx   734K 1 version


  • 8.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 10:37
    Can you filter out on the combined text summary field where the exit date is blank?

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



  • 9.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 11:04
    When all of the conditions are true...
    combined text field [Active] summarizes when "Exit Date is equal to blank" 
    combined text field [Exited] summarizes when "Exit Date is not equal to blank"

    Are you saying that I should change something in the [Active] summary field?
    I guess I don't understand what to "filter out".

    Note: blank bullets appear in both the active and exited columns when the relative combined text field is blank.

    ------------------------------
    Jennifer Schafer
    ------------------------------



  • 10.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 11:18
    Can you tell me what field is being summarized?

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



  • 11.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 11:26
      |   view attached
    Program Name.

    I just realized that the document I uploaded had only 1 of the 3 screenshots so I attached a new doc with all 3.

    ------------------------------
    Jennifer Schafer
    ------------------------------

    Attachment(s)

    docx
    bulleted list 2.docx   1.50MB 1 version


  • 12.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 11:31
    Can you post your formula for the field with the bullets?

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



  • 13.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 12:44
    var text CombinedText = ToText([Active]);

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

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

    ------------------------------
    Jennifer Schafer
    ------------------------------



  • 14.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 12:49
    Can you try one last thing and if that does not work I'm willing to have a quick look at your app on a GTM session.
    Can you add to the summary field filters where Program Name is not equal to blank?

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



  • 15.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 13:02
    Using your suggestion, I tried it a couple of ways. Still have the dots.

    ------------------------------
    Jennifer Schafer
    ------------------------------



  • 16.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 13:19
    Just to close the loop on this, I got in a GTM with Jennifer and immediately discovered that my post with the bullets formula had an extra space,  (now fixed in this thread way above)

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



  • 17.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 02-06-2020 13:20
    I appreciate the assist and the super quick fix!

    ------------------------------
    Jennifer Schafer
    ------------------------------



  • 18.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 24 days ago
    Can you create a list without bullets? In other words, can you take a combined Summary Field, and eventually find a way to convert it back to a user list to send notifications from?

    ------------------------------
    Rob Salaj
    ------------------------------



  • 19.  RE: Formula to turn a Combined Text Summary field into a vertical list

    Posted 24 days ago
    yes
    if you had a Combined Text Summary field of the text value of the userids, then on the Parent you can use this formula.  Just keep adding as many rows to the formula as you need. 

    var text value = ToText([Combined Text Userid in text format]);

    ToUserList(
    ToUser(Trim(Part($value,1,";"))),
    ToUser(Trim(Part($value,2,";"))),
    ToUser(Trim(Part($value,3,";")))
    )

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