Using the direction enum from a dropdown control selected item works just fine, but using a text value for column in SortByColumn() shows delegation warning.

[hjaf]

Providing the text value in the formula works and there is no issue using the sort direction value (enum) dropdown:

However, using the same method for the column gives me a delegation warning and no result:

(Yes, the Value is identified as datatype text)

Why does referencing a control work for the SortOrder enum but not when referencing the same kind of control for a text?
the argument for the SortByColumns function should be the text "real name" for the column as mentioned in the documentation: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort#syntax 

PS: Delegation for dataverse supports text, number and datetime: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-common-data-serv...
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-common-data-serv...

I have also tried putting the text value in a local variable, this doesn't work either.

[RandyHayes]

@hjaf 

SortByColumns is not delegable when you do not specify the sort column directly.  When using it as you have, you will get delegation warnings and if your datasource is more than your app record limit (2000 max), then you will potentially get incomplete data results.

And yes, the column name for the SortByColumns should be the "real name" of the column.

I hope this is helpful for you.

[hjaf]

Hi, thanks for your reply!

I see that this is not possible, the question was more about the why. I don't understand the behavior from reading the documentation as it only appears to specify the datatype being a string. It seems strange and not very "low-code" that it's not enough to consider the parameters type alone, but also how the parameters are provided. 

Perhaps I've missed some essential part of the core principle and concept, but I really can't see why the the string being "hardcoded" into the filter statement works, but not when the string is referenced (variable or control); it's perhaps even harder to understand since the sort order is also referencing a similar control.

The documentation says that the requirement for this parameter is a string, but also suggests that it can be referenced by saying "IF" directly included:

Column names are specified as strings, requiring double quotes if directly included in the parameter list. 
For example, SortByColumns( CustomerTable, "LastName" ).

You can combine SortByColumns with a Drop down or List box control to enable users to select which column to sort by.

(Edit: added missed relevant part of documentation)

I can "solve" this limitation by putting the entire filter into a switch, but this seems awfully backwards and just complicates everything as I will have to repeat the filter statement for every possible column:

Switch(
    drpSortColumnSIRS.SelectedText.Value,
    "aksoaim_inspectiontimeanddate",
    SortByColumns(
        Filter(
            'Inspection Results',
            ThisRecord.'Inspection Scope'.'Inspection Scope Id' = varInspectionScope.'Inspection Scope Id'
        ),
        "aksoaim_inspectiontimeanddate",
        drpSortDirectionSIRS.Selected.Value
    ),
    "aksoaim_inspectionprogress",
    SortByColumns(
        Filter(
            'Inspection Results',
            ThisRecord.'Inspection Scope'.'Inspection Scope Id' = varInspectionScope.'Inspection Scope Id'
        ),
        "aksoaim_inspectionprogress",
        drpSortDirectionSIRS.Selected.Value
    ),
    SortByColumns(
        Filter(
            'Inspection Results',
            ThisRecord.'Inspection Scope'.'Inspection Scope Id' = varInspectionScope.'Inspection Scope Id'
        ),
        "aksoaim_tag",
        drpSortDirectionSIRS.Selected.Value
    )
)

If this is a "by design" thing, is there some documentation that can shed some more light on the concept or principles this it is based on?😊  

[v-qiaqi-msft]

Hi @hjaf,

It is by design that the dropdown control selected value could not be delegable when you reference itself as column name.

Actually, it is a update for SortByColumns() function recently. It didn't support dropdown control selected value as column name until 12/01/2021.

Since we have supported the reference from a dropdown control selected value as a column name, a delegation warning for dropdown selected exists for that Data sources vary on what functions and operators they support with delegation.

If complete delegation of a formula isn't possible, the authoring environment will flag the portion that can't be delegated with a warning. When possible, consider changing the formula to avoid functions and operators that can't be delegated. 

I have made a test on my side that this delegation warning you have will do affect the records display in your SortByColumns() function. Currently, PowerApps supports 2000 records at max for delegation, you could ignore the delegation warning if your records are less than that. However, if you have more than that, you should change your column name parameter as a literal text for better.

Hope this could help you at some degree.

[hjaf]

Hello @v-qiaqi-msft 😊

Thanks for trying to help me understand this 😅

I'm not exactly sure what you mean by referencing itself:

It is by design that the dropdown control selected value could not be delegable when you reference itself as column name.

Just to clarify, the data I am sorting is the items in a gallery, the dropdown where the user chooses which column to sort, is not tied to the data and contains a hardcoded table of display names and column names (real names) to make the options more readable: 

Table(
    {
        Title: "Tagnumber",
        Value: "aksoaim_tag"
    },
    {
        Title: "Inspection progress",
        Value: "aksoaim_inspectionprogress"
    },
    {
        Title: "Inspection date",
        Value: "aksoaim_inspectiontimeanddate"
    }
)

Actually, it is a update for SortByColumns() function recently. It didn't support dropdown control selected value as column name until 12/01/2021.

I just had a look into the history for this documentation and the part that suggest support for referring to a listbox or dropdown control has been there since March 10, 2018 (which is the earliest public commit i can see f952538), you can see this version of the documentation here. So if I understand you correctly, the documentation is not up to date or incorrect?

The initial response by @RandyHayes led me to create an issue for this part of the documentation, but this issue was closed saying that I should create a support ticket, not sure if this means my understanding is wrong or if it is in fact an issue 😕

Currently, PowerApps supports 2000 records at max for delegation, you could ignore the delegation warning if your records are less than that. However, if you have more than that, you should change your column name parameter as a literal text for better.

This is the items setting which is set to 500 by default right? Not entirely sure why you think this figure is relevant, in terms of delegation in this context? The filter assumes that the amount returned will not exceed the limit I have configured (1000)
 
If I do SortByColumn() on the modified or created or any other indexed table column on a datasource that supports delegation for the datatype, I expect the first 500 rows returned to be the same, regardless if the data source table itself has 500 or 500 000 rows. The only difference is how I specify the column, the parameter value for SortByColumns(), is still the same and of the same datatype. If, for some reason, the how a parameter is provided to functions is just as important as its type, then shouldn't this be pointed out in the documentation? 🤔

In any case, if I am able to work around this with a switch (as mentioned earlier), is there anything in the way of improving the function to do this for us?

I'll create a support ticket as suggested by the documentation team, and update if I get some more clarification 🤔

Thanks again.