cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

CDS - Filtering records by Owner

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.

4 REPLIES 4
Highlighted
Resolver I
Resolver I

Re: CDS - Filtering records by Owner

@chris61 

 

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.

Highlighted
Solution Supplier
Solution Supplier

Re: CDS - Filtering records by Owner

According to the delegation document:

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

Then : 

 

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!

Highlighted
Helper II
Helper II

Re: CDS - Filtering records by Owner

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

Highlighted
Solution Supplier
Solution Supplier

Re: CDS - Filtering records by Owner

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.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (10,157)