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?
Solved! Go to Solution.
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
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.
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?
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"]
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)
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?
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?
yes, looks the same.
I found out that when I do it using a connection to an Excel file it doesn't work.
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.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
Continue your learning in our online communities.
Features releasing from October 2019 through March 2020
Coming to a city near you
Fill out a quick form to claim your user group badge now!
Connect, share, and learn with your peers year-round