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 @SteRe 

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

[SG013]

HI @Jmanriquerios 

I have done all the steps as you guided,create rollup count in Table A,as you mentioned(in above screenshot you mentioned it as Table B,it should be Table A as you have written in the content).

Now i am able to create Rollup field with related field in Table B and apply filter as below,but still i am unable to see the data of Rollup field in Table A

filter in rollup column of Table A

Output of Rollup column in Table A is blank

Please help me  i think i am near to the result.

Appreciate your Responses

Thanks

SG

[SG013]

HI @SteRe 

I am able to get the results with aggregate in the form.

The problem here is i have edit the item and do recalculate then only the calculation works in form and in the view.

Any workaround for the same?

Appreciate your help 🙂

Thanks

SG

[SG013]

HI @Jmanriquerios 

It worked with agrégation instead of count in Table A. Also i have edit each item and recalculate the field to display the value. Any other approach to get the values updated? once we create the Rollup column?

Also ,can we apply the background color of the rollup value which is the client requirement.I have tried with Javascript event handler but it is not working?

Appreciate your Great help in resolving my issues. 🙂

Thanks

SG

[SteRe]

Recalculation is done by a periodic system job. I think by default these jobs run every or every second hour. 

If you need data right now you can trigger recalculation for a single roll up field/record.

If you need more real-time data and if you want to prevent having this information about last update in Form, you might consider some plug-in to do this calculation. 

[Jmanriquerios]

Hi @SG013 

var formContext;
var refresh = 0;



function recalcularConsolidados(context) {
  var formContext = context.getFormContext();
  var fields = ["jm_montototalcampamento"];
  var entityid = formContext.data.entity.getId().replace("}", "").replace("{", "");
  var cont = 0;


  fields.forEach(function (a) {
    var req1 = new XMLHttpRequest();
    req1.open("GET", formContext.context.getClientUrl() + "/api/data/v9.1/CalculateRollupField(Target=@Target,FieldName=@FieldName)?@Target=%7B%22jm_campamentoid%22%3A%22" + entityid + "%22%2C%22%40odata.type%22%3A%22Microsoft.Dynamics.CRM.jm_campamento%22%7D&@FieldName=%27" + a + "%27", true);
    req1.setRequestHeader("OData-MaxVersion", "4.0");
    req1.setRequestHeader("OData-Version", "4.0");
    req1.setRequestHeader("Accept", "application/json");
    req1.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req1.onreadystatechange = function () {
      if (this.readyState === 4) {
          req1.onreadystatechange = null;
          if (this.status === 200) {
              var results = JSON.parse(this.response);
              if (cont == 1) {
                refresh++;
                formContext.data.save();
                formContext.data.refresh(save);                 
            }
            cont++;
        } else {
            
            cont++;
        }
        }
        };
        req1.send();
        cont++;
        })}

This is what ROLLUP has to do with the recalculation time, there is JS to force the recalculation.

I leave you this code, replace the fields with yours

[SG013]

HI @Jmanriquerios 

i Have multiple rollups columns approx 10.Should i need to implement the JS code for all the fields? and also should i bind this js script code function to each field in the view?

eg: in classic view select specific column,manage properties --> select JS script/library and give function?

Please help me ASAP

Appreciate your response.

Thanks 

SG

[Jmanriquerios]

Hi @SG013 

Well, you must implement it for each rollup column, and depending on what your trigger is, whether it is in the selection of the view, or when a new one is added to the form's grid.
Normally I use it in the form grid because if I add the record from there, when I save the record and return to the main form it will do so.

But perhaps from what you say, an approach with Power Automate that refreshes from time to time, doing so with a scheduled flow, would serve you better.

https://www.ameyholden.com/articles/force-dataverse-rollup-column-calculation-in-power-automate

[Jmanriquerios]

Hi @SG013 

You also have some community tools
https://github.com/james-tekstack/Dynamics-365-Workflow-Tools?tab=readme-ov-file

The issue is that they are solutions that you must install in your environment, and be aware that they are from third parties and may become unmaintained or obsolete.