Forum Discussion

CBH_Service_Acc's avatar
CBH_Service_Acc
Qrew Member
11 days 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 report on my "PSL Survey" table, but the problem is that the data is automatically sorted alphabetically (see 1st screenshot attached) and not how it's entered on the table shown in the 2nd screenshot (see attached). Does anyone know how I can sort the data by it's Record ID#?

NOTE: I have read through different posts that suggest creating a formula text field, but I would like better explanation here on this post.

reports
  • MarkShnier__You's avatar
    MarkShnier__You
    11 days 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
    11 days 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
      11 days 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
        11 days 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,";"),"-")
        )