cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WorkHard
Super User
Super User

SortByColumns on a sharepoint choice field

I'm trying to sort a gallery using two columns. One column is a text field and the other is a combobox/choice field. (single selection).

 

SortByColumns(Filter(DataSource,***filter parameters here****),"SortColumn1",Ascending,"SortComboColumn2",Acending)


this throws a "Cannot sort on the expression type".

 

I tried this as well and I can't seem to convert this column to a compatible type.

SortByColumns(Filter(DataSource,***filter parameters here****),"SortColumn1",Ascending,AddColumns(DataSource,""SortComboColumn2"",'SortCombo Column2'.Value),Acending)

 

1 ACCEPTED SOLUTION

Accepted Solutions

There is a way to sort by a choice field by extracting the value first and then dropping the column later so that the schema of the table is kept:

 

 

 

DropColumns(
    SortByColumns(
        AddColumns(
            Filter(DataSource,***filter parameters here****)
            ,
            "SortComboColumn2Extract", SortComboColumn2.Value
        ),
        "SortColumn1",
        Ascending,
        "SortComboColumn2Extract",
        Ascending
    ),
    "SortComboColumn2Extract"
)

 

 

 

This will only work within the delegable limit so your inner filter will need to always return less than the delegable limit.

 

I have tested and confirmed this sorts correctly, however it is still reliant on the delegable limit so this needs to be kept in mind.

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

9 REPLIES 9
iAm_ManCat
Super User
Super User

Hi,

 

You cannot Sort or SortByColumns on complex data fields (choice is a complex field even when its only one choice).

 

I would recommend creating a text field on your datasource that is populated with the single choice that has been selected, then you should be able to sort/filter by these.

 

Cheers!

 

 

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Hi @iAm_ManCat , I am actually able to use Sort on that "complex data field" without any issues and have been using it in multiple apps for over a year.
The documentation says it's non delegable, however it is working without delegation.

"The following Power Apps operations, for a given data type, may be delegated to SharePoint for processing (rather than processing locally within Power Apps)."

 

The SortByColumns on the other hand is the first time I use it and I noticed that limitation.

Delegation means that Power Apps will attempt to delegate the action to the datasource where possible, and if it is not able to delegate then the number of records being accessed/read will not exceed the delegable limit for the App (this defaults to 500 and has a max of 2000).

 

So in light of that, I have a few questions:

  1. Is the formula you provided copy-pasted from your App (apart from the obfuscation), as you wrote Acending instead of Ascending for the last sort type
  2. How many records are in your datasource
    1. Or more accurately, how many rows are returned by your inner filter 
      CountRows( Filter(DataSource,***filter parameters here****)​ )
  3. What exact column types are "SortColumn1" and "SortComboColumn2"
    1. Is SortColumn1 Text?
    2. Is Sort ColumnComboColumn2 Choice?

 

Cheers,

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


In addition to this, I've done some further testing, and my investigation leads me to agree with the documentation

 

Sort:
image.png

Partially working, gives delegable warning, will stop including any data after the delegable limit is reached.

For Example, using Sort and sorting by a choice column, if only pulls the delegable limit (which in this case I have set to 2), meaning that once you have over 2k records within your inner filter, your formula will stop including all items newer than the first 2k.

This example List has multiple items, but only two are showing as I've set the delegable limit to 2 for testing, and used a basic sort formula:

image.png

Additionally, whether I choose Ascending or Descending, the order is not changed, so it is not sorting...

 

When using SortByColumns, it knows that it cannot sort by a choice field (complex data) and warns you of this:

image.png

 

So I stand by my original comment, the documentation says its not delegable for complex types, and I have confirmed this through testing

image.png

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


There is a way to sort by a choice field by extracting the value first and then dropping the column later so that the schema of the table is kept:

 

 

 

DropColumns(
    SortByColumns(
        AddColumns(
            Filter(DataSource,***filter parameters here****)
            ,
            "SortComboColumn2Extract", SortComboColumn2.Value
        ),
        "SortColumn1",
        Ascending,
        "SortComboColumn2Extract",
        Ascending
    ),
    "SortComboColumn2Extract"
)

 

 

 

This will only work within the delegable limit so your inner filter will need to always return less than the delegable limit.

 

I have tested and confirmed this sorts correctly, however it is still reliant on the delegable limit so this needs to be kept in mind.

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Thanks, @iAm_ManCat , again, I confirm that Sort works on a choice field using the following method:

 

 

 

Sort(Filter(MySharePointList,
    If(!IsBlank(ComboBox2.Selected.Value),ComboBox2.Selected.Value in Status.Value,true),
    If(!IsBlank(ComboBox3.Selected.Value),ComboBox3.Selected.Value in Type.Value,true)),'SortCombo Column'.Value, If(locSortAscending, Ascending, Descending)),...and so on.

 

 

 

I indeed used a general example, not real data. sorry for the "Acending" typo. WIll try your suggestion tomorrow by extracting the choice field. My real example is much more convoluted with multiple sorting criteria and filters.
Thank you!

Ah ok, in your example there's no indication of the field its sorting by, only what its Filtering by - is the Sort at the end also one of your choice columns and does the sorting sort by the choice column alphabetically/numerically?

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Correct, and yes. Updated the comment above with the end of that method.

Ah yes I see what you mean, because you can reference the .Value of it which is no longer the Choice object we are filtering by but the first text value inside it instead. With SortByColumns it uses the string-based name to lookup the field and can't access sub-properties of complex items.

 

I think the only thing we can do about this is submit an idea on the ideas forums

 

Let me know when you've raised it and I'll vote for it!

 

Cheers,

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,657)