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
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.
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
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
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
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.
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
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
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
Hi @SteRe
I am using it for counting, creating the fields then it helps to generate the dashboards you need at the graphics level
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 ?
WarrenBelz
791
Most Valuable Professional
MS.Ragavendar
410
Super User 2025 Season 2
mmbr1606
275
Super User 2025 Season 2