SharePoint in operator delegation warning

(0) Share

Report

Posted on by lukas.schauffele

186

I have a SharePoint table called 'ITP Result Line' with hundreds of thousands of records that looks like this:

No	Description	Result	JobNo	ITPTemplateCode
841	External CFC glued and fixed off with consistent margins	Pass	J001315-21	MANUF12-02
816	Ensure all joinery doors and locks are functioning	Pass	J000667	MANUF12-01
810	Ensure steel is dry before applying paint	Pass	J001020-02	STEEL08-01

I have another SharePoint table called 'ITP Contact Access' with a few hundred records that looks like this:

AccessType	AccessCode	VendorCode
Job	J000667	S20003
ITP Template	MANUF12-02	S20003

I build two one-column collections like this:

ClearCollect(
 colContractorAccessITPTemplate,
 Filter(
 'ITP Contact Access',
 AccessType.Value = "ITP Template",
 VendorCode = cmbVendor.Selected.No_
 ).AccessCode
);
ClearCollect(
 colContractorAccessJob,
 Filter(
 'ITP Contact Access',
 AccessType.Value = "Job",
 VendorCode = cmbVendor.Selected.No_
 ).AccessCode
)

In the filter for 'ITP Result Lines', I have added the following condition:

If(
 gblContractorMode,
 ITPTemplateCode in colContractorAccessITPTemplate Or JobNo_ in colContractorAccessJob,
 true
)

This correctly filters the list down to only the first two records 841 and 816 and does not display 810. However, it produces a delegation warning:

Delegation warning. The "If" part of this formula might not work correctly on large data sets. The data source might not be able to process the formula and might return an incomplete data set. Your application might not return correct results or behave correctly if the data set is incomplete.

I'm not sure if the delegation warning is for 'ITP Contact Access', which would be fine as it will only contain a handful of records; or if it's for 'ITP Result Line', which would not work due to the number of records in the table.

If it's the latter, I'd love to hear ideas on how to resolve it. I was thinking of dynamically building the filter string like so:

Set(
 gblContractorAccessITPTemplate,
 Concat(
 Filter(
 'ITP Contact Access',
 AccessType.Value = "ITP Template",
 VendorCode = cmbVendor.Selected.No_
 ),
 "ITPTemplateCode = " & Char(34) & AccessCode & Char(34),
 " || "
 )
)

Which produces the correct filter, but gives me a runtime error:

Error when trying to retrieve data from the network: The query is not valid. clientRequestId: f0a02140-0d56-42f1-9d38-3e8fba0e85cb serviceRequestId: f0a02140-0d56-42f1-9d38-3e8fba0e85cb

Thanks for reading and would love to hear your ideas.

Thanks

All responses (4)

Answers (0)

SpongYe 5,580 Super User 2025 Season 1 on at

Like (0)

Report
Re: SharePoint in operator delegation warning

Hi @LKS

I see your using my code but still add the Sharepoint list and not the _colContractorAccessJob
With( { _colContractorAccessJob: Filter( 'ITP Contact Access', VendorCode = cmbVendor.Selected.No_, AccessType.Value = "Job" ).AccessCode }, Filter( _colContractorAccessJob,// I changed it to the created pre-filter 'ITP Result Line' // removed other filters for visibility JobNo_ in _colContractorAccessJob ) )
The in operator is not delegable for SharePoint lists.
This means that PowerApps will only apply the in filter to the first 2000 records of your data source, and ignore the rest.

My code above should be able to remove the delegation warning using pre-filtering.

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻
lukas.schauffele 186 on at

Like (0)

Report
Re: SharePoint in operator delegation warning

Hi @SpongYe - thanks for the reply.

I did the pre-filtering in my collections colContractorAccessJob and colContractorAccessITPTemplate.

I've tried your suggestion but still getting a blue delegation line in the below:

With( { _colContractorAccessJob: Filter( 'ITP Contact Access', VendorCode = cmbVendor.Selected.No_, AccessType.Value = "Job" ).AccessCode }, Filter( 'ITP Result Line', // removed other filters for visibility JobNo_ in _colContractorAccessJob ) )

Delegation seems to stop working the moment I have the 'in' operator in my filter query.

Which is why I thought I could try and build the query string dynamically using the '=' operator.

Set( gblContractorAccessITPTemplate, Concat( Filter( 'ITP Contact Access', AccessType.Value = "ITP Template", VendorCode = cmbVendor.Selected.No_ ), "ITPTemplateCode = " & Char(34) & AccessCode & Char(34), " Or " ) );

produces:

ITPTemplateCode = "CONST02-01" Or ITPTemplateCode = "CONST01-01" Or ITPTemplateCode = "MANUF06-03"

And my filter is:

Filter( 'ITP Result Line', // removed other filters for visibility gblContractorAccessITPTemplate )

But I think Power Apps requires the field name to be hardcoded despite this being a delegable query string.
SpongYe 5,580 Super User 2025 Season 1 on at

Like (0)

Report
Re: SharePoint in operator delegation warning

Hi @LKS

The delegation warning is due to the use of the in operator in your code. This means that the data processing cannot be delegated to your data source. You can use a With() functon to pre-filter your data before applying the in operator. You code would look like:
If( gblContractorMode, With( { _colContractorAccess: Filter( 'ITP Contact Access', VendorCode = cmbVendor.Selected.No_ ).AccessCode, }, Filter( _colContractorAccess, ITPTemplateCode in AccessType.Value || JobNo_ in AccessType.Value ) ), true )
If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻
Duane Montague Moderator on at

Like (0)

Report

Re: SharePoint in operator delegation warning

Can anyone from the community assist with this question? We'd appreciate it!

Duane
Community Manager