Delegation question

[JimmyWork]

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? 

[JimmyWork]

@PaulD1 @mdevaney Thank you both for all the help.

If i move away from count as my other formula shows to filter the Status column.

This type of filtering should be supported?

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

ClearCollect(colList,Filter(
    SharePointList,
    (Requester.Email = varUser)
    && (Status.Value = "Unassigned") ||
    (Requester.Email = varUser)
    && (Status.Value = "Assigned") ||
    (Requester.Email = varUser)
    && (Status.Value = "Closed")
    
))

What I'm trying to achieve is to display all items with a certain statuses that belongs to a specific user. The above does not give me a delegation warning but if i set my app to Delegation limit 1 it will still only show 1 item. Or how can i actually test this?

My old formula that did give me delegation warning.

ClearCollect(SharePointList, Filter(Helpdesk, 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"))

I also tried using Claims to filter but that gives me a delegation warning but when i use .Email, why is that?
Set(CurrentUserClaim,Concatenate("i:0#.f|membership|",User().Email))

ClearCollect(colList,Filter(
    SharePointList,
    (Requester.Claims = CurrentUserClaim)
    && (Status.Value = "Unassigned") ||
    (Requester.Claims = CurrentUserClaim)
    && (Status.Value = "Assigned") ||
    (Requester.Claims = CurrentUserClaim)
    && (Status.Value = "Closed")
    
))

[PaulD1 · Accepted solution]

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

[JimmyWork]

@PaulD1 Thank you again.

Do i understand this correct.

If i have a list with 600 items, delegation is set to 500.
I then use the formula below that does not give me a delegation warning, then i should retrieve items in the monitor that are beyond 500 items?

ClearCollect(colList,Filter(
    SharePointList,
    (Requester.Email = varUser)
    && (Status.Value = "Unassigned") ||
    (Requester.Email = varUser)
    && (Status.Value = "Assigned") ||
    (Requester.Email = varUser)
    && (Status.Value = "Closed")
    
))

What i did was i created a test list with 620 items.
I set delegation to 100 in the app.

Item 620 is the one i want to get and that item is = varUsers email.

My result should then display item 620 correct?

This is working.

So my question now is the delegation in the app i set would fetch only 100 items from that specific user?

I don't really get this part.

[JimmyWork]

@mdevaney I do get a delegation warning with the suggested part, delegation warning comes from CountRows.

But it seems to work.

Tested like this.

List with 600 items

App setting for delegation is set to 100.

I put the data in the items random so some item is 100 other is item 550 and so on.

Now due to my app setting to only display 100 i will only pull 100 items but from the full list? So it does not matter if my list have 5000 items i will get the 100 items for that user no matter where in the list that item is correct?

[JimmyWork]

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.

Filter(
    TestList,
    ('Created By'.Email = varUser)
    && (Status.Value = "Unassigned") ||
    ('Created By'.Email = varUser)
    && (Status.Value = "Assigned") ||
    ('Created By'.Email = varUser)
    && (Status.Value = "Closed")   
)

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

ClearCollect(
    colList,
    Filter(
    TestList,
    ('Created By'.Email = varUser)
    && (Status.Value = "Unassigned") ||
    ('Created By'.Email = varUser)
    && (Status.Value = "Assigned") ||
    ('Created By'.Email = varUser)
    && (Status.Value = "Closed")
    
))

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

[mdevaney · Accepted solution]

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

[JimmyWork]

@mdevaney Yes i have tested that but when you use CountRows you instantly get a delegation warning.

What im not understandning is that it looks like im still getting the data i need.

Maybe im understanding delegations wrong.

My list have 5000 items.
Delegation in the app is set to 500.

With this i will always only get the first 500 results.

Now do i understand it correctly when i say i will only get the result from the first 500 items in the list so item 1-500.

If the item i would like to collect is item 501 i will never get it?

Or is the delegation that it filters and then give me the 500 first results based on my filter? Meaning i would be able to collect item 501 and so on if the first 300 items is not the Requester?

CountRows seems to get all the items up to my apps delegation meaning if a Requester always have less items then 500 it's ,fine even if the list is 5000 items?

Im using the RequesterEmail singlestring column that contains the email in lower cases. And im using varUser = Lower(User().Email)

I must say thank you for taking your time with me

[JimmyWork]

@mdevaney 

CountRows always shows as not delegate.

But it works i tested on a list with 2100 items it will pull the 500 items from the list, i guess it respects the app delegation settings?

CountRows(Filter(TestList,RequesterEmail=varUser)) 

[mdevaney]

@JimmyWork 

Yes COUNTROWS will always show as cannot be delegated.  But in your case you may disregard it?

Why?  The FILTER function on the inside of COUNTROWS can be delegated.  FILTER is allowed to pull all the rows from the database.  However, COUNTROWS will only count the first 2,000 rows of that subset.

Since your original problem stated the COUNTROWS will never exceed 2,000 it is perfectly fine to ignore the delegation warning.

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

[PaulD1 · Accepted solution]

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.