Wednesday, October 15, 2008

Query the List by Look up field id

In one scenario we have a same data in look up and when we are querying on that lookup list by value it will give us first value only.

Here is the scenario
We have a contact name as title column and two contacts can have same name.
Now we are referring this title field of contact list to another list named company.

So if we don’t know which contact we are selecting.

And after selecting when we want to find other information from contact list then if we query look up field by text then it will return first entry only.

So chances are there that we are getting the miscellaneous records.

So for that first we have to find ID if selected Look up field
Here is the post how to find it.

Now I want to query the list by Look up id.
I came across the this sites and also want to thank for their suggestion
Adis Jugo
Abstract Spaces

Here is the CAML query for the same

By text value
<Query>
   <Where>
      <Eq>
        <FieldRef Name='Contact' />
        <Value Type='Lookup'>Patel, Parth</Value>
      </Eq>
   </Where>
</Query>


By ID
<Query>
  <Where>
      <Eq>
         <FieldRef Name='Contact' LookupId='TRUE' />
        <Value Type='Lookup'>15</Value>
      </Eq>
   </Where>
  </Query>


This is working fine for us in all the list except one.
And it is not working in the list which has around 40 Lookups

Then we came to know that from Abstract Spaces
If you have lookups more than 16 then it will not work it. If you query on 17 or 18 or further look up it will query again on first or second and so on…

The site also says it is already log on tech net but I cannot find it. If anyone find such article then please give a link in comments.

1 comment:

Steve Ottenad said...

I have not found that Technet article either. I have the same scenario playing out right now. I can tell anyone who stumbles on this that changing the order of the columns doesn't work. SharePoint appears to be Counting/Numbering the columns in the order they were created, not the order they are presented in the list/view.




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com