caml query for filtered lookup column

Question

My project uses the 2010 version of the Sharepoint Filtered Lookup Field, which has the option to filter a column using a caml query.

So I have three lists: Categories, SubCategories and MainList.

Categories is just a plain list.

SubCategories is a list with two columns: Title and Category where Category is a regular lookup column leading to the Categories list.

MainList has a column for Category and for SubCategory - but SubCategory is supposed to filter on Category.

So I want to write a CAML query that filters the SubCategories list. It should show only those SubCategories where [Title] is equal to the Category.

<Query>
    <Where>
        <Eq>
            <FieldRef Name="Category"/>
            <Value Type="Text">[Title]</Value>
        </Eq>
    </Where>
    <OrderBy>
        <FieldRef Name="Title" />
    </OrderBy>  
</Query>        

Answer 1

Let's try like this:

<Query>
    <Where>
        <Eq>
            <FieldRef Name="Category"/>
            <Value Type="Lookup">[Title]</Value>
        </Eq>
    </Where>
    <OrderBy>
        <FieldRef Name="Title" />
    </OrderBy>  
</Query>  

Default lookup query uses the value instead of lookup Id

The typical query looks like

<Query>
    <Where>
        <Eq>
            <FieldRef Name='RefCountry'  />
            <Value Type='Lookup'>India</Value>
        </Eq>
    </Where>
</Query>

To query by ID you have o setup LookupId='TRUE' attribute of FieldRef element

<FieldRef Name='RefCountry' LookupId='TRUE' />

http://msdn.microsoft.com/en-us/library/office/ms442728(v=office.15).aspx

Ruslan Dayanov

Answer 2

Ah, as it turns out, this cannot be done with the FilteredLookup addon. You cannot add the current value into the query via variables, which is what I expected you could do with the [Title] code in my example.

Here is where the authour says it doesn't work that way.

This is too bad; it would make for wonderful functionality.