locked
Sum values from Subgrid with if statement RRS feed

  • Question

  • Hello,

    I'm having some difficult getting this code to execute properly. I am trying to total the Quantity field from the subgrid based on the product type. So when a sales rep is entering in opportunity products a field below the subgrid would show the total units. I only want the field to sum the quantity of products with the value of software or hardware. I get odd behavior back with the below code. If I have the if statement point to just one product type i.e "Software" the summed value is returned as 0. If I use the or statement "Software" || "Hardware" the summed value totals everything thing in the subgrid not just the hardware or software items. Below is my code. Any help is greatly appreciated.

    function UnitsTimeout(){
     setTimeout(calcUnits, 1500);
    }
    function calcUnits() {
    var grid = document.getElementById('opportunityproductsGrid'); 
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    for(i = 0; i < ids.length; i++) {
      if(grid.control.getCellValue('producttypecode',ids[i]) == "Software" || "Hardware") {
      var cellValue = grid.control.getCellValue('quantity',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    }
    Xrm.Page.data.entity.attributes.get("new_itemssold").setValue(sum);
    Xrm.Page.data.entity.attributes.get("new_itemssold").setSubmitMode("always");
    }

    Friday, August 23, 2013 8:14 PM

Answers

All replies

  • Hi,

    Change the if statement to 

    if(grid.control.getCellValue('producttypecode',ids[i]) == "Software" || grid.control.getCellValue('producttypecode',ids[i]) == "Hardware") {


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Proposed as answer by MJFPalmer Saturday, August 24, 2013 7:38 PM
    Saturday, August 24, 2013 7:38 PM
  • Hello Michael and thank you for your response. Unfortunately I tried that change and the value summed is still 0.00. I'm wondering if there is an issue because the producttypecode is on the product entity and it is a drop down menu. I attempted to replace the "Software" and "Hardware" with the numeric values of the drop down and that didn't work either. I'm not sure what the issue is?

    Thanks again for any help.

    Monday, August 26, 2013 1:48 PM
  • Hi,

    Well the right way to do this would actually be to retrieve the related records using a SOAP or OData call and not read the data from the sub-grid.

    To solve this problem though, please try alert the value of

    grid.control.getCellValue('producttypecode',ids[i])

    to see what it returns.


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    Monday, August 26, 2013 1:56 PM
  • I'm sorry, you'll have to forgive me but I'm not sure I understand what you are asking or how to accomplish it? I'm still new to the programing side of the CRM. Thanks again I appreciate the help.

    Monday, August 26, 2013 2:34 PM
  • Is this what you were referring to? I ran the below code:

    function GPSTimeout() {
    setTimeout(calcCostoTotall, 5000);
    }
    function calcCostoTotall() {
    var grid = document.getElementById('opportunityproductsGrid'); 
    var ids = grid.control.get_allRecordIds();
    for(i = 0; i < ids.length; i++) {
      var cellValue = grid.control.getCellValue('new_ongoing',ids[i]);
    }
    alert(grid.control.getCellValue('producttypecode',ids[i]));
    }

    and got the following error report:

    Microsoft Dynamics CRM Error Report Contents

    <CrmScriptErrorReport>
      <ReportVersion>1.0</ReportVersion>
      <ScriptErrorDetails>
       <Message>Unable to get value of the property 'toUpperCase': object is null or undefined</Message>
       <Line>1</Line>
       <URL>/_static/_grid/GridControl.js?ver=1915704322</URL>
       <PageURL>/userdefined/edit.aspx?etc=3&id=%7b7BFBD730-4CB7-E211-AC09-12A32B1E376A%7d&pagemode=iframe&preloadcache=1377536211139</PageURL>
       <Function></Function>
       <CallStack>
       </CallStack>
      </ScriptErrorDetails>
      <ClientInformation>
       <BrowserUserAgent>Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; BRI/2; BOIE9;ENUS; Microsoft Outlook 14.0.6131)</BrowserUserAgent>
       <BrowserLanguage>en-us</BrowserLanguage>
       <SystemLanguage>en-us</SystemLanguage>
       <UserLanguage>en-us</UserLanguage>
       <ScreenResolution>1600x900</ScreenResolution>
       <ClientName>Outlook Laptop - Online</ClientName>
       <ClientTime>2013-08-26T10:56:57</ClientTime>
      </ClientInformation>
      <ServerInformation>
        <OrgLanguage>1033</OrgLanguage>
        <OrgCulture>1033</OrgCulture>
        <UserLanguage>1033</UserLanguage>
        <UserCulture>1033</UserCulture>
        <OrgID>{AACB118A-823E-4A12-BFCD-190E5E9BE731}</OrgID>
        <UserID>{E200F73B-57CC-E111-B35F-12A32B1E376A}</UserID>
        <CRMVersion>5.0.9690.2839</CRMVersion>
      </ServerInformation>
    </CrmScriptErrorReport>

    Monday, August 26, 2013 4:58 PM
  • Hi,

    Please replace the code in your earlier post with the code below and check what value the alert gives you. I am just trying to confirm what value the grid.control.getCellValue('producttypecode',ids[i]) call returns so that we can confirm that you are comparing it correctly to a text field.

    function UnitsTimeout(){
     setTimeout(calcUnits, 1500);
    }
    function calcUnits() {
    var grid = document.getElementById('opportunityproductsGrid'); 
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    for(i = 0; i < ids.length; i++) {
      alert(grid.control.getCellValue('producttypecode',ids[i]));
      if(grid.control.getCellValue('producttypecode',ids[i]) == "Software" || grid.control.getCellValue('producttypecode',ids[i]) == "Hardware") {
      var cellValue = grid.control.getCellValue('quantity',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    }
    Xrm.Page.data.entity.attributes.get("new_itemssold").setValue(sum);
    Xrm.Page.data.entity.attributes.get("new_itemssold").setSubmitMode("always");
    }

    Also, have you tried to attach a debugger to the code? See http://msdn.microsoft.com/en-us/library/gg589507.aspx and http://msdn.microsoft.com/en-us/library/vstudio/7seh8d72.aspx.

    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    Monday, August 26, 2013 8:14 PM
  • Alright, I ran that code and received the pop up message below.I clicked Ok and it pop up one more time then upon clicking ok again it went away.

    I have not ever looked into the debugger but I will read up on it and see if I can give it a try. I really appreciate all your help and taking the time to do so!


    [edit] I have figured out the error itself lies within the productypecode field. If i switch that to say productid the code works correctly as it should. The productid is the look up value for existing products. The producttypecode is a drop down menu on the product record. I'm not sure if the issue is because the field is pulled from a an option set menu and populated into the subgrid or because the field is a regarding field from the product record. I could always have a dozen or statements listing every product name that needs to be summed but if I could do the product type it would be much easier.
    • Edited by Mich_Ridge Monday, August 26, 2013 10:10 PM
    Monday, August 26, 2013 8:31 PM
  • Hi,

    Is the producttypecode field actually one of the visible fields in the view? If it isn't, just add it to the view and your code above should start working.

    If it already is and the code is not working, then I strongly suggest you switch to using an OData call (see http://msdn.microsoft.com/en-us/library/gg309461.aspx) to do the calculation.


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Marked as answer by Mich_Ridge Tuesday, August 27, 2013 1:55 PM
    Tuesday, August 27, 2013 11:37 AM
  • Yes the productypecode is a visible column on the subgrid. I'll look into the OData. Thank you for all your help.
    Tuesday, August 27, 2013 1:55 PM