Solved

CRM Grand Total of associated entities

Posted on 2010-08-23
15
531 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part I
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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