Discussions

 View Only
Expand all | Collapse all

Combined Text Summary Field (Order and Clean)

Brad Elmore

Brad Elmore01-31-2019 22:31

  • 1.  Combined Text Summary Field (Order and Clean)

    Posted 01-29-2019 19:12
    The Combine Text summary field is great. But what I would like to do is determine the order -- Which I have achieved by creating a [Order] field within my child table....

    [Combine Text Field] = 1 Group A, 2 GroupD, 3 GroupH, 4 GroupY, 5 GroupU, 6 GroupK  

    The new Ordered Combine Text Field is now ordered -- but I want to remove the numbers as well as ";" which is "hidden" in the combine text field.

    Example: when I create a rich text formula field -- and only insert the [Combine Text Field] -- the field is displayed -- [Rich Text Formula fieldA] = 1 Group A, ; 2 GroupD, ; 3 GroupH, ; 4 GroupY, ; 5 GroupU, ; 6 GroupK  

    I would like for the field to display as =  Group A, GroupD, GroupH, GroupY, GroupU, GroupK

    I was almost successful with the formula below -- but the formula would only display the first five Groups --- so if greater than 6 groups --- no luck -- also issue is I had "10" as a position...

    Almost worked formula below = Group A, GroupD, GroupH, GroupY, GroupU, 

    List ("", 
    Part(ToText([Combined Text]),1,"123456789;"),
    Part(ToText([Combined Text]),2,"123456789;"),
    Part(ToText([Combined Text]),3,"123456789;"),
    Part(ToText([Combined Text]),4,"123456789;"),
    Part(ToText([Combined Text]),5,"123456789;"),
    Part(ToText([Combined Text]),6,"123456789;"),
    Part(ToText([Combined Text]),7,"123456789;"),
    Part(ToText([Combined Text]),8,"123456789;"),
    Part(ToText([Combined Text]),9,"123456789;"),
    Part(ToText([Combined Text]),10,"123456789;"),
    Part(ToText([Combined Text]),11,"123456789;"))

    Any advice is greatly appreciated.


  • 2.  RE: Combined Text Summary Field (Order and Clean)

    Posted 01-29-2019 19:55
    var text value = ToText([my combined text summary field]);

    List(", ",
    Trim(Part($value,1,";")),
    Trim(Part($value,2,";")),
    Trim(Part($value,3,";")),
    Trim(Part($value,4,";")),
    Trim(Part($value,5,";")),
    Trim(Part($value,6,";")),
    Trim(Part($value,7,";")),
    Trim(Part($value,8,";")),
    Trim(Part($value,9,";")),
    Trim(Part($value,10,";")),
    Trim(Part($value,11,";")),
    Trim(Part($value,12,";")),
    Trim(Part($value,13,";")),
    Trim(Part($value,14,";")),
    Trim(Part($value,15,";")),
    Trim(Part($value,16,";")),
    Trim(Part($value,17,";")),
    Trim(Part($value,18,";")),
    Trim(Part($value,19,";")),
    Trim(Part($value,20,";")),
    Trim(Part($value,21,";")),
    Trim(Part($value,22,";")),
    Trim(Part($value,23,";")),
    Trim(Part($value,24,";")),
    Trim(Part($value,25,";"))
    )


  • 3.  RE: Combined Text Summary Field (Order and Clean)

    Posted 01-29-2019 20:08
    Mark,

    Your formula works great for removing the ";" --- any suggestions for removing the numbers -- 

    1 Group A, ; 2 GroupD, ; 3 GroupH, ; 4 GroupY, ; 5 GroupU, ; 6 GroupK  

    I use the numbers to allow me to determine the order --- 

    Thank you


  • 4.  RE: Combined Text Summary Field (Order and Clean)

    Posted 05-21-2019 17:18
    Hi I am running into a similar issues and need to figure out how Quickbase sorts the Multi-select Text (summary) field. I need a way to set the sorting order. 

    Thank you


  • 5.  RE: Combined Text Summary Field (Order and Clean)

    Posted 05-21-2019 18:07
    I recently needed to have the Summary field sort by Record ID# which is not alpha.  The raw summary field will sort alpha.

    I used a formula rich text field and prefaced each element with a fake href hyperlink. The hyperlink has a "URL" component, but no words to be displayed, so it ends up being invisible in a formula  Rich Text field.

    "<a href=" & PadLeft(ToText([Record ID#]),7,"0") & ">" & "</a>"




  • 6.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-30-2019 20:35
    Edited by System Admin 10-30-2019 20:36
    Mark, 
    Would you be able to explain this again? I've been trying to figure out where you put this formula, and I'm not having any luck. Is it on the field before you create a combined text? or is it the field that you add to your report? 

    Also, do you happen to have any ideas on how to get this thing to sort by date, from newest to oldest, where the number of  texts fields you're combining will vary? 

    I think if I created a field where it's yyyymmdd and then used your method above I'd get most of the way there. I just would then need to sort it new - old.

    ------------------------------
    Amanda Luna
    ------------------------------



  • 7.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-30-2019 20:53
    Amanda,
    Can you ask a complete question?  You are asking me to re-explain "this".

    As for sorting by date, yes, you will need to make a field in the format YYYYMMDD and have that be the first field so as to sort on that.  The combined text summary field will sort alpha, from low to hight. 

    You will need to zero pad the MM and DD to handle months and days with 1 digit.

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



  • 8.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-31-2019 12:06

    Hi,
    Maybe I didn't reply on top of the comment to which I was referring..

    Would you mind elaborating on this:

    I recently needed to have the Summary field sort by Record ID# which is not alpha.  The raw summary field will sort alpha.
    I used a formula rich text field and prefaced each element with a fake href hyperlink. The hyperlink has a "URL" component, but no words to be displayed, so it ends up being invisible in a formula  Rich Text field.
    "<a href=" & PadLeft(ToText([Record ID#]),7,"0") & ">" & "</a>"


    ------------------------------
    Amanda Luna
    ------------------------------



  • 9.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-31-2019 12:19
    I believe what I did was to that code on the child table data as i needed the Combined text summary field to sort by [Record ID#] so the oldest ones would be first, but I did not want to show the date field.

    For example let's say that the child data had Student names who were wait listed for a class and I wanted to list them in the order they registered, which I deemed to be by [Record ID#] as opposed to alpha. 

    So I created a field on the wait list child record like

    "<a href=" & PadLeft(ToText([Record ID#]),7,"0") & ">" & "</a>" & [Student name]
     
    Then do a combined text summary field
    Then on the parent record make a Formula Rich Text field which will convert the combined text field to text (which will end up being semi colon delimited), and substitute the semi colons with a html <br> character which will force a line break.

    var text ConvertedToText = ToText([my combined text summary field]);

    SearchAndReplace($ConvertedToText, " ; ", "<br>)

    Let me know if that works in your use case.


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



  • 10.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-31-2019 17:25
    There may not be a good answer for this... 

    The fields that I am combining in to one are a rich text field, so when I create a combined text it's changing all the "&" to &amp; and all the line breaks to "&nbsp;" The problem I am now running in to is that the Trim(Part($value,1,";")) is using the first semicolon in the text to break it up, which is sometimes contained in the "&nbsp;" etc. Do you know if there's a way around that? Looks like that is the ticket to getting it to sort new - old. 

    ------------------------------
    Amanda Luna
    ------------------------------



  • 11.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-31-2019 17:41
    If you like contact me directly at mark.shnier@gmail.com and I will have a look at your app with you, "off the clock"

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



  • 12.  RE: Combined Text Summary Field (Order and Clean)

    Posted 11-01-2019 14:34

    Figured out a work around for anybody in the future: 
    1. Append a not commonly used character to the end of the field that you are going to create a combined text out of. I used "|". 
    2.  Then use the
    List(", ",
    Trim(Part($value,1,"|")),
    Trim(Part($value,2,"|")),
    Trim(Part($value,3,"|")),
    Trim(Part($value,4,"|")), Etc 
    Formula posted by Mark, but instead of using a semicolon, you're going to use the special character you appended to the field above. I did warn everybody on staff that that character will break up the text weird if used, and they said they never use it anyway. That's the best I could work out. 



    ------------------------------
    Amanda Luna
    ------------------------------



  • 13.  RE: Combined Text Summary Field (Order and Clean)

    Posted 06-22-2023 15:01

    Thank you so much, this is genius!



    ------------------------------
    Gerry Munroe
    ------------------------------



  • 14.  RE: Combined Text Summary Field (Order and Clean)

    Posted 10-30-2019 23:28
    Is that that applying right(variable, 6) function mentioned above give you the desired outcome to remove the initial digits.

    ------------------------------
    Babi Panjikar
    ------------------------------



  • 15.  RE: Combined Text Summary Field (Order and Clean)

    Posted 01-30-2019 00:21
    List(", ",
    Trim(NotLeft(Part($value,1,";"),2)),
    Trim(NotLeft(Part($value,2,";")),2),

    etc




  • 16.  RE: Combined Text Summary Field (Order and Clean)

    Posted 01-31-2019 22:31
    Thank you Mark.....worked great!