Forum Discussion

CBH_Service_Acc's avatar
CBH_Service_Acc
Qrew Member
2 months ago
Solved

Sorting Combined Text Field by Record ID#

I have a Combined Text summary field titled "Languages Spoken by Admin" that pulls data from a table also named "Languages Spoken by Admin" that I created. I added the combined text field onto a repo...
reports
  • MarkShnier__You's avatar
    MarkShnier__You
    2 months ago

    OK, try this.

    Numeric field called sort on the Child table.

    List("-", PadLeft(ToText([Sort]),2,"0"), [Language])

     

    That will result in a string like 02-Korean

     

    Then make a combined text summary of that field.

    Then use this formula in a formula text field type to strip out the prefixes.

    var text AllLanguagesSorted = ToText([Combined Text Sorted name for roll up]);

    List("\n",
    NotLeft(Part($AllLanguagesSorted,1,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,2,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,3,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,4,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,5,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,6,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,7,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,8,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,9,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,10,";"),"-")
    )

      

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
2 months ago

Combined Text Summary fields always sort alpha.

My suggestion would be to create a different formula text field down in the child table which has a prefix to control the sort. You might want to actually have a numeric field to control the sort and then make a formul field to cal,culate like

02-Korean

But then you would need to have a formula back up on the parent record to strip out that prefix.  I don't have time right now to do the formula, but that would be the concept.

 

Post back if you need help with the formula once you decide to go this direction.

CBH_Service_Acc's avatar
CBH_Service_Acc
Qrew Member
2 months ago

Hey Mark,

Thanks a lot for responding so quickly. This sounds like a good idea, so I will use your direction. Since you're short on time, I'll try working on this on my own, but if you get any time, I would appreciate you helping me with the formula(s) since I haven't used formulas much. I've been using QuickBase for about 4 months now.

A

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 months ago

    OK, try this.

    Numeric field called sort on the Child table.

    List("-", PadLeft(ToText([Sort]),2,"0"), [Language])

     

    That will result in a string like 02-Korean

     

    Then make a combined text summary of that field.

    Then use this formula in a formula text field type to strip out the prefixes.

    var text AllLanguagesSorted = ToText([Combined Text Sorted name for roll up]);

    List("\n",
    NotLeft(Part($AllLanguagesSorted,1,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,2,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,3,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,4,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,5,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,6,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,7,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,8,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,9,";"),"-"),
    NotLeft(Part($AllLanguagesSorted,10,";"),"-")
    )

      

    • CBH_Service_Acc's avatar
      CBH_Service_Acc
      Qrew Member
      2 months ago

      Mark,

      Thanks a lot for this! I'm going to try working on this and see if I can get it working. If I run into any problem, I'll follow-up to see if you can help me out more.

      Thanks again!