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

collect forall items in another collection without delegation warning

Hi, 

 

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: 

offices
clients
client_contract_cycles
client_contract_cycles_products

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));;
Clear(client_contract_cycles_products_col);;
ForAll(client_contract_cycles_products_col;
    Collect(client_contract_cycles_products_col;
        Filter('[dbo].[client_contract_cycles_products]';
        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!

3 REPLIES 3
Super User
Super User

Re: collect forall items in another collection without delegation warning

@TiagoFreire 

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:

Gallery_ContractCycles

    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 Team
Community Support Team

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:

 

ClearCollect(
client_contract_cycles_col;
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.
TiagoFreire
Level 8

Re: collect forall items in another collection without delegation warning

pass.pngpass2.png

 

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

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (3,702)