SortByColumns mixed sortorder conditions

[ahaugstad]

Hello,

I am attempting to use SortByColumns in a Gallery in the following way (see code snippet below) - I want to first sort by Status, but it is not sufficient from a business standpoint to sort by ascending or descending order. Thus, I specified the order I would like the Status field sorted by defining values in a comma separated list.

Then, I want to subsort by timestamp (a date field) descending. End state, I would ideally end up with all 'Unclaimed' items first, subsorted from most recent to least recent timestamp, then all 'Claimed' items, subsorted from most recent to least recent timestamp, and so on. 

Power Apps throws a syntax error with the below formula, saying that the SortByColumns function has invalid arguments, but by my eyes I am following the syntax outlined in the documentation (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort).

Is this a limitation of the SortByColumns function, that it cannot combine two kinds of sortorder methods (i.e. one specifying your values in a list, and one simply ascending/descending)? Or am I missing something?

If it is a limitation, am I able to use Sort to accomplish this somehow or has anyone crafted a way to code around this?

Thanks to anyone for any help you can provide!

Alex

SortByColumns(

<Table>,

"Status",
[
"Unclaimed",
"Claimed",
"Pending",
"Completed",
"Cancelled"
],

"Timestamp",

Ascending

)

[WarrenBelz]

Hi @ahaugstad ,

The syntax needs a sort order for each

SortByColumns(
   <Table>,
   "Status",
   Ascending,
   "Unclaimed",
   Ascending,,
   "Claimed",
   Ascending,
   "Pending",
   Ascending,,
   "Completed",
   Ascending,
   "Cancelled"
   Ascending,,
   "Timestamp",
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[ahaugstad]

Hi Warren,

Thank you for the quick reply! This is still throwing a syntax error for me - error text "The specified column does not exist", starting at "Unclaimed".

To clarify, the list of values I included in my code snippet (["Unclaimed", "Claimed", etc.]) are allowed values for the "Status" field.

Does that change things at all? 

Thanks again for the help,

Alex

[ahaugstad · Accepted solution]

I was able to resolve this - there is another post that addresses a similar example:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Using-Multiple-Sort-Orders-in-a-Gallery/td-p... 

If it helps others to see my example through, to accomplish my end goal (which was having the order be all 'Unclaimed' items first, subsorted from most recent to least recent timestamp, then all 'Claimed' items, subsorted from most recent to least recent timestamp, etc.), I implemented the below:

SortByColumns(SortByColumns(<Table>,"Timestamp",Descending),"Status",["Unclaimed", "Claimed", "Pending", "Completed", "Cancelled"])

[WarrenBelz]

Hi @ahaugstad ,

Glad you got it working. Timezone issues prevented me responding to your last post (5:30am now in Australia)

[ahaugstad]

Hi Warren, no problem at all - your first reply got me thinking in the right direction. Thanks so much for the assistance!