cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
seanbrogan
Level 8

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
Microsoft v-siky-msft
Microsoft

Re: ClearCollect Inside A ForAll Loop

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.

 

 

MrNappa
Level: Powered On

Re: ClearCollect Inside A ForAll Loop

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.

seanbrogan
Level 8

Re: ClearCollect Inside A ForAll Loop

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.

seanbrogan
Level 8

Re: ClearCollect Inside A ForAll Loop

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
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,142)