The function clear collection has some invalid arguments

(0) Share

Report

Posted on by johnjohn123

3,514

I have 2 SharePoint lists:-

1) Parent_1

2) Child2 . has a ParentId field which store the Parent_1 id in a single line of text.

now i want to do these 2 operations:-

1) Build a collection which contain all the Parentid from the Child2 list.

2) set the Items property of a ComboBox to only contain the Parent_1 records which have an Id inside the collection.

In other words the ComboBox should only contain Parent_1 records which are assigned to atleast one Child2 record. but i tried this formula inside the combobox Item property where i got this error:-

second question, how i can do the filter on the second line without getting a delegation warning?

Thanks

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@johnjohn123
You cannot use ClearCollect in the Items property, it must be used in a behaviour formula eg OnVisible, OnSelect, OnChange, etc

You could just use this instead of the ClearCollect
// Note: use these code windows to post code to the forums // It greatly helps responders to adjust your code without having to re-type it ShowColumns(Child2, "ParentID")

How many records are you looking at in your SP List? This will likely change the options you have available to you here.
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@EddieE i have 2.1 millions records inside the Parent_1 list and around 200,000 inside the Child2 list.. so how i can get the related Parent_1 records which have their IDs inside the array of Project_1IDs?
EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohn123
Are you wanting access to all 2.1 million Parent records inside a PowerApp?

You could get all the Child IDs using 100 ClearCollect functions inside a Concurrent function but why would you? I’d say this would nearly grind your app to a halt?

I’ve created collections of around 350k records before but it made my app extremely slow. It was only an experiment in what could be done do it never went anywhere.

I’m just confused about the volume of data you want to use because PowerApps doesn’t sound like the right tool for this project.
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@EddieE I do not want to get the whole 2.1 million records... what i want to do , is to get all the ParentsIDs from the Child2 list and store them in a collection (this can have a maximume of 600 records)>> then the ComboBox need to only allow searching over the Parent records which IDs are in the collection.. i will allow searching the comboBox and allow do so a search using Startswith.. so after typing couple of charecters you will only get 250-350 results.. i hope you got my requirements now.. thanks
EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohn123
As far as I’m aware, there’s no easy way to get that many records (ie from the child list) into PowerApps. You could use a combo of a few formulas but you’d first need to have a column in your child list of type Number that mirrors the list ID. Best way to do this if you don’t already have it is with Power Automate, which I assume you are adept at?

You could then do this

Concurrent(
ClearCollect( col1, Filter(child, IDNumber >=1 && IDNumber <=2000);

ClearCollect( col2, Filter(child, IDNumber >=2001 && IDNumber <=4000);

ClearCollect( col3, Filter(child, IDNumber >=4001 && IDNumber <=6000);

…

ClearCollect( col100, Filter(child, IDNumber >=198001 && IDNumber <=200000)
);

ClearCollect( colALL, col1, col2, col3 … col100)

This will give you your collection.

If you need distinct values (ie no duplicates) you could use the Distinct function in this collection without issue, I think?
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!
@EddieE here is what i get:-

Inside the Screen Onvisible , i build the collection:-

ClearCollect(childcol,ShowColumns(Child2,"ParentId"));

and it get all the related ParentIDs from the child2 list and store them inside the childcol collection..

then inside the ComboBox >> i define this inside the Item property:-

Filter(Parent_1, ID in childcol.ParentId).Title

where i got a delegation warning on the "in" operator >> so the combox box did not show anything, as the first 2,000 records inside the Parent_1 do not have their Ids inside the childcol collection... so is there a way to fix this?

Thanks

**EDIT**

I also tried this approach, where inside Onvisible property of the screen i define this formulas to get the ParentId inside the Child2 list + forall of the ParentId and get the related Parent_1 records:-

ClearCollect(childcol,Child2, ["ParentId"]); ForAll(childcol,Collect(parentcol,Filter(Parent_1,ID=childcol[@ID])));

and then inside the Combobox items property i define this formula:-

parentcol.Title

where i did not get any delegation warning,,, but the combobox will be empty...
EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@johnjohn123
You say that this formula
ClearCollect(childcol,ShowColumns(Child2,"ParentId"));
is getting " ... all the related ParentIDs from the child2 list ... ". This isn't possible with your Child2 list containing 200,000 records. The above formula will only return, at most, 2,000 records, meaning you are short 198,000 records.

Secondly, this formula
Filter(Parent_1, ID in childcol.ParentId).Title
produces a delegation error due to the 'in' operator. This operator will only return, at most, 2,000 records as well so is ineffective on a list with 2.1 million records.

As I said earlier, PowerApps may not be the best tool for what you are trying to achieve with lists of these sizes. It may be worth your while to watch this video on Large Data Sets in PowerApps. Daniel Christian goes through CDS, Azure/SQL and SharePoint options. SharePoint starts around the 23min mark but the whole video is worth watching IMO

https://www.youtube.com/watch?v=IvTiAanB3Cs