cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahaugstad
Frequent Visitor

SortByColumns mixed sortorder conditions

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

)

1 ACCEPTED SOLUTION

Accepted Solutions

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"])

View solution in original post

5 REPLIES 5
WarrenBelz
Super User III
Super User III

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.

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

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"])

View solution in original post

Hi @ahaugstad ,

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

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (29,157)