Forum Discussion

CBH_Service_Acc's avatar
CBH_Service_Acc
Qrew Trainee
8 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
    8 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
8 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 Trainee
    8 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
      8 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 Trainee
        8 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!