CRM Grand Total of associated entities

I am creating an Assets App in CRM4, so far I have an assets entity, an items entity and a asset items entity.  Everything is related and working fine, I even iframed the associated view in assets.  but my issue is a grand total.  

I am able to subtotal each individual asset item, but how do i then get a grand total from the separate entities?

I tried to create a report (iframe it) and do a summary of the asset items but the results were lacking.

any ideas would be appreciated.
crm4.jpg
LVL 9
Justin ImesFull Time SharePoint JediAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Feridun KadirConnect With a Mentor Principal ConsultantCommented:
I did something like this recently so here is the javascript that I used.

You'll need to make some changes -

Change new_assetitem to the name of your entity
Change new_asset to the name of your asset (parent) entity
Change new_amount to the approriate field name in your assetitem entity
Change new_assetitemtotal to the total field name in your asset entity.

Hope this makes sense and is helpful.

If (crmForm.FormType ==2)

{

var eventguid=crmForm.ObjectId;


var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
authenticationHeader+ 
"<soap:Body>"+ 
"<RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
"<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query'"+
" xsi:type='q1:QueryExpression'>"+ 
"<q1:EntityName>new_assetitem</q1:EntityName>"+ 
"<q1:ColumnSet xsi:type='q1:ColumnSet'>"+ 
"<q1:Attributes>"+ 
"<q1:Attribute>new_amount</q1:Attribute>"+ 
"</q1:Attributes>"+ 
"</q1:ColumnSet>"+ 
"<q1:Distinct>false</q1:Distinct>"+ 
"<q1:Criteria>"+ 
"<q1:FilterOperator>And</q1:FilterOperator>"+ 
"<q1:Conditions>"+ 
"<q1:Condition>"+ 
"<q1:AttributeName>new_asset</q1:AttributeName>"+ 
"<q1:Operator>Like</q1:Operator>"+ 
"<q1:Values>"+ 
"<q1:Value xsi:type='xsd:string'>"+eventguid+"</q1:Value>"+ 
"</q1:Values>"+ 
"</q1:Condition>"+ 
"</q1:Conditions>"+ 
"</q1:Criteria>"+ 
"</query>"+ 
"</RetrieveMultiple>"+ 
"</soap:Body>"+ 
"</soap:Envelope>";

// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result.
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
 var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
 alert(msg);
}
// Parse and display the results.
else
{
 var results = resultXml.getElementsByTagName('BusinessEntity');
 var msg = "";
 if (results.length == 0)
 {
  msg = "No asset items were found for "+eventguid+".";  
  alert(msg);
  return;
 }
 else
 {
  var total = 0;

    for (i=0;i < results.length;i++)
    {
     var assetitemamount = results[i].selectSingleNode('./q1:new_amount').nodeTypedValue;

    total = total+parseFloat(assetitemamount);  break;

    }

    crmForm.all.new_assetitemtotal.DataValue = total;
    crmForm.all.new_assetitemtotal.ForceSubmit=true;

    crmForm.Save();
 }
}
}

Open in new window

0
 
stacko111Commented:
I would personally do it using a report in a iFrame via SQL Server Reporting Services as you have mentioned.

However you could probably also achieve this using a plugin that adds up all the subtotals and puts it into a field on your main Assets form. I.e. like how the Quote Product lines are totalled on the main Quote form.

Someone else may be able to help you with this - plugins aren't my strong point. I could help you with the iFrame report if you wanted to go down that route.
0
 
Feridun KadirPrincipal ConsultantCommented:
You could take this approach:

1. Create a total field in the Asset entity.
2. In the form OnLoad script for the Asset run a query back to the CRM database for all AssetItems parented by the asset.
3. Then loop through the results adding up the subtotal fields.
4. Set the total field to the value of the calculation in step 3.

For more info on step 2 have a look at the CRM SDK under calling webservices from JScript.

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Justin ImesFull Time SharePoint JediAuthor Commented:
Thanks for the reply, yes thats exactly what i'm looking for some type of plug in, i can not figure out the code to get it to pull from another entity, maybe I will look into the onload code on the main quote form, thanks for the hint.

The report did seem like the way to go, but if you use report wizard you can't pull view the report through SSRS like normal, you need to export the crm report and add your own parameters, after doing all that it still didn't render the way i would like it to... thats why i'm back to the plug in.
0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
Thanks Feridun, does this go in the onload of your asset entity or the assetitems entity?
0
 
Feridun KadirPrincipal ConsultantCommented:
The asset entity (the parent item).

The first part of the script queries the CRM database for assetitems that have the particular asset record as a parent.

It then loops through the records (if any) and adds the relevant amount column and then finally sets the total column on the asset item to that total.

The formtype 2 at the beginning is to ensure this only happens on a existing asset record form not a new form.
0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
I am receiving a generic CRM error, i feel i'm almost there... i took out the iframe and put in this code.

I feel like i have an attribute named wrong...
new_assetitem = name of asset item entity
new_assets = name of main asset entity
new_subtotal = attribute of asset items
new_grandtotal = attribute of asset

new_itemassettagid = links asset and item together in N:1 under asset item entity (*not being used in code)

in your code, "<q1:AttributeName>new_assets</q1:AttributeName>"+

is that looking for the main asset entity name? or an lookup attribute under assetitems?


var CRM_FORM_TYPE_CREATE = "1";
var TODAY;

// Only make these changes when the form is opened in Create mode.
if (crmForm.FormType==CRM_FORM_TYPE_CREATE) 
    {
    // Get today's date.
    TODAY = new Date();
    
crmForm.all.new_dateordered.DataValue = new Date();
    }



If (crmForm.FormType ==2)

{

var eventguid=crmForm.ObjectId;


var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
authenticationHeader+ 
"<soap:Body>"+ 
"<RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
"<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query'"+
" xsi:type='q1:QueryExpression'>"+ 
"<q1:EntityName>new_assetitem</q1:EntityName>"+ 
"<q1:ColumnSet xsi:type='q1:ColumnSet'>"+ 
"<q1:Attributes>"+ 
"<q1:Attribute>new_subtotal</q1:Attribute>"+ 
"</q1:Attributes>"+ 
"</q1:ColumnSet>"+ 
"<q1:Distinct>false</q1:Distinct>"+ 
"<q1:Criteria>"+ 
"<q1:FilterOperator>And</q1:FilterOperator>"+ 
"<q1:Conditions>"+ 
"<q1:Condition>"+ 
"<q1:AttributeName>new_assets</q1:AttributeName>"+ 
"<q1:Operator>Like</q1:Operator>"+ 
"<q1:Values>"+ 
"<q1:Value xsi:type='xsd:string'>"+eventguid+"</q1:Value>"+ 
"</q1:Values>"+ 
"</q1:Condition>"+ 
"</q1:Conditions>"+ 
"</q1:Criteria>"+ 
"</query>"+ 
"</RetrieveMultiple>"+ 
"</soap:Body>"+ 
"</soap:Envelope>";

// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result.
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
 var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
 alert(msg);
}
// Parse and display the results.
else
{
 var results = resultXml.getElementsByTagName('BusinessEntity');
 var msg = "";
 if (results.length == 0)
 {
  msg = "No asset items were found for "+eventguid+".";  
  alert(msg);
  return;
 }
 else
 {
  var total = 0;

    for (i=0;i < results.length;i++)
    {
     var assetitemamount = results[i].selectSingleNode('./q1:new_subtotal).nodeTypedValue;

    total = total+parseFloat(assetitemamount);  break;

    }

    crmForm.all.new_grandtotal.DataValue = total;
    crmForm.all.new_grandtotal.ForceSubmit=true;

    crmForm.Save();
 }
}
}

Open in new window

0
 
Feridun KadirPrincipal ConsultantCommented:
in your code, "<q1:AttributeName>new_assets</q1:AttributeName>"+

is that looking for the main asset entity name? or an lookup attribute under assetitems?


This is the lookup attribute field name on the assetitems entity.
0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
I'm still unable to get this to total the entities and display properly.
0
 
Feridun KadirPrincipal ConsultantCommented:
Did you resolve this or are you still having an issue?

If so, could you post the javascript that you are currently using please.
0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
I am still having troubles... I will post the code when i get back to the office, thanks in advance
0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
This is what I currently have, its throwing an error when you try and save and close it.
this is placed on the onload in the main form properties of Asset.

Any help will be appreciated, This is the last task I need to complete before we can launch this section of CRM.
var CRM_FORM_TYPE_CREATE = "1";
var TODAY;

// Only make these changes when the form is opened in Create mode.
if (crmForm.FormType==CRM_FORM_TYPE_CREATE) 
    {
    // Get today's date.
    TODAY = new Date();
    
crmForm.all.new_dateordered.DataValue = new Date();
    }



If (crmForm.FormType ==2)

{

var eventguid=crmForm.ObjectId;


var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
authenticationHeader+ 
"<soap:Body>"+ 
"<RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
"<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query'"+
" xsi:type='q1:QueryExpression'>"+ 
"<q1:EntityName>new_assetitem</q1:EntityName>"+ 
"<q1:ColumnSet xsi:type='q1:ColumnSet'>"+ 
"<q1:Attributes>"+ 
"<q1:Attribute>new_subtotal</q1:Attribute>"+ 
"</q1:Attributes>"+ 
"</q1:ColumnSet>"+ 
"<q1:Distinct>false</q1:Distinct>"+ 
"<q1:Criteria>"+ 
"<q1:FilterOperator>And</q1:FilterOperator>"+ 
"<q1:Conditions>"+ 
"<q1:Condition>"+ 
"<q1:AttributeName>new_itemassettagid</q1:AttributeName>"+ 
"<q1:Operator>Like</q1:Operator>"+ 
"<q1:Values>"+ 
"<q1:Value xsi:type='xsd:string'>"+eventguid+"</q1:Value>"+ 
"</q1:Values>"+ 
"</q1:Condition>"+ 
"</q1:Conditions>"+ 
"</q1:Criteria>"+ 
"</query>"+ 
"</RetrieveMultiple>"+ 
"</soap:Body>"+ 
"</soap:Envelope>";

// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result.
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
 var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
 alert(msg);
}
// Parse and display the results.
else
{
 var results = resultXml.getElementsByTagName('BusinessEntity');
 var msg = "";
 if (results.length == 0)
 {
  msg = "No asset items were found for "+eventguid+".";  
  alert(msg);
  return;
 }
 else
 {
  var total = 0;

    for (i=0;i < results.length;i++)
    {
     var assetitemamount = results[i].selectSingleNode('./q1:new_subtotal).nodeTypedValue;

    total = total+parseFloat(assetitemamount);  break;

    }

    crmForm.all.new_grandtotal.DataValue = total;
    crmForm.all.new_grandtotal.ForceSubmit=true;

    crmForm.Save();
 }
}
}

Open in new window

0
 
Feridun KadirPrincipal ConsultantCommented:
Apologies for not replying. I'm deeply involved with a number of projects at the moment so don't have time to review this at the moment.

0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
This was never resolved, please close question and refund points.
0
 
Justin ImesFull Time SharePoint JediAuthor Commented:
was not able to come up with a perfect solution, but thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.