Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

CRM Grand Total of associated entities

Posted on 2010-08-23
15
Medium Priority
?
561 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
  • 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 30

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 30

Accepted Solution

by:
Feridun Kadir earned 2000 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 30

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 30

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 30

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 30

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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 III
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

916 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