Calculated field at the CRM facility
I have a 1: N ratio between account and portfolios in Dynamics CRM I. Each account has multiple portfolios and each portfolio has specific assets.
I am trying to create a field on an account form that calculates the "ALL Assets of all linked portfolios" amount of an account and displays it on the "Account" form
As a workaround, I tried to create a Group by Portfolio View by Account, but this did not SUM and did not group portfolio assets at the account level.
So, in a Form account, I am trying to create a text box that calculates the total assets of the account in this example as $ 25,000.
source to share
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 have pieced this together from several sources and am currently using this one of my solutions. Let me know if you need more help or if I misunderstood the question. (Btw, this solution is based on the assumption that you want the total to change when records are added or removed in the subgrid. If this is not a requirement, I would suggest calling RetrieveMultiple OData.)
source to share
Take a look at AutoSummary from Gap Consulting which is well worth it. Or take the time to create your own. You need a field in your account that is updated every time you:
- create a portfolio entry
- update the value in the Portfolio record
- delete portfolio entry
- rename a member record from one account to another
The first two are light enough to deal with the workflow or javascript in the onSave event in the portfolio. The third can only be done with a workflow, not with javascript (I think). The latter will need the javascript onLoad to store the current lookup value in the account so onSave can compare and then shrink one and increment the other. All four can be done using the plugin.
source to share
Although this has already been answered, I will put a second option for you. Take a look at the FormulaManager with North 52. You get a certain number of formulas for free, so this could be an even more economical solution.
Update To add to this, if the field is solely for posting a value (and doesn't need to be stored in the database), then instead of using a physical field and plugins, you can create a web resource that makes an Aggregated FetchXml request and just displays the resulting value.
Again, this is what I know what Formula Manager does out of the box. Never used Auto Message.
source to share