In One-Many relation ship ,how to get related records to one related dataverse table from many related dataverse table in model driven app

(0) Share

Report

Posted on by SG013

In One-Many relation ship ,how to get related records to one related table from many related table in model driven app

HI All

I have 2 dataverse tables A,B and there is one to many relationships between A(one)--> B(Many).

I need to get the columns from B to A.

Also i need count of the records from B table to A.

Can anyone suggest the best approach or is it feasible in any other way in model driven App .It is bit urgent.

Appreciate your help in Advance.

Thanks & Regards

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (18)

Answers (0)

Jonathan Manrique 2,695 on at

Like (0)

Report
Copy link

Link copied!

Hi @SG013

You can have a column to count or add,

I give you two examples: you have a column in your table b that is the total sales, now it is related to table A and in it you have more related records that are part of column b.

Now you would like a field that adds up the total sales of all the records from table b to table a, then you can use a Rollup field to make this consolidated.

If you want to count it, use count instead of sum

Create and define calculation or rollup columns in Microsoft Dataverse - Training | Microsoft Learn

Was this reply helpful? Yes No
SteRe 254 on at

Like (0)

Report
Copy link

Link copied!

Hi @SG013 ,
I'm not sure if I got what you mean by "getting columns from B to A", but I guess 1 of two options.
So you might have a table A customer and table B quotes.
Option 1: At customers you would like to show a list of quotes to this customer. In this case you can add a subgrid to your customer form and activated "related records".
Option 2: At quote you have selected the customer and would like to display some details like adress. In this case you can define a quick view form at customer entity having the fields and at form of quote you can add this form then.

Beside this you can do rollups like written by @Jmanriquerios or if needed you could even copy data from one record to another (most times not the best approach from data point of view)

Was this reply helpful? Yes No
SG013 74 on at

Like (0)

Report
Copy link

Link copied!

HI @Jmanriquerios

I am facing 2 issues here.
1.) Not able to get the related table B(many) columns in Table A ,to write calculations or formula fields
2.)In Table B ,not able to write rollup because all the columns are text or choice columns.
Eg: In Table B there is column Completed with choices: Yes,NO,NA.
I need the count of the records with values "Yes" as One rollup column
and One more column with count of values with records "No" .
I am able to get only the sum of "Item no column" ,but not count of records in the rollup.
3) If i create any rollup fields ,how i can get these fields data in Table A.
I could see challenge here,not able to get the columns to use it in the calculated field or business rules of "one to Many " A-->B relationship' records(In A table i couldnt write any calculations).Where as i can write the formulas or calculations for "many to one" B-->A relationship.(In B table i can get lookup column in the calculated fields).

Hope you understand the challenges i am facing

Appreciate your help
Many Thanks
SG

Was this reply helpful? Yes No
Jonathan Manrique 2,695 on at

Like (0)

Report
Copy link

Link copied!

Hi @SG013

Create a numerical column in table b, call for example count and always assign the value 1, hide it, and then you can do the cumulative.
With this option you will be solving questions 1 and 2.
Then create a rollup column in table A called for example count and what it will do is add the count column that you created in table b

Was this reply helpful? Yes No
SG013 74 on at

Like (0)

Report
Copy link

Link copied!

HI @SteRe
I agree with you. But what i am looking here is to get those related columns in the views not in the form.I need total items count based on choice field value from related table B. Need to implement some formulas with related table B columns and columns in the table A .

Hope you understand the challenge i am facing here.

Appreciate for your quick response

Many Thanks
SG
In the form i have already added the related table views in the sub grid.

Was this reply helpful? Yes No
SteRe 254 on at

Like (1)

Report
Copy link

Link copied!

Now I got it.
Pretty simple issue. Just create a new column at table A: type = number, behavior = rollup. In rollup definition you select table B and as filter you define the criteria. As aggregate function you use count.
I did a quick and dirty approach by adding my field to filter on at contact + added 3 fields at account to aggregate on:

Was this reply helpful? Yes No
SG013 74 on at

Like (0)

Report
Copy link

Link copied!

HI @Jmanriquerios
In Table B i created whole number column and in calculation i selected choice column value and set default value to 1.
Please find the below screenshot when i create rollup column,i am unable to select related entity of newly created number column and i am facing the below issue

Was this reply helpful? Yes No
Jonathan Manrique 2,695 on at

Like (1)

Report
Copy link

Link copied!

Hi @SG013

The image doesn't let me see well but I'll explain.

The field that you have created numerically in table b, always assign it the value of 1, not the one of choice.

Then in table A, is where you create the rollup and what you do there is count the field of table b, when for example the choice is equal to green

then you create a business rule to always set the value 1

now create the count in table B

if you want to set filters

Was this reply helpful? Yes No
SteRe 254 on at

Like (0)

Report
Copy link

Link copied!

Hi @Jmanriquerios
why would you have a number field at your oferata table without using it?
In you approach going for a sum as aggretation would totally fit as it would sum up those fields with "1". When using count as aggregate function it doesn't matter which field you aggregate. And as only some records should be counted, use the filter option.
Or did I miss a part ?

Was this reply helpful? Yes No
Jonathan Manrique 2,695 on at

Like (1)

Report
Copy link

Link copied!

Hi @SteRe

I am using it for counting, creating the fields then it helps to generate the dashboards you need at the graphics level

Was this reply helpful? Yes No