Answered

sort data using choice column by the definition order

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Is it possible to sort information that includes a choice column by the order in the choice column's definition? So for example, there is a column with choices defined in this order:

Choice Z

Choice C

Choice B

Choice A

If I want to pull together a bunch of records with counts for each of those values, and I want to display them in a gallery and order it by not the choice value alphabetically, but by the definition order, like this:

Choice Z - 4

Choice C - 8

Choice B - 0

Choice A - 1

Can it be sorted by the defined order? I tried pulling in 'Choices([@SP].field) but the resulting table only has the Values and not also an ID. I was thinking if I can get the ID and Value, then I could sort by that ID.

Is it possible to get the value and ID? (I'm guessing there is an ID) Since there can be quite a few choices, I don't want to hard code it.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Anonymous - I am not clear on what you mean by counting each of the values.

If you want to sort a SharePoint choice column using a defined sort order, here are couple of examples:

Sort( AddColumns( 'Your SharePoint List', "_custom_sort", Switch( 'Your Choice Column'.Value, "Choice Z", 4, "Choice C", 8, "Choice B", 0, "Choice A", 1 ) ), _custom_sort, SortOrder.Ascending )

With( { _sortIndex: [ { Index: 4, ChoiceName: "Choice Z"}, { Index: 8, ChoiceName: "Choice C"}, { Index: 0, ChoiceName: "Choice B"}, { Index: 1, ChoiceName: "Choice A"} ] }, Sort( 'Your SharePoint List', LookUp( _sortIndex, 'Your Choice Column'.Value = _sortIndex[@ChoiceName] ).Index, SortOrder.Ascending ) )

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

but the would somewhat hard code it and if there's a lot of choices, it wouldn't seem feasible. I was hoping there was an internal mapping, so if choices are added/deleted/modified, I don't have to change the code.

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Let's suppose your SharePoint List is called List A.

You can create a separate SharePoint list (List B) and populate it with each of your Choice values against a single line text column.

You can then add a Lookup column from List B into List A.

The Lookup data type includes two accessible attributes, the SharePoint ID for the record (Id) in List B, and the Value for the record in List B. You can leverage that as your "automated" sort definition:

Sort( AddColumns( 'Your SharePoint', "_custom_sort", 'Your Lookup Column'.Id //notice the case is "Id", not "ID" ), _custom_sort, SortOrder.Ascending )

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I ended up adding a collection that loaded the choices and as loading it, counted the rows and used that +1 as a sort order column. Then sorted by that. This makes it soft enough should more statuses be added.

Was this reply helpful? Yes No