0

I have a list named "Security Groups" inside my sharepoint online site, which contain a lookup field named Templates and a single line of text named "Folder", with the following sample data:- enter image description here

and i execute the following CAML query:-

<View Scope=\"RecursiveAll\"><Query><Where><AND><Eq><FieldRef Name='Templates' /><Value Type='Text'>8</Value></Eq><Eq><FieldRef Name='Folder' /><Value Type='Text'>Co. Sec.</Value></Eq></AND></Where></Query></View>

i thought that i will only get a single item, but i got the 5 items. so can anyone advice on this please? Thanks

EDIT here is my Lookup field:-

enter image description here

1

For querying on a lookup field, you first need to set the "Type" on your <Value> element to "Lookup". In addition, if you are specifically querying on the ID of the lookup column (versus the displayed value), on the <FieldRef> element, you need to add the attribute LookupId="TRUE".

If you are specifying this CAML for a View, Give this a try:

<View Scope=\"RecursiveAll\"><Query><Where><AND><Eq><FieldRef Name='Templates' LookupId='TRUE' /><Value Type='Lookup'>8</Value></Eq><Eq><FieldRef Name='Folder' /><Value Type='Text'>Co. Sec.</Value></Eq></AND></Where></Query></View>

Another potential downfall, the schema for CAML is actually slightly different depending on where you are using your CAML. You did not specify whether you are setting this on a custom list view, or executing this in some code somewhere, but if you are executing this through a client Query, then the <View> element is actually an optional element inside the <Query>, so you would want to set it like this:

<Query><Where><AND><Eq><FieldRef Name='Templates' LookupId='TRUE' /><Value Type='Lookup'>8</Value></Eq><Eq><FieldRef Name='Folder' /><Value Type='Text'>Co. Sec.</Value></Eq></AND></Where></Query>

The most frustrating part about the schemas being slightly different, is that no schema validation error is ever thrown, the client Query just gives you all items in the list.

| improve this answer | |
  • thanks for the reply, the templates values inside the screen is a column named "Template Number" and not the ID. i am not query the ID, i am querying the "Template Number" field..also i am using this CAML inside my CSOM and not inside a view, so will remove the <view> – SharePoint TestDev Feb 25 at 21:04
  • 1
    Okay, you still need the <Value Type='Lookup'>8</Value> for any query on a Lookup columns, you just don't need the LookupId='TRUE' on the <FieldRef> – willman Feb 25 at 21:17
  • so the question is; should 8 be the ID or the column i defined inside the "In this Column" inside my lookup field,which is Template Number in my case? i updated the question with a screen shot of how my Lookup field is defined. – SharePoint TestDev Feb 27 at 0:55
  • Based on your shreenshots, your list view is displaying the Template Number value, if you are filtering on the same value you are seeing in the list view, then you should not use LookupId='TRUE'. If you know what what the item ID is of the original item and are filtering on that, then you need to specify the LookupId attribute. – willman Feb 27 at 1:13
0

You can modify the CAML Query as below. Note: Replace "AND" with "And" in CAML query.

<View Scope='RecursiveAll'><Query><Where><And><Eq><FieldRef Name='Templates' /><Value Type='Lookup'>8</Value></Eq><Eq><FieldRef Name='Folder' /><Value Type='Text'>Co. Sec.</Value></Eq></And></Where></Query></View>

Example C# code.

static void Main(string[] args)
{
    string listTitle = "Security Groups";
    string targetSiteURL = @"https://xxx.sharepoint.com/sites/team";

    var login = "admin@xxx.onmicrosoft.com";
    var password = "xxx";

    var securePassword = new SecureString();
    foreach (char c in password)
    {
        securePassword.AppendChar(c);
    }
    SharePointOnlineCredentials onlineCredentials = new SharePointOnlineCredentials(login, securePassword);

    ClientContext ctx = new ClientContext(targetSiteURL);
    ctx.Credentials = onlineCredentials;
    CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml = "<View Scope='RecursiveAll'><Query><Where><And><Eq><FieldRef Name='Templates' /><Value Type='Lookup'>8</Value></Eq><Eq><FieldRef Name='Folder' /><Value Type='Text'>Co. Sec.</Value></Eq></And></Where></Query></View>";
    ListItemCollection collListItem = ctx.Web.Lists.GetByTitle(listTitle).GetItems(camlQuery);
    ctx.Load(collListItem);
    ctx.ExecuteQuery();
    foreach (ListItem oListItem in collListItem)
    {
        Console.WriteLine("ID: {0} \nTitle: {1} ", oListItem.Id, oListItem["Title"]);
    }
    Console.ReadKey();             
}
| improve this answer | |
  • so the question should 8 be the ID or the column i defined inside the "In this Column" inside my lookup field,which is Template Number in my case? i updated the question with a screen shot of how my Lookup field is defined. – SharePoint TestDev Feb 26 at 10:55

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.