cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SergiP
Level: Powered On

Sort not behaving as expected

hi,

I have a Table (TaulaModels) with 2 columns:  SKU (containing product code) and Nom_Prod (containing product name)

I set up a DropDrown where I want to choose an SKU Number with Items= Sort(TaulaModels, SKU).SKU and it doesn't display any value...

the weird thing is that if I set it up as Item= Sort(TaulaModels, "SKU")   it displays the values, but not sorted!

I tried as well SortByColumns(TaulaModels, "SKU").SKU  and it doesn't display any value either...

 

I don't understand what is wrong, acc to the documentation Sort has no need of "" for column name, while SortbyColumns does need it. Actually the sort works with any string "x"...

what is wrong with my formula for Items? how can I get a DropDown showing me a sorted list of all my product codes under the SKU column of my Table?

thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sort not behaving as expected

It sounds like it must be a limitation of Excel. I mainly use SQL, so I don't have much experience with using Excel in PowerApps. Pulling the data into a collection for lists/reference tables would be a good solution. Something like

 

ClearCollect(TaulaModelsCollection, TaulaModels)

which will collect the data into a collection. Then, you would use TaulaModelsCollection as the source for the drop down or combo box.

 

This has the same 500 item limit as everything else, so you may have to get creative depending on how large your list is. For instance, if you have 1500 items, you would do the initial ClearCollect and then write Collect code that filters out the first 500 items and collects the next 500, then another Collect code that filters out the first 1000 items and collects the final 500.

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Sort not behaving as expected

Hi @SergiP

 

To setup the items property of a drop down control, you don't need to specify the column name at the end of Sort or SortByColumns functions.

 

If you try the following, does that return the data that you expect?

 

Sort(TaulaModels, SKU)
SergiP
Level: Powered On

Re: Sort not behaving as expected

No. 

Sort(TaulaModels, SKU)  is showing an empty Dropdown

Super User
Super User

Re: Sort not behaving as expected

How about this?

 

SortByColumns(TaulaModels, "SKU")

Also, have you set the value property of the drop down to SKU?

 

SergiP
Level: Powered On

Re: Sort not behaving as expected

SortbyColumns doesn't work either.

what do you mean with:   value property of the drop down to SKU

 

how do you set this up? I can't find any property field

DisplayField and SearchField is set to:  ["SKU"]

Super User
Super User

Re: Sort not behaving as expected

Does the following work? The issue could be that you are not defining the sort order. 

 

Sort(TaulaModels, SKU, Ascending)

 For SortByColumns it should be:

 

SortByColumns(TaulaModels, "SKU", Ascending)
SergiP
Level: Powered On

Re: Sort not behaving as expected

the missing Ascending is not changing it, by default Ascending is taken if nothing is entered.

 

I tried and no change, so empty dropdown

Super User
Super User

Re: Sort not behaving as expected

Value is a property of the drop down control, whereas the DisplayFields and SearchFields are properties of the ComboBox control. 

 

If you add a new drop down control to your screen (rather than a ComboBox control), do any of these formulas provide better results?

Super User
Super User

Re: Sort not behaving as expected


I wasn't aware of the default ascending behavior, so thank you for that tip. 

 

Using a new app created from data, I tried the following on a drop down and a combo box with positive results. Are these images similar to your approach?

 

Combo Box Example.pngCombo boxSort Example.pngDrop down

SergiP
Level: Powered On

Re: Sort not behaving as expected

yes, looks the same.

 

I found out that when I do it using a connection to an Excel file it doesn't work.

Sort(TaulaModels,SKU)

 

I created a list and did exactly the same and works perfectly

Sort(Llista_Models, Title)                 the SKU column becomes Title in the list...

 

Is there anything special to do when using Excel files?

I can use the list but I'd preffer to use the excel file instead.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 276 members 1,715 guests
Please welcome our newest community members: