Unanswered

Delegation issue for filtering SharePoint list with over 2000 items

(1) Share

Report

Posted on by theappwhisperer

Hello! I have a booking app that minuses the amount of bookings (determined by finding matching entries) from the capacity (a column in the data source) to give a number of spots still available. It was working fine until I hit 2000 items but now has delegation issues. Please help me rewrite this to avoid delegation issues if possible! I've tried a few solutions but can't get it quite right.

(ThisItem.Capacity) -
CountIf(
[@DataSourceName],
'Date/Time' = ThisItem.'Date/Time' &&
'Session'.Value = ThisItem.'Session'.Value &&
'Trainer'.DisplayName = ThisItem.'Trainer'.DisplayName &&
'Entry Type'.Value = "Booking" &&
'Attendance Status'.Value <> "Cancelled"
)
& " Spots Left"

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

iAm_ManCat 18,228 Most Valuable Professional on at

Like (0)

Report
Hi!

I don't believe CountIf is in the list of delegable functions, so you'd need to do this a different way (ie shape the data first then count it).

In addition to the above list there are different limitations depending on which datasource you are using:

Power Apps delegable functions and operations for Microsoft Dataverse

Power Apps delegable functions and operations for SharePoint

Power Apps delegable functions and operations for SQL Server

Power Apps delegable functions and operations for Salesforce

So your first test is whether you can filter without delegation using that same formula. Then we can Count the filtered set of items after the filter.

(ThisItem.Capacity - Count( Filter( [@DataSourceName], 'Date/Time' = ThisItem.'Date/Time' && 'Session'.Value = ThisItem.'Session'.Value && 'Trainer'.DisplayName = ThisItem.'Trainer'.DisplayName && 'Entry Type'.Value = "Booking" && 'Attendance Status'.Value <> "Cancelled" ) ) ) & " Spots Left"

The second hurdle might be your use of sub-properties of those items, like 'Session'.Value but let's first see if the above formula is considered delegable by your datasource.

Was this reply helpful? Yes No
theappwhisperer 14 on at

Like (1)

Report

Hi! Thanks for your response. I should have included that the connector is SharePoint. I've tried your suggestion but am still getting a delegation warning and not returning the correct answer (even if I delete Attendance status). Appreciate any further guidance 😊

Was this reply helpful? Yes No
iAm_ManCat 18,228 Most Valuable Professional on at

Like (0)

Report
Sure, happy to provide some troubleshooting you could look at

The delegation warning will still be there for Count as that's a non-delegable query, but as long as the content within it, the Filter, is delegable, then the value should be valid.

What I would do is to create a new page, put a bunch of galleries in it, then take your Filter code ONLY, and put it as the Item/Datasource for the gallery - in each gallery, only put one line of your filter conditions, then that way you should be able to find out which of your conditions is returning incorrect results by looking at the datasource itself and comparing to the items you get in each of the galleries. This way you can systematically determine which of the filters is giving you issues by going through them one by one and comparing against the values you expect from the datasource when filtered the same way.

Gallery 1:

Filter( [@DataSourceName], 'Date/Time' = ThisItem.'Date/Time' )

Gallery 2:

Filter( [@DataSourceName], 'Session'.Value = ThisItem.'Session'.Value )

etc

Also, based on the code, it looks like you have a gallery within a form or a gallery within another gallery - is that correct? You'd need to replicate that scenario when creating your separated galleries for testing.

Was this reply helpful? Yes No
Jose Ramon 107 on at

Like (0)

Report

The other day I was watching a video of someone who has solved the problem of delegation in a SharePoint list using Microsoft Graph, the video is in Spanish.
https://www.youtube.com/watch?v=plXNRszEXxU&t=983s
Basically, what it does is query the SP list by making a call to the Microsoft Graph API using the Office365Groups connector.
Then, using a Timmer it is able to store in a collection all the elements of a list and from there, it can do the filtering.

Was this reply helpful? Yes No
iAm_ManCat 18,228 Most Valuable Professional on at

Like (0)

Report

The Office365Groups API call exploit was noted and fixed by Microsoft @jrestebanez - this is why they deprecated the V1 connector.

The V2 connector only allows calls to the Groups segment, and the V1 connector will be fully deprecated (and no longer be available) in the near future

You can see more here in the Connector reference for Office365Groups

To call other graph API functions you must now use the 'Invoke an http request' with Azure AD action.

Cheers,

Sancho

Was this reply helpful? Yes No
Jose Ramon 107 on at

Like (1)

Report

Wow, I didn't know that, so I'll remove it from my interesting links.
Thank you very much!!!

Was this reply helpful? Yes No