0

I want to sum the amount of multiple rows/entries having the same request ID(common column) for the current User.

To achieve this, I am trying to retrieve a list, On successfully retrieved I pick the current requestID, I need to Sum all his expenditure and append that Total Amount in the table.

Below is my Code:

 function GetTransactions(employeeMail) {
    var caml = new SP.CamlQuery();
    caml.set_viewXml("<View><Query><Where><Eq><FieldRef Name='RequesterEmail' /><Value Type='Text'>" + employeeMail + "</Value></Eq></Where></Query></View>");
    returnedTrans = list.getItems(caml);
    context.load(returnedTrans);

    context.executeQueryAsync(onSuccess, onFails);
}
function onSuccess() {
    //Formulate HTML from the list items 
    var lenum = returnedTrans.getEnumerator();
    var count = returnedTrans.get_count();
    var previousRequestId = '';
    //Loop through all the items  
    while (lenum.moveNext()) {
        var listItem = lenum.get_current();
        myRequestID = listItem.get_item("RequestID");
        submissionDate = listItem.get_item("SubmissionDate");
        amount = listItem.get_item("Amount");
        status = listItem.get_item("ClaimStatus");
        manager = listItem.get_item("ManagerName");

        requesterName = listItem.get_item("EmployeeName");
    //will not print duplicate rows
        if (previousRequestId = '' || previousRequestId != myRequestID) {
            try {
                var tr = $('<tr>');

                var td = $('<td>');

                td.append("<a href='#' class='anchorRequester' id=" + myRequestID + ">" + myRequestID + "</a>");
                tr.append(td);

                var td = $('<td>');
                td.append(status);
                tr.append(td);

                var td = $('<td>');
                td.append(submissionDate.toDateString());
                tr.append(td);

                var td = $('<td>');
                var totalAmount = 0;
                totalMoney(myRequestID).then(
                    function (amountTrans) {
                        var genum = amountTrans.getEnumerator();
                        //Loop through all the items  
                        while (genum.moveNext()) {
                            var listItem = genum.get_current();
                            var thisRequestID = listItem.get_item("RequestID");
                            amount = listItem.get_item("Amount");

                            totalAmount += amount;
                        }
                        deferred.resolve(listItem)
                    }, function (sender, args) {
                        console.log('An error occured while retrieving list items:' + args.get_message());
                    }
                );
                td.append(totalAmount);
                tr.append(td);

                $("#tableRequest").append(tr);

            }
            catch (e) { alert(e.message) };
        }
        previousRequestId = myRequestID;

    }
    $('#tableRequest').DataTable();
}
function totalMoney(myRequestID) {
    var deferred = $.Deferred();
    var caml = new SP.CamlQuery();
    caml.set_viewXml("<View><Query><Where><Eq><FieldRef Name='RequestID' /><Value Type='Text'>" + myRequestID + "</Value></Eq></Where></Query></View>");
    amountTrans = list.getItems(caml);
    context.load(amountTrans);
    //Here is the problem
    context.executeQueryAsync(Function.createDelegate(this,
        function () { deferred.resolve(amountTrans); }),
        function (sender, args) { deferred.reject(sender, args); });
    //Return your Promise Object  
    return deferred.promise();
}
  • Whats not working? Does the context returns failure, do you get any error? Add more description. I would try to replace created delegates with function() { code } – Luke359 Jul 9 '18 at 12:27
  • It is printing TotalAmount = 0, which I have defined as ` var totalAmount = 0` before executing the promise and while I was debugging I got to know the deferred promise function runs after executing the asynchronous function. – Tezz Chaudhary Jul 9 '18 at 12:43
  • Add your code for adding each row inside totalMoney.then callback. Because it only runs after it gets the total money. – Luke359 Jul 9 '18 at 12:54
  • The easiest way to do that would be to bind necessary arguments submissionDate or status to totalMoney method and return them all in array from promise. I know it's not the cleanest way but it should do the trick – Luke359 Jul 9 '18 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.