cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power Participant
Power Participant

Delegation question

Hi

 

In my powerapp im pulling data from a SharePoint list.

In some functions i use i filter that SharePoint list per user.

Now i wonder how delegation works if i do a countIf

CountIf(SharePointList, Lower(Requester.Email) = Lower(User().Email) && Status.Value="Closed")

If my list contains 5000 items will the countIf only work on the 500 first items for that specific user or for all items?

A single user will not pass 2000 items so i can raise this if needed to. But if it counts only works on the 500 first items then i will display the wrong information based on countIf?

 

And most important how do you workaround this delegation? And do i need to work around it based on my criteria above? 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Delegation question

>>Or how can i actually test this?

Try using the monitor tool (far left of the screen in the vertical strip of icons - a spanner and screwdriver - click on that and then on Open Monitor).

With the monitor tool open, click on your buttons that fetch the data. You can then see in the monitor tool how much data was returned and view the first few rows of the data. You can use this to give you some idea of what is going on under the surface and whether your filters are being delegated to the data source.

You may get delegation issues with User().Email - PowerApps doesn't seem to always 'resolve' such references properly but you can force it by setting a label or variable to the value and then using that in your formula.

View solution in original post

Highlighted
Super User
Super User

Re: Delegation question

@JimmyWork 

Have you tried this approach I suggested in an earlier post?

Create a new column in your datasource called assignedEmail. When requester is submitted to the datasource update the assignedEmail column at the using this code

Lower(Requester.Email)


Then put this code in the OnStart property of your app

Set(currentUserEmail, Lower(User().Email))


Finally, you can use this code to count the rows for a user. Notice that LOWER is no longer needed since we took steps earlier on to clean the data.

CountRows(Filter(SharePointList, assignedEmail = currentUserEmail && Status.Value="Closed"))


—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

View solution in original post

Highlighted
Super User
Super User

Re: Delegation question

Can someone explain why this happens.

I have delegation set to 500 in the app.

My list has 620 items.

If I put this in the gallery item it will show me all 620 items.

If i put it in a ClearCollect or a variable it will only show me 500 items.

 

Then i set the Gallery to colList and it only shows me 500 items, why?

 

If your filter is delegable, a gallery will initially bring back the first100 rows. As you scroll to the end of the gallery it will fetch the next 100 rows - this is because a delegable filter can be 'paged'. Only 100 rows are returned with each call so this is not exceeding the delegation limit, though the total number of items in your gallery can eventually be more than the delegation limit if you keep scrolling.

When you do a ClearCollect, you are not paging, but pulling all the data in one go, up to the delegation limit.

CountRows is not delegable (and nor is Count, even though it does not show the delegation warning!). To work out the count, PowerApps must pull back all the matching rows and then count them (very inefficient). If Count or CountRows was delegated you would get back just the number of rows (not the actual rows) as that would have been calculated server-side, so the actual rows would not need to be returned.

I don't know why Count shows as delegable (at least on SQL Server datasources) as the Count is not being calculated server-side. To get an efficient count with SQL you must create a view which returns just the Count and then call that.

View solution in original post

21 REPLIES 21
Highlighted
Super User
Super User

Re: Delegation question

@JimmyWork 

This code would be more delegation friendly.  The FILTER function can find an infinite amount of rows.  Then the COUNTROWS function will count up to the first 2,000 rows assuming you have increased the delegation limit in the advanced settings menu.

 

CountRows(
    Filter(
        SharePointList,
        Requester.Email = User().Email
        && Status.Value="Closed"
    )
)

 

I would suggest not using the LOWER function anywhere in this formula.  LOWER cannot be delegated.  But the good news is that LOWER is probably not necessary here since the User().Email should have the same capitalized letters as Requester.Email.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

Highlighted
Power Participant
Power Participant

Re: Delegation question

@mdevaney Thank you for answering.

 

If i do not use lower i will not get the correct data due to users email can have both big and small letters resulting in a mismatch.

And  actually this wont give you a delegation warning: CountIf(SharePointList, Lower(Requester.Email) = Lower(User().Email))

But if i use your code i do get 3 delegation warnings and it will display the wrong amount.

Highlighted
Super User
Super User

Re: Delegation question

@JimmyWork 
If you found a result that does not give you a delegation warning does that mean you found a solution?

Highlighted
Power Participant
Power Participant

Re: Delegation question

@mdevaney Im tryinig to understand but sometimes lower give delegation sometimes not and im trying filter on more column and then i just end up with delegation. Im trying to work out if i can use ClearCollect or something?

 

Trying to filter this.

ClearCollect(RecordsTickets, Filter(SharePointList, Lower(Requester.Email) = Lower(User().Email) && Status.Value="Unassigned" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="Assigned" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="In Progress" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="Waiting for user response" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="Waiting for external resource"))
Highlighted
Super User
Super User

Re: Delegation question

In my experience, Count is not delegated even with SQL, even though PowerApps may not give a delegation warning (don't rely on the warning, especially if you have 'nested' filters, it will often give you the false impression that a formula is delegating when it isn't).

You can see this using the Monitor tool. Try Collecting based on the Count function and Collecting just for the Filter - in my tests I found they return exactly the same amount of data, so the 'Count' is clearly not being delegated.

If you are using SQL you could create a server-side view to group by the 'lower' of the email name and return the count, and then filter against that view. You may want to put the 'lower' of the email address you are looking for into a variable or text box as including 'lower' in the filter itself may break the delegation as @mdevaney points out.

If you are using SharePoint or CDS then I'm afraid you're at the mercy of the delegation limits AFAIK. Although I believe CDS can do a count in a 'roll up' field, these are not 'live' (data is refreshed periodically) so at best will only be telling you want the count was an hour ago (or whatever cycle the roll-up is refreshing on).

Highlighted
Power Participant
Power Participant

Re: Delegation question

@PaulD1 Thank you for answering.

But can i do anything about my other formula to remove delegation?

 

ClearCollect(RecordsTickets, Filter(SharePointList, Lower(Requester.Email) = Lower(User().Email) && Status.Value="Unassigned" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="Assigned" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="In Progress" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="Waiting for user response" || Lower(Requester.Email) = Lower(User().Email) && Status.Value="Waiting for external resource"))
Highlighted
Super User
Super User

Re: Delegation question

@JimmyWork
The most reliable method I could offer is this:

Create a new column in your datasource called assignedEmail. When requester is submitted to the datasource update the assignedEmail column at the using this code

Lower(Requester.Email)

Then put this code in the OnStart property of your app

Set(currentUserEmail, Lower(User().Email))

Finally, you can use this code to count the rows for a user. Notice that LOWER is no longer needed since we took steps earlier on to clean the data.

CountRows(
Filter(
SharePointList,
assignedEmail = currentUserEmail
&& Status.Value="Closed"
)
)

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.
Highlighted
Power Participant
Power Participant

Re: Delegation question

@mdevaney Thank you i will try this.

Sorry for this but Requester is actually the Author column just renamed

Highlighted
Super User
Super User

Re: Delegation question

You could try something like...

 

CountRows(

Filter(

Filter(
SharePointList,
 Status.Value="Closed"
), lower(assignedEmail) = lower(currentUserEmail)
)

 

This should first return records where the status.value = "Closed" and this will be delegated (so you should get the first 500 (or 2000 if you have upped the delegation limit in advanced settings) matching records regardless of where they appear in the list.

The second (outer) filter will then work on the results of the first filter and finally you will count the rows.

If you know that at any given time, there should be fewer records with status.value="closed" than the delegation limit, you should get an accurate count (although you may not get great performance).

The issue here (sorry for repeating myself from other threads) is that SharePoint is not a database and does not have database type features (such as proper server-side queries). For these sort of requirements you really want SQL, but the licensing model makes it too expensive in most circumstances. 

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,442)