Sorting Combined Text Field by Record ID#

Question

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.

MarkShnierYou · Accepted Answer

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("
",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,";"),"-"))

MarkShnierYou · Answer

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.

Sign up

Login to the community