Watch out for PCF (code-components) sort silently failing

One of the biggest causes of unexpected bugs in canvas apps is the delegation of queries. For instance, if you want to sort by the owner of an account, you can use the Power Fx query:

Sort(Accounts,'Created By'.'Full Name', Ascending)

You will get a delegation warning on this since the sorting will only happen in memory and not on the server. This means if you have the delegation limit set to 500, only the first 500 records will be sorted instead of sorting the entire dataset on the server-side. This may not show up whilst you are developing the app, but it might not work as expected in production once deployed.

Perhaps more concerningly, if you are using data-shaping to add a column (AddColumns) to then sorted using this column, the delegation warning will not even show up.

When using PCF (code-components) inside canvas apps, a nice feature is that we have much more control over the paging/filtering/sorting/linking of Dataverse queries. This is part of the declarative vs imperative story (but that’s for another time).

When binding a dataset to a Dataverse connector, we can use OData style operators to manipulate the query, rather than the Power Fx Sort and Filter functions and their associated delegation challenges.
E.g.

    onSort = (name: string, desc: boolean): void => {
        const sorting = this.context.parameters.records.sorting;
        while (sorting.length > 0) {
            sorting.pop();
        }
        this.context.parameters.records.sorting.push({
            name: name,
            sortDirection: desc ? 1 : 0,
        });
        this.context.parameters.records.refresh();
    };

    onFilter = (name: string, filter: boolean): void => {
        const filtering = this.context.parameters.records.filtering;
        if (filter) {
            filtering.setFilter({
                conditions: [
                    {
                        attributeName: name,
                        conditionOperator: 12, // Does not contain Data
                    },
                ],
            } as ComponentFramework.PropertyHelper.DataSetApi.FilterExpression);
        } else {
            filtering.clearFilter();
        }

This is awesome since we don’t need to worry about any delegation provided that the query can be translated into an OData query.

But…watch out

If you have a grid that performs a dynamic sort operation using each column name, and the user sorts on the account.createdby column which is of type Lookup.Simple - you might think that this would be a matter of using the column name:

this.context.parameters.records.sorting.push({
            name: "createdby",
            sortDirection: desc ? 1 : 0,
});

After all, createdby is the column name that is given to us by Power Apps in the dataset metadata:

{
  "name": "createdby",
  "displayName": "createdby",
  "order": 5,
  "dataType": "Lookup.Simple",
  "alias": "createdby",
  "visualSizeFactor": 1,
  "attributes": {
    "DisplayName": "Created By",
    "LogicalName": "createdby",
    "RequiredLevel": -1,
    "IsEditable": false,
    "Type": "Lookup.Simple",
    "DefaultValue": null
  }
}

Strangely, this does not cause any errors a run-time and the data is actually sorted so it looks like it’s working - but on closer examination, the query that is set to the server is:

accounts?$orderby=createdby+desc&$select=accountid,name,_createdby_value

Seems legit? But the response is actually an exception:

The $orderby expression must evaluate to a single value of primitive type.

The reason being is that the createdby logical name is not expected by the WebApi when sorting, instead, it expects the name _createdby_value.
What appears to be happening is that after the query fails,  canvas apps uses a fallback approach of performing the sorting in-memory in a non-delegated fashion - but this is not reported in an obvious way. The only indicators are the network trace and the somewhat confusing errorMessage on the dataset object of Invalid array length.

To get around this, we can’t pass the column name that is used in our dataset, but instead to use the OData name expected:

this.context.parameters.records.sorting.push({
            name: "_createdby_value",
            sortDirection: desc ? 1 : 0,
});

This seems slightly unusual until you remember that we are simply hitting the OData endpoint - bringing us back into the imperative world with a bit of a bump! Remember it won’t do all that fancy caching and performance optimizations that Power Fx does for you!

Hope this helps,
@ScottDurow

Continue to website...