6

I have a document library that has reached it's 5000 view limit and I'm trying to retrospectively create views on indexed lookup columns.

I can successfully return views that are customised in SharePoint Designer to use the Interger Lookup ID. However on queries that exceed 5000 with one column filter I need to filter on an additional lookup and I can't seem to get that to work.

<Query>
    <GroupBy Collapse="TRUE" GroupLimit="500"><FieldRef Name="Project_x0020_Category"/></GroupBy>
    <OrderBy>
        <FieldRef Name="LinkFilenameNoMenu"/></OrderBy>
        <Where>
            <And>
                <Eq>
                    <FieldRef LookupId="TRUE" Name="Program"/>
                    <Value Type="Integer">6</Value>
                </Eq>
                <Eq>
                    <FieldRef LookupId="TRUE" Name="Responsible_x0020_Group"/>
                    <Value Type="Integer">9</Value>
                </Eq>
            </And>
        </Where>
        </Query>

Update

In response to a query additional information may be of help pinpointing the issue:

I'm querying a document library that has reached it's 5000 view limit, it is managed by Document sets and the filter data is to be used in the custom document set for each content type.

  • Does your Program & Responsible_x0020_Group are indexed? – Gaurravs May 31 '16 at 3:53
  • Yes, they are both indexed. The only reason I can see for a problem is that they were indexed after the library reached the threshold (not that it should cause an issue) – GrantMcW Jun 1 '16 at 1:25
  • I should say, we temporarily increased the threshold in order to index relevant columns – GrantMcW Jun 1 '16 at 1:39
  • Did you get any resolution for your query? – Hardik Jun 6 '16 at 19:50
5
+25

Your caml query should have <Value Type="Lookup">9</Value> instead of <Value Type="Integer">9</Value>

4

You need to use below updated code as your CAML query.

SPQuery query = new SPQuery();
query.Query = "<Where><And><Eq><FieldRef LookupId='TRUE' Name='Program'/><Value Type='Lookup'>6</Value></Eq><Eq><FieldRef LookupId='TRUE' Name='Responsible_x0020_Group'/><Value Type='Lookup'>9</Value></Eq></And></Where><GroupBy Collapse='TRUE' GroupLimit='500'><FieldRef Name='Project_x0020_Category'/></GroupBy><OrderBy><FieldRef Name='LinkFilenameNoMenu'/></OrderBy></Query>";

Note:-

  • You can not use <Query> parameter if you are using it for SPQuery.Query.
  • You should use Lookup inplace of integer to get lookupID
  • You should do GroupBy & OrderBy after Where clause in CAML query.

CAML Query to get Document Set

<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>Document Set</Value></Eq></Where>
  • Thanks, in relation to the SPQuery, I'm building this in a List view, correct me if I'm wrong, but is that not why the <Query> is used. How would I go about changing this? I've tried your suggestions and made a bit of progress, I had to also change the scope to Recursive, which outputted a lot of items, however within the document sets the view was still returning no items. Is there any reason for this? – GrantMcW Jun 1 '16 at 1:42
  • I have updated my answer & added sample CAML query to get document set. Please try it. – Hardik Jun 1 '16 at 12:22
  • Can you please confirm me where & for what you are querying? Is my answer helps you to get proper resolution? – Hardik Jun 1 '16 at 12:38
  • I'm querying a document library that has reached it's 5000 view limit, it is managed by Document sets and the filter data is to be used in the custom document set for each content type. Your answer has given me a different result than my previous tests, however it still hasn't helped me with a complete solution. – GrantMcW Jun 2 '16 at 1:26
  • Have you tried CAML Query to get Document Set mentioned in my answer? – Hardik Jun 2 '16 at 14:33
0

If your using two lookupfields, make sure that the two columns are added sepeartely but the second column should not be added as extra column while adding the first column from the same list. it will fix the issue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.