SPUpdateMultipleListItems dynamic query and value?

Nov 9, 2012 at 6:40 PM

I am attempting to use SPUpdateMultipleListItems to update the "Date Completed" field of all sub tasks that are connected to a parent task through a lookup column.  My code is designed so it generates a query dynamically, as well as setting the value to match one entered on the parent Task.

The code currently checks to see if the Parent Task Date Completed Field holds a value.  If it does, it then grabs the ID of the Parent Task (from the URL), and then is suppose to build a CAML Query to identify what subtasks to update based on the Parent Task ID (matching the SubTask's Lookup).  It then uses the value of the Parent Task's "Date Completed" field to put into the "Date Completed" field of each of it's Sub Tasks.

I don't know if this is actually possible to do with SPServices, but I do not run into any errors when I test my code, and the debug and Complete Function of SPUpdateMultipleListItems don't show indicators either.  Any thoughts on why my code isn't working?

Here is the code described above without the if else statement I am using.

var dateComp = $('h3:contains("Date Completed")').closest('td').next('td').text();

var queryStringVals = $().SPServices.SPGetQueryString();
var taskID = queryStringVals["ID"];

var query = "\"<Query><Where><Eq><FieldRef Name='bscParentTask' LookupId='TRUE'/><Value Type ='Lookup'>"+ taskID +"</Value></Eq></Where></Query>\"";
var value = "[[\"Date Completed\", \"" + dateComp + "\"]]"

listName: "{D8D7FC23-4B16-4D80-AF04-7894392A99EF}",
CAMLQuery: query,
valuepairs: value,

In the query I am using the static (internal) field name, but in my value pairs I have tried both the static field name, and display. Neither seems to work for me.  Any thoughts?

Nov 10, 2012 at 4:56 PM

I usually alert the query and value parameters to make sure they construct properly.

In my constructions, I don't have the backward slashes.  Also, the text strings are included in one double quote.  Try:

var query = "<Query><Where><Eq><FieldRef Name='bscParentTask' LookupId='TRUE'/><Value Type ='Lookup'>"+ taskID +"</Value></Eq></Where></Query>";
var value = "[['Date Completed', " + dateComp + "]]"

Nov 10, 2012 at 6:07 PM

I have alerted the values (just did not post that part of the code) and they appeared valid to me.  I also alerted them a second time using the Complete Function to make sure the values were actually being passed properly.  I will test out your suggestion and see if it helps.  Thanks!

Nov 11, 2012 at 11:20 AM

You should also look at the traffic with Fiddler or Firebug to see what's going back and forth. you may see an error there. Also try debugging with the unminified version to see if you can spot the issue.

The may be too obvious, but your comma at the end of this line valuepairs: value, will cause an error.


Nov 13, 2012 at 12:01 PM

You're retrieving the date value from a text field on the form in the format "m/d/yyyy."  That date value, as presented, is not in the format required for writing back into a SharePoint list.  You have to write dates to SharePoint in the ISO 8601 format  such as "yyyy-mm-ddTh:mm:ss-00:00."  There is a function in the thread at http://spservices.codeplex.com/discussions/349356 that will convert a JavaScript date object to the format you need.  Of course, it requires a JavaScript date object to start.  To convert your existing variable to a JavaScript Date object, you can use...

var dateComp = new Date($('h3:contains("Date Completed")').closest('td').next('td').text());
...Then pass 'dateComp' to the ISO date conversion function. The return from that function should be what you need to write to your list.

Nov 19, 2012 at 8:20 PM
Edited Nov 19, 2012 at 11:30 PM

Thanks for the tips Marc and Geoff,

So I added the conversion function you mentioned Geoff, as well as removing the extra comma Marc suggested and still nothing. 

When I alert the query variable I get

"<Query><Where><Eq><FieldRef Name='bscParentTask' LookupId='TRUE'/><Value Type='Lookup'>1</Value></Eq></Where></Query>"

and when I alert the value variable I get

[["Date Completed", "2012-11-7T0:0:0-08:00"]]

My Date Completed field does not use time, so the time seems to format as 0:0:0 when converting.
I thought this might be the issue so I tried a few different ideas. I modified the code to test each of these values "2012-11-7T00:00:00-08:00", "2012-11-7T00:00:00-8:00", and even "2012-11-7" but none of them made a difference.

I will continue to look into this, but wanted to post an update on where things stand. Thanks for all the help so far.

Nov 19, 2012 at 10:32 PM
Edited Nov 19, 2012 at 11:31 PM

Got it!

Ended up NOT building the query and value outside of the SPUpdateMultipleListItems function.  Here is my working code;

$(document).ready(function() {

fields = init_fields_v2();
// Get value from a date field
var dateCompleted = getFieldValue('DateCompleted',true);
var dateComp = new Date(dateCompleted);
var strLength = dateCompleted.length;

if ((strLength) > 6)

var d = dateComp
//Generate ISO 8601 date/time formatted string
  var s = "";
  s += d.getYear() + "-";
  s += d.getMonth() + 1 + "-";
  s += d.getDate();
  s += "T" + d.getHours() + ":";
  s += d.getMinutes() + ":";
  //Replace the "Z" below with the required
  //time zone offset (eg "+10:00" - Australian EST)
  s += d.getSeconds() + "-08:00";


var queryStringVals = $().SPServices.SPGetQueryString();
var taskID = queryStringVals["ID"];

listName: "Custom Aging",
CAMLQuery: "<Query><Where><Eq><FieldRef Name='bscParentTask' LookupId='TRUE'/><Value Type ='Lookup'>"+ taskID +"</Value></Eq></Where></Query>",
valuepairs: [["DateCompleted", s]]
else {
alert("There is no Completed Date");

The above code works.  I also discovered that the ISO date can have time as either 0:0:0 or 00:00:00, didn't seem to make a difference and both worked for me. 

Next I plan on modifying the query to only update items which belong to the the parent and don't have a Date value in their Date Completed fields.  Thanks again for all the suggestions.