Now this might be common knowledge to some, but it came as a pain in the butt surprise to myself and my team. There are a few gotchas with using CAML in SharePoint, specifically with the SPQuery object. The results of using it incorrectly can cause missing data, which will cause a man to go crazy looking for a solution.
I primarily use
U2U CAML Query Builder Tool to create my CAML queries and then modify them as I need in the code. I recommend downloading this free tool and use it to generate your CAML queries fast and easily.
Field_x0020_Names are different in CAML. If you're not using a CAML builder and are trying to simply type a CAML query by hand for the first time. Stop and download U2U CAML Query Builder. Certain fields are not named the same in CAML.
For instance:In a calendar, the Start Time field is actually named EventDate. The End Time is EndDate.
Names with spaces, like Assigned To is actually Assigned_x0020_To. Spaces are translated as _x0020_.
Some names are completely different. If you take the Title column of a list and change the column name to be Company Name, for example, the CAML label is still Title. Any column name changes are not reflected in CAML.
Long field names are trimmed at 32 characters. So column Inspected By Supervisor in CAML is Inspected_x0020_by_x0020_Supervi. Odd I know. If you happened to create two fields with the same first 32 characters, like Inspected By Supervisor Phone, the CAML label is Inspected_x0020_By_x0020_Supervi0. Why doesn't SharePoint just use the full name? I have no idea.
Lack an <And> and you won't get it. Fair enough, if you don't put in enough <And> in a CAML query the filter isn't complete.
<Where>
<And>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>value</Value>
</Eq>
<Eq>
<FieldRef Name='Location' />
<Value Type='Text'>value</Value>
</Eq>
</And>
<Eq>
<FieldRef Name='fAllDayEvent' />
<Value Type='AllDayEvent'>1</Value>
</Eq>
</And>
</Where>
Above, I'm missing an <And> at the top. As a result, the last piece, AllDayEvent, will not be included in the filter. The frustrating part about this is that it doesn't error! It just simply ignores it and moves on. VERY frustrating when you're working with 8-9 <And> statements and you happen to miss one.
Filter by Lookups and Users. Unfortunately, the current release of U2U doesn't handle lookups very well in CAML. There is a way around it however. Normally, U2U will filter a lookup similar to the following.
<Eq>
<FieldRef Name='Company' />
<Value Type='Lookup'>value</Value>
</Eq>
If you put the value as a name of the Company that should work. If you're use to relationships in SQL, you probably want to use the ID of the company record instead of the name. A better way to handle it is to use the LookupId option
<Eq>
<FieldRef Name='Company' LookupId='true' />
<Value Type='Integer'>1</Value>
</Eq>
When using the LookupId it is important to switch the type to integer. If you keep it on Lookup, it won't error and it won't get you the data you want.
That's what I got for now. I hope this helps!