SortbyColumns on Option Set

[Anonymous]

Hi,

I have a gallery I want to sort by a column of a CDS entity. This column is a local option set.

When I add the column to the 'sortbycolumns' function I get the error:
"The specified column does not exist or is an invalid sort column type"

Anyone know how to make this work?

Thanks

Sam

[eka24]

What formula Are you using

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Drrickryp]

Hi @Anonymous 

If @eka24 can come up with a solution, my hat is off to him.  I have tried numerous ways to do this and so far have not found one other than to manually create a hard coded collection of the values you have in the Option set and put it in the OnStart of the app 

Something like 

Sort(Collect(colStatus,["New","Shipped","InProgress","Closed"]),Value)

 I have tried a number of ways to sort an option set that wasn't sorted to begin with and could not do it.  BTW @Anonymous , you definitely have a knack for asking the impossible. 

[Anonymous]

@eka24 Here is the formula I'm trying to use:

SortByColumns(Tasks,"cr68b_priority",[Priority.Urgent,Priority.High,Priority.Normal,Priority.Low])

@Drrickryp  Haha, yes well I only like to post when I'm really stumped. How does setting that collection up help here? I still need to choose a column from the original CDS entity. The only thing I can think of is to load the values without the sort into a collection with an additional column which would store the text value of the 'priority' field. Then I could set my gallery to call the collection and sort by the new text field. 

Thanks!

[Drrickryp]

Hi @Anonymous 

I tried that but it didn't work. 'Order status' is an option set.  I tried ClearCollect(colStatus,Distinct(Orders,'Order Status')) and that worked to create the collection colStatus, but if I tried to sort colStatus by Result it errored out and if I tried to sort it by Value it produced a null set. My suggestion above using a hard coded collection may work but it defeats the purpose of having an option set field in the entity.

[Anonymous]

This is what I've come up with that seems to work:

SortByColumns(AddColumns(Tasks,"Priority_Text",Text(Priority)),"Priority_Text",["Urgent","High","Normal","Low"])

Let me know if there's a better way.

[Drrickryp]

@Anonymous 

I don't think so.  Your solution is essentially the same principle as mine, creating a hard coded replacement for the option set field. In one of his videos, Shane Young refers to option sets in CDS and differentiates them from Options in SharePoint.  He mentions that they are difficult to work with and I believe he suggests creating another entity just for the options and referencing it in a one to many relationship. If you do that, then there is an easy way to add new options and modify ones already created from within PowerApps.

[Drrickryp]

@Anonymous 

This all gets back to my aversion to using compound fields in a database table.  In the case of the Option set in CDS, this is really a table consisting of  ID and Value columns.  I am a purist with respect to database principles and as I have discussed previously many times, in https://powerusers.microsoft.com/t5/News-Announcements/Relational-database-design-and-PowerApps-Step-2-Data-Gathering/ba-p/188092 and in https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-Step-3-Keys-and/ba-p/188640  each intersection of a row and column in a table should be a single data point.  Otherwise, it becomes difficult (or impossible) for a user to Add, Modify or Delete it without damaging the integrity of the database.   

[Anonymous]

@Drrickryp Thanks for this, I'll take a read.

[Drrickryp]

@Anonymous 

At least 50% of the questions that I respond to here are related to a lack of understanding of the principles of database management or a misunderstanding of the role of delegation in PowerApps.