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.
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.
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.
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.
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.
- Categories:
- sharepoint
- code
A common requirement is to filter SharePoint records by email address. This use case scenario often attracts many questions from app builders who struggle to implement this in a delegable way. In this post, I'll describe how to carry out this type of filtering in a delegable way.
One of the most frequent questions I see relates to how to filter a SharePoint list by the current user. If we attempt to carry out this task with the filter function, it's necessary to specify the condition in a specific way if we want to avoid delegation problems.
This post highlights the syntax that app builders usually attempt to use, the reason why this results in a delegation error, and the ways that we can avoid this problem.
To demonstrate this scenario, let's take the example of a SharePoint list of property records.
To show only the records that were created by the currently logged on user, app builders often attempt to accomplish this by adding a gallery control and applying a filter condition that looks like this:
Filter[Property, 'Created By'.Email = User[].Email
]
Whilst this condition is logically correct, the designer will indicate that expression is not delegable and at runtime, it will not return all the expected records.
Why is this and more importantly, how can we express this condition in a delegable way?
By definition, a delegable query is one that Power Apps can pass [or delegate] to the data source, so that the data source can natively execute outside of Power Apps.
Like every other data source, SharePoint is completely separate from Power Apps. It has no way of knowing who the current Power Apps user is, and therefore it cannot natively execute a query that makes reference to the Power Apps User object.
Whilst it is impossible to construct a query that references the User object in a delegable way, we can accomplish what we want to achieve if we were to express the email address of the current user in a static way. By comparison, the expression beneath IS delegable, because it removes the requirement for the data source to resolve the current user.
Filter[Property, 'Created By'.Email = ""
]
With the above in mind, the strategy is to make our query delegable by creating a filter condition that refers to a static email value. Here are two methods that we can use to accomlish this.
The first method is to store the email address in a variable, and to refer to that variable in the call to the filter function. To declare and set a variable value, we can add the following function to the OnStart property of our app.
Set[varUserEmail, User[].Email
]
We can then reference this variable [varUserEmail] in our call to the Filter function like so. The formula beneath will be delegable.
Filter[Property,
'Created By'.Email = varUserEmail
]
The second method is to use the With function. Here, the With function evaluates the value of User[].Email and stores the result in userEmail. The Filter function references this static value, and produces a SharePoint query that is delegable.
With[{userEmail:User[].Email}, Filter[Property,
'Created By'.Email =
userEmail ]] To filter SharePoint people columns in a delegable way, we can filter by a static value that we define through a variable, or by using the with function. This technqiue applies not only to the Filter function, but also to other functions where we want to filter by user, such as the LookUp function.
- Categories:
- sharepoint
- delegation
If your organization is like most, your employees’ primary computing device is their phone. The rise of mobile device popularity has forced technology companies and their customers to rethink how systems are accessed and engaged by users. However, not all companies can afford a development staff to create and support mobile interaction. Microsoft created a solution for this.
Microsoft’s PowerApps provides a great way for non-technical users to create and publish custom forms for SharePoint lists. Even novices can quickly spin up mobile and tablet versions of forms for submitting, viewing, and editing SharePoint list items by simply letting PowerApps create base forms.
PowerApp Forms include a “gallery” form that automatically shows items in a SharePoint list. This is great when you use the built-in search controls to find an item in the list or to use your application for submitting new items to your SharePoint list.
But what if you want your gallery to only show specific list items? PowerApps has a Filter function, so that should be easy, right? Well, maybe…
The Business Problem
To help illustrate the issue and the solution, I’ll walk you through an example from our experience. We created a “Time off Request” application in PowerApps so our IT consultants and managers, who work remotely and at client sites, can make and approve time off requests from their smartphones. We have an existing SharePoint list we use to track time off requests, so it only took a few minutes to create the base application in PowerApps.
For our managers, I added a button in the application that takes them to a gallery form where they can see open requests that are assigned to them. Our SharePoint list is already set up to control access to individual time off requests based on permissions. Therefore, I only needed to use a filter to display the open requests. In our SharePoint list, we use a choice field to identify the state of individual requests. As managers approve or deny time off requests, the workflow updates the label in the choice field, and our “Open Request” view manages the display.
PowerApps supports a wide variety of data sources, including SQL databases, SharePoint lists, and third-party data sources. For SharePoint lists, however, we found that the PowerApps Filter function does not recognize Choice columns in the Filter function.
How do we get around this?
In our solution, we created a number column called “PowerAppFilter” as a hidden column in our SharePoint “Time Off Request” list. We then created a SharePoint workflow that updates the value in the “PowerAppFilter” column each time an item is created or edited. The workflow reads the value in the “Status” field of the list and adds or updates the number value to the “PowerAppFilter” column. For requests that have not yet been processed, the list Status field equals “Open” and the “PowerAppsFilter” field equals “1”.
In the PowerApps Filter function, I set a filter on the Items property to only display items where “PowerAppFilter” = “1”.
The function looks like this:
Sort[Filter[‘Time Off Request’ ,PowerAppFilter = “1”], Created, Descending]
Now my managers only see their open requests, eliminating the need to search through all requests, thus speeding up the approval process.
Do you have more questions about PowerApps or SharePoint workflows? Need help to evaluate your work processes and determine what collaboration solutions can work for you? Peters & Associates has been assisting organizations in solving business problems since 1981. Give us a call or drop us a line – 630.832.0075 or .
Check out these related articles to get the most our of your PowerApps services: