Function to sum list column; use sum as variable in SPUpdateMultipleListItems

Feb 21, 2013 at 4:20 PM
Edited Feb 21, 2013 at 4:23 PM
I have a list with 4 rows and 4 columns:
  • Company
  • AllocatedDollars
  • TotalDollars
  • Allocated %
    Is there a function or approach I can use to get the total of the AllocatedDollars column, and then use SPUpdateMultipleListItems to set TotalDollars in each row to that total? Allocated% is a calculated column =[AllocatedDollars]/[TotalDollars] - in order to keep Allocated % current I need to update TotalDollars any time a user updates AllocatedDollars. We currently do this manually since there are only 4 rows of data, but I've been asked to automate this procedure.
Feb 21, 2013 at 8:31 PM
You can run a function that enumerates the list items, incrementing the variable curAllocatedDollarsTotal as it goes, then run the Update operation to update all records to the final value. However, the challenge will be where to trigger it from. If you try to trigger it from within one of the records you're trying to edit, it could get messy. For instance, if the function runs as part of a PreSave action, it will not get the changed value of the current record. You would have to trigger the function somewhere else after the record is saved.
    var curAllocatedDollarsTotal = 0;

    //Add up the values
    $().SPServices({
        operation: "GetListItems",
        async: false,
        listName: "Your List Name",
        CAMLViewFields: "<ViewFields Properties='True' />",
        CAMLQueryOptions: "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>",
        completefunc: function (xData, Status) {
            $(xData.responseXML).SPFilterNode("z:row").each(function() {
                curAllocatedDollarsTotal = curAllocatedDollarsTotal + $(this).attr("ows_AllocatedDollars");
            });
        }
    });

    //Update the records
    $().SPServices.SPUpdateMultipleListItems({  
        listName: "Your List Name",
        valuepairs: [["TotalDollars", curAllocatedDollarsTotal]]
    });
Feb 21, 2013 at 8:40 PM
Oh, by the way, if "AllocatedDollars" is stored as a currency type, you'll have to do a little work on each data item to strip it out...
    var curRcdValue = $(this).attr("ows_AllocatedDollars");
    curRcdValue = curRcdValue.split(";#");
    curRcdValue = Number(curRcdValue[1]);
    curAllocatedDollarsTotal = curAllocatedDollarsTotal + curRcdValue;
Feb 21, 2013 at 10:16 PM
This is great - exactly what I was looking for.

Could you tell me if/how I could trigger it from an "Update Percentages" button that the user clicks after changing AllocatedDollars?

Many thanks - this looks like it will get me out of the soup!
Coordinator
Feb 22, 2013 at 4:54 PM
I'll step in for the easy part:
<input type="button" value="Update Percentages" onclick="javascript: yourFunction();" />
M.
Feb 22, 2013 at 5:12 PM

Thanks! I’m new to jQuery and wasn’t sure exactly how everything hangs together.

Dave

From: sympmarc [email removed]
Sent: Friday, February 22, 2013 11:55 AM
To: dg_moore@comcast.net
Subject: Re: Function to sum list column; use sum as variable in SPUpdateMultipleListItems [SPServices:433981]

From: sympmarc

I'll step in for the easy part:

<input type="button" value="Update Percentages" onclick="javascript: yourFunction();" />

M.

Feb 26, 2013 at 6:57 PM
Thanks for all your help and suggestions - I'm pretty close now.

My code and library references (jquery-1.4.1.min.js, jquery.SPservices-0.7.2.js) are all in a text file addressed from a CEWP on my page.

I now have this issue with the function: debugging with Firebug, when I step from the line

completefunc: function (xData, Status) {

to

$(xData.responseXML).SPFilterNode("z:row").each(function() {

I get console error TypeError: $().SPServices is not a function

I don't know what this means - presumably the SPServices library is being found, since otherwise I don't think the function would run at all. Do you have any suggestions?

Thanks
Coordinator
Feb 26, 2013 at 8:42 PM
That error usually indicates that SPServices isn't referenced properly. However, you're running a very old version of jQuery. I'd suggest upgrading to 1.8.x.

M.
Feb 28, 2013 at 2:53 PM
marc, gk, thanks again for your help on this. I'm still having an issue that I can't resolve, though: I can't assign a value to the variable curAllocatedDollarsTotal

When the code launches the variable is set to 0, and when the first row of data is evaluated a value of 900000.000000000 is returned by an alert on $(this).attr("ows_Allocated_x0020_Dollars");,which is the correct value for that row.

Before the line curAllocatedDollarsTotal = curAllocatedDollarsTotal + $(this).attr("ows_Allocated_x0020_Dollars"); executes, the value of curAllocatedDollarsTotal is 0, and according to the alert the value of $(this).attr... is 900000.000000000.

But when the line executes I get a console error ReferenceError: curAllocatedDollarsTotal is undefined.

So it looks like 0 + 900000.000000000 isn't being assigned to curAllocatedDollarsTotal. Do you have any idea what could be causing this?

Many thanks
Dave
Coordinator
Feb 28, 2013 at 3:10 PM
Dave:

I can't tell from what I see above, but check your spelling of the variable. Capitalization must match.

Also, you will need to get good at stepping through your code in the browser. Firebug is especially good for this part. (Better than the IE Developer Tools, IMO.)

M.
Feb 28, 2013 at 9:43 PM
I'm glad to report I finally have this working. I found through trial and error that I needed to use parseFloat() to make the math work; once I had that figured out the rest was easy thanks to your suggestions.

Thanks
Dave
Coordinator
Feb 28, 2013 at 9:49 PM
Dave:

I almost recommended that. Sorry if my telling you would have saved you time, since I didn't.

M.
Feb 28, 2013 at 10:34 PM
No worries - next time I'll know how to do it.

Thanks again

Dave