cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sammybridge
Helper IV
Helper IV

Sorting Option Set

Hello,

 

I have an option set (single select choice field) which I only have integers in and I am trying to sort these ascending. The option set is for Case Size and since going live an addition needs to be added in but I want my dropdown to show the right ordering. I have tried every type of sorting possible and I can't work out why it isn't working:

 

This is my standard unsorted code: Choices('Case Size Option Set')

 

These are the variations I have tried without success:

 

  • Sort(Choices('Case Size Option Set'), Ascending)
  • Sort(Choices('Case Size Option Set'),"Value", Ascending)
  • SortByColumns(Choices('Case Size Option Set'), Ascending)
  • SortByColumns(Choices('Case Size Option Set'),"Value", Ascending)

 

Can anyone help with this?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
sammybridge
Helper IV
Helper IV

I finally figured it out today, the solution was:

 

Sort(
Choices([@'NPI SKU Informations'].'Bottle Size (Litres)').Value,
Text(Value),
Ascending
)

 

edit: the above only gets it sorted in Text so the below works better for integers

 

Sort(
Choices([@'NPI SKU Informations'].'Bottle Size (Litres)').Value,
Value(Text(Value)),
Ascending
)

View solution in original post

11 REPLIES 11
StevePenner
Helper I
Helper I

@sammybridge 
Does this post help?

Hi @StevePenner 

 

Unfortunately that concept isn't going to work for what I need. Thanks anyway

v-qiaqi-msft
Community Support
Community Support

Hi@sammybridge,

Based on the issue that you mentioned, do you want to sort the integers  within the option set?

Could you please share a bit more about your scenario?

I have a test and the situation is confirmed on my side.

Actually, the option set could bot be sorted. If we try to pull the choices from the option set column, the only way is to collect a collection to store the values, however, if you want to sort the choices within the collection, a error occurs.

All the formula I tried as you listed could not successfully sort.

In a word, the Option Set column could not be sorted in PowerApps.

As an alternative solution, you could try the hard code to sort. It means that you write every choice to a collection and sort the value.

Set the Items property of the Combo Box as below:

 

Sort(Collect(TT,["Chioce1","Chioce2","Chioce3"]),Value)

 

Hope it could help.

Best Regards,

Qi

 

 

Best Regards,
Qi
sammybridge
Helper IV
Helper IV

Hi @v-qiaqi-msft 

 

Yes it is only Integers that I want to sort. Currently I only have around 10 to sort so I could hard code it, but this isn't a suitable solution for me as I want my client to be able to add new records into the Option Set without having to come to me to update the code.

 

This seems like such an easy thing and I don't understand why Microsoft haven't allowed this. Is there any other way that you are aware of?

 

Thanks

sammybridge
Helper IV
Helper IV

Hi @v-qiaqi-msft,

 

Do you know if there are any other solutions I can use for this? Hard coding it isn't a solution for me I'm afraid.

 

Thanks

Hi@sammybridge,

Could you please tell that "add new records into the Option Set without having to come to me to update the code."?

Do you want to sort the whole Choice column based on the Table?

If I understand correctly, you want to write a formula to sort the whole column in a table no matter how many records your clients created, right?

Based on your needs, I think the collection could achieve your needs.

You should create a collection to store the option sets in a Text column, and then sort the Text column.

I do not know how many different column types within your entity, generally, the Collect() function could only collect  Text columns automatically, if there are different column types, please convert it to a Text firstly.

In my scenario, I have only two column types, Text and Option Sets.

Even though I do not know how you add new records, I assume that you use an Edit Form to submit data, so here is a workaround you can check for reference.

Set the OnSelect property of the Submit button as below:

 

SubmitForm(EditForm1);
Clear(Test);
ClearCollect(
    Test,
    AddColumns(
        [@TestCases],
        "NewOptions",
        Value(Text(First(CaseSize).Value))
    )
);
Navigate(Screen1)

 

Note: There is a Gallery in the Screen1 to display and sort data. Once you submit the form, a collection will be created automatically.

Set the Items property of the Gallery as below:

 

SortByColumns(Test,"NewOptions",Ascending)

 

 

52.png

Hope this could help you some degree.

Best Regards,

Qi

Best Regards,
Qi
sammybridge
Helper IV
Helper IV

I finally figured it out today, the solution was:

 

Sort(
Choices([@'NPI SKU Informations'].'Bottle Size (Litres)').Value,
Text(Value),
Ascending
)

 

edit: the above only gets it sorted in Text so the below works better for integers

 

Sort(
Choices([@'NPI SKU Informations'].'Bottle Size (Litres)').Value,
Value(Text(Value)),
Ascending
)

View solution in original post

Sammybridge - this has been bugging me for MONTHS. I cannot thank you enough for your post. Happy Christmas!

Thank you @sammybridge  - this is not intuitive functionality, many thanks for going thru all the iterations!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,382)