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 it out!
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand