Multiple Lookup Columns return no items CAML Query SharePoint Views

Question

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.

Answer 1

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

Answer 2

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>

Answer 3

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