Solved

CRM Grand Total of associated entities

Posted on 2010-08-23
15
524 Views
Last Modified: 2012-10-31
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
0
Comment
Question by:Justin Imes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 7

Expert Comment

by:stacko111
ID: 33501592
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
 
LVL 29

Expert Comment

by:Feridun Kadir
ID: 33501699
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
 
LVL 9

Author Comment

by:Justin Imes
ID: 33501704
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 29

Accepted Solution

by:
Feridun Kadir earned 500 total points
ID: 33501836
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
 
LVL 9

Author Comment

by:Justin Imes
ID: 33501913
Thanks Feridun, does this go in the onload of your asset entity or the assetitems entity?
0
 
LVL 29

Expert Comment

by:Feridun Kadir
ID: 33501945
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
 
LVL 9

Author Comment

by:Justin Imes
ID: 33502156
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
 
LVL 29

Expert Comment

by:Feridun Kadir
ID: 33502249
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
 
LVL 9

Author Comment

by:Justin Imes
ID: 33663051
I'm still unable to get this to total the entities and display properly.
0
 
LVL 29

Expert Comment

by:Feridun Kadir
ID: 33742820
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
 
LVL 9

Author Comment

by:Justin Imes
ID: 33744596
I am still having troubles... I will post the code when i get back to the office, thanks in advance
0
 
LVL 9

Author Comment

by:Justin Imes
ID: 33770659
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
 
LVL 29

Expert Comment

by:Feridun Kadir
ID: 33895548
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
 
LVL 9

Author Comment

by:Justin Imes
ID: 34156647
This was never resolved, please close question and refund points.
0
 
LVL 9

Author Closing Comment

by:Justin Imes
ID: 38554465
was not able to come up with a perfect solution, but thank you for your help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question