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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,786)