Blog

SharePoint - How to filter records by the current user

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.

Edit (25-Aug-2021):
Great news! Microsoft has now fixed the delegation problem that's described in this post. There are more details about this update here:
http://powerappsguide.com/blog/post/filter-sharepoint-list-by-user-now-delegable


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.

How NOT to do this!

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?

Why is this not delegable?

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.

Logically - How to work around this problem

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 = "timl@powerappsguide.com"
)

Two ways to implement this

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.

Method 1 - Use a variable

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
)

Method 2 - Use the With command

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
     )
)

Conclusion

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.
Previous
Next
Related posts