“Is it possible to use multiple fields in the ‘Order By’ Power Automate OData filter on a SharePoint ‘Get items’ action? i.e. sort by Title and Date field?”
Power Automate action ‘Get items’ will always return the SharePoint items in an order, by default ascending by ID – the oldest items first. In most situations the order doesn’t really matter, but there’re some cases when it does. For example, if you export the SharePoint list items into an HTML table, it makes sense to sort it differently. It’s always easier to read an export if you group the related items together, e.g. requests for a specific user.
Since Power Automate doesn’t have an action to sort an array later, you should sort the results right away. There’s a workaround to sort an array, but with SharePoint items it’s an extra work. If you can sort the items directly in ‘Get items’, you should do it.
‘Order By’ format
The expected format of the ‘Order By’ field is very simple. Type in the SharePoint column internal name and the order: asc for ascending or desc for descending.
e.g. ascending by Title
Title asc
If one column is not enough, you can order by multiple columns, just separate the columns and order by a comma. The sorting priority will be from the left to the right.
e.g. ascending by Title, then descending by DateTime
Title asc, DateTime desc
Summary
As already mentioned, if you need to sort the items, you should do it right away. The ‘Get items’ action gives you the possibility in the ‘Order By’ field – just define the columns and their sorting order.
And if you’d like to filter the items before that, take a look on the filtering section on the blog.
Hi
Have you tried looping through the ordered get items. If I use apply to each based on the get items order by, in my case asc, the order is not in really in ascending order. It might start with A’s and B’s but then jumps to W and then back B etc – thanks
Hello Mike,
I didn’t notice anything like that, did you try adding ‘Select’ to select only the sort property to confirm the order? I can imagine it could happen also if you used some level of parallelism in the ‘Apply to each’ that some items would be processed faster than others.
But if that’s really broken then I’m afraid there’s no other easy way how to sort the returned items.
Hi
Thank you very much. I found this website is very useful.
I want to order a list by a lookup value. The way I tried was to use get item/order by/use the lookup field name in the address ( everything after ‘Field=’). However the system told me the field name can not be found. is there other way to use lookup value to order a list? thank you
Hello Zoe,
there’re the same rules as when filtering using the lookup columns. You must use the internal name + by which value it should sort, e.g.
Lookup/Id asc = ascending by Id in the lookup list
Lookup/Title desc = descending by the displayed value from the lookup list
Hi there, my field is a value, in the code the field name is Status_x003a_/Value. I have tried a variety of configurations and cannot figure out how to get this value work with the desc appended.
Simple field names like: RequestDate are no issue
What am I missing?
Is there an expression I need to turn it into?
Hello Nancy,
I’d double check the column internal name (https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/), I don’t think / is a supported character for that.
Hi Nancy, I’m sure you’ve long figured this out but for me I had to remove the “/Value” part from the lookup field name for the Order By to work.
Thanks for this.
Wanted to filter my list down to see if a checklist was completed for the day and if not, email everyone.
Works well.
Thank you – exactly what I needed for sorting on more than one field.
That is the opposite of “ID asc ” tried everything and I get responses back like “Action ‘Get_items_1’ failed: The field ‘field_7’ of type ‘Note’ cannot be used in the query sort expression”
or
“ID desc ” tried everything and I get responses back like “Action ‘Get_items_1’ failed: The API ‘sharepointonline’ returned an invalid response for workflow operation ‘Get_items_1’ of type ‘OpenApiConnection’. Error details: ‘The API operation ‘GetItems’ is missing required property ‘body/value/3/field_7’.'”
sometimes it wants column name or field name. Its really quiet buggy?