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

[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

GS

[Jmanriquerios]

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

[SteRe]

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)

[SG013]

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

[Jmanriquerios]

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

[SG013]

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.

[SteRe · Accepted solution]

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:

[SG013]

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

[Jmanriquerios · Accepted solution]

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

[SteRe]

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 ?