Calculated Field on CRM Entity

Question

I have a 1:N relationship between Account and Portfolios in Dynamics CRM I.e each account has multiple Portfolios and Each Portfolio has Specific Assets.

I am trying to create a field on Account Form which calculates the sum of "ALL Assets of All related portfolios" of the account and display it on the Account form

As a workaround,I tried to create a Portfolio view grouping by Account but it doesnt SUM and rollup the Portfolio assets to Account level.

enter image description here

So on account Form i am trying to create a textfield which calculates the Total Account Assets to be $25,000 in this example

When is the calculation supposed to happen? When you create an Account? When you update it? Or maybe during creation of a Portfolio? — Konrad Viltersten
It should appear on "Account" Form just like any other field and be able to pull it onto a view too !! — Ravi Yenugu
Youcan populate the field using javascript on form load by fetching data from the db with fetch xml or any other form of going to thr db.. — Greg Oks
Does it need to be stored, so you can run reports off of it, or is it solely for the user viewing the entity? — Daryl
Doesnt need to be stored because its a variable number, My goal is to create a view on account and pull in the Total assets column — Ravi Yenugu

Mike_Matthews_II Mike_Matthews_II · Accepted Answer · 2013-03-22T18:03:28

function setupGridRefresh() {
var targetgrid = document.getElementById("NAME OF SUBGRID");

// If already loaded
if (targetgrid.readyState == 'complete') {
    targetgrid.attachEvent("onrefresh", subGridOnload);
}
else {
    targetgrid.onreadystatechange = function applyRefreshEvent() {
        var targetgrid = document.getElementById("NAME OF SUBGRID");
        if (targetgrid.readyState == 'complete') {
            targetgrid.attachEvent("onrefresh", subGridOnload);
        }
    }
}
subGridOnload();
}

function subGridOnload() {
//debugger;
var grid = Xrm.Page.ui.controls.get('NAME OF SUBGRID')._control;
var sum = 0.00;

if (grid.get_innerControl() == null) {
    setTimeout(subGridOnload, 1000);
    return;
}
else if (grid.get_innerControl()._element.innerText.search("Loading") != -1) {
    setTimeout(subGridOnload, 1000);
    return;
}

var ids = grid.get_innerControl().get_allRecordIds();
var cellValue;
for (i = 0; i < ids.length; i++) {
    if (grid.get_innerControl().getCellValue('FIELD NAME LOWER CASE', ids[i]) != "") {
        cellValue = grid.get_innerControl().getCellValue('FIELD NAME LOWER CASE', ids[i]);
        cellValue = cellValue.substring(2);
        cellValue = parseFloat(cellValue);
        sum = sum + cellValue;
    }

}

var currentSum = Xrm.Page.getAttribute('DESTINATION FIELD').getValue();
if (sum > 0 || (currentSum != sum && currentSum != null)) {
    Xrm.Page.getAttribute('DESTINATION FIELD').setValue(sum);
}
}

I pieced this together from a couple of sources and currently use it one of my solutions. Let me know if you need some more help or if I've misread the question. (Btw, this solution is based on the assumption that you need the total to change when the subgrid has entries added or removed. If this is not the requirement, I would suggest the RetrieveMultiple OData call.)

Calculated Field on CRM Entity

3 Answers