SharePoint 2010 - is it possible to populate a dataform web part with REST?

0
votes

I have a custom page in SharePoint 2010 that is using a DataFormWebPart to display data from a very large list (almost 5000 items). I have a query defined in XSL in the DataFormWebPart that is querying data from the list. When I increase the size of the list to over 5000 items, I get an error saying that I'm exceeding the list view threshold.

I'm using a simple CAML query to filter the data from the list. My query should never return even close to 5000 items. I've got indexing turned on for the columns that I'm filtering on in the query. I'm not sure what else to try at this point, except to possibly try a different data source.

Is it possible to replace the SPDataSource call to the list with a REST call?

edit: Here's the CAML query

<Query><Where><Contains><FieldRef Name='Location' /><Value Type='Lookup'>{$Location}</Value></Contains></Where></Query>
1
restsharepointsharepoint-2010
Could you show us your CAML query? It's possible that the way it's designed is causing it to bump up against the list view threshold even if it would return fewer than 5000 items. – Thriggle
Please see above. I edited the question and added the CAML query that I'm using. I have also tried it with a second lookup value within the <contains> tags, so that it would filter on both values, but that had the same results. – Jason
Ah, that's the problem. Queries that use the "contains" condition can't take advantage of indexing to circumvent the list view threshold. This makes sense if you think about it... indexing a column is equivalent to putting books in alphabetical order on a shelf. It makes it easier to find a particular title because you can pinpoint the correct spot for it without checking every title in sequence. But a "contains" query is like trying to find every book on a shelf that has the word "dog" somewhere in its title; the books being in alphabetical order won't help you. – Thriggle
Unfortunately, that means switching to REST wouldn't help you... you'll still be restricted by the list view threshold. Would you be able to use <BeginsWith> instead of <Contains>? – Thriggle
<BeginsWith> unfortunately results in the same error message when the list is over 5000 items. I also tried <Eq> and got the same results. Any other query tags that you're aware of that I could try that might work with the indexing? Thanks for the help by the way. I'm fairly new to this and trying to fix an older project written by someone else. – Jason

1 Answers

1
votes

Alright, here's what I've discovered.

Filtering against the text value of a lookup column does not work in a CAML query vs a list that has surpassed the list view threshold.

It appears that indexing a lookup column only indexes the lookup IDs of the column values, not the lookup values themselves.

You can, however, filter against the lookup ID of the lookup column in your CAML query.

Example: Filtering on Timbuku

For example, let's say you want to query for any items with their Location lookup column set to "Timbuktu".

You might use this query:

<Where>
    <Eq>
        <FieldRef Name=\"Location\" />
        <Value Type=\"Lookup\">Timbuktu</Value>
    </Eq>
</Where>

That query will work up until the list reaches the list view threshold of 5000, at which point it will fail, even if the Location column is indexed.

To work around this, you can find Timbuktu's corresponding item in the foreign list and determine its ID. Let's say it's 42. You can then use the ID number in the query like so:

<Where>
    <Eq>
        <FieldRef Name=\"Location\" LookupId=\"true\" />
        <Value Type=\"Lookup\">42</Value>
    </Eq>
</Where>

Note the addition of the LookupId=\"true\" in the FieldRef element.

That will work even after the list view threshold is surpassed.

What this means for you

Overall, it's not good news for your web part.

You would need to first query the locations lookup list for any items corresponding to the desired value, then use the IDs of those items to filter your actual data.

This actually isn't too tough to do programmatically, either with REST or the JavaScript Object Model, but it means you'll likely need to scrap the existing XSLT web part.