A new version of the U2U CAML Query Builder is released today!
This version includes the following updates:
- extra data types like ModStat: when selecting a column of type ModStat, a dropdown with possible values will be displayed
- it is now possibility to add more than 2 fields in the WHERE clause
- you can now build a query where the fields are filtered and sorted by ID instead of by name. This is because some type of queries require filtering by ID.
You can download the full U2U SharePoint Solution package here.
It contains features like List Properties, Site Properties and Move/Copy list items. (Only activate the feature you need).
How to add more filters to the where clause?
In the Filter On section fill out a first filter. If you want to add a second one, click the Add a column to the where clause hyperlink:
This adds a second filter to the Filter On section. You can add as many filters you need and indicate whether you want to Or or to And.
Click on the Preview button to see the actual query and the resulting data after running the query against the list:
You can save your query and it will be saved to a specific CAML Query List from where you can use and preview your queries.
How to build a query to filter and sort fields by ID?
In the previous example the query was filtered by field name:
<FieldRef Name='LastName' />
But you can also execute a query with a filter and a sort by ID. To achieve this, there is a new section added under the query Name section:
The default is query by name but you can choose the option Query by field ID. The user interface stays the same but the previous query looks like this while the resulting data is still the same (don't forget to press the Preview button again):
<Query>
<ViewFields>
<FieldRef Name='EmployeeID' />
<FieldRef Name='LastName' />
<FieldRef Name='JobTitle' />
<FieldRef Name='Phone' />
<FieldRef Name='EmailAddress' />
</ViewFields>
<Where>
<Or>
<Or>
<BeginsWith>
<FieldRef ID='66cb931d-ff9c-47b2-8e13-d3554c1798af' />
<Value Type='Text'>E</Value>
</BeginsWith>
<BeginsWith>
<FieldRef ID='66cb931d-ff9c-47b2-8e13-d3554c1798af' />
<Value Type='Text'>F</Value>
</BeginsWith>
</Or>
<BeginsWith>
<FieldRef ID='66cb931d-ff9c-47b2-8e13-d3554c1798af' />
<Value Type='Text'>G</Value>
</BeginsWith>
</Or>
</Where>
<OrderBy>
<FieldRef ID='13d408ed-79a3-4b67-a384-e4bac030df9d' Ascending='False' />
</OrderBy>
</Query>
As you can see in this query all FieldRef elements in the Order By and the Where clause have an ID attribute, except the FieldRef elements in the ViewFields part. The weird thing is that when I replace the Name attribute with an ID attribute, only the ID field (and some other required fields) is returned. It seems ViewFields cannot handle the <FieldRef ID='[guid]' /> construct.
If you use this query against an SPQuery object, you have to:
- remove the <Query></Query> tags (I know this already caused a lot of confusion among the developers community but I need a root in an XML document)
- Assign the ViewFields node to the ViewFields property of the SPQuery object.
- Assign the Where and Order By node to the Query property of the SPQuery object.
This functionality and some other will be soon released in the windows version of the U2U CAML Query Builder.
Hope you like the new functionality!