locked
Calculate Total Cost from Sub Grid with column Cost RRS feed

  • Question

  • Hi guys,

    I'm really new in Dynamics CRM, and I would like to know how to calculate in a field Total Cost (on the parent form) the sum from all columns (cost of item)  inside subgrid Products.

    It's very importante to know what is the cost and margin as well.

    Thks for your help in advance.

    • Changed type Dynrules Monday, September 19, 2011 4:29 PM
    • Changed type Dynrules Monday, September 19, 2011 4:30 PM
    Monday, September 19, 2011 4:21 PM

Answers

  • Hi Amreek,

    Thanks for all, you're right, yesterday I got tired to run scripts and I started to play with "alert(cellValue) and "alert(sum)" so the sum was returning Nan or for example 0$ 1 000 0$ 2000 (problem with sum). I read in many web pages where they say that Dynamics CRM has not problem in operations with currency and number...false for this case.

    I converted currency value to number, before to sum, finally the code works! :D

    function timeout(){
     setTimeout(calcCostoTotal, 3000);
    }

    function calcCostoTotal() {

    var grid = document.getElementById('opportunityproductsGrid');
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    var cellValue;

    for(i = 0; i < ids.length; i++) {

      var cellValue = grid.control.getCellValue('new_costo',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    Xrm.Page.data.entity.attributes.get("new_costototal").setValue(sum);

    }

    Regards


    • Marked as answer by Dynrules Tuesday, September 27, 2011 3:24 PM
    • Edited by Dynrules Tuesday, September 27, 2011 3:29 PM
    Tuesday, September 27, 2011 3:24 PM
  • Sorry,

    I end up running the code

    here it is , I played around with it for 20 minutes and it works.

    I insert a grid name opps on contact entity. just remember you may get null or something like that if there is no opportunity related to the contact.

    function subGridOnload() 

    //debugger;

    var grid = document.getElementById('opps'); 

     if (grid.readyState!="complete") 

     { 

     // delay one second and try again. 

     setTimeout(subGridOnload, 1000); 

     return; 

     } 

    var ids = grid.control.get_allRecordIds(); 

    var cellValue;

     for(i = 0; i < ids.length; i++) 

     { 

     cellValue = grid.control.getCellValue('estimatedvalue', ids[i]); 

     

     // logic 

     } 

    alert(cellValue);

    // logic 

    }

    I hope this helps.

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Thursday, September 22, 2011 1:37 AM
    • Marked as answer by Donna EdwardsMVP Sunday, February 9, 2014 6:37 PM
    Thursday, September 22, 2011 1:37 AM

All replies

  • You could create a field on the parent Entity (new_totalcost in this case) and then register a plugin for post Retrieve of the parent entity that will sum the cost of all the children and then return the value to new_totalcost on the parent entity.        

    Your plugin code would look something like this:

    var outEntity = (Entity)pluginContext.OutputParameters["BusinessEntity"];
    
    var totalCost = CalculateTotalCost();
    
    outEntity["new_totalcost"] = totalCost;
    
    
    

    Then Create a CalculateTotalCost method that queries all the child records and sums up the cost.

     


    Blake Scarlavai - http://mscrmdev.blogspot.com/ - Sonoma Partners - http://www.sonomapartners.com/
    Monday, September 19, 2011 9:01 PM
  • Hi Blake,

    In fact, I've just started to manage codes. Im configuring Dynamics CRM and I realized that I need some good codes for many things. And I really want to learn how it does.

    Look, I was using this function (jscript)..I put it on event OnLoad of Form, is correct?

    function CostoTotal(){

      var gridControl = document.getElementById('opportunityproductsGrid').control;
      var ids = gridControl.get_allRecordIds();
      var sum = 0,00;
      for(i = 0; i < ids.length; i++) {
        var cellValue = gridControl.getCellValue('new_costo', ids[i]);
        cellValue = cellValue.replace( /,/,"." );
        var sum = sum + parseFloat(cellValue);
       }
     Xrm.Page.getAttribute('new_costototal').setValue(sum);
    }

    My subgrid is 'opportunityproductsGrid', I want to set the sum of all 'new_costo'´in the field 'new_costototal'. Although this code doesnt work :(..I don't know why..What's wrong?

    Help pls, thank you in advance.

    Luna

     

    Monday, September 19, 2011 9:27 PM
  • Are you getting an error?  What reference are you looking at for methods like getCellValue and get_allRecordIds?  
    Blake Scarlavai - http://mscrmdev.blogspot.com/ - Sonoma Partners - http://www.sonomapartners.com/
    Monday, September 19, 2011 9:39 PM
  • No, Im not getting an error. But the field "total cost " is still empty.

    I want to obtain the total cost in a field, from all products of a subgrid. Is not necesary to use getCellValue?

    Thanks,

    Luna

    Monday, September 19, 2011 9:50 PM
  • I haven't interacted with a subgrid in 2011 JavaScript yet so I am unaware of getCellValue.  I would stick a debugger; statement in your code and step through the javascript with Visual Studio to debug it.
    Blake Scarlavai - http://mscrmdev.blogspot.com/ - Sonoma Partners - http://www.sonomapartners.com/
    Monday, September 19, 2011 9:58 PM
  • There´s must be another way to use jscript and set a fiedl with the total cost of subgrid columns :(..Im getting grazy, I dont know why..don't set any value.

    Regards,

    Luna

    Monday, September 19, 2011 10:24 PM
  • One of the reason can be it executes your code, before the grid is loaded.

    Try this, its bit dodgy but it should work

    function subGridOnload() 

    var grid = document.getElementById('grid_identifications'); 

     if (grid.readyState!="complete") 

     { 

     // delay one second and try again. 

     setTimeout(subGridOnload, 1000); 

     return; 

     } 

    // logic 

    }

    Second try to use debugger; if its doing anything.

    add

    debugger;  in the first line of the function you are calling.

    I hope this helps.

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Tuesday, September 20, 2011 3:00 AM
    Tuesday, September 20, 2011 3:00 AM
  • Hi Amreek,

    Thks so much for your help, but the result was the same..nothing happens..I thought it was very easy to sum up a list of cost from a subgrid

    Tuesday, September 20, 2011 6:00 AM
  • Hi,

    You could easily acheive this using this product: http://pinpoint.microsoft.com/en-gb/applications/auto-summary-for-dynamics-crm-2011-12884924896

    It's currently in beta but we expect it to be moved to RTM in the next couple of weeks.

    Rob


    Microsoft Certified Technology Specialist (CRM) GAP Consulting Ltd Microsoft Community Contributor Award 2011
    Tuesday, September 20, 2011 8:46 AM
    Answerer
  • Sorry,

    I end up running the code

    here it is , I played around with it for 20 minutes and it works.

    I insert a grid name opps on contact entity. just remember you may get null or something like that if there is no opportunity related to the contact.

    function subGridOnload() 

    //debugger;

    var grid = document.getElementById('opps'); 

     if (grid.readyState!="complete") 

     { 

     // delay one second and try again. 

     setTimeout(subGridOnload, 1000); 

     return; 

     } 

    var ids = grid.control.get_allRecordIds(); 

    var cellValue;

     for(i = 0; i < ids.length; i++) 

     { 

     cellValue = grid.control.getCellValue('estimatedvalue', ids[i]); 

     

     // logic 

     } 

    alert(cellValue);

    // logic 

    }

    I hope this helps.

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Thursday, September 22, 2011 1:37 AM
    • Marked as answer by Donna EdwardsMVP Sunday, February 9, 2014 6:37 PM
    Thursday, September 22, 2011 1:37 AM
  • Hi buddy,

    I had a look at it. I CAN'T SPEND MUCH TIME ON IT AS I am working rightnow.

    The problem is with parseFloa as it is returning NAN error -- Not a number.

    Have a look at it.

    Can you mark any of the post as helpful?



    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    Tuesday, September 27, 2011 1:50 AM
  • Hi Amreek,

    Thanks for all, you're right, yesterday I got tired to run scripts and I started to play with "alert(cellValue) and "alert(sum)" so the sum was returning Nan or for example 0$ 1 000 0$ 2000 (problem with sum). I read in many web pages where they say that Dynamics CRM has not problem in operations with currency and number...false for this case.

    I converted currency value to number, before to sum, finally the code works! :D

    function timeout(){
     setTimeout(calcCostoTotal, 3000);
    }

    function calcCostoTotal() {

    var grid = document.getElementById('opportunityproductsGrid');
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    var cellValue;

    for(i = 0; i < ids.length; i++) {

      var cellValue = grid.control.getCellValue('new_costo',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    Xrm.Page.data.entity.attributes.get("new_costototal").setValue(sum);

    }

    Regards


    • Marked as answer by Dynrules Tuesday, September 27, 2011 3:24 PM
    • Edited by Dynrules Tuesday, September 27, 2011 3:29 PM
    Tuesday, September 27, 2011 3:24 PM
  • Thanks for posting the code.
    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    Tuesday, September 27, 2011 11:01 PM
  • Thank you so much this has taken me a few weeks to sort out. I was using the parseFloat method mentioned in so many other posts and i kept getting the NAN error.

    Thank you, I can now sign off my Project and hand it over to our Client

     

    Tuesday, January 17, 2012 12:00 AM
  • Hi HelOz

    A long time ago I didnt sing in, its good to know that the code was very useful to you :D!.

    Greeting,

    Dynrules

    Friday, April 6, 2012 12:51 AM
  • hi all,

    As i am having the same problem, i tried to use above code "var number = Number(cellValue.replace(/[^0-9\.]+/g,""));" but, var number returns NaN. so it is not working for me. then i tried the below code i written  using debbuger it is working for me.

    hope it is work for u guys.

                var ord_totalitemamount = gridControl.getCellValue('extendedamount', ids[i]);           

                var numberta = ord_totalitemamount.replace(/[^0-9\.]+/g,"");
                var numberrm = numberta.substring(1);
                var numta = Number(numberrm);
                ord_totalamount = ord_totalamount + numta;

                         

    kathiravan sathasivam

    Tuesday, April 17, 2012 7:07 AM
  • Hi

    Thanks for grate posts please someone give; I need to pass two values from child to parent. Here it is passing (sets) only one value. I have tried by creating another function as calcCostoTotal but no luck it’s still setting same value. Please update can we set another value to parent using grid field

    Kind regards

    Radika

    Thursday, August 9, 2012 5:46 AM
  • Hi,

    Thank you for your advices.

    This is exactly what I need to do, but I can't make it work.

    I have the following code to test which values are returned.

    cellValue is null

    and number is 0

    function sommeprix(){

    var gridControl = document.getElementById('Memberships').control;
    var ids = gridControl.get_allRecordIds();


    var cellValue = gridControl.getCellValue('new_prix' , ids[0]);

    var number = Number(cellValue);
    alert(cellValue);
    alert(number); 
    }

    I am trying onSave just for testing purposes.

    I have some data in my field 'new_prix' but it returns me a null ?

    I don't know what I did wrong here.


    Consultant at BSE Consulting Korea www.bse-c.co.kr

    Wednesday, October 24, 2012 4:00 AM
  • Hi,

    I have same type requirement and I used  above jscript and it  works fine but it calculates only the first page of the grid .what about the second page of the grid? I need to calculate all rows of the sub grid both page one and two.

    How to do this?


    Krishn Prasad Shetty

    Monday, October 29, 2012 6:04 PM
  • I don't know apparently document.getElementById is not supported.

    I found something to do it via a ribbon button. But it requires a click from the user so it's not very convininent

    http://lucianodangelo.blogspot.kr/2012/03/how-to-pass-parameters-from-subgrids-to.html

    I wonder if this is do-able via REST or SOAP ?

    Edit notes >

    The SelectedControlAllItemIds returns me an undefined so i'm blocked here.

    And I found my problem with the strategy of using getElementById

    You actually need to have the field appearing on your SubGrid, so you need to make a custom view to be able to do the sum.


    Consultant at BSE Consulting Korea www.bse-c.co.kr


    • Edited by Emile.Korea Wednesday, November 14, 2012 11:11 AM
    Wednesday, October 31, 2012 2:58 AM
  • Hi Emile,Shetty

    Did you ever solve the problem with the grid summing more than 1 page of data - have the same problem.

    Thanks

    Paul 

    Tuesday, February 4, 2014 4:04 PM
  • Hi Amreek singh ,

    I have same requirement and trying from one week but not getting the required solution so please help me.

    Tuesday, September 22, 2015 11:30 AM