Blog

Code - Can we hack SharePoint ID columns to be delegable using the greater than/less than operators?

Power Apps recognise a SharePoint ID column in a peculiar way. It recognises an ID column as numeric, but does not support delegable filter operations against this type of column using the greater than/less than operators. In this post, we'll investigate whether or not we can fix this problem in code.

In my previous post (link beneath), we looked at how to change the data type of a SharePoint column by modifying the table definition in code. We were able to change the data type of a calculated SharePoint column to fix a problem where Power Apps does not correctly recognise numeric calculated columns as numbers.

As a logical extension to this technique, let's now explore whether or not we can perform a similar task with the SharePoint ID column.

What's the point of this exercise?

The current behaviour is that Power Apps recognises SharePoint ID columns as numbers. But unlike normal numeric columns, it's not possible to express a Filter condition that uses the ID column in a delegable way using the greater than/less than operators.

The reason why this causes a problem is because the ID column provides a natural sequential identifier for the records in a list. Compared to Dataverse and SQL Server, SharePoint provides far less support for delegable operators. The typical workaround for this problem is to pre-load SharePoint recoreds into collections. To do this, we need to fetch the records in batches of 2,000, based on a numeric identifier

In theory, the SharePoint ID column would provide an ideal candidate for this task. However, it's not possible to use this column based on this lack of delegable support for the greater than/less than operators. If we could resolve this problem, it would remove the need to create a custom sequential numeric column in SharePoint to support this type of delegation workaround.

Adding delegable operators to the ID column

We'll attempt this excercise with an app that connects to a SharePoint list of property records. The first step is to open the table definition of the table and to review the definition of the ID column.

Here, we can see precisely the reason for this problem. The ID column is defined as an integer but unlike other integer columns, the "x-ms-capabilities" > "filterFunctions" node only defines the single delegable function "eq" - that is, the equals function.


Let's now modify this definition to add additional delegable functions "gt", "ge", "lt", "le",  and "ne" to this array. These are the OData operators that correspond to the "greater than", "greater or equal than", "less than", "less than or not equal to", and "not equal to" operators. Here's the syntax that we'll use:

{
    \"ID\":{\"title\":\"ID\",
    \"description\":\"List item id. Use this value for specifying 
                      the item to act on in other list related actions.\",
    \"type\":\"integer\",
    \"format\":\"int64\",
    \"x-ms-keyType\":\"primary\",
    \"x-ms-keyOrder\":1,
    \"x-ms-permission\":\"read-only\",
    \"x-ms-sort\":\"asc,desc\",
    \"x-ms-capabilities\":{\"filterFunctions\":[\"eq\",\"gt\",\"ge\",\"lt\",\"le\",\"ne\"]}
}

The next step is to modifity the definition of the ID column with the above escapted version, and to re-package our project with PSAopa.

Opening and testing our app in Power Apps Studio

Here comes the moment of truth! Let's now open our app in Power Apps Studio and see whether we've been successful in adding extra delegable support for the ID column. Sadly, this change has not had the desired effect, and Power Apps still recognises the ID column as one that is not delegable using the greater than/less than operators.


As a further experiment, I attempted to change the data type of the ID column to other numeric data types. This did not work either, and I was also able to verify that Power Apps was definitely not not issuing delegable queries by reducing the "row count" setting.

Conclusion

From this experiment, I was unable to modify a SharePoit ID column to add support for additional delegable operators. There are additional measures that are built into the Power Apps service/designer to prevent this from happening.

Previous
Next
Related posts