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));; 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!
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.
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]'.
Check out these cool Power Apps & vote on your favorite!
Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)
Check out whats happening in Power Apps
FIll out a quick form to claim your community user group member badge today!