New version of the CAML Designer Released!

Andy Van Steenbergen and I finally released a new version of the CAML Designer; and this is a version we are very proud of!

This version of the CAML Designer now works on SharePoint 2007, SharePoint 2010, SharePoint 2013, SharePoint 2016 and SarePoint Online; AND it supports multi-factor authentication for SharePoint Online!

 

As you can see we changed the way you can connect to SharePoint: there are only 2 ways to connect:

• through the client-side object model (CSOM) for SharePoint versions 2010, 2013, 2016 and O365
• through the web services to be able to connect to SharePoint 2007

Yes, you read it correctly! The CAML Designer now works on ALL versions of SharePoint (except for SharePoint 2001)! We didn’t test the connection to SharePoint 2003, but I bet it will also work.

Another cool and much desired functionality we added to the CAML Designer, is the possibility to connect to SharePoint Online through multi-factor authentication.

The snippets are still generated for:

• raw CAML
• Server object model
• CSOM
• REST
• Web Services
• PowerShell

You can download the tool from the BIWUG site. There you will find a link to download the application.

A detailed explanation on how to use the tool can be found here.

Have fun with it and please, don’t hesitate to give us your feedback at camlfeedback@biwug.be !!

What if your sandbox solution contains a Visual WebPart?

As already mentioned in previous post, Microsoft decided to stop support for coded sandbox solutions on SharePoint Online. Developers will have to do the necessary effort to migrate the impacted sandbox solutions into solutions that comply with the recommended coding practices. You can find a lot of code samples and recommendations on the PnP site.

If your sandbox solutions contains a Visual WebPart, you will have to take action, because a Visual WebPart generates an assembly.In general, a Visual Web Part consists of the following:

• an .ascx file with UI elements like HTML controls and ASP.NET controls
• an .ascx.cs code behind file with some business logic that interacts with the UI and other data in the SharePoint site
• a .webpart file that contains the definition of the web part (i.e. title of the web part, description, full name of the assembly, etc)

You could try to replace all ASP.NET controls by HTML controls, and develop the business logic in JavaScript and JSOM or REST. Now you don’t need that DLL anymore, right? Now you can get rid of the the assembly by setting the Include Assembly in Package project property to false in Visual Studio, right?

Wrong! You can even try to remove the <%@Assembly> directives and the <%@Register> directives from the .ascx control:

Even if your Visual WebPart contains nothing else than HTML and JavaScript, that assembly is generated. This is because a Visual WebPart consists of an .ascx control. In SharePoint on premises environment, an .ascx control gets deployed to the /TEMPLATE/CONTROLTEMPLATES folder; but this is not the case with sandbox solutions. The .ascx control gets compiled into the DLL, meaning that all UI elements defined on the .ascx control are generated in code.

Trying to force things by removing the metadata element from the .webpart file, will result in an import error when the page loads:

In case of a complex Visual WebPart, your best option is to create a SharePoint Add-in Part:

• A SharePoint hosted add-in consisting of HTML, JavaScript and using JSOM or REST to communicate with the host SharePoint site
• A provider hosted add-in represented by an MVC application using CSOM to communicate with SharePoint

In case of less complex Visual WebPart, you could choose to replace it by embedding JavaScript

But all these approaches require you to go to each page that hosts the old Visual Web Part to replace it by the new development. This can be quite a challenge for large O365 tenants.

Another approach is to keep your .webpart file and change it to point to a JavaScript file. The advantage of this approach is that pages hosting your old Visual WebPart will now automatically host your new web part.

What are the steps you have to take to make this approach work?

1. Write the JavaScript to replace the logic of your existing Visual Web Part
2. Point the .webpart file to this JavaScript file
3. Deactivate and remove the old sandbox solution
4. Upload your JavaScript file into the Style Library of your site collection
5. Upload the new .webpart file into the WebPart Gallery

Following image shows how you can modify the .webpart file:

• Change the <metadata> tag to point to the ScriptEditorWebPart

    <metaData>
      <type name="Microsoft.SharePoint.WebPartPages.ScriptEditorWebPart, Microsoft.SharePoint, Version=16.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
      <importErrorMessage>Cannot import the migrated Demo Visual Web Part.</importErrorMessage>
    </metaData>

• Add a <property> element with name Content
• Add a reference to your JavaScript file
• Add a <div> element that will be the container of the UI that you have to develop in the JavaScript file

In your JavaScript you will have to retrieve this <div> element, to be able to embed the rest of the UI.

You can even automate steps 4 and 5. You can upload the JavaScript into the Style Library using the following CSOM code:

        private static void UploadFileToStyleLibrary(ClientContext ctx, string sourcePath, string styleLibFolder, string jsFileName)
        {
            // get the Style Library
            List styleLibrary = ctx.Site.RootWeb.GetList(ctx.Url + "/Style%20Library");
            ctx.Load(styleLibrary);
            ctx.ExecuteQuery();

            // upload the js file
            string fullSourcePath = sourcePath + jsFileName;
            if (!styleLibFolder.EndsWith("/")) { styleLibFolder += "/"; }
            string fullTargetPath = "/Style%20Library" + styleLibFolder + jsFileName;
            
            var fileCreationInfo = new FileCreationInformation
            {
                Content = System.IO.File.ReadAllBytes(fullSourcePath),
                Overwrite = true,
                Url = fullTargetPath
            };

            Microsoft.SharePoint.Client.File uploadFile = styleLibrary.RootFolder.Files.Add(fileCreationInfo);
            ctx.Load(uploadFile);
            ctx.ExecuteQuery();
        }

You can upload the new .webpart file into the WebPart Gallery using the following CSOM code:

        private static void UploadWebPartFile(ClientContext ctx, string sourcePath, string wepartFileName)
        {
            // get the WebPart Gallery
            List wpGallery = ctx.Site.RootWeb.GetCatalog((int)ListTemplateType.WebPartCatalog);
            ctx.Load(wpGallery);
            ctx.ExecuteQuery();

            // check if there is a file with the same name
            try
            {
                Microsoft.SharePoint.Client.File file = wpGallery.RootFolder.Files.GetByUrl(wepartFileName);
                ctx.Load(file);
                ctx.ExecuteQuery();

                // delete the file if it already exists
                file.DeleteObject();
                ctx.ExecuteQuery();
            }
            catch { }

            // updload the webpart file
            var fileCreationInfo = new FileCreationInformation
            {
                Content = System.IO.File.ReadAllBytes(sourcePath + wepartFileName),
                Overwrite = true,
                Url = wepartFileName
            };

            Microsoft.SharePoint.Client.File uploadFile = wpGallery.RootFolder.Files.Add(fileCreationInfo);
            ctx.Load(uploadFile);
            ctx.ExecuteQuery();
        }

If you prefer to work with a SharePoint Add-in to provision your files, you could follow the PnP approach for App Script Parts.

Good luck!

Do not use SPUtility.CreateNewDiscussions(SPListItemCollection, title) but use SPUtility.CreateNewDiscussions(SPList, title) method instead

Internally the method SPUtility.CreateNewDiscussions(SPListItemCollection, title) executes the SPListItemCollection.Add method to add a new discussion item to the list.

The SPListItemCollection.Add method is know for loading the whole list item collection into memory before adding a new Discussion list item. This can cause poor performance and even throttling for large lists.

It is recommended to use the SPUtility.CreateNewDiscussions(SPList, title) method instead, as this method internally executes the SPList.ItemAdd() method.

SPList.ItemAdd() does not load all list items in memory but executes a dummy CAML query to retrieve an empty SPListItemCollection to which a new item is added.

Office Dev PnP survey

The Office Dev PnP program has done a great job on building sample code and scenarios to guide SharePoint developers away from Full Trust code solutions to the add-in model. Through the past year the guidance started to evolve to other areas like ffice 365 APIs, Office Add-ins and unified APIs. PnP program has now evolved as open source community effort with both internal and external contributors.

This program is open source and driven by the community, with both internal and external contributors.

Every SharePoint developer gains from this initiative, and therefore I’m a big fan. and therefore I want to ask for your cooperation to fill out the survey http://aka.ms/officedevpnpsurvey.

Thanks for your cooperation!

 

Repair content type retention policies

Recently I was at a customer who implemented retention policies on content types. They have about 20 content types and a site collection with hundreds of sub sites with thousands of documents.

Becky Bertram wrote a nice detailed article on how to define retention policies for SharePoint 2010.

Problem description

When retention policies are applied on content types, there are 2 timer jobs that run (by default during the weekend):

• Information management policy timer job: by default, runs on friday 11 PM. The job goes through libraries that have policies applied. It calculates the expiration date for every item.
• Expiration policy timer job: by default, runs on saturday 11 PM. This job executes the action part of the retention policy. For example, if the action is to move expired documents to the recycle bin, expired documents will be deleted; if the action is set to move the documents to a send-to location, the expired documents will be moved.

My customer explained that when the  timer jobs ran for the first time, they ran for several hours and then just stopped running. As of then the jobs ran weekly, but with a lot of similar errors in the ULS logs:

"Error processing expiration of content in list <list name> in site <url to sp site>. 
 Error: Invalid field name. {b0227f1a-b179-4d45-855b-a18f03706bcb}".

"Error processing expiration of content in list <list name> in site <url to sp site>. 
 Error: Invalid field name. {acd16fdf-052f-40f7-bb7e-564c269c9fbc}".

From this post you can see that these guids refer to out of the box SharePoint fields:

Exempt from Policy	b0227f1a-b179-4d45-855b-a18f03706bcb	_dlc_Exempt
Expiration Date	acd16fdf-052f-40f7-bb7e-564c269c9fbc	_dlc_ExpireDate

When I tried to take a look at the Compliance Details of a document, I got the following error message: “column  ‘_dlc_exempt’ does not exist. It may have been deleted by another user”.

The Compliance Details menu option is only available on the context menu when retention policies are active for the specific document:

After investigation I found out that part of the sites had the retention policies correctly applied, and part of the sites had not.

In the rest of the article I will use the term “active retention policy”. It means that retention policies are defined on content types, and that these content types are in use on a document library.

Solution

I found a very interesting article on retention policies on the net to get me started, so kudos to Mike Berryman. I started my investigation to repair the site collection based on this article.

When a SharePoint web has active retention policies, it should have the following properties on its property bag:

• allowslistpolicy
• dlc_sitehaspolicy
• dlc_sitehasexpirationpolicy
• dlc_webhasexpirationpolicy

As some SharePoint sites were working correctly and some not, I corrected each SPWeb as follows:

$web.AllowUnsafeUpdates = $true
if (!($web.Properties.ContainsKey("allowslistpolicy")))
{
    $web.Properties.Add("allowslistpolicy", $true)
}
if (!($web.Properties.ContainsKey("dlc_sitehasexpirationpolicy")))
{
    $web.Properties.Add("dlc_sitehasexpirationpolicy", $true)
}
if (!($web.Properties.ContainsKey("dlc_sitehaspolicy")))
{
    $web.Properties.Add("dlc_sitehaspolicy", $true)
}
if (!($web.Properties.ContainsKey("dlc_webhasexpirationpolicy")))
{
    $web.Properties.Add("dlc_webhasexpirationpolicy", $true)
}
$web.Properties.Update()
$web.Update()

A library with active retention policies should have the following hidden fields:

• _dlc_Exempt
• _dlc_ExpireDateSaved
• _dlc_ExpireDate

I used the XML definition of these fields in order to create the missing fields:

$displayName_exempt = "Exempt from Policy"
$schemaXml_exempt = "<Field ID='{B0227F1A-B179-4D45-855B-A18F03706BCB}' 
    Name='_dlc_Exempt' StaticName='_dlc_Exempt' DisplayName='_dlc_Exempt' 
    SourceID='http://schemas.microsoft.com/sharepoint/v3' Group='Document and Record Management Columns' 
    Type='ExemptField' Indexed='FALSE' Hidden='TRUE' CanToggleHidden='TRUE' 
    ShowInNewForm='FALSE' ShowInEditForm='FALSE' ShowInFileDlg='FALSE' ShowInDisplayForm='FALSE' Required='FALSE' 
    Sealed='TRUE' ReadOnly='TRUE' OverwriteInChildScopes='TRUE'/>"
 
$displayName_expireDateSaved = "Original Expiration Date"
$schemaXml_expireDateSaved = "<Field ID='{74E6AE8A-0E3E-4DCB-BBFF-B5A016D74D64}' 
    Name='_dlc_ExpireDateSaved' StaticName='_dlc_ExpireDateSaved' DisplayName='_dlc_ExpireDateSaved' 
    SourceID='http://schemas.microsoft.com/sharepoint/v3' Group='Document and Record Management Columns' 
    Type='DateTime' Indexed='FALSE' Hidden='TRUE' CanToggleHidden='TRUE' ShowInNewForm='FALSE' 
    ShowInEditForm='FALSE' ShowInFileDlg='FALSE' ShowInDisplayForm='FALSE' Required='FALSE' 
    Sealed='TRUE' ReadOnly='TRUE' OverwriteInChildScopes='TRUE' />"

$displayName_expireDate = "Expiration Date"
$schemaXml_expireDate = "<Field ID='{ACD16FDF-052F-40F7-BB7E-564C269C9FBC}' 
   Name='_dlc_ExpireDate' StaticName='_dlc_ExpireDate' DisplayName='_dlc_ExpireDate' 
   SourceID='http://schemas.microsoft.com/sharepoint/v3' Group='Document and Record Management Columns' 
   Type='DateTime' Indexed='TRUE' Hidden='TRUE' CanToggleHidden='TRUE' 
   ShowInNewForm='FALSE' ShowInEditForm='FALSE' ShowInFileDlg='FALSE' ShowInDisplayForm='FALSE' Required='FALSE' 
   Sealed='TRUE' ReadOnly='TRUE' OverwriteInChildScopes='TRUE' />"

As some SharePoint libraries were working correctly and some not, I corrected each SPList as follows:

function RepairField($lib, $fieldInternalName $fieldDisplayName, 
       $schemaXml) { 
   $field = $null
   try
   {
      $field = $lib.Fields.GetFieldByInternalName($fieldInternalName)
   }
   catch {}
            
   if ($field -eq $null)
   {
       $lib.Fields.AddFieldAsXml($schemaXml)
       $field = $lib.Fields[$fieldInternalName]
       if ($field -ne $null)
       {
            $field.Title = $fieldDisplayName
            $field.Update()
       }
   }
}       
       
RepairField($lib, "_dlc_exempt", 
    $displayName_exempt, 
    $schemaXml_exempt)

RepairField($lib, "_dlc_ExpireDateSaved", 
    $displayName_expireDateSaved, 
    $schemaXml_expireDateSaved)

RepairField($lib, "_dlc_ExpireDate", 
    $displayName_expireDate, 
    $schemaXml_expireDate)

$lib.Update()

Each document on which a retention policy applies, has the following properties in its property bag:

• ItemRetentionFormula
• _dlc_ItemStageId
• _dlc_ItemScheduleId

I corrected the items as follows:

$files = $lib.RootFolder.Files
foreach ($file in $files)
{
    $item = $file.Item
            
    # add the property ItemRetentionFormula to the property bag
       write-host ("set the retention properties  for item ID " + $item["ID"])
    $item["Modified"] = $item["Created"]

    if ($item.Properties.ContainsKey("ItemRetentionFormula") -eq $false)
    {
        $item.Properties.Add("ItemRetentionFormula", $true)            
    }
    if ($item.Properties.ContainsKey("_dlc_ItemStageId") -eq $false)
    {
        $item.Properties.Add("_dlc_ItemStageId", $true)
    }
    if ($item.Properties.ContainsKey("_dlc_ItemScheduleId") -eq $false)
    {
        $item.Properties.Add("_dlc_ItemScheduleId", $true)
    }
                    
    # clear the item stage id
    $item.Properties["_dlc_ItemStageId"] = ""
    # set the schedule type
    $item.Properties["_dlc_ItemScheduleId"] = $null
    # set the property to contain the formula
    $item.Properties["ItemRetentionFormula"] = $null
    $file.Update()  
    $item.SystemUpdate()
}

For completeness, at the end I disposed the SPWeb object:

$web.AllowUnsafeUpdates = $false
$web.Dispose()

Remarks:

• I used the server-side object model.
• As I had to work on the production environment, I had to write the code in PowerShell, but you can also do it in C#.

 

 

 

 

SharePoint 2007 out of support

Last week I received the question “Is SharePoint 2007 still supported?”

My first reaction was “euh, what??” SharePoint 2010 mainstream support has just ended, so SharePoint 2007 is surely not supported anymore. But the customer referred to this page, saying that SharePoint 2007 was under support till October 10th 2017: https://support.microsoft.com/nl-be/lifecycle/search?sort=PN&alpha=SharePoint%20Server%202007&Filter=FilterNO

My colleague Peter Loete and I took a closer look to the support page, and this is the table from which the customer deducted that SharePoint 2007 was still under support:

So, no mainstream support anymore for SP2007 SP3: it ended on September 10th 2012. Extended support, at the other side, is still available till October 10th 2017.

What’s included in extended support? You can read more about it here.

SharePoint 2010 mainstream support ends on October 13th 2015

Although a lot of my customers still are on SharePoint 2010, mainstream support ends for all SharePoint 2010 installations with SP2 on October 13th 2015. If you’re not yet on SP2, you can still download it from here.

If you are a Microsoft Premier customer, you get extended support. You can read the details here.

You can find official information on the product lifecycle of SharePoint 2010 here.

Setting the value of a lookup field using CSOM

Today I had a hard time to find out how to set the value of a lookup field using CSOM. In most of the code samples I found on the internet, the LookupId is set hard coded, and this is not what I needed. Additionally, in some cases the lookup list was situated on the root web, and in another case I had to query a different field than the Title field. I ended up writing the following method:

public static FieldLookupValue GetLookupValue(ClientContext clientContext, string value, 
   string lookupListName, string lookupFieldName, string lookupFieldType, bool onRootWeb)
{
     List list = null;
     FieldLookupValue lookupValue = null;

     if (onRootWeb)
     {
          list = clientContext.Site.RootWeb.Lists.GetByTitle(listName);
     }
     else
     {
          list = clientContext.Web.Lists.GetByTitle(listName);
     }

     if (list != null)
     {
         CamlQuery camlQueryForItem = new CamlQuery();
         camlQueryForItem.ViewXml = string.Format(@"<View>
                  <Query>
                      <Where>
                         <Eq>
                             <FieldRef Name='{0}'/>
                             <Value Type='{1}'>{2}</Value>
                         </Eq>
                       </Where>
                   </Query>
            </View>", lookupFieldName, lookupFieldType, value);

          listItemCollection listItems = list.GetItems(camlQueryForItem);
          clientContext.Load(listItems, items => items.Include
                                            (listItem => listItem["ID"],
                                             listItem => listItem[lookupFieldName]));
          clientContext.ExecuteQuery();

          if (listItems != null)
          {
              ListItem item = listItems[0];
              lookupValue = new FieldLookupValue();
              lookupValue.LookupId = Int.Parse(item["ID"].ToString());
          }             
     }

     return lookupValue;
}

This method works on both SharePoint 2010 and 2013.

I hope this code snippet can help others.

Enjoy!

 

SharePoint Saturday

SharePoint Saturday 2014 is coming near and we all start to feel the vibes. April 26th is the big day! We just made the session agenda available online. Check it out here!

Declaring a taxonomy field that allows multiple values

There are two different types of taxonomy fields:

• TaxonomyFieldType: This defines a taxonomy field to store a term to tag the list item.
• TaxonomyFieldTypeMulti: This defines a taxonomy field that allows users to tag the list item with more than one term from the same term set.

When using CAML, you can declare taxonomy fields as follows:

  <Field  ID="{CB736B25-C980-4688-B076-1C5315B20092}" 
          Name="ProductCategory" DisplayName="Product Category" 
          Type="TaxonomyFieldType"
          ShowField="Term1033" 
          EnforceUniqueValues="FALSE" 
          Group="XYZ Site Columns" 
          SourceID="http://www.xyz.be/v1" />

  <Field  ID="{6E8C115D-43DE-4AD9-9DE4-27F5D4C008F4}" 
          Name="ProductCategories" DisplayName="Product Categories" 
          Type="TaxonomyFieldTypeMulti"
          ShowField="Term1033" 
          EnforceUniqueValues="FALSE" 
          Group="XYZ Site Columns" 
          SourceID="http://www.xyz.be/v1" />

This will create your field of the correct type but it will not be mapped to a term set yet. This cannot be done through CAML, but only through code, ideally in the feature receiver:

   field.SspId = termStore.Id;
   field.TermSetId = termSet.Id;
   field.AnchorId = Guid.Empty;

These properties mean:

• TermSetId: This is the Guid of the term set to which the field is mapped. Users will only be able to tag the item with term(s) from this term set.
• AnchorId: If the entry point for tagging is a term within the term set, the anchor id needs to be set to the guid of this term. If the entry point for tagging is any term within the term set, this property does not need to be set.
• SspId: This is the Guid of the term store to which the term set belongs.

You would think that, when you create a field of type TaxonomyFieldTypeMulti, you would be able to store more than one term, but unfortunately that’s not the case.

When you inspect the field in the user interface, you see that the check box Allow multiple values is not checked.

It’s only after having checked this property that you are able to store more than one term using the TaxonomyFieldValueCollection. Unfortunately there is no attribute in CAML to set this checkbox, so you will have to solve this in your feature receiver by setting the AllowMultipleValues property:

if (field.TypeAsString == "TaxonomyFieldTypeMulti")

      field.AllowMultipleValues = true;

As of then you will be able to store multiple terms in the taxonomy field.