Edit: I’ve since learned more about this behaviour – but it’s still not very obvious
I found what I can only describe as a but in the Lists.asmx web service in SharePoint. I was using Lists.GetListItems to get a list of the folders in a Document Library, and I found that I was getting one of the folders twice. However, when I looked in SharePoint, there was only one, and my program showed that both these folders were supposed to be at the same URL. WTF?
Naturally, I went back and had a look at the XML I was getting back from the web service. I expected to get two, so I was somewhat surprised to get 3…
As you can see, 2 records have the same URL, the same internal ID (both of which have to be unique!), and the only difference is in the AWBMultiLookup column. This set alarm bells ringing. Firstly, I was surprised to see it in the results. In GetListItems you can define the fields you want returned in the responses (the ‘view fields’ ). I had only asked for the URL, the ‘link file name’ (the item’s name), and it’s ID. I’d expect to get some other details (the web services always seem to include more), but not field data.
Secondly, AWBMultiLookup is a multi-select lookup column. In other words, it looks up data from another list, and you can can select zero to many items from it. For my folder, I’d selected 2 options from this column (‘A’ and ‘C’), and now I have 2 records with the only difference being the value of the AWBMultiLookup column – ‘A’ or ‘C’. On a hunch, I went and selected a third option (‘B’), and my XML I was getting back became:
Yup, now I got 3 records with different values of AWBMultiLookup, rather than just the one. Clearly, and incredibly, the web service was returning a seperate ‘item’ for each value of the multi-select lookup column that was selected. I can’t think of a situation where that would be the desired behaviour – and everywhere else in the system, multiple values are just encoded into one string. Hence, I’m calling that a bug.
However, this raised a question. Elsewhere in the system I get just the one folder and it was definitely getting the multi-select lookup field correctly. I ran that code and found that the XML I got back was:
Yup, you’ll notice that the results from GetListItems this time shows all the values for the multi-select lookup in one attribute. What gives? What’s different about this query to the other one where multiple items would be returned?
Well, the only difference that seemed like it could be relevant is that this second query requests the ‘AWBMultiLookup‘ column in the ‘view fields’. I decided that I’d test this by modifying the view fields for my first query to include the AWBMultiLookup column like so:
Having done this, my query now worked as I’d expected it to in the first place:
Notice that all values are now encoded in the one attribute, and that I’m getting the two records I’d originally expected.
Or course, this doesn’t help me. When my application is browsing across folders I don’t actually know what the columns against folder really are, so even if I was happy to request the multi-select lookup fields (despite their being superfluous ), I can’t, ‘cos I don’t know what they are yet. I’ve gotta admit, though, that I’ve lost my temper at whoever made the web services work that way. It’s worth noting that the SharePoint API doesn’t behave similarly
One thing I haven’t checked yet is what happens if you hav many multi-select lookups. Combinatorial explosion anyone?
2 thoughts on “Duplicate rows from the SharePoint Lists webservice.”
[…] recently I was doing some work with SharePoint Web Services, getting possible values for Lookup fields. One of the functions I was using was GetListItems(), to […]
[…] couple of months ago I was making some calls to the SharePoint Lists Web Service, and I found that I was getting the same item back multiple times. This puzzled me – I mean, it was clearly by design, but why would anyone want a copy of the item […]