U2U Blog

for developers and other creative minds

How to modify the custom People/Groups type column of a SharePoint List using Lists.asmx

Based on this post I wanted to integrate Sowmyan's findings into the U2U CAML Query Builder. But I came to the conclusion that I can also update a user column in a list using the UpdateListItems method of the Lists.asmx web service with following query:

<Batch PreCalc='TRUE' OnError='Continue'>
   <Method ID='1' Cmd='Update'>
      <Field Name='ID'>12</Field>
      <Field Name='AssignedTo'>9</Field>
   </Method>
</Batch>

I don't specify a field type and for the user column I only specify the ID of the user I want to assign the task to.

To retrieve rows using the GetListItems method I couldn't use the ID of the user but I can use his/her name:

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' />
         <Value Type='User'>Karine Bosch</Value>
      </Eq>
   </Where>

Luckily it doesn't always need to be complicated image

Bouvet (Norway) Extending a BluePrint Sample

Yes, I know, it has been a while since I opened up Live Writer and updated my blog. But I'll do my best to work on it again on a regular basis.

This week I am in Stavanger, a nice little place in Norway, with a lovely harbor and it is also this year's cultural capital of Europe. The training for this week is an on-site at Bouvet, one of the services companies in Norway that focuses very heavily on SharePoint. Yesterday, I had my Silverlight/SharePoint talk and they showed me a very cool extension they added to the BluePrint ColleagueViewer Web Part. Really like there approach to pick it up, learn from it and add new stuff to it. Here is a small screencast showing the extension. Basically they have a document library in SharePoint with all of the CVs of their consultants and employees. That one is indexed and a search is executed programmatically in the WCF service that is running in the background for this sample. All of the results together with profile information is then picked up by the Silverlight application.

bouvet

I am interested in other work that has been done with the samples. All the samples will be soon into the BluePrint S+S format.

Right, back to work now.

One CAS Policy for Many Web Part Assemblies

Very often when working with a team of developers you build not one but multiple .NET assemblies containing Web Parts that are deployed in the private application folder (BIN) of the IIS Web Application. Deploying in the BIN folder results in the assemblies depending on the trust level configured by the administrators in the web.config that is in place for the IIS Web Application. Setting this trust level to FULL will solve all of the possible security problems for the deployed assemblies but this is not directly a good choice in a production environment. In most cases, the trust level is set to WSS_Minimal and best practice for a developer is to include in the manifest.xml, which is part of the SharePoint Solution (the .WSP), a blob of CAML asking the administrator to grant the required permissions for the proper working of the Web Part.

Daniel Larson has a good overview of the steps that are required.

Here is a sample of a possible custom CAS entry in the manifest.xml where the Web Part requests access to the SharePoint object model and the permission to communicate with a Web Service.

   1: <CodeAccessSecurity>
   2:     <PolicyItem>
   3:         <PermissionSet class="NamedPermissionSet" version="1" 
   4:          Description="My webpart's permission set">
   5:             <IPermission class="AspNetHostingPermission" version="1"
   6:                 Level="Minimal"/>
   7:             <IPermission class="SecurityPermission" version="1"
   8:                 Flags="Execution" />
   9:             <IPermission version="1" Unrestricted="True" 
  10: class="Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=12.0.0.0, 
  11: Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
  12:             <IPermission class="System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, 
  13: PublicKeyToken=b77a5c561934e089" Unrestricted="True" 
  14:             version="1">
  15:                 <ConnectAccess>
  16:                     <URI uri="$OriginHost$"/>
  17:                     <URI uri="http://springfield:95/webservices/.*"/>
  18:                 </ConnectAccess>
  19:             </IPermission>
  20:         </PermissionSet>
  21:         <Assemblies>
  22:             <Assembly Name="WeatherwebPart" Version="1.0.0.0" PublicKeyBlob="0x002400000480000094000
  23: 00006020000002400005253413100040000010001000DAF8ED8D945CD2ABB2EE7953A6039B791A725F11B4588AC6D70B3E06
  24: 48F955E9ED4C3C43CB044B8B0E8A6FF4D4FFBE9E3B9297D45F688A7264534E12414E17539305207EC961DA94DF294E7722CC
  25: D9BDBFC95A896E996F57156705D281EC39280BD604E87724556AF5807D146963F19F5B43DB69E1F22695463153A553260D2" />
  26:         </Assemblies>
  27:     </PolicyItem>
  28: </CodeAccessSecurity> 

 

Note that the Assembly element is set to the full details of the Web Part assembly. You can also have only the name of the assembly defined here (not including the full key). When the solution is deployed in the farm, the targeted IIS Web Application web.config will have the trust level set to a custom config file with the content of the WSS_Minimal.config (assuming that was the level before the deployment) and the contents you see above.

At U2U we are currently creating new Features for administrators working with SharePoint solutions. For example, we have a page like this that shows the full details of the solution in the SharePoint 3.0 Central Administration, showing also the custom CAS policy that is requested in the WSP. (More on this new project later)

solutionsext1

Assume now that there is the request to create 20 more solutions like this and these assemblies also need specific permissions. It is not such a good idea to come up for each of them with their own CAS policies like shown above. If you sign them all with the same SNK file, you have enough with one configuration containing all of the permissions for all of the individual assemblies that are required. You could push this out in the farm with a solution dedicated to this with only the custom permission settings. The Assembly element then should only contain the PublicKeyBlob, not anymore a Name and a Version attribute.

   1: <Assemblies>
   2:   <Assembly PublicKeyBlob="0x002400000480000094000
   3: 00006020000002400005253413100040000010001000DAF8ED8D945CD2ABB2EE7953A6039B791A725F11B4588AC6D70B3E06
   4: 48F955E9ED4C3C43CB044B8B0E8A6FF4D4FFBE9E3B9297D45F688A7264534E12414E17539305207EC961DA94DF294E7722CC
   5: D9BDBFC95A896E996F57156705D281EC39280BD604E87724556AF5807D146963F19F5B43DB69E1F22695463153A553260D2" />
   6: </Assemblies>

This is yet another reason to enforce the use of only a few SNK files in your SharePoint development projects and not allow SNK files to be created directly by the developers for each assembly they are signing.

U2U Caml Query Builder Feature: new version

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:

image

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.

image

Click on the Preview button to see the actual query and the resulting data after running the query against the list:

 image

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:

image

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!

MOSS 2007 Search Workshop

In two weeks, I'll deliver my first two MOSS 2007 Search workshops. One in Belgium on 16th & 17th of June and one in Copenhagen on the 19th and 20th. Really looking forward to it to spend 2 days entirely on this topic. You'll get plenty of topics and some hands-on labs to work through. Seats are still available. Read the full agenda over here.

U2U Caml Query Builder (Windows version): new version

This version includes the following updates:

- Query fields by ID

- GetListItemChanges (method of the Lists.asmx web service)

- GetListItemChangesSinceToken (method of the Lists.asmx web service)

- extra options of the QueryOptions part

- support for data type ModStat 

You can download the version 3.1.0.0 here.

Remark for those who have put the dlls in the GAC: the version of U2U.SharePoint.CAML.dll has NOT been augmented so the users of the feature version are not bothered. The versions of U2U.SharePoint.CAML.Client.dll and U2U.SharePoint.CAML.Server.dll have also been raised to version 3.1.0.0 because of the changes. So users who use these dlls for custom development will have to check. But as I didn't remove any method, no interfaces should be broken.

Query fields by ID

In most of the cases list items are retrieved using the <FieldRef Name="x" /> syntax. But there are cases that you need to query by ID. In that case you can set the Query by ID option in the upper right corner of the form.

camlv2querybyid

Your query will look like the following:

camlv2querybyid2

Keep in mind that when you already built a query the default way and then choose to build the query by ID, your query will be cleared and you will have to start over again.

The ModStat data type

In case a column in your list is of data type ModStat, a combo box with the values Approved, Pending and Rejected will be displayed. A query containing such a field will look as follows:

<Where>
      <Eq>
         <FieldRef Name="_ModerationStatus" />
         <Value Type="ModStat">Approved</Value>
      </Eq>
</Where>

GetListItemChanges

With this method you can retrieve items that have been modified since a certain date. If you pass no date, all list items will be returned.

For a more in-depth explanation of this method I refer to:

- http://msdn2.microsoft.com/en-us/library/lists.lists.getlistitemchanges.aspx

To build a CAML query for the GetListItemChanges, open your site using the web service mode:

camlv2connectws

Right-click the list for which you want to build a query from the treeview at the left and choose GetListItemChanges:

camlv2getlistitemchanges1

You get 3 tabs to build your query:

- the ViewFields tab as in the GetListItems method

- the Contains tab where you can define a sort of filter

- the Since tab to specify a date

image

If you want to specify a date from which changed rows must be returned, check the Retrieve changes as of check box and choose a date from the date picker. In case you also need to specify a time, use the up/down controls. The date will be passed to the GetListItemChanges method.

GetListItemChangesSinceToken

With this method you can retrieve  list items that have been changed. On the first run of the method, the server returns all rows and a token. In the next runs you pass this token back to the server and only the modified rows since that token will be returned.

For a more in-depth explanation of this method I refer to:

- http://blogs.msdn.com/sharepointdeveloperdocs/archive/2008/01/21/synchronizing-with-windows-sharepoint-services-part-1.aspx

- http://msdn2.microsoft.com/en-us/library/lists.lists.getlistitemchangessincetoken.aspx

To build a CAML query for the GetListItemChangesSinceToken, open your site using the web service mode. Right-click the list for which you want to build a query from the treeview at the left and choose GetListItemChangesSinceToken.

You get 5 tabs to build your query:

- the ViewFields tab as in the GetListItems method

- the Query tab as in the GetListItems method where you can define a more complex filter and a sort order

- the Contains tab where you can define a sort of filter

- the ChangeToken tab to specify a token returned by the server

The Query tab and the Contains tab are mutually exclusive. This means that you can build a (more complex) filter with a WHERE clause or a simple CONTAINS filter.

image

The set of query options is a bit more extended for this method.

image

Don't forget, if you want to use the methods GetListItemChanges and GetListItemChangesSinceToken you can use the CAML generated by this tool but you can also use the underlying dlls U2U.SharePoint.CAML.dll and the U2U.SharePoint.CAML.Client.dll. If this is your case, don't hesitate to ask for a code sample!

Configuring your SharePoint Server for Silverlight 2

Recently I did a webcast on the different steps that are required to prepare your SharePoint Development Server for hosting Silverlight 2 applications in the SharePoint sites. I also went through these steps yesterday during my session here. It is targetting beta 1 and beta 2 will be available later today so you might have to tweak the steps a bit. I'll try to get an update on that done in the next coming weeks.

The webcast is available on http://www.codeplex.com/SL4SP/Release/ProjectReleases.aspx?ReleaseId=14118.

Retrieving Items from a SharePoint List with Powershell and the SharePoint Lists.asmx

There is not yet much information on the net on how to combine Powershell with the SharePoint web services. One of the most used methods of the Lists.asmx, which is a standard SharePoint web service, is the GetListItems method. With this method you can retrieve all list items but also a subset of data when you use a CAML query. You can also limit the number of columns returned if you use a ViewFields node.

If you read previous posts and saved the necessary environment variable declarations in your profile, the only thing you should do to start is:

$listservice = New-Object Lists

$listservice.Credentials=[System.Net.CredentialCache]::DefaultCredentials

Otherwise you have to go back to my post and execute the first steps to declare the environment variables and compile and load the lists dll.

In its most simple form, you can retrieve all list items of a certain list:

$result = $listservice.GetListItems("Customers", $null, $null, $null, $null, $null, $null)

$result is an xml node containing the following:

image

It's the data element that contains the list items.

If you execute something like the following, you will get the last name of each customer in the result set:

$result.data.row | foreach ($_.ows_Title)

image

When working with the Lists.asmx SharePoint web service, all fields are prefixed with ows_.

If you want to retrieve a subset of list items you have to pass a CAML query to the GetListItems method. For example, if you want to retrieve all customers living in the city Bendigo, you have to create following CAML query:

<Query><Where><Eq><FieldRef Name='WorkCity' />

<Value Type='Text'>Bendigo</Value></Eq></Where></Query>

You execute the GetListItems method as follows:

$query = "<Query><Where><Eq><FieldRef Name='WorkCity' /><Value Type='Text'>Bendigo</Value></Eq></Where></Query>"

$result = $listservice.getlistitems("Customers", $null, $query, $null, $null, $null, $null)

image

You can always build your more complex queries with the U2U Caml Query Builder which exists in windows version (download) and feature version (download). Consult my blog or the U2U web site for more detailed information about building CAML queries with the tools. 

Another way to limit the number of rows returned is specifying a row limit, with or without a query:

$result = $listservice.getlistitems("Customers", $null, $query, $null, 3, $null, $null)

If you want to view the complete content of each row, you can execute the following:

image

You can also limit the number of columns returned in the result set by specifying a ViewFields node. Lets only retrieve the Title and Email for customers living in Bendigo:

[xml]$viewfields = "<ViewFields><FieldRef  Name='Title' /><FieldRef Name='EMail' /></ViewFields>

$result = $listservice.getlistitems("Customers", $null, $query, $viewfields, $null, $null, $null)

As you view the result you will notice that indeed the Title and the Email field are returned but also another bunch of fields. These are the system fields and will always be returned, even if you use the IncludeMandatoryColumns element of the QueryOptions (because this only works for custom fields defined as required). For completeness, you define QueryOptions as follows:

[xml]$queryoptions = "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>"

$result = $listservice.getlistitems("Customers", $null, $query, $viewfields, $null, $queryoptions, $null)

You can read more about the GetListItems method on the msdn site.

If you need extra information about this method or about other method on other SharePoint web services, you can leave me a comment.

Nick, now you owe me a beer!