SPUpdateMultipleListItems not working on my large list

May 21, 2012 at 4:59 PM

Hi!  First of all, I would like to say thanks for these utilities!  I have used them in the past and currently and love them!  :)

There seems to be an issue with this utility working on my large list called Base Reporting.  Currently, this list has 32 custom columns and 6694 items but it will increase to about 13000 items when it goes into production.  Also, one of those columns is a column named "test" which will be removed later.  Orginally, I had the following code in a CEWP on the edit form of another list named "Functional Area":

<script type="text/javascript" src="http://pilot.egovservices.net/sites/AFCOLS/Reporting/Javascript%20Code/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="http://pilot.egovservices.net/sites/AFCOLS/Reporting/Javascript%20Code/jquery.SPServices-0.7.1a.min.js"></script>

<script type="text/javascript" src="http://pilot.egovservices.net/sites/AFCOLS/Reporting/Javascript%20Code/SPUtility.js"></script>
<script type="text/javascript">
    $(document).ready(function() {

//Event.observe(window,'beforeunload',function(){
 
   try
   {

      var funcArea = SPUtility.GetSPField('Functional Area');   
      funcArea = funcArea.GetValue();
      var level = SPUtility.GetSPField('COLS Level');   
      level = level.GetValue(); 
      alert(funcArea); 
      alert(level);
 alert ("Starting...");

$().SPServices.SPUpdateMultipleListItems({
  listName: "{7EC3505B-2D16-4B5D-B0EB-92CBBA4BC3A5}",
  CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='Functional Area'/><Value Type='Lookup'>funcArea</Value></Eq><Eq><FieldRef Name='COLS Level'/><Value Type='Text'>Level 1</Value></Eq></And></Where></Query>",
batchCmd: "Update",
  valuepairs: [["COLS Level", level]],
 debug: true,

 completefunc: function(){alert("finished");} 
    });

   }
   catch (ex)
   {
  
      alert(ex.toString());
 
   }

});
</script>

So basically, when an item is edited in the Functional Area list, changing the COLS Level for the Functional Area, then the COLS Levels for Functional Area in the Base Reporting list (can be hundreds of line items) need to be changed to what the COLS Level was changed to in the Functional Area list. 

The code runs the alerts and the data is correctly gathered from the edit form.  I had also added the line for getting the current URL using an alert and $().SPServices.SPGetCurrentSite which also gave the data back.  The code seems to be running through the list when I use it GUID because it takes a few minutes but doesn't seem to run the code at all when I use the name of the list because it returns in a few seconds with the last alert. 

I have tried adding the webURL ("/Lists").  I have tried using the base Reporting list's URL name which is DD_Base which returns just as fast as using the "Base Reporting" name.  I have tried it with and without the batchCmd: "Update", with and without the CAML query, with and without adding _x0020_ in the field names, changing the value in the value pairs to a static value of "Level 2", by changing the value pairs to  [["test", level]] and [["test", "Level 2"]], by removing the lookup part of the CAML Query, etc.  I have even tried using a clean list because the orginal list IDs started at 879 instead of 1 due to deleting of the orginal test data and have tried putting the code on another ASPX page in a CEWP.  The only thing I can think of is that the size of the list is the problem.  I tried the utility on a two smaller lists (2 lines of data and 1-4 columns, using the Title column in the one with 1 column and a custom column in the other with 4 columns) and they worked.  I have also tried using UpdateListItems instead:

<script type="text/javascript" language="javascript" src="http://pilot.egovservices.net/sites/AFCOLS/Reporting/Javascript%20Code/jquery-1.7.1.min.js"></script>
<script type="text/javascript" language="javascript" src="http://pilot.egovservices.net/sites/AFCOLS/Reporting/Javascript%20Code/jquery.SPServices-0.7.1a.min.js"></script>
<script type="text/javascript" language="javascript" >
    $(document).ready(function() {
   try
   {
 alert ("1... Starting...");
var title =  $("input[Name='ctl00$m$g_86345320_89f8_415e_a319_ce683d457082$ctl00$ctl04$ctl00$ctl00$ctl00$ctl04$ctl00$ctl00$TextField']").val();
//alert($().SPServices.SPGetCurrentSite());
var level = "Level 3";
 
$().SPServices({
        operation: "UpdateListItems",
        async: false,
        batchCmd: "Update",
        listName: "TestList",
        updates: "<Batch OnError='Continue'>" +
                "<Method ID='1' Cmd='Update'>" +
                    "<Field Name='testcol'>1</Field>" +
                "</Method>" +
            "</Batch>",
 completefunc: function(){alert("finished");}      
});

   }
   catch (ex)
   {
  
      alert(ex.toString());
 
   }

});
</script>

And still no dice.

Am I doing something wrong or is there an issue with large lists, please?

Peace and Blessings,

Nikki

May 21, 2012 at 5:03 PM

Well, slight correct to my post above.  Actually the second script worked on the smaller list "TestList" but not on the large "Base Reporting" list.

I would greatyly appreciate your help!

Peace and Blessings,

Nikki

Coordinator
May 22, 2012 at 1:24 PM

The size of the lists shouldn't matter. I notice that you are using DisplayNames rather than InternalNames in your queries. That could be the problem.

M.

May 22, 2012 at 1:33 PM
Edited May 22, 2012 at 2:17 PM

Hi Marc!  I tried code with and without the query and with and without internal names in the query and in the value pairs.  Also, I added and switched to using a column called "test" as well (valuepairs: [["test","Level 1"]]).

Peace and Blessings,

Nikkia Carter

Coordinator
May 22, 2012 at 4:17 PM

Nikkia:

I think you're going to need to try some debugging. Change your alert in the completefunc to this:

$().SPServices({
  operation: "UpdateListItems",
  async: false,
  batchCmd: "Update",
  listName: "TestList",
  updates: "<Batch OnError='Continue'>" +
      "<Method ID='1' Cmd='Update'>" +
        "<Field Name='testcol'>1</Field>" +
      "</Method>" +
    "</Batch>",
  completefunc: function(xData, status){
    alert(xData.responseText);
  }
});

That alert will show you the raw XML coming back from the call. If it's successful, you get the item you just changed back. If not,m there's usually some sort of error message, though they aren't always all that helpful.

M.

May 23, 2012 at 2:12 PM

Hi Marc! Thanks so much for your help!  I actually figured out that the problem was that I am using SPUtility.js to make the field "COLS Level" readonly on the edit form of the Base Reporting list.  :-P  Almost two weeks of banging my head on a wall!  Anyway, it works now but not completely.  What I mean by that is this:

Here is a snapshot of a partial view of the All Items view of the Base Reporting list that I customized in order to reduce the number of columns:

 
Use SHIFT+ENTER to open the menu (new window).  
 
   
   
Functional AreaUse SHIFT+ENTER to open the menu (new window).(Lookup field) Open Menu
IDUse SHIFT+ENTER to open the menu (new window).
ENV 1.1Use SHIFT+ENTER to open the menu (new window).
Level 2 Environmental 1
ENV 2.1Use SHIFT+ENTER to open the menu (new window).
Level 2 Environmental 2
ENV 3.1Use SHIFT+ENTER to open the menu (new window).  
Level 2 Environmental 3
SAF 1.1Use SHIFT+ENTER to open the menu (new window).  
Changed Safety 4
SAF 1.2Use SHIFT+ENTER to open the menu (new window).
Changed Safety 5
SAF 1.3Use SHIFT+ENTER to open the menu (new window).
Changed Safety 6
SAF 2.1Use SHIFT+ENTER to open the menu (new window).
Changed Safety 7
SAF 2.2Use SHIFT+ENTER to open the menu (new window).
Changed Safety 8
SAF 3.1Use SHIFT+ENTER to open the menu (new window).
Changed Safety 9
SAF 3.2Use SHIFT+ENTER to open the menu (new window).
Changed Safety 10
SAF 3.3Use SHIFT+ENTER to open the menu (new window).  
Changed Safety 11
SAF 4.1Use SHIFT+ENTER to open the menu (new window).  
Changed Safety 12
SAF 4.2Use SHIFT+ENTER to open the menu (new window).  
Changed Safety 13
SAF 4.3Use SHIFT+ENTER to open the menu (new window).
Changed Safety 14
FR 1.1Use SHIFT+ENTER to open the menu (new window).
Changed Facilities Recapitalization 15
FS 1.1Use SHIFT+ENTER to open the menu (new window).
Changed Facility Sustainment 16
FS 2.1Use SHIFT+ENTER to open the menu (new window).
Changed Facility Sustainment 17
FS 2.2Use SHIFT+ENTER to open the menu (new window).
Changed Facility Sustainment 18
FS 3.1Use SHIFT+ENTER to open the menu (new window).
Changed Facility Sustainment 19
FS 4.1Use SHIFT+ENTER to open the menu (new window).
Changed Facility Sustainment 20
FS 5.1Use SHIFT+ENTER to open the menu (new window).
Changed Facility Sustainment 21
LS 1.1Use SHIFT+ENTER to open the menu (new window).
Changed Legal Support 22
LS 2.1Use SHIFT+ENTER to open the menu (new window).
Changed Legal Support 23
LS 3.1Use SHIFT+ENTER to open the menu (new window).
Changed Legal Support 24

Here is the function I used:

$().SPServices.SPUpdateMultipleListItems({
  listName: "Base Reporting2",
  CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='Functional_x0020_Area' /><Value Type='Lookup'>Environmental</Value></Eq><Eq><FieldRef Name='COLS_x0020_Level' /><Value Type='Text'>Level 1</Value></Eq></And></Where></Query>",
batchCmd: "Update",
  valuepairs: [["COLS_x0020_Level", "Level 2"]],
 debug: true,
 completefunc: function(){alert("finished");}
    });

Which works as you can see from the snapshot above but if I change the query to: <Query><Where><And><Eq><FieldRef Name='Functional_x0020_Area' /><Value Type='Lookup'>Safety</Value></Eq><Eq><FieldRef Name='COLS_x0020_Level' /><Value Type='Text'>Changed</Value></Eq></And></Where></Query>

then it doesn't work.  Safety stays "Changed".  Is something wrong with my query please?

Peace and Blessings,

Nikki

May 23, 2012 at 3:13 PM

Whenever dealing with lookup fields, I always use the attribute:

LookupId='True' in my <FieldRef> node.

An example looks like this:

<FieldRef Name='Functional_x0020_Area' LookupId='True' /><Value Type='Lookup'>1</Value>

I find it to be much more accurate and straightforward.  When dealing with client side technologies, the less ambiguity the better.  Find the ID of the item in your lookup list and modify your CAML query to see if that helps any.  So wherever "Safety" lives as an item, in that list get the ID for "Safety".  Then modify the example above with the correct ID of "Safety".  

I didn't see anything wrong with the query BTW, so you may want to try a simple query first, then expand on it to get fine tuned results.  e.g. Filter on the LookupId for "Saftey".  Test the results for accuracy, then layer in another filter in your query for "Changed".  Eventually, you'll spot an issue.  If that fails, you'll have to crack open the dev tools and start debugging.  F12 is your friend. 

:-)

Cheers,

Matt

May 23, 2012 at 7:12 PM

Hi Matt!  Thanks for the advice!  Yes, that worked better but for some the ID didn't work.  I looked in the actual lookup list (Functional Area), turned on the ID field so I could see the IDs in the view, and used those IDs in the query.  Some worked and some didn't which is very weird.  Is there another way to get the IDs that works better please?

Peace and Blessings,

Nikki

May 23, 2012 at 7:12 PM

Hi Matt!  Thanks for the advice!  Yes, that worked better but for some the ID didn't work.  I looked in the actual lookup list (Functional Area), turned on the ID field so I could see the IDs in the view, and used those IDs in the query.  Some worked and some didn't which is very weird.  Is there another way to get the IDs that works better please?

Peace and Blessings,

Nikki

May 23, 2012 at 8:08 PM
Edited May 24, 2012 at 1:09 PM

Seems odd that some worked and others didn't especially using the ID's.  You can get the ID's from the list and store them in a variable within your script if you want.  I've done that many of times on projects.  The structure I lean towards is similar to this:

 

var myLookupIds = {
	"listNames" : {
		"MyLookupList" : {
			"ValueFromList" : 1, // ID from list
			"OtherValueFromList" : 2
		},
		"OtherLookupList" : {
			"ValueFromList" : 1,
			"OtherValueFromList" : 2
		}
	}
}

 

Then you can create a mapping that is human readable to your ID's: 

myLookupIds.listNames.MyLookupList.ValueFromList; //Results in 1

My suggestion to you would be to add a little bit of debugging code to your script.  I've modified it below.

***Buyer Beware***

I've always have had issues with the completefunc firing from within the MultiUpdate function that Marc has graciously coded.  Because of that, I've generally did a GetListItems then UpdateListItems myself.  Not sure if the code base has changed since I last used it though...

 

$().SPServices.SPUpdateMultipleListItems({
	listName: "Base Reporting2",
	CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='Functional_x0020_Area' /><Value Type='Lookup'>Environmental</Value></Eq><Eq><FieldRef Name='COLS_x0020_Level' /><Value Type='Text'>Level 1</Value></Eq></And></Where></Query>",
	batchCmd: "Update",
	valuepairs: [["COLS_x0020_Level", "Level 2"]],
	debug: true,
	completefunc: function(xData, Status){
		console.log(xData.responseText);
		console.log(Status);
	} 
});

 

To get this to work, you'll want to use the Developer tools that are integrated with your browser.  Generally pressing F12, will pop those tools open for you.  The console.log() will send a message to the Script tab that you can easily copy and paste here for further troubleshooting.

Hope this helps!

Matt

May 24, 2012 at 12:34 AM

Thanks again, Matt!  I am also going to create a clean list because the IDs are all of the place.  I had created each functional area 4 times initialy then deleted three of them and I think I did some other deletes as well.  I think some corruption may be to blame.

Thanks so much for your help, Marc and Matt!  You guys are life savers!

Peace and Blessings,

Nikki

May 24, 2012 at 6:16 PM

BTW, do you guys know how to make a field in another list readonly and editable?  This code is on the Edit form of the Functional Area is but is updating the Base Reporting field.  On the edit form of the Base Reporting list, there is code for making the COLS Level Field readonly using SPUtility.  Is there a way to make the COLS Level field editable, update the field using SPUpdateMultipleListItems, then make it readonly again?

Peace and Blessings,

Nikki

May 24, 2012 at 7:45 PM
Edited May 24, 2012 at 7:45 PM

"Thanks so much for your help, Marc and Matt!  You guys are life savers!"

What job do you have that your life depends on it? ;-)  You are very welcome by the way...

I'm guessing that the COLS Level is a text field based on the CAML above.  

 

Is that correct?

 

If that's the case, you can use this snippet and it should work:

 

$("input[title='COLS Level']").attr("disabled", true);

 

When disabling form elements, they will not submit values regardless of your manipulations.  If you disable a form element, you must enable it prior to saving.  That's just the way web forms work.  That's not related to SharePoint functionality.  Don't fret... You can easily undo your code using this snippet:

 

function PreSaveAction() {
//PreSaveAction must return true for the form to submit

$("input[title='COLS Level']").attr("disable", false);

return true;
}

 

This function *is* native to SharePoint and will run prior to any saving of an OOTB form.  So prior to the commit, we can simply enable our element(s) and be glad our data is saved.

The code snippets above were just thrown together and not tested.  You may need to tweak them.

 

Cheers,

Matt

May 25, 2012 at 1:54 AM
You can also make the field Readonly (instead of disabled), which gets around the need to re-enable them prior to submit. Readonly will not let the user change its value but will still be sent to the server on submit.

_____
Paul

Sent from mobile device.

May 25, 2012 at 1:20 PM

^Didn't know that Paul.  Can you share the deets when you get a second?

Cheers,

Matt

May 25, 2012 at 3:02 PM

Hey guys!  I don't think I explained what I am trying to do well so I am going to take another stab at it (I have been up for over 24 hours so I hope I am coherent... LOL).  I have 2 lists, one named "Functional Area" and another named "Base Reporting".  On the Edit Form of the Functional Area list I have the code for updating multiple list items.  This code takes the COLS Level field on the Functional Area list's Edit Form and saves it to the COLS Level field in the Base Reporting list where the Functional Area in the Functional Area list is equal to the Functional Area in the Base Reporting.  On the Base Reporting list's Edit Form the is code for making readonly fields using SPUtility (also available on CodePlex).  I found that The SPUpdateMultipleListItems was not working for me intially because when it tried to access that item in the Base Reporting list from the Functional Area Edit Form it was being blocked.  When I commented out that part of the code on the Base Reporting Edit Form then I was able to update.  My question is it possible to make a field in list (Base Reporting) readonly through another list (Functional Area)?

Another thing, and this is wierd, but when I used GetListItems and UndateListItems (code on the Functional Area Edit Form) to update items in Base Reporting based on comparing lookup id in Functional Area to yet another list call COLS Threshold.  I found that items with a lookup if of 31 or greater will not update.  Is that weird or is it just me?

 

Peace and Blessings,

Nikkia T. Carter

May 25, 2012 at 9:09 PM
Matt,
I have found that in most cases the use of the html attribute 'readonly="readonly"' is the most desirable. I already mentioned that when a form is submitted, those fields are still picked up and sent to the server for processing... some of the other advantages are:

- User is able to click inside that field and copy value (perhaps for use else where)
- you have control over css styling (not possible in most browsers with disable)

With disabled="disabled", you will find that the field shape/style changes... the value becomes dark-grey on grey background (users find it hard to read)... you will be unable to style the field using CSS and the user is also unable to copy the field value for use elsewhere.

Hope this helps.

_________
Paul T

Sent from mobile

May 30, 2012 at 3:00 PM

Hi guys!  Thanks again so much for your help!  It seems that both of the issues I described in my last message are not even an issue.  They seem to have worked themselves out as other issues were worked out.  Go figure.  :)  All seems fine now.  Thanks again!

Peace and Blessings,

Nikkia Carter