Solved

CRM Grand Total of associated entities

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

Expert Comment

by:feridun
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
 
LVL 29

Accepted Solution

by:
feridun 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
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 29

Expert Comment

by:feridun
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
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
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now