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

 

 

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (22,619)