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
