Showing results for 
Search instead for 
Did you mean: 
Kudo Collector
Kudo Collector

collect forall items in another collection without delegation warning



I am trying to fetch client information from a database, but I am having some difficulty when trying to fetch multiple tables into collections at once.

I has a several of one-to-many relationships, in a hierarchy like this: 


Each of the former has many of the latter.

I do not have to fetch everything all at once - and the 500 item limit would hit me.
But, in some circumstances I would like to dig deeper while showing a higher level. 

In the example below, ComboBoxClients is a single-select combobox to choose the client_id, to show all contracts.

In the case of client_contract_cycles, each has a handful of client_contract_cycles_products, not dozens or hundreds.
When showing a gallery or table of client_contract_cycles, I would like to show every product of each cycle, from client_contract_cycles_products

The problem is, if I do a

ClearCollect(client_contract_cycles_col;Filter('[dbo].[client_contract_cycles]';client_id = ComboBoxClients.Selected.client_id));;
        client_contract_cycles_id = client_contract_cycles_products_col[@client_certification_id]

I get the delegation warning.

I could not find a way to bypass with some creative formula-ing, but I believe there may be a way to write it to avoid the delegation warning.

Or maybe there is some way to do lateral thinking and feth the information in a different way, but I couldn't find it. 

Any help? Cheers!

Super User
Super User

Re: collect forall items in another collection without delegation warning


Is there any particular reason that you need to Collect all of them into a collection?


I'm not entirely clear on your interface, but I believe what you might be looking for is a nested gallery.

ComboBox selects the Client

Gallery shows the rows of contract cycles

Each row has a gallery of contract cycle products.


If that is the case, then you can consider formulas like this:


    Items :  AddColumns(Filter(client_contract_cycles, clientID=ComboBox.Selected.ClientID), "CycleProducts", Filter(client_contract_cycles_products, CycleID=Contract_CycleID))


This would give you a Gallery with all of the items from the contract cycles that match the cleint ID chosen with an added column of records from the cycles_products table. 

Then you can put a sub Gallery in your Gallery and set its Items property to : ThisItem.CycleProducts


Since you are working with SQL tables, the delegation should not be an issue with the = operator.  And, with this, you will not have the overhead of a Collection and the constant refreshing of the Collection you have to do to maintain the data accurately. 


Just a suggestion to consider...I hope it fuels some thought.


Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Community Support
Community Support

Re: collect forall items in another collection without delegation warning

Hi @TiagoFreire ,

Could you please share a screenshot about the Delegation warning within your formula?

Further, could you please share a bit more about the client_id, client_certification_id and client_contract_cycles_id that you mentioned? Are they both int type column or text type column in your SQL Table?


Based on the formula that you provided, I think there is something wrong with it. Please modify your formula as below:


Filter('[dbo].[client_contract_cycles]'; client_id = ComboBoxClients.Selected.client_id)
);; Clear(client_contract_cycles_products_col);; ForAll(
client_contract_cycles_col; /* <-- Please type client_contract_cycles_col collection here rather than client_contract_cycles_products_co that you mentioned */ Collect(
client_contract_cycles_products_col; Filter('[dbo].[client_contract_cycles_products]'; client_contract_cycles_id = client_contract_cycles_col[@client_certification_id]) ) )

Please take a try with above formula, then check if the issue is solved on your side.

Note: The client_certification_id represents a column in your '[dbo].[client_contract_cycles]', which relates to the client_contract_cycles_id column in your '[dbo].[client_contract_cycles_products]'.


Best regards,


Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kudo Collector
Kudo Collector

Re: collect forall items in another collection without delegation warning



delegation warning: the filter part of this formula may not work correctly in large data sets. (...) the classic 500 limit warning

Helpful resources


Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!


Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)


Community Highlights

Check out whats happening in Power Apps


Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

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