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? 

[mdevaney]

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

[JimmyWork]

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

[mdevaney]

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

[JimmyWork]

@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"))

[PaulD1]

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

[JimmyWork]

@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"))

[mdevaney]

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

[JimmyWork]

@mdevaney Thank you i will try this.

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

[PaulD1]

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.