If you have not read the series introduction, I will highly recommend that you do. It discussed the three factors that causes delegation warnings.

In this post, I will explain how to delegate the filter function to SharePoint on the LookUp column.

Note: What I will discuss here applies to the PowerApps LookUp function as well.

The LookUp column is of the complex type. Delegation of complex types in SharePoint is done by deferring the decision for delegation to the subfield involved. For LookUp column, the Value is the only delegable subfield in this data type. The Id subfield is not delegable.

Within the Filter functions, the only delegable operator supported for the delegable subfield (Value) is =. This is because, this subfield is Text type, which has the = operator as the only delegable operator.

Expressions in the filter functions can be joined with And or Or, and they will be delegable to SharePoint.

If you want to compare the subfields, do so using constant values that are the same across all records, such as control properties and global and context variables.

With all this information, let’s look at some examples.

===== Useful Information =====

  • Set(UserEmail,User().Email)
  • Set(MyRecord,{Id:10})
  • MyList: SharePoint List
QueryDelegable?
Filter(MyList,LookUp.Value = User().Email)
Filter(MyList, LookUp.Value = UserEmail)
Filter(MyList, LookUp.Value = “name@example.com”)
Filter(MyList, LookUp.Value <> UserEmail)
Filter(MyList, LookUp.Value <> “name@example.com” )
Filter(MyList, LookUp.Id = 10)

References

https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-delegable-functions-and-operations-for-sharepoint

Other posts in this series