CAML – Query Lookup Field by ID; not by Value

When lookup fields are created, sharepoint stores them as
ID;#Value format in related list.

for e.g.
in list “Contacts” there is column called “Country”.
There is item in contact with value “India” assigned in Country, having item ID=10.

Now for another list “List2″ when column “Country” is stored as Lookup column named “refCountry”; and for certain data item “India” is selected from combo box, Sharepoint stores it in,

10;#India in  “List 2″ -> “refCountry” column.

When having CAML query in List2

The typical query looks like
<Query>
<Where>
<Eq>
<FieldRef Name=”RefCountry”  />
<Value Type=”Lookup”>India</Value>
</Eq>
</Where>
</Query>

The disadvantage of this way is If  list Contacts is having more than one entry(item or row) having value India for Country it will return the only first one. This may give inconsistent data for further.

To avoid this query should be base on ID not by Value.
This can be achieved thru :

<Query>
<Where>
<Eq>
<FieldRef Name=”RefCountry” LookupId=”TRUE” />
<FieldRef Name=”RefCountry” LookupId=”TRUE” />
<Value Type=”Lookup”>10</Value>

</Eq>
</Where>
</Query>

where 10 is the ID for item having country=”India”

About these ads

28 Comments

  1. May 9, 2008 at 8:28 PM

    [...] Lists – Views Bug ? in earlier blog post “caml query: lookup field by id not by value” i have mentioned how you can query lists for lookup field based on lookup field [...]

  2. July 3, 2008 at 12:16 PM

    Thanks for this great tip. I was just solving the exact issue of searching list by lookup id, and fortunatelly I find your site.

  3. Marc Bayé Saltó said,

    September 23, 2008 at 9:17 AM

    Be carefully using the LookupId=”TRUE”, it seems that doesn’t work with lookups over the 16th.
    If you’ve got a list with more than 16 lookups and you need a query over the 17th, you’ll obtain results matching another lookup (if you didn’t deleted any lookup on that list, the results will match the first one, if you query the 18th, results will be on the second one, and so one…)
    There’s a post on the Technet about this possible bug.

  4. PeterB said,

    January 13, 2009 at 12:05 AM

    Thanks.

  5. priya said,

    March 20, 2009 at 6:24 AM

    it looks great

  6. priya said,

    March 20, 2009 at 6:25 AM

    this was an great article

    • Anonymous said,

      June 13, 2013 at 9:25 AM

      Yes… it is!

  7. hussain naqvi said,

    September 10, 2009 at 7:34 PM

    Is there any way to get the data without having “ID;#”? I want to get only Value from that dropdown column.
    Because I am binding returned data set to my gridview without doing any changes in dataset. So my grid is showing value of that column as ID;#Value.

    • October 13, 2011 at 4:29 PM

      If u knw hw to remove ID;# before a column value,plz let me knw.Its very urgent for me.I have created a customer name Lookup column.I displaying the values like 1;#Honey

    • shirisha said,

      October 26, 2011 at 12:03 AM

      to remove ;# from ID;#Name for a lookup field column value, use XSLT code function substring-after(@fieldname,’;#’) in designer.
      for eg:substring-after(@Customername,’;#’) to get only customername as a field value(say shirisha rather than 1;#shirisha)

  8. ashiena said,

    December 7, 2009 at 8:20 AM

    hye,

    how do we find the value of 10 ?

  9. Steve Bloomer said,

    March 2, 2010 at 11:40 PM

    Perfect answer to the question I was asking on Google

    Thanks

  10. Gary said,

    May 19, 2010 at 5:32 AM

    Thank you for posting this. It was incredibly helpful at just the right moment.

  11. Art said,

    May 19, 2010 at 10:22 PM

    Thanks, it was very helpful for me.

  12. Charles Wieland said,

    June 8, 2010 at 3:31 AM

    Very useful, thank you!

  13. rpnanet said,

    June 23, 2010 at 1:45 AM

    Hi, you have link for solution in technet???

  14. July 16, 2010 at 6:32 PM

    thanks for the article. this query system sucks. may MS decide to use SQL in next version. your hints seem to point me in the right direction. however, I still get “One or more field types are not installed properly. Go to the list settings page to delete these fields.”. whom do they hire to author their ingenious error messages? as if I would’ve added extra fields just for fun so to just delete some afterwards, aaargh.
    did I mention this CAML/SP query architecture is cumbersome? :-)

  15. Ivan said,

    September 9, 2010 at 8:58 AM

    How sort in by lookup field id?

    dosn`t work

  16. Ivan said,

    September 9, 2010 at 8:59 AM

    _OrderBy_
    _FieldRef Name=’MacroRegion’ LookupId=’TRUE’ Ascending=’TRUE’ /_
    _/OrderBy_

    dosn`t work

  17. Chetan said,

    February 4, 2011 at 2:34 PM

    Very useful, thank you!

  18. MichaelL said,

    March 29, 2011 at 8:29 PM

    Life saving post, but I think you have a typo right?

    why do you need this twice?

  19. mswin said,

    April 23, 2011 at 12:02 AM

    Hi,

    I have to do the filtering for a multi value lookup column using CAML query.
    So, is this Syntax going to work even for the Multivalue lookup columns?
    Scenario is like in my case Country is a Mutli Select column, can I use the same above syntax for getting all the contacts which has India as one of the value for country in multi select column.

    Can someone confirm this.

    Thanks In Advance.

  20. April 26, 2011 at 7:27 PM

    [...] http://abstractspaces.wordpress.com/2008/05/05/caml-query-lookup-field-by-id-not-by-value/ per la dritta! This entry was posted in CAML Query, Uncategorized and tagged CAML Query. [...]

  21. Sander said,

    July 6, 2011 at 12:34 PM

    Thanks!

  22. siri said,

    October 13, 2011 at 4:36 PM

    Can some body help what is the formula for 12 months rolling data dynamically depending on current data.eg:last 12 months from today

  23. bushan said,

    October 19, 2011 at 6:38 PM

    How to pass the site log in name(Different users) to CMAL Query and assigning the out put to Filter lookup control..

  24. August 5, 2013 at 5:30 AM

    There are a few interesting vaccine trials around the world as well as a few potential cures being researched.
    Witch Hazel can also be applied on sores which will give you a relief.

    The natural treatment of cold sores can be very fast and
    effective.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: