Updating a Lookup Column Using SharePoint’s Lists Web Service
The SPServices discussions are a fount of interesting information. I find myself answering all sorts of questions which, while connected to SPServices, are often just as much about how SharePoint works in general. I had two questions recently about how to update Lookup columns with UpdateListItems, and I thought they would be of general interest.
Lookup column values are stored in the format “n;#Text Value”, where n is the ID of the value in the reference list and “Text Value” is, well, the text value of the Lookup column. There are probably some good historical reasons for this having to do with Office compatibility, if you think about it. Just storing the text value means that you lose the connection to the original item in the reference list, and just storing the ID for the item would mean that you’d need expensive calls to the database every time you wanted to display the value. As for the ‘;#’ delimiter, you see that sprinkled throughout how SharePoint stores data values. It’s probably related to some very old standard of some sort, but in any case it’s highly likely to be a unique string which no one will use in their text values, so it’s a reasonable delimiter.
Update a Lookup Column Value
The first question was how to update a Lookup column value. The code for this in SPServices is pretty straightforward; the tricky bit is knowing how to structure your value.
In this example, the State column in Sales Opportunities is a Lookup column to the Title column in a list called States.
$().SPServices({
operation: "UpdateListItems",
async: false,
debug:true,
listName: "Sales Opportunities",
ID: 5,
valuepairs: [["State", "2;#Rhode Island"]],
completefunc: function (xData, Status) {
alert(xData.responseText);
}
});
It also works fine to just specify the index:
valuepairs: [["State", "2"]],
The trick is knowing what the right index is. You’ll need to figure that out by calling GetListItems on the reference list or by using some other method.
“Emptying” a Lookup Column Value
The second question was how to “empty”, or remove, a previously set value in a Lookup column.
$().SPServices({
operation: "UpdateListItems",
async: false,
debug:true,
listName: "Sales Opportunities",
ID: 5,
valuepairs: [["State", ""]],
completefunc: function (xData, Status) {
alert(xData.responseText);
}
});
It’s important to keep in mind that when you are interacting with SharePoint’s XML (SOAP) Web Services, *all* of the traffic back and forth is text. There’s no such thing as a null or some of the other datatypes you might be used to in other ways of interating with the API. An empty string [“”] in text essentially *is* a null; it represents “no value”.
I wish that I could say that this little tip was foolproof in interacting with all of SharePoint’s Web Services, but I can’t. There is a *lot* of internal inconsistency across the Web Services, and in some cases, even among operations in the same Web Service. If you want to set other values to “empty” or “null”, you may need to experiment to see what works. I didn’t write ’em; I just wrapped ’em! Note that the line:
alert(xData.responseText);
can be an absolute lifesaver. It will alert what has come back from the server as a response to the Web Service call. Even if the AJAX request is successful (Status=”success”), there may be useful (or totally indecipherable) error information in the response.
“It’s probably related to some very old standard of some sort”
I think this sums up just about everything uncool in all of Microsoft’s products, from a user’s and a developer’s perspective. Gotta give them credit for backward compatibility though…
Josh:
In many circles “backward compatibility” === “enterprise class”. I don’t really agree, but I can see the point. At some point, and I actually hope it’s vNext, SharePoint needs a total gutting to bring it into the 21st century Web-wise. Of course, then you and I wouldn’t have as much fun stuff to work on.
M.
I couldn’t agree more! SharePoint 2013 or whatever should cut the cord. Go full HTML5/CSS3. Go leaner and faster. Establish a new standard for the MS Office family. Then us Middle Tier devs will have some really cool tools at our disposal. MS needs to get some new blood on the SharePoint dev team. Don’t hire .NET ppl who know web stuff, hire web ppl who know .NET stuff (and PHP stuff and Ruby stuff, etc.). Every single “thing” in SharePoint should be looked at by a team of web guys/gals who can say “no, don’t do that, do this instead.”
Sorry for the OT rant :)
Hey, we can dream, right? Or we could go work with some crazy upstart startup doing exactly what you describe. Or we could start one!
M.
Hi Marc,
can you please tell me, how to update the multi selection lookup values?
Kindly help to sort it out.
Yore not giving any information about what you’re doing or what you’ve tried, so I have no idea how to help.
M.
Hi Marc,
Thanks again for a fantastic blog.
I have the following scenario. I have a form where the text fields and radio buttons are blank. I’m using SPServices to collect the values entered (in a text field) or selected (through a radio button) to either update or create a new list item.
At the moment, the only two columns that aren’t updating both happen to be lookup columns. With my example below, i’m trying to update the “Person” column with the text that has been entered into the person container (which is using jquery UI to provide an autocomplete on person names from a list)
[“Person”, $(‘#person-container’).val()],
I’m having the same problem with a lookup radio button:
[“Partner”, $(‘input:radio[name=eop]:checked’, ‘.partner-wrapper’).val()]
Any input on how to populate these fields so they appear in the new or updated list item would be greatly appreciated.
Thanks Marc.
Mattius:
You’re welcome.
To update lookup columns, you also need to know the id of the underlying item or you can pass zero for the id. So your passed values could be
["Person", "0;" + $('#person-container').val()] ["Partner", "0;" + $('input:radio[name=eop]:checked’, ‘.partner-wrapper’).val()]I *think* that will work, but obviously I can’t test it.
Since this is an SPServices question, if you want to pursue it further, can you post over on the SPServices Discussions?
Thanks,
M.
Thanks Marc, unfortunately this didn’t work. Here’s a link to my new post on the SPServices forum in the event any users here wish to know :)
https://spservices.codeplex.com/discussions/443755
I am so stuck. I can usually puzzle this stuff out.
I’ve got a PreSaveAction() on a list NewForm.aspx where I’m trying to insert an item into another sites list.
The current list has a multi value people picker called ‘Designer’
The other list also has a multi value people picker called ‘Designer’.
I get the structure of the multi value people picker, but I can’t seem to get the current value correctly.
Example:
I type in ‘first last; first2 last2’ names into the people picker and click save (no clicking of the validate of user)
I get the current list designer as:
var designer = $().SPFindPeoplePicker({
peoplePickerDisplayName: “Designers”,
checkNames: true
});
I do the checkNames to make sure the user validations happen. Users will never stop and click that checkbox.
But I can’t find the data I need in the designer object that will give me the id;#name;#id that I need for the insert caml:
insertCAML += “” + designer[“currentValue”] + “”;
doesn’t work. The currentValue just contains ‘first last; first2 last2’ and not the validated values with ids.
Am I really going to have to pick apart the currentValue into individuals, then use GetUserInfo to get their IDs and put the string back together again for the CAML?
Thanks for any pointers…
Robin:
The dictionaryEntries object should contain the information you need. Here’s an example:
M.
Ah thanks, Marc, I’ll give that a shot. I couldn’t get my debugger to show me what was available in the dictionsaryEntries, so I was just trying to guess the attributes.
Thanks for the quick response!
I am creating a edit rendering template allows you to update a list which contains a multiuser peoplePicker in sharepoint 2007. Unfortunately, I am a bit confused on 2 areas:
FIRST: Sometimes I get a SPUserID from my peoplepicker & sometimes I don’t. It appears that I don’t get a SPUserID when I select an active directory user which has never been used before. Do I need to do something special when this happens such as update the SPUser list before I can update my list?
SECOND,: How would i update multiple users using $().SPServices “UpdateListItems”?
For instance, lets pretend the form loads with 3 users – ows_AssignedTo=”1;#Jones, Steve V.;#18;#Barker, Bob W.;#17;#Lewis, Jerry L.”
Let say the user removes Jerry Lewis & replaces him with Lou Abbott & Bud Costello:
What would my $().SPServices “UpdateListItems call look like?
Would I use a single valuepair to represent all the users or would I need separate calls for each user (i.e. 4)?
Do I have to do anything different if Lou Abbott & Bud Costello don’t contain a SPUserID?
I appreciate any help I can get on this (my proj mgr is asking me for status updates several times a day)
[email protected]
Never mind, I got it.
The multi-users are delimited the same way you get them and you don’t have to worry about deletes you just call the UpdateListItems with the current items (”1;#Jones, Steve V.;#18;#Barker, Bob W.;#17;#Lewis, Jerry L.”) and everything is updated properly.
In regards to the missing SPUserIDs: You can either add the users missing the SPUserId or restrict the people picker – your choice.
[email protected]
Glad you got it, Charlie.
M.
I just wanted to thank you! I’ve been messing with WebServices for our SharePoint 2007 site and was getting frustrated trying to update a list with lookup values. I noticed it requires the ID, a semicolon and the field value, but I couldn’t get it to update… FRUSTRATING. All that I needed to add was a “#”! Thank you very much for posting this and allowing me to acquire this knowledge.
Is the need for: ID;#Item-Name still a valid requirement for SharePoint 2013 On-Prem?
@Steve:
As I note in the post, you can pass the full value, like ID;#Item-Name, or just the ID. I almost always just pass the ID.
M.