locked
Calculting Total Sum of Related Entity Fields. RRS feed

  • Question

  • I have 2 Entites, VehicleJobs and VehicleSupplies. This is a 1:N relationship.

    A  VehicleJob will have many VehicleSuplies, each VehicleSuplpy has a cost field.

    I am using a subgrid to show the related VehicleSupplies on the VehicleJobs Form.

    I want to calculate the total sum of related VehicleSupplies and put it inside a field in the VehicleJobs Form. It must automatically update if related VehicleSupplies records are added or removed.

    Here is what i have tried so far from another piece of code i found.


    function setupGridRefresh() {
    var targetgrid = document.getElementById("Parts");
    
    // If already loaded
    if (targetgrid.readyState == 'complete') {
        targetgrid.attachEvent("onrefresh", subGridOnload);
    }
    else {
        targetgrid.onreadystatechange = function applyRefreshEvent() {
            var targetgrid = document.getElementById("Parts");
            if (targetgrid.readyState == 'complete') {
                targetgrid.attachEvent("onrefresh", subGridOnload);
            }
        }
    }
    subGridOnload();
    }
    
    function subGridOnload() {
    //debugger;
    var grid = Xrm.Page.ui.controls.get('Parts')._control;
    var sum = 0.00;
    
    if (grid.get_innerControl() == null) {
        setTimeout(subGridOnload, 1000);
        return;
    }
    else if (grid.get_innerControl()._element.innerText.search("Loading") != -1) {
        setTimeout(subGridOnload, 1000);
        return;
    }
    
    var ids = grid.get_innerControl().get_allRecordIds();
    var cellValue;
    for (i = 0; i < ids.length; i++) {
        if (grid.get_innerControl().getCellValue('c2_price', ids[i]) != "") {
            cellValue = grid.get_innerControl().getCellValue('c2_price', ids[i]);
            cellValue = cellValue.substring(2);
            cellValue = parseFloat(cellValue);
            sum = sum + cellValue;
        }
    
    }
    
    var currentSum = Xrm.Page.getAttribute('c2_totalcost').getValue();
    if (sum > 0 || (currentSum != sum && currentSum != null)) {
        Xrm.Page.getAttribute('c2_totalcost').setValue(sum);
    }
    }

    "Parts" = Unique Name of Subgrid which holds the related VehicleSupply records.

    "c2_price" = The field in VehicleSupplies which shows the price of the supply.

    "c2_totalcost" = The field in VehicleJobs where i want the totalsum to go.

    However, i get an error. "Unable to get property 'ReadyState' of undefined or  null reference.






    • Edited by KKHAN1991 Thursday, July 25, 2013 10:15 AM
    Thursday, July 25, 2013 10:13 AM

All replies

  • Hi

    Can I suggest another way? When you add a record in the subgrid i.e. VehicleSupplies  just REST query on save of that record for all the vehicle supplies for that VehicleJobs and add the  costs  and then you can use window.parent.xrm.page.data.entity.attributes.get("field").setValue(totalCost); to set the value in VehicleJobs.


    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful" Thank you, DT


    Thursday, July 25, 2013 10:28 AM
  • Out of curiosity now. Can you provide some material on how to do it this way, i've only ever used Javascript.
    Thursday, July 25, 2013 10:29 AM
  • Hi

    Here is a link that can help you to get started,

    http://msdn.microsoft.com/en-us/library/gg334427.aspx


    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful" Thank you, DT

    Thursday, July 25, 2013 10:38 AM
  • I think i will stick with JS for now.#

    The above code works for calculting the sum, its just i need it to refresh and change based on whether a child reocrd has been added or removed.

    function setupGridRefresh() {
    var targetgrid = document.getElementById('Parts');

    // If already loaded
    if (targetgrid.readyState == 'complete') {
        targetgrid.attachEvent("onrefresh", subGridOnload);
    }
    else {
        targetgrid.onreadystatechange = function applyRefreshEvent() {
            var targetgrid = document.getElementById("Parts");
            if (targetgrid.readyState == 'complete') {
                targetgrid.attachEvent("onrefresh", subGridOnload);
            }
        }
    }
    subGridOnload();
    }
    ^ this bit is whats causing a problem.

    Thursday, July 25, 2013 10:54 AM
  • Hi Khan,

           Your code is perfect. Just check 'names' of the subgrid 'Parts'.

    Your code works for me, also i tried to replicate your error. Done.

    Thanks

    • Proposed as answer by prakashmsdyn Thursday, July 25, 2013 2:41 PM
    Thursday, July 25, 2013 2:21 PM
  • The subgrid is deffi named "Parts" i have used it in the bottom half of the code to get the sum of the price.
    Monday, July 29, 2013 10:01 AM
  • In your code, you defined right. Is it that matching as exactly your name of the field. For my knowledge, that is the only reason which causing the error.
    Monday, July 29, 2013 3:35 PM