cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

ClearCollect Inside A ForAll Loop

Is this even possible?

Basically I have a user select a job they are currently working on, then based on the client ID I build a collection of assets on that Clients site, then a list of questions to be asked for each asset.

When a user selects the job they are working on the client ID is saved as variable JG_Client. And when they go to load the client info I do...

ClearCollect(Asset_List,Filter('SQL_Client_Asset_Table',Client_ID = JG_Client))

 

So Far So Good.

 

But now I want to build a collection for each Asset inside Asset_List. I've been thinking along the lines of...

 

ForAll(Asset_List,ClearCollect("Asset_ID_No",Filter('SQL_Asset_Questions_Table',Asset_ID = Asset_List_Asset_ID)))

 

This is partly to get around the disambiguation limit, If i just put all the asset questions into one table there could be 7000 questions per job, so way too much for powerapps to handle in one list.

I want this to be part of a download so the user can download all the questions to their device before they start the job, because once they are in the work location there is a high probability of losing connection.

Am I barking up the wrong tree entirely here?

4 REPLIES 4
Community Support
Community Support

Hi @seanbrogan ,

 

Do you mean you want to create multiple collections at one-time?

First of all, ClearCollect function can't be invoked within ForAll function which is regulated. 

I also try ForAll(["1","2","3"],Collect(Value,{Title:"1"})), but error shows the first parameter should be a collection, it fails.

So I guess that it is not supported to create multiple collections at one-time.

If you want this feature, you can submit an idea to PowerApps Ideas Forum: https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas 

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

 

 

Advocate V
Advocate V

Well it makes sense not being able to use a Clearcollect in a forAll statement. 

 

I required something similar in the app I'm developing, although I'm using a CDS datasource instead. The construction I came up with is the following:

Clear( parentCollection ); Clear( childCollection );

ForAll( Filter( parentEntities, <condition> ),
  Collect( parentCollection, { copy of the required record fields } );
  ForAll( Filter( [@childEntities], childentity.Parent=parentId ),
    Collect( childCollection, { copy of the required record fields } );
  );
);

Since the child entity contains a link to the parent, I can afterwards simply query the child collection with a parent constraint locally to filter based on the parent.

 

I'm not sure if you can use this construction as is, but maybe it helps in pointing towards a workable solution.

Thanks For your response.

Yes what I am trying to achieve is to have a child collection for each row in the parent collection. i.e. if there are 10 rows in the parent collection I would have 10 different child collections.

I think I may have to get the SQL database do most of the work here though as if I try to only have one child collection it is liable to have up to 7000 rows in it, which is too big for powerapps to handle.

So I have built my workaround. I have created a number of SQL queries that filter the data down into chunks smaller than 2000 rows.

Then I build a collection from each query, then collect those individual collections into a final collection that has more than 2000 rows.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (4,929)