JavaScript to Total Columns in SubGrid on Insert of new record

So I came across a project where I needed to create payment records in CRM and then export into a spreadsheet for importing into GP to run checks.  Yes I know CRM is not really inteded to be an accounting system, and that it could be integrated, blah blah blah, but without going into the requirements and time and budget constraints, this was the best solution for this particular client.

The thing that was slightly more difficult was that these payments needed to have deductions of various types taken out of them. My desired solution here was to have a Payment record, with Gross and Net Payment fields, and then have a subgrid for all the various types of withholdings.  When a new record was inserted into the subgrid, the Withholding total field would update with the total of all the withholdings and the Net payment field would update to Gross Payment minus Withholding Total.

One thing that has kind of frustrated me with CRM over the years (even now that we can insert subgrids in a supported manner) is that there isn’t really an easy way to total up columns in the subgrid and that we don’t have an onInsert event in the subgrid to write script against.   That is really what I needed here: the ability to trigger an update via javascript on inserting a record.  (BTW if you are reading this and are on the MS CRM development team this would be a cool feature to add!)

So anyway, here is my entity after configuring everything in CRM:

Ok so my next step was to create a function to total up the Amount fields from all the withholdings.  No sweat using web services, but I was still out of luck with getting it to run on inserting a new record into the grid.  So (offical disclaimer) I was forced to step outside the bounds of what is offically supported with scripting in CRM.   In the onLoad event of the Payment form I put the following line of script:

window.onfocus = function() { updateWithholding(Xrm.Page.data.entity.getId());

The udpateWithholding function uses the RetrieveMultiple web service method to return and then sum up the Amount field of all of the Withholding entities related to this specific Payment entity and then update the Withholding $ and Net Payment Amount fields.  The key bit of script is inserting it into the window.onfocus event.  This will run the function whenever focus returns to the window.  Which is exactly what happens after you click the Add New button in the subgrid to add a new Withholding and then Save and Close.  It works almost completely seamlessly.  The only bad news is that it will re-run the function under other circumstances than strictly this one, which may be undesirable if you use a function that takes a long time to process.  Well, I thought this was a neat trick, hope it helps someone else as well.

Here is a screenshot of the entity after adding a couple of withholdings, no Save needed.

image

For completeness here is the updateWithholding code as well if anyone is interested:

function updateWithholding(paymentid)

{

    if (paymentid != null)

    {

        var totW = getTotalWithholdings(paymentid);

 

        var wPercent = Xrm.Page.getAttribute("new_withholding");

        var wAmount = Xrm.Page.getAttribute("new_withholdingamount");

        var paymentAmount = Xrm.Page.getAttribute("new_percappayment");

 

        wAmount.setValue(totW);

 

        var wp = 0;

        if (wAmount != 0 && paymentAmount.getValue() != 0)

        {

            wp = wAmount.getValue() / paymentAmount.getValue();

        }

        wPercent.setValue(wp);

 

        updateNet();

    }

}

 

function updateNet()

{

    var net = Xrm.Page.getAttribute("new_netpayment");

    var gross = Xrm.Page.getAttribute("new_percappayment");

    var wHold = Xrm.Page.getAttribute("new_withholdingamount");

 

    net.setValue(Math.max(gross.getValue() – wHold.getValue(), 0));

}

 

function getTotalWithholdings(paymentid)

{

    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/’&quot; +

        " xmlns:xsi=’http://www.w3.org/2001/XMLSchema-instance’&quot; +

        " xmlns:xsd=’http://www.w3.org/2001/XMLSchema’>&quot; +

        authenticationHeader +

        "<soap:Body>" +

        "<RetrieveMultiple xmlns=’http://schemas.microsoft.com/crm/2007/WebServices’>&quot; +

        "<query xmlns:q1=’http://schemas.microsoft.com/crm/2006/Query’&quot; +

        " xsi:type=’q1:QueryExpression’>" +

 

        "<q1:EntityName>new_withholding</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_paymentregarding</q1:AttributeName>" +

 

        "<q1:Operator>Like</q1:Operator>" +

        "<q1:Values>" +

            "<q1:Value xsi:type=’xsd:string’>" + paymentid + "</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&quot;);

    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;

 

    //alert(resultXml.xml);

 

    var total = 0;

 

    var beNodes = resultXml.getElementsByTagName("BusinessEntity");

 

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

    {

        if (beNodes[i].getElementsByTagName("q1:new_amount").length > 0)

        {

            var w = parseFloat(beNodes[i].getElementsByTagName("q1:new_amount")[0].text);

 

            total = total + w;

        }

    }

 

    return total;

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: