Mark Gilbert's Blog

Science and technology, served light and fluffy.

CAML = Needle is passing through my eye

My current project called for a CAML query that took, as one of its filters, a Lookup field’s ID.  In past instances, I had been doing this based on the Lookup field’s text value – what the user actually sees when they select it from the SharePoint page layout drop down – but in my current case this wouldn’t do because the text wasn’t unique.  My data had perfectly valid, but duplicate, items in it.  Hence, I needed to filter based on the underlying ID.

Items in a SharePoint list have both an “ID” and a “UniqueID”.  The latter is a GUID, which, as the acronym indicates is a globally unique identifier.  The former is an integer that works more like an Identity in SQL Server – the number starts at 1 for the first item added to the list, and grows from there.  IDs for items that are deleted don’t appear to be reused for new items, so within the context of a specific list, IDs are also unique.

My original CAML query looked like this:

<Query>
  <Where>
    <And>
      <And>
        <Eq>
          <FieldRef Name=”Product_x0020_Field”/>
          <Value Type=”Lookup”>My Product</Value>
        </Eq>
        <Eq>
          <FieldRef Name=”Question_x0020_Field”/>
          <Value Type=”Lookup”>What is your favorite color?</Value>
        </Eq>
      </And>
      <Eq>
        <FieldRef Name=”Previous_x0020_Question_x0020_Re”/>
        <Value Type=”Lookup”>Blue</Value>
      </Eq>
    </And>
  </Where>
</Query>

Now, that worked to retrieve data, except that the data it pulled back had duplicates in it.  I needed a way to weed the unwanted duplicates out.  If you look at the “formatted” version of “Previous Question Required Answer” (which is what the last EQ clause is querying on; see the second rant below for an explanation as to why the field name appears as it does above), you will see that it returns both the ID of the lookup field value, as well as the text of the field value, separated by a ;# character combination, for example “1034;#Blue”.  Trying to query on the ID portion of that value is in vain – there doesn’t appear to be any way to do it.  CAML queries to that effect simply result in an error.

So, to get around this issue, I ended up using the query above, and then manually weeding through the results looking for the ID that I really wanted.  A hack, to be sure, but since my particular query was pretty well guaranteed to not bring back more than a dozen or so results ever, the performance hit walking through the records one by one was acceptable.

OK sports fans, now it’s time for a rant double header.

<rant>

Notice the use of the two AND keywords in the CAML query above.  It appears that the AND operator only allows two operands.  At most.  Ever.  To achieve a three-way filter, I have to nest one AND inside the other.

</rant>

<rant>

Notice the field name that I need to use for my third EQ test: “Previous_x0020_Question_ x0020_Re”.  Why would I use such a silly naming convention for my field?  Well, the field started out life as “Previous Question Required Answer”.  I couldn’t query on that name, however.  I am required to use the Internal Name of the field in the CAML query.  The Internal Name is assigned by SharePoint, and is created based on the name that you give the field.  So “Previous Question Required Answer” gets each of its spaces replaced with “_x0020_” to give you “Previous_x0020_Question_x0020_Required_x0020_Answer”.  If the conversion ended there, I’d be fine with it.  “x0020” is not that far off from “%20”, a common replacement in URLs.  However, why this renaming qualifies as a rant in my book is that the Internal Name property of a SharePoint field apparently has a 32-character limit, which is why the field needs to appear as “Previous_x0020_Question_x0020_Re” in the CAML.

</rant>

A camel passing through the eye of a needle may be difficult, but CAML sometimes feels like a needle is passing through my eye.

Advertisements

April 19, 2007 - Posted by | MOSS

7 Comments

  1. Awesome Post. My compliments to the author.
    ——————————–SIG———————————-
    Buy Salvia
    Buy Salvia Extract

    Comment by TearIzUp | September 29, 2007

  2. Ran across this post through google and… well, that’s just an awesome title 🙂

    Cheers.

    Michael

    Comment by Michael A. Vickers | September 11, 2008

  3. Oh, and by the way — there *is* a way to lookup by ID number in case you haven’t found it since the time of this post…

    All you have to do is add the attribute LookupId=’True’ to the FieldRef tag, and then use the ID number of them record in the Value tag.

    Comment by Michael A. Vickers | September 11, 2008

  4. Thank you Michael! I wish I had known that 18 months ago.

    Comment by markegilbert | September 11, 2008

  5. Can I pass values to CAML Query?
    i.e
    Desktop

    How can I pass “Desktop” to the above code via a control (textbox) value?

    Comment by andyoye | January 14, 2009

  6. looks like it dint like the tags… 🙂

    Value Type=”Text”>Desktop /Value

    Comment by andyoye | January 14, 2009

  7. I’ll preface this by saying it’s been over 18 months since I’ve had to really dig into Sharepoint.

    From what I recall, the lookup values in the above query such as “My Product” or “Blue” actually came from lookup fields. The codebehind would grab those values off of the form post, build the CAML query, run it, and then parse through the results. Instead of trying to use a textbox in the CAML query, get just its value on the server-side, and build the query from there.

    Comment by markegilbert | January 15, 2009


Sorry, the comment form is closed at this time.

%d bloggers like this: