Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have two Lists in SharePoint 2007

  1. Clients
  2. Actions

Actions list has a Lookup column called Client which takes its values from Clients List. I want to display all those clients which are NOT present in Actions list. My approach is to first get all the clients from Clients list and then iteratively check them whether they are present in Actions list or not. This approach is not scalable and I was wondering if there is any better approach for it?

I am using GetListItems method of Lists.asmx web service as I do not have permission to deploy custom web part assembly on server.

share|improve this question

4 Answers 4

up vote 0 down vote accepted

See the <NotIncludes> CAML element here

share|improve this answer
    
it looks like this element was only introduced in SharePoint 2010. –  TZHX Nov 2 '12 at 9:44

I think the only option would be to build a CAML query with clauses in the .

So something like

<Query>
   <Where>
     <And>
       <Neq>
         <FieldRef Name="Title" /><Value Type="Text">Text</Value>
       </Neq>
       <Neq>
          <FieldRef Name="Title" /><Value Type="Text">Other Text</Value>
       </Neq>
     </And>
   </Where>
 </Query>

There is actually a LINQ to SharePoint project for SP 2007 on CodePlex. I've NEVER used it in production and it never got out of Alpha...

share|improve this answer

Don't think this is possibe in a single CAML query. Have you ever used U2U's CAML Query Editor ?

http://www.u2u.be/res/tools/camlquerybuilder.aspx

It's extremely useful for building CAML queries.

Shame your not using Sharepoint 2010 or you take advantage of some LINQ!

Ian.

share|improve this answer

should return the equivalent of

WHERE Title IN ([Title1], [Title2],...[TitleN]) 

for get list of title :

private string _camlTitleEq = "<Neq>" +
                             "<FieldRef Name=\"Title\" />" +
                             "<Value Type=\"Text\">{0}</Value>" +
                          "</Neq>";

private XElement BuildOrClause(List<string> listItemTitles, int index)
{
//If we've reached the last item in the list, return only an Eq clause
if (index == listItemTitles.Count - 1)
    return XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index]));
else
{
    //If there are more items in the list, create a new nested Or, where
   BuildOrClause
    XElement andClause = new XElement("And");
    andClause.Add(XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index])));
    andClause.Add(BuildOrClause(listItemTitles, index + 1));
    return andClause ;
}
}

and for set query

SPQuery query = new SPQuery();
string titleIn = BuildOrClause(listItemTitles, 0).ToString(SaveOptions.DisableFormatting);

query.Query = "<Where>" +
              titleIn +
          "</Where>";
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.