U2U CAML Query Builder Feature Second Version is Released

It has taken me a long time to get the second version ready because I have been doing a lot of other SharePoint development stuff. But the last weeks I have been working very hard on it.

You can download this new version here. The format is a bit different then for the first version. It is a self-extracting .exe file that you only have to double click to start the installation wizard, thanks to the SharePoint Solution Installer of Lars Fastrup. Thanks Lars for this wonderful tool! For all those that have been working with the first version, choose to upgrade the solution. For the newbies a fresh installation will be started.

The major ameliorations can be found in the Filter On section of the CAML query build page. The value control changes in function of the data type of the field you choose. The windows version of the tool already had that capability but now it is also integrated in the feature version.

For example, if you choose to build a Where clause based on a choice field or a lookup field you will get a populated drop down list from which you can choose a value.

Lookup

In the example below the selected field is of type User. In that case a standard SharePoint people picker is displayed to let you choose a user. For a yes/no field a checkbox is displayed. Leave it unchecked for false value,, check if for a true value.

PeoplePicker and YesNo

If you take a good look at the picture, you'll notice another difference: there is a new section named Preview result. This section will be filled and expanded after you have clicked the Preview button. The section contains a text field that shows you the query you are building and the result grid will contain eventual results. Click the Preview button each time you want to test your query and see the changes to it.

I also want to draw your attention on DateTime fields which are a real pain in the ass for SharePoint developers. When selecting a column of type DateTime in the Filter On section, you can choose between Today and fixed date. 

image

If you choose fixed date, a calendar control will appear.

image

You can also opt to add or subtract a certain number of days from the selected date. In case of a fixed date, the new date will be calculated and the query will look as follows:

image

In case you choose Today,

image

an offset of days will be added to the query:

image

Also fields based on business data can be queried now.

But pay attention with custom field types. You can build queries using custom field types in the where clause, for those who derive from simple data types like text, number, yes/no etc. Those who derive from SPFieldMultiColumnValue still are an issue that need to be solved.

One of the major differences to notice is that the resulting queries are not save into a document library anymore but into a list. Old queries will be moved from the document library to the list so that you don't loose your work. The query field is split into two different fields: Query and ViewFields so that you can use them separately. For those who hated the double quotes in the Query field, there is good news: they are gone :).

Caml Query List

You can download the tool here.

Hope you enjoy the new version! If you have suggestions, don't hesitate to drop them in the comments of this post.