This is something I've asked before as others have too but I'm not finding the answers satisfactory. This is because the answers are old and no longer work or we run in to potential delegation issues.
Previously when filtering a set of records from the common data service by Owner you used the following formula:
Filter(datasource, Owner=LookUp(Users, 'Primary Email'=User().Email))
This worked for me, made sense and nice and simple. In fact, it still works on some live PowerApps that we have. The problem is, when you go to edit them (since Microsoft changed how the User field works) it does not work and breaks the app. So for the time being, we are leaving these alone.
The current solution when I asked this question was to use the following:
Filter(AddColumns(datasource, "OwnerEmail", AsType(Owner, [@Users]).'Primary Email'), OwnerEmail = User().Email)
This does work and it does not throw any delegation errors. However, using the new monitor feature in PowerApps it looks like we will run in to delegation issues. I will try to explain:
When using monitor it makes a call to the datasource held in the common data service. Using monitor, it is clear that the call is returning all records within the datasource due to the JSON response.
Then, there is a call to the CDS datasource for each User that is assigned as an Owner to a record in the datasource. In my case this is another 4 calls in test environment. In a large organisation this could run in to the hundreds of calls.
Even though PowerApps does not throw a delegation warning, it appears that we will easily hit delegation limits if enough records are in the system. Therefore the change from the original formula posted above to the one that's now suggested has the potential to break apps and lead to slow performance. I hope I'm wrong on this and appreciate it if anyone can shed some light on this.
If you are always filtering the owner on the current user, try querying the User entity matching User().Email to 'Primary Email' once in the App.OnStart, then filter the CDS entities using the owner ID.
FYI, it's more efficient to persist the User() values in a global variable in the App.OnStart than constantly referencing User().Email.
Hope this helps. Let me know if you need some sample code.
According to the delegation document:
AddColumns, DropColumns, RenameColumns, and ShowColumns partially support delegation. Formulas in their arguments can be delegated. However, the output of these functions are subject to the non-delegation record limit.
You will not see a delegation warning, but you won't get more than 2000 (delegation limit) records.
Why don't you use this approach?
on App Start Set(glbUser, User().Email)
Filter(YourEntity, 'Owning User'.'Primary Email' = glbUser)
Please Accept as Solution if this post answered your question so other members can find it. If you found this post helpful consider giving my post a Thumbs Up!
Thanks both for your responses so far.
To clarify I had been using global variable for the email which I should have indicated in my original post. This gets around one delegation issue of course.
It's disappointing that the current way of filtering by owner suggested by most on here means you do actually run in to a 2000 delegation limit which is hidden due to the AddColumns formula.
@Edwin-Abdalian I've tried using your formula:
Filter(Meters, 'Owning User'.'Primary Email'=gblUserEmail);
However, I'm presented with the message "Invalid use of '.' " - despite this being a valid and available field in the entity within CDS.
Finally, I'm still non-the-wiser as to why the old formula similar to the statement above do not work in edit mode yet work live as long as they are never put in to edit mode (from when they were originally published).
I don't know why it gives you that syntax error. the formula works fine in my environment. I have two workarounds, which might solve your issue.
1- create a view in your model-driven app, and add the email column to your view. then use 'YourEntity'.'TheNewView' as the datasource.
2- Create a view in your model-driven app, and add a criteria to it to filter the records that belong to the current user. then use 'YourEntity'.'TheNewView' as the datasource.
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Check out our new Discover Your Career Path blog post series and get all the details.