SPServices & CAML Query Options

This is a strange behaviour when using JQuery's SPServices on a list with multi-valued person/group field.

Operation: "GetListItems"
CAMLViewFields: "<ViewFields><FieldRef Name='Title' /><FieldRef Name='ID' /></ViewFields>"
CAMLQuery: "<Query></Query>"

E.g. If the list has this item: [ID=1, Title=My First Item, PersonsResponsible=Test User A; Test User B, Status=Pending], then the output of the method call would have 2 rows:
- Row 1: [ID=1, Title=My First Item, PersonsResponsible=Test User A]
- Row 2: [ID=1, Title=My First Item, PersonsResponsible=Test User B]
* Status field does not appear because not part of the ViewFields. However, PersonsResponsible appear because it is a mandatory column.

I'm not too sure why 2 rows of result are returned (could it be a bug?) but to solve this problem, you simply have to add
CAMLQueryOptions: "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>"

This way, the method call correctly returns 1 row [ID=1, Title=My First Item]

CAML Query Lookup

<Query>
  <Where>
    <Eq>
      <FieldRef Name='Fruit' />
      <Value Type='Lookup'>15</Value>
    </Eq>
  </Where>
</Query>

Doesn't Work, but this works:

<Query>
  <Where>
    <Eq>
      <FieldRef Name='Fruit' />
      <Value Type='Lookup'>Grapes</Value>
    </Eq>
  </Where>
</Query>

Correct query:

<Query>

  <Where>

    <Eq>

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

      <Value Type='Lookup'>15</Value>

    </Eq>

  </Where>

</Query>

Build CAML Query using Boolean Field

If the field is of type "Yes/No", you may query it with a 1/0 value instead of boolean, for example:

<Eq><FieldRef Name='Headline'/><Value Type='Integer'>0</Value></Eq>

or

<Eq><FieldRef Name='Headline'/><Value Type='Integer'>1</Value></Eq>

Additional note:

When adding a new Yes/No field to a existing list, we have three values in the list:

True,
False
Null

So we need to use the <IsNull> or <IsNotNull> element in the query, for
example:

<IsNull>
<FieldRef Name="Headline"></FieldRef>
</IsNull>

CAML Query with Person/People/Group columns

<Eq><FieldRef Name='Supervisor'/><Value Type='User'>Bob</Value></Eq>
- to retrieve list items where Supervisor field has value "Bob".
- "Bob" is the username

<Contains><FieldRef Name='Leads'/><Value Type='User'>Jane</Value></Contains>
- to retrieve list items where "Jane" belongs to the multi-valued people column Leads
- "Jane" is the username
Important! The condition above will actually return list items with the following values in the column Leads: "Jane", "Jane Doe", "Test Jane".

To avoid this situation, use the following instead:
<Contains><FieldRef Name='Leads' LookupId='TRUE'/><Value Type='User'>18</Value></Contains>

CAML: Working with DateTime Queries in SharePoint

Reference: SharePoint Magazine – Writing CAML Queries for Retrieving List Items From A SharePoint List

<Where><Gt><FieldRef Name="StartDate" /><Value Type="DateTime">2009-08-10T10:00:00Z</Value></Gt></Where>

-- Important! Your date must be in ISO8601 format so that it can be identified by MOSS. Use SPUtility.CreateISO8601DateTimeFromSystemDateTime(..)

-- The query returns all list items with a start date as of Aug 10 2009

-- The query does NOT take the time element into account



<Where><Gt><FieldRef Name="StartDate" IncludeTimeValue="TRUE" /><Value Type="DateTime">2009-08-10T10:00:00Z</Value></Gt></Where>

-- The query returns all list items with start date as of Aug 10 2009, 10am.



<Where><Gt><FieldRef Name="StartDate" /><Value Type="DateTime"><Today /></Value></Gt></Where>

-- Today does not take into account the time part

-- There is no Now element (unfortunately)



<Where><Gt><FieldRef Name="StartDate" /><Value Type="DateTime"><Today Offset="10" /></Value></Gt></Where>

-- Offset attribute adds or subtracts the given number of days from today's date

-- Negative is -10
--if Today Offset="1"  does not work for you, try Today OffsetDays="1" 

CAML Queries for Sharepoint

Operators:
Eq -- Equal
Neq -- Not Equal
Gt -- Greater than
Geq -- Greater than or equal
Lt -- Lower than
Leq -- Lower than or equal
IsNull -- Is Null
BeginsWith -- Begins with
Contains -- Contains

Value: (This attribute is optional, and if not defined, is considered as text type)
Text
Number
Lookup -- remember to set attribute LookupId = true in FieldRef tag if you are comparing Id value