Dataverse (for Teams) (old name is CDS) supports relationships between data. When you set up a data model, you can create nested tables.
Let’s imagine that a parent table “Company” has a relationship with a “Project” table.
The “Project” table has a relationship with a “Task” table.
If you want to show how many “Project” rows a “Company” row has, the formula is easy.
Let’s imagine the companies are showed in a gallery gal_Companies. When you click on a company, you want to show the amount of projects in a label field. The text parameter will be:
CountRows(gal_Companies.Selected.'Projects')Now, you want to show how many “Task” rows that company has (not how many tasks a project has). You cannot use the previous formula, because you need to go 1 level deeper. You need to count the amount of rows of a nested table.
First, you have to enable the option below in your Power App settings.
Go to File, Settings, Advanced Settings.
Enable “Record scope one-to-many and many-to-many relationships”:
After you enabled the option, add following formula to a second label:
Sum(
ForAll(
gal_Companies.Selected.'Projects', CountRows(ThisRecord.'Tasks')
), Value)
You will see the amount of tasks for the selected company. Using the ForAll function, the amount of tasks will be counted for every project assigned to the company you selected.
If you have large data tables, pay attention with the CountRows() function. At this moment it still has a double underline which is a warning for delegation issues.
Delegation means that the Power App sends the query to the server and that the data only answers with the relevant information. When a query is non-delegable, all the data is sent to the client (the Power App) which needs to process it. This limited is standard 500 items but can be increased to 2.000 items.
You need to check that you don’t exceed the data row limit for non-delegable queries.
