Pages

Monday 10 February 2014

Calculate the sum of a particular field of all records in an Entity

In this Scenario, I have Three Entities viz.,

1)   "Finance" ,
2)   "Accounts_Payable" and 
3)   "Accounts_Receivable".

 In "Accounts_Payable" Entity I have a field named as "hrm_BillAmount",  similarly in "Accounts_Receivable" Entity I have a field named as "hrm_AmountSpend". In "Finance" entity I have fields "hrm_totalamountpayable" and "hrm_totalamountrecievable".

Now in "hrm_totalamountpayable" feild of Finance entity, I want sum of a particular field(hrm_BillAmount)  of all the records from  Accounts_Payable entity.
Similarly in "hrm_totalamountrecievable" feild of Finance entity, I want sum of a particular field(hrm_AmountSpend) of all the records from  Accounts_Receivable entity.

 The below Snippet code can be used for doing so.,

----------------->
function pay()
{
debugger;
for(var i=1;i<=2;i++)                         //I want to fill two feilds in finace entity so using "for-loop" for doing so
{
if(i==1)
{
var r=recordRetrive();                    //Calling for using Odata Query to get the record details
var d=common(r);                         //Calculating the sum of the fields
Xrm.Page.getAttribute("hrm_totalamountpayable").setValue(d);                       
}
else
{
var r=recordRetrivee();
var d=commonn(r);
Xrm.Page.getAttribute("hrm_totalamountrecievable").setValue(d);
}
}
}
function recordRetrive()
{
var serverUrl = Xrm.Page.context.getServerUrl();
var oDataUri = serverUrl + "/XRMServices/2011/OrganizationData.svc/hrm_accountspayableSet?$select=hrm_BillAmount";        //You can get this query by using Odata Query Designer
var jSonArray = new Array();
   jQuery.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: oDataUri,
        async: false,
        beforeSend: function (XMLHttpRequest) {
            //Specifying this header ensures that the results will be returned as JSON.           
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
            if (data && data.d != null) {
                jSonArray.push(data.d);
            }
        },
          error: function (XmlHttpRequest, textStatus, errorThrown) {
            alert("Error :  has occured during retrieval of the contacts");
        }
    });

    return jSonArray;
}
function recordRetrivee()
{
var serverUrl = Xrm.Page.context.getServerUrl();
var oDataUri = serverUrl + "/XRMServices/2011/OrganizationData.svc/hrm_accountsrecievableSet?$select=hrm_AmountSpend";
var jSonArray = new Array();
   jQuery.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: oDataUri,
        async: false,
        beforeSend: function (XMLHttpRequest) {
            //Specifying this header ensures that the results will be returned as JSON.           
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
            if (data && data.d != null) {
                jSonArray.push(data.d);
            }
        },
          error: function (XmlHttpRequest, textStatus, errorThrown) {
            alert("Error :  has occured during retrieval of the contacts");
        }
    });

    return jSonArray;
}
function common(r)
{
    if(r!=null && r[0].results.length>0)
    {
        var i = new Array();
        var d=0;
        for (var count = 0; count < r[0].results.length; count++) {
                i[count]=r[0].results[count].hrm_BillAmount.Value;
                d=d+parseFloat(i[count]);                  //Converting to float since my field is of Currency type
                }
                return d;
    }
}
function commonn(r)
{
    if(r!=null && r[0].results.length>0)
    {
        var i = new Array();
        var d=0;
        for (var count = 0; count < r[0].results.length; count++) {
                i[count]=r[0].results[count].hrm_AmountSpend.Value;
                d=d+parseFloat(i[count]);            
//Converting to float since my field is of Currency type
                 }
                return d;
    }
}

-------------->

No comments:

Post a Comment