Find Items in External List Programmatically

While working on a recent project I had to connect to an external DB to pull in some data and then use this in other lists in a SharePoint site. The obvious solution for this was to use BCS and set up an external content type to get the data from my DB. I had never setup BCS before so decided to use SharePoint Designer to set it up as this seemed the easiest way. It took a little trial and error, especially around permissions, but eventually I managed to get my list up and running displaying data. In addition I added a filter to my external content type to allow users to search on the data. I won’t go over how to add a filter as there are numerous articles on the web about how to do this, see examples below.

http://msdn.microsoft.com/en-us/library/ff798274.aspx
http://www.lightningtools.com/blog/archive/2010/01/14/creating-comparison-and-wildcard-filters-for-bcs-in-sharepoint-designer.aspx

With the external list working I then created another list and within this I created a column which was an lookup to my external list. I think SharePoint must realise this is a special case as even though you add a column as a lookup to another list it changes the type to an External Data column. I was then able to add new items to my custom SharePoint list and associate them with data from my external DB.

This was all fine but I then need to migrate some of the customers existing data from an Excel SpreadSheet into SharePoint. Adding items into SharePoint is a fairly easy task and something I have done numerous times over the years but the one new section to this was how I would query the external list and then set the value of the external data column in my custom list.

After some research I found a lot of people suggesting that you could simply use CAML in the normal manner, however during my testing the external list always had 0 items. I then found an MSDN article on Using the BDC Object Model so I decided to try this.

I copied the code, see full code below, from the Using Filters section of this article and added a reference for the BCS code, Microsoft.BusinessData.dll located in the ISAPI folder in the SharePoint root.

MSDN Example Code

1. const string entityName = "Machines";
2.         const string systemName = "PartsManagement";
3.         const string nameSpace = "DataModels.ExternalData.PartsManagement";
4.         BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
5.         IMetadataCatalog catalog =
6.           bdcService.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
7.         ILobSystemInstance lobSystemInstance =
8.           catalog.GetLobSystem(systemName).GetLobSystemInstances()[systemName];
9.         IEntity entity = catalog.GetEntity(nameSpace, entityName);
10.         IFilterCollection filters = entity.GetDefaultFinderFilters();
11.  
12.         if (!string.IsNullOrEmpty(modelNumber))
13.         {
14.             WildcardFilter filter = (WildcardFilter)filters[0];
15.             filter.Value = modelNumber;
16.         }
17.  
18.         IEntityInstanceEnumerator enumerator =
19.           entity.FindFiltered(filters, lobSystemInstance);
20.  
21.         entity.Catalog.Helper.CreateDataTable(enumerator);

 

My first issue was I had no idea what the string variables at the top of the code should be set as. I tried debugging the code and I was able to find out the system name but it took some time. It turns out all the required details are in SharePoint Designer, see below.

• Entity name should be the name at the top of the external content type information
• System name should be the external system at the bottom of the external content type information
• nameSpace should be the Namespace in the middle of the external content type information

Similarly to the example MSDN code I had setup one filter and this was a wildcard filter so I didn’t have to change that section of the code. All I changed was I set the filter value to be a value I was interested. When I ran the code everything seemed to be working as expected but I noticed if I was searching for ‘Test123’ and there was an item in the external list which matched exactly then it would return my result, however if I changed the code to search for ‘Test’ it didn’t find anything. I tried adding ‘*’ in various places to act as a wildcard but it made no difference to the results.

I expected since the filter was a wildcard filter I could search on only a part of the phrase and it would return what I was interested in but it wasn’t working. I checked the filtering functionality by adding a new item in my custom list and using the searching functionality for BCS and it worked in that I could search for only part of a phrase such as ‘Test’ and it would find partial matches like ‘Test123’. After confirming the out of the box functionality was working I rechecked my code but it seemed to match the example provided above. I then check and rechecked my settings in SharePoint Designer. but everything was as I would expect.

I then turned to a colleague Ross MacKenzie and we both went through the code together but even then we were unable to establish what was going wrong. He then suggested if I have tried ‘*’ around my search criteria why not try ‘%’ as this is the wildcard in SQL. After spending a good couple of hours looking about it finally started working with the code below.

Correct Wildcard Syntax

1. WildcardFilter companyReferenceFilter = (WildcardFilter)filters[0];
2.         companyReferenceFilter.Value = String.Format("%{0}%", siteRefCode.ToString());

 

Conclusion

I hope this helps others and stops them encountering the same issues as me as while the change to the example code was small it made a massive impact and from what I could see it wasn’t documented very well.

Happy SharePointing

CAML Query Join Lists

I was in a situation where I had a parent list with some details and then a separate list for child items. The child list had a lookup to the parent list to make the association between the two. I needed a query which allowed me to search on some fields in the parent list and some fields in the child list.

Approach

When I was thinking about this I came up with a few different ideas which are each discussed below:

1. Multiple Caml Queries. One option was I could run a Caml query against the parent list to get the items which meet the parent item criteria and then do another Caml query looking for items in the child list which are associated to the items returned in the parent Caml query but also meet the filtering on the child list. Obviously this is not idea as it would involve a decent amount of code to do but would also be fairly inefficient as well.
2. Linq to SharePoint. While this would probably have been the easiest approach as you can very quickly join two lists using Linq, once you have your entry classes generated,  but I decided against this approach. My reasoning was based around a solution design perspective. I decided that while Linq to SharePoint would prove beneficial in this situation it would require adding and maintain another element in the solution thus increasing the complexity.
3. Join Caml Query. This was something I had never done before but I was aware it was one of the new features in SharePoint. I done some research and it seemed like a fairly straightforward approach so I decided to go with this.

 

Method

There are various articles out there on how to join lists via a Caml query so I won’t go into a great deal of detail only the points I found interesting.

The first thing is the Caml query will be created as you normally would be it needs to be run against the child list not the parent list.

In order to map the lists together you use the Joins property of the SPQuery object. As I mentioned there is a lot of information out there for more complicated joins but in my situation it was simply joining parent and child lists together, see example below.

Join Lists

1. StringBuilder joinDetails = new StringBuilder();
2.         joinDetails.Append("<Join Type='INNER' ListAlias='ParentListName'><Eq><FieldRef Name='ChildListLookupColumnName' RefType='Id'/><FieldRef List='ParentListName' Name='ID'/></Eq></Join>");           

 

With the join created the next step is to set up the projected fields, these are the fields from the parent list which you want to display or filter against in the where clause, see below. The Name element is any name you want to give it and this will be used in the view fields and query sections of the SPQuery object. As far as I can see the type is always Lookup. The ShowField element is the name of the field in the parent list you want to map to this new field.

Projected Fields

1. StringBuilder projectedFields = new StringBuilder();
2.         projectedFields.Append("<Field Name='ParentListField1' Type='Lookup' List='ParentListName' ShowField='Field1'/>");
3.         projectedFields.Append("<Field Name='ParentListField2' Type='Lookup' List='ParentListName' ShowField='Field2'/>");
4.         projectedFields.Append("<Field Name='ParentListField3' Type='Lookup' List='ParentListName' ShowField='Field3'/>");
5.         projectedFields.Append("<Field Name='ParentListField4' Type='Lookup' List='ParentListName' ShowField='Field4'/>");
6.         projectedFields.Append("<Field Name='ParentListField5' Type='Lookup' List='ParentListName' ShowField='Field5'/>");
7.         projectedFields.Append("<Field Name='ParentListField6' Type='Lookup' List='ParentListName' ShowField='Field6'/>");

 

You must ensure all projected fields are listed in the view fields SPQuery property. Again anyone who has done basic Caml queries will have seen this before the only consideration is when using fields from the parent list you have to use the name set in the projected fields not the name of the column in the parent list, see below.

View Fields

1. StringBuilder viewFields = new StringBuilder();
2.         viewFields.Append("<FieldRef Name='ParentListField1'/><FieldRef Name='ParentListField2'/><FieldRef Name='ParentListField3'/>");
3.         viewFields.Append("<FieldRef Name='ParentListField4'/><FieldRef Name='ParentListField5'/><FieldRef Name='ParentListField6'/>");

With the join between the lists done and the mapping for the fields in the parent list you can then write your Caml query as per normal and filter against details in the parent list, see below for a simple example Caml query.

Example Caml Query

1. sb.Append("<Where><IsNull><FieldRef Name='ParentListField1' /></IsNull></Where>");

 

The final element is to associate all these with your SPQuery object and then pass this to the list GetItems method.

Associated with SPQuery

1. var query = new SPQuery();
2.         query.Joins = joinDetails.ToString();
3.         query.ProjectedFields = projectedFields.ToString();
4.         query.ViewFields = viewFields.ToString();
5.         query.Query = sb.ToString();

 

Getting to this point did take some changing of the various Caml query properties and it was slightly frustrating but I don’t think this is particularly related to joins within Caml but more of a general issue with Caml.

Issues

There was one issue which seems particularly related to joins and this was when trying to get a DataTable of the results instead of a SPListItemCollection, see example below. This throws a NullReferenceException, see stack trace below, and it seems the only way around this is to work with the SPListItemCollection and not a DataTable

Get DataTable

1. var queryResults = list.GetItems(query).GetDataTable();  

 

Stack Trace

The error was System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.SharePoint.SPFieldMap.EnsureFieldArray()     at Microsoft.SharePoint.SPFieldMap.GetFieldObject(Int32 columnNumber)     at Microsoft.SharePoint.SPListItemCollection.GetVisibleFieldIndices(Boolean isJsGrid, Int32[]& arrVisibleFieldIndices, Int32& iVisibleFieldCount)     at Microsoft.SharePoint.SPListItemCollection.GetDataTableCore(Boolean isJsGrid)

Conclusion

I think that the join functionality in SharePoint 2010 is a useful feature, however given the fiddly nature of this along with how easy it is to accomplish the same thing using Linq to SharePoint will mean a lot of people won’t use this approach. In my circumstances it works very well and with a few extra lines of code, compared to a normal Caml Query, gives me a lot of extra functionality.

Incorrect date format when using SharePoint DateTimeControl in application page launched via ModalDialog

I was working on a project where I needed to create a custom UI for a customer in order to do some complex logic and form formatting. The form was developed as an application page which was deployed into the 14 hive and it was presented to the user via a Popup using the JavaScript ModalDialog functionality. The modal dialog was being launched from a custom web part which was in a subsite below the main site

As part of the form I needed to prompt the user with a field which would capture a date so I used the SharePoint DateTimeControl under the Microsoft.SharePoint.WebControls namespace. I have used this several times before so I didn’t think anything of it until when testing I noticed the date format was US instead of UK. I have had this before so I added some code in the page load of my application page to set the controls regional settings to match the current web regional settings, see below.

Set DateTimeControl Region

1. DtDueDate.LocaleId = Convert.ToInt32(SPContext.Current.Web.RegionalSettings.LocaleId);

 

This normally works, however when I started testing it was still not setting the region to UK, 2057, instead it was still set to US, 1033. While debugging I noticed the SPContext details where indicating the current web was the top level web not the subsite from which the modal dialog was getting launched.

This explained why the DateTimeControl was displaying in the wrong format as the regional settings at the top level site where set to US. I changed the regional settings at the top level web and the control starting displaying in UK format.

While I was happy this was working I was slightly confused as to why the context was showing the current web to be the root web not the web for the subsite. After some digging around I realised that while I was on the subsite in the main browser window when I was calling the popup I was passing in a relative URL, see examples below, and this meant the context in the popup used this URL not the location from which it was getting called.

Original URL of popup window where context is the root site

“/_layouts/ApplicationPages/Page.aspx”

Adjusted URL of popup window where context is the subsite

“/subsite/_layouts/ApplicationPages/Page.aspx”

This caused me some issues but once I got my head around it it made perfect sense so hopefully this will help others or help me in the future when I forget all about it

Generate WSP and coy to another location on post build event in Visual Studio 2010

When working on all SharePoint 2010 projects deployments are generally done via PowerShell scripts. I usually have a set PowerShell script which I copy and alter updating items such as the site URL, solution name and feature ID and I save this in a deployment folder under where the solution file is located. I then create a solution folder in Visual Studio called ‘Deployment files’ and add my PowerShell scripts to this. This way the deployment files are part of the solution and should be added into our code repository as well.

In my PowerShell scripts I don’t hard coded the WSP location I find the current directory from which the script is getting executed and then append the WSP name, see below. This means the WSP has to be in the same folder as the scripts which isn’t generally an issue but it means I have to package the project in Visual Studio and then copy the WSP from the build folder to my scripts folder. While this only takes a few minutes you need to do it each time which can get a bit repetitive.

Find Solution Location

1. $scriptpath = $MyInvocation.MyCommand.Path
2. $dir = Split-Path $scriptpath
3. $solutionName="WSPNAME.wsp"
4. $solutionPath = $dir + "\" + $solutionName

 

I decided to look into using post build commands to see if I could generate the WSP then copy it to the folder where my scripts are located. Since I have done it several times I first looked at copying the WSP file to another location. This can be done by

1. Right click on the project and select properties

1. Next select the Build Events option on the RHS
2. In the post-build event command line window enter the script below

copy $(TargetDir)$(TargetName).wsp $(SolutionDir)DeploymentFiles

1. The TargetDir should be the full path to the build folder where the WSP will be created
2. The TargetName should be the same name as the project so in my case if the project name was TestProject I would be looking for a file called TestProject.wsp
3. The SolutionDir should be the full path to the folder where the solution file is located.
4. \DeploymentFiles is simply the name of the folder located under the main solution folder where I keep my PowerShell scripts

3. Save the file and build the project

If it has worked you should get a message in the Output screen indicating the file has been copied.

This was fine except this simply copies the WSP from the bin folder to my deployment scripts folder but what I need is to ensure I am getting the latest version of the WSP so I need to generate the WSP before copying the file. As this was not something I had done before I done some research and it seems the only way to do this is to edit the project file.

I opened my project file in Notepad++, might be worth taking a backup of this first, and found the section at the end, see below, and added the suggested XML, see below.

Section After which I need to add my addition XML
<Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v10.0\SharePointTools\Microsoft.VisualStudio.SharePoint.targets" />

Required XML
<PropertyGroup>
  <BuildDependsOn>$(BuildDependsOn);CreatePackage</BuildDependsOn>
</PropertyGroup>”

What I found was as I had already added my post build event to copy the WSP there was already a section in the project file called “PropertyGroup”, see below.

At this point I wasn’t sure if I was supposed to add a new PropertyGroup element or add my BuildDependsOn section to the existing property group. I looked around on Google but there wasn’t much details on this so added the section to generate the WSP as another PropertyGroup element but I added this before my copy build event in case the order was important. When I opened Visual Studio, or if you already had it opened you will need to reload the project, and build it I found it did copy the file and it did build the WSP it did them in the wrong order. It first copied the WSP then it built a new version of the WSP.

Obviously this isn’t much use as I would always end up with an old version of the WSP. I tried adding the post build code which moves the file into the same property group, see below, but I still ended up with the same outcome where the file was moved first then a new version created.

XML with generate new WSP and move file in one property group in project file

I spent some time researching this issue on Google and found an article on how to generate a WSP in post build command and noticed that I had “<BuildDependsOn>” in my version but in the article above it was “<PostBuildEventDependsOn>”. As soon as I changed this, see new project XML below, things executed in the correct order and it first built my WSP then copied it.

Final version of project file XML

<PropertyGroup>  <PostBuildEventDependsOn>$(PostBuildEventDependsOn);CreatePackage</PostBuildEventDependsOn>
  <PostBuildEvent>copy $(TargetDir)$(TargetName).wsp $(SolutionDir)DeploymentFiles</PostBuildEvent>
</PropertyGroup>

I hope this will help others as it took me a while to get this right. As always please be careful with making changes to the project file as this can cause issues.

JQuery to see if the user is on either the display, edit or new list item form

There have been a few situations where I have wanted to run some JQuery on either a display, edit or new item form to change the layout of the pages or hide certain fields. Typically I done this by checking the URL to see if I’m on the list in question, see example below, and then running my JQuery.

1. if (window.location.href.indexOf('/Lists/TestListName/') != -1) {
2.         alert('Am in list');
3.         }

 

This works fine but it results in the above condition generally being true when you are interacting with the list i.e. looking at a particular view. I decided to take a look around to see if there was anything out there which would allow me to see if I was on one of the list forms but I didn’t find anything.

I spent some time looking at the HTML of the list forms to see if there was any way I could accurately check which page I was on. In the end I decided I would use the ribbon as part of the way I would target the type of form along with the breadcrumb. When checking the HTML I noticed the display form had an element for the ribbon which had an ID of ‘Ribbon.ListForm.Display’. From my previous experience of working with the ribbon I knew this was a relatively safe way of checking as the element wouldn’t appear on any other pages as it was particularly targeted at List Forms ribbon options, see function below.

Check if display form

1. function IsDisplayForm()
2. {
3.     var isDisplayForm = false;
4.  
5.     var ribbonLiElement = document.getElementById("Ribbon.ListForm.Display");    
6.     if ($(ribbonLiElement).length > 0) {
7.         isDisplayForm = true;
8.     }            
9.  
10.     return isDisplayForm;
11. }

 

While this worked for display forms the solution for the edit and the new forms was slightly more complex as while both of these have a ribbon element they are both ‘Ribbon.ListForm.Edit’. This meant I needed another way to be able to distinguish between a new and an edit form. After comparing the HTML I decided to use the current breadcrumb node as the text for the two types of forms is slightly different. For the new forms it is ‘New Item’ and for the edit form it is ‘Edit Item’, see code below.

Check if edit form

1. function IsEditForm() {
2.     var isEditForm = false;
3.  
4.     var ribbonLiElement = document.getElementById("Ribbon.ListForm.Edit");
5.     var currentBreadcrumbElement = $("span.s4-breadcrumbCurrentNode");    
6.     if (currentBreadcrumbElement.length > 0 && currentBreadcrumbElement.text().toLowerCase() == "edit item"
7.     && $(ribbonLiElement).length > 0) {
8.         isEditForm = true;
9.     }
10.  
11.     return isEditForm;
12. }

 

Check if new form

1. function IsNewForm() {
2.     var isNewForm = false;
3.  
4.     var ribbonLiElement = document.getElementById("Ribbon.ListForm.Edit");
5.     var currentBreadcrumbElement = $("span.s4-breadcrumbCurrentNode");        
6.     if (currentBreadcrumbElement.length > 0 && currentBreadcrumbElement.text().toLowerCase() == "new item"
7.     && $(ribbonLiElement).length > 0) {
8.         isNewForm = true;
9.     }
10.  
11.     return isNewForm;
12. }

Conclusion

The easy option for this may have been to simply check the URL for NewForm.aspx, DispForm.aspx  or EditForm.aspx but I decided it would be better not to use this approach as it is possible to create custom versions of these forms which would have different names.

The one area I have tested where this will not work is if you create a custom InfoPath form using the option in the list ribbon so this is worth keeping in mind.

The other limitation is I have hardcoded the text I’m testing against so this will only work in English but it could be change for other languages.

As always please check this works on a development environment and is tested thoroughly before using. Fingers crossed this will help some people.

InfoPath 2010 Security Exceptions

While working on a recent project I had to create an InfoPath 2010 form which took some parameters and queried CRM to retrieve additional details. At first I tried to setup a new Web Service data connection but I kept getting errors so instead decided to handle it all in code on the page load.

Anyone who has developed an InfoPath form with code behind knows it can be a very frustrating process to write the code and debug issues. In addition the deployment process can take a while which results in the whole processes being very time consuming. To get around this I decided there was no reason why I couldn’t create a Windows Forms application and use it as a test harness for the code.

I created a new Windows Forms Application, designed my UI, added my CRM references and implemented the code. After a few iterations of testing I was successfully able to pull information back from a development CRM instance. I then moved my code into the InfoPath form, made the required changes and deployed it. As with all development I was hopeful this would work first time around but it didn’t . I looked at the ULS logs on the SharePoint server and noticed the error below.

Error

System.Security.SecurityException: Request for the permission of type ‘System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.

 

After much looking around and testing I was unable to establish what was causing the issue. I checked my original application and it worked and compared the code with the InfoPath version but I was unable to see any major differences. After checking the code and when I was happy it was not a code issue my attention turned to what security issues could be causing this error. Immediately it suddenly occurred to me that I hadn’t changed the Security Level option in the form settings, steps to adjust InfoPath Security settings. To be honest this probably should have been my first port of call but fingers cross this article will help someone else or at least act as a reminder for me

Adjust InfoPath 2010 Security Settings

Only set forms you have developed to Full Trust as doing this can leave you open to potential security issues.

• Open form in design mode
• Click on the ‘File’ tab
• In the ‘Form Information Section’ click on ‘Advanced form options’

 

• In the new window click on ‘Security and Trust’ in the LHS
• Deselect ‘Automatically determine security level’
• Select the ‘Full Trust’ option

• Click ok
• Publish and redeploy your form

Archiving documents timer job

Whilst working a recent project I had to create a timer job which moved some InfoPath forms from an archive document library to a location which was dynamically generated depending on the forms modified date. I am not going to cover the basics on how to create a timer job as Andrew Connell already has a very good article on how to create a custom timer job.

In this article I will focus on how to move a InfoPath form but it could equally to word documents, PDF, etc. The same could apply to list data but it will require some code changes.

As with all my projects I first mapped out what steps, see below.

1. Get configuration data
2. Open the site in which my forms are stored
3. Get the document library
4. Get the items which meet a certain criteria
5. Iterate through the items and for each item perform the following
1. Get the form modified date
2. Check and see if a document library already exists for the current year and if not create one
3. In the document library check if a folder for the month exists and if not create it
4. Copy the form to the new location
5. Set the metadata to be copied across. One consideration that may apply to others is version, however this was not relevant for me.
6. Delete the original

I will assume you have read the post above by Andrew Connell so I will jump in once we have already got our site collection.

Point 1

As with all projects you want to minimise the amount of data which is hard coded as this reduces the need for additional deployments when certain environment specific variables change. There are a few options on where to store these but I decided to store mine in the web.config of the web application. This requires a few steps to read the data from the web.config so I created a helper method which returned a custom class which is used to store the variables. 

1. ConfigurationData config = GetConfigurationData(webApplication.Name);

 

1. /// <summary>
2.         /// Method to get the configuration data from the web.config
3.         /// </summary>
4.         /// <param name="webAppName">A string of the web application name</param>
5.         /// <returns>An ConfigurationData object with the configuration details</returns>
6.         private ConfigurationData GetConfigurationData(string webAppName)
7.         {
8.             ConfigurationData configData = new ConfigurationData();
9.             Configuration config = WebConfigurationManager.OpenWebConfiguration("/", webAppName);
10.             if (config != null)
11.             {
12.                 if (config.AppSettings.Settings["Property1"] != null)
13.                 {
14.                     configData.Property1 = config.AppSettings.Settings["Property1"].Value;
15.                 }
16.  
17.                 if (config.AppSettings.Settings["Property2"] != null)
18.                 {
19.                     configData.Property2 = config.AppSettings.Settings["Property2"].Value;
20.                 }
21.  
22.                 if (config.AppSettings.Settings["Property3"] != null)
23.                 {
24.                     configData.Property3 = Int32.Parse(config.AppSettings.Settings["Property3"].Value);
25.                 }
26.             }
27.  
28.             return configData;
29.         }

Points 2, 3 and 4

With the configuration data retrieved from the web.config the next few steps are very straightforward to anyone who has done any SharePoint development.

1. //open site
2.             using (var site = new SPSite(siteCollection.Url))
3.             using (var web = site.OpenWeb(config.Property1))
4.             {                
5.                 web.AllowUnsafeUpdates = true;
6.                 try
7.                 {
8.                     SPList list = null;
9.                     try
10.                     {
11.                         //get library
12.                         list = web.Lists[config.Property2];
13.                     }
14.                     catch (ArgumentException ae)
15.                     {
16.                         LogDetails(String.Format("There was a problem gettting the archive list, the error was {0}", ae.ToString()));
17.                         return;
18.                     }
19.  
20.                     if (list == null)
21.                     {
22.                         LogDetails("Unable to get the archive list");
23.                         return;
24.                     }                    
25.  
26.                     var query = new SPQuery();                    
27.                     query.Query = "YOUR CAML QUERY";
28.  
29.                     LogDetails(String.Concat("CAML Query is ", query.Query));
30.  
31.                     //get items
32.                     var listItems = list.GetItems(query);
33.                     var listItemCount = listItems.Count;
34.                     if (listItems == null || listItemCount == 0)
35.                     {
36.                         LogDetails(String.Concat("There were no items returned by the query {0}", query.Query));
37.                         return;
38.                     }
39.  
40.                     LogDetails(String.Concat("Got items ", listItemCount.ToString()));

Point 5

At this stage we now have a list of all items items which meet the relevant archive criteria but for obvious reason the actual logic has been removed from this blog. My next step was to iterate through all items and move them to the appropriate location. At first I used a foreach loop but this didn’t work as I was adjusting the item collection and this caused a runtime error. Next I tried a for loop using the count of the number of items. While this seemed to work I found it was only iterating through half of the list and after the half way point I was getting an error saying “Specified argument was out of the range of valid values.”. For example if the count of items was 10 it could loop through items 1-5 but as soon as it reached 6 it give the error above. To get around this I changed the code to start at the last item in the count and work backwards i.e. 10, 9, 8. To keep the code contained I separated the main functionality out into a few different methods.

Loop through items

1. //loop through items moving them
2.                     for (int itemNumber = listItemCount; itemNumber > 0; itemNumber–)
3.                     {
4.                         try
5.                         {
6.                             LogDetails(String.Concat("Start item ", itemNumber.ToString()));
7.                             SPListItem listItem = listItems[itemNumber -1];
8.                             if (listItem == null)
9.                             {
10.                                 LogDetails(String.Format("There was a problem getting the list item at index {0} returned by the query {1}",
11.                                     itemNumber.ToString(), query.Query));
12.                             }
13.                             else
14.                             {
15.                                 MoveItem(listItem, web);
16.                             }
17.                         }
18.                         catch (Exception ex)
19.                         {
20.                             LogDetails(ex.ToString());                            
21.                         }                                                                        
22.                     }

 

This function takes an item, gets the modified date and passes this to another helper function which gets the folder which the item has to be moved to. Next it builds up the URL to where the item has to be copied to. The item is then moved but because this method doesn’t return the SPFile object I had to then get it from the destination folder. Once I had the new item I then set some properties to ensure metadata is retained as otherwise the created and modified details would be incorrect. Finally the original item is deleted.

Function for each item

1. /// <summary>
2.         /// Method to perform the move of an individual item
3.         /// </summary>
4.         /// <param name="listItem">An SPListItem of the item to be moved</param>
5.         /// <param name="web">An SpWeb object which represents the web in which the item is located</param>
6.         private void MoveItem(SPListItem listItem, SPWeb web)
7.         {           
8.             DateTime modifiedDate = DateTime.Parse(listItem[SPBuiltInFieldId.Modified].ToString());            
9.             SPFolder destinationLocation = GetDestinationLibrary(modifiedDate, web);            
10.             string destinationLocationURL = String.Format("{0}/{1}/{2}", web.Url, destinationLocation.Url, listItem.File.Name);           
11.  
12.             try
13.             {
14.                 listItem.File.CopyTo(destinationLocationURL);
15.             }
16.             catch (Exception ex)
17.             {
18.                 LogDetails(String.Concat("There was a problem copying the file to the new URL, the error was {0} ",
19.                     ex.ToString()));
20.                 throw;
21.             }
22.  
23.             SPFile file = null;
24.             try
25.             {
26.                 file = destinationLocation.Files[listItem.File.Name];
27.             }
28.             catch (Exception ex)
29.             {
30.                 LogDetails(String.Format("There was a problem getting the new file {0} in location {1}, the error was {2}",
31.                     listItem.File.Name, destinationLocation.Url, ex.ToString()));
32.                 throw;
33.             }
34.  
35.             if (file == null)
36.             {
37.                 LogDetails(String.Format("Unable to find file {0} in new location.",
38.                     listItem.File.Name));
39.                 throw new NullReferenceException("Unable to find new file in destination location");
40.             }
41.  
42.             var fileItem = file.Item;
43.  
44.             try
45.             {
46.                 /*since we are running with elevated permissions we need to set the
47.                  * author and editor back to the details from the original item
48.                  * otherwise it will appear as system account                                         *
49.                  * */
50.                 fileItem[SPBuiltInFieldId.Author] = listItem[SPBuiltInFieldId.Author];
51.                 fileItem[SPBuiltInFieldId.Created] = listItem[SPBuiltInFieldId.Created];
52.  
53.                 fileItem[SPBuiltInFieldId.Editor] = listItem[SPBuiltInFieldId.Editor];
54.                 fileItem[SPBuiltInFieldId.Modified] = listItem[SPBuiltInFieldId.Modified];
55.                 fileItem.UpdateOverwriteVersion();
56.  
57.                 listItem.Delete();
58.             }
59.             catch (Exception ex)
60.             {
61.                 //log error and move on to next record
62.                 LogDetails(String.Format("There was a setting the file properties for item {0} the problem was {1}",
63.                     listItem.Title, ex.ToString()));
64.                 throw;
65.             }
66.         }

As mentioned above the previous snippet calls this GetDestinationLibrary method which uses the modified date to see if a document library exists for the year in the same web and if not it creates one. Next it calls a separate function which performs the same idea but this one uses the month and creates a folder inside the document library.

Get document library

1. /// <summary>
2.         /// Method to get the URL of document library which the list item should be added to. If the library is not found one is created.
3.         /// </summary>
4.         /// <param name="modifiedDate">A DateTime of the last modified date of the item</param>
5.         /// <param name="web">An SPWeb object representing the web which contains the items</param>
6.         /// <returns>An SPFolder of the location the item should be copied to</returns>
7.         private SPFolder GetDestinationLibrary(DateTime modifiedDate, SPWeb web)
8.         {
9.             string destinationLibraryURL = String.Empty;
10.             string year = modifiedDate.Year.ToString();
11.  
12.             SPList desintationDocumentLibrary = null;
13.  
14.             try
15.             {
16.                 //get library
17.                 desintationDocumentLibrary = web.Lists[year];
18.             }
19.             catch (ArgumentException)
20.             {
21.                 desintationDocumentLibrary = CreateDocumentLibrary(year, web);
22.             }
23.  
24.             SPFolder monthlyFolder = GetMonthlyFolder(CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(modifiedDate.Month), desintationDocumentLibrary);
25.  
26.             return monthlyFolder;
27.         }

 

Create monthly folder

1. /// <summary>
2.         /// Method to try and find the folder representing the month given a month name. If a month is not found one is created.
3.         /// </summary>
4.         /// <param name="monthlyName">A string of the month name</param>
5.         /// <param name="list">An SPList which is the list the folder will be created in</param>
6.         /// <returns>An SPFolder representing the folder to which we should move the item</returns>
7.         private SPFolder GetMonthlyFolder(string monthName, SPList list)
8.         {
9.             SPFolder monthlyFolder = null;
10.             //loop through to see if the folder exists
11.             foreach (SPListItem folder in list.Folders)
12.             {
13.                 if (folder.Name.Equals(monthName, StringComparison.CurrentCultureIgnoreCase))
14.                 {
15.                     monthlyFolder = folder.Folder;
16.                     break;
17.                 }
18.             }
19.  
20.             //if we don't have it create it
21.             if (monthlyFolder == null)
22.             {
23.                 monthlyFolder = list.RootFolder.SubFolders.Add(monthName);
24.                 monthlyFolder.Update();
25.             }
26.  
27.             return monthlyFolder;
28.         }

 

Conclusion

Putting all this together hopefully some people will find this an interesting article with some useful ideas. As always if anyone who reads this can think of any improvements I am always happy to discuss them.

Lastly if you do use this code as a base for your own project please ensure this is tailored to meet our own solution and has been properly tested on a development environment before being deployed to a live farm.

Filtering Data from a SharePoint List in SSRS Reports

I have created a few SSRS reports which pull data from SharePoint lists and while its relatively easy I have come across the same problem a few times. Every time I try and filter my results using dates it never seems to work as I expect.

In this latest scenario I had a custom SharePoint List which had a column for the item start date. All I wanted to do was present the user with 2 date pickers which allow them to set a start and an end date. I then wanted to execute a query using these to return all items where the start date was in between the selected dates.

I generated my CAML query using U2U CAML Query Builder and it worked as expected with some hardcoded dates.

Next I opened Visual Studio and created a new Report Project. I then created a new report using the wizard to help setup a new DataSource of type ‘Microsoft SharePoint List’. I entered the query using the one generated in U2U leaving the values as hard coded to test it was working.

After formatting the report I ran it and all was well. Next I decided to create two new parameters one for the start date and one for the end date. I set the default values to be the first day of the month for the start date and the last day of the month for the end date, see formula for default values below.

Start date default value: =CDate(DateSerial(Year(Now), Month(Now), 1))
End date default value: =DateSerial(Year(Now), Month(Now)+1, 0)

I checked and these displayed on the report preview with the correct default values. At this point I was thinking everything was going too smoothly and I was right. My final step was to update the query to add in the values from the date pickers. I started by adding parameters to the dataset properties and mapped these to my report parameters, see figure 1.

Figure 1

Next I went to my query and removed the hardcoded, see figure 2, values and added what I think is the correct way to include parameters, see figure 3.

Figure 2

Figure 3

As soon as I make this change my report stops returning any results. I have spent time looking around and playing with passing different formats of the dates but no matter what I do it simply doesn’t return any values.

For now as a workaround I have managed to get my report working by not including any filters in my query but by adding a filter on the dataset, see figure 4. While this works I am aware this will probably return all items from the list and then show or hide the rows as needed. If anyone can help with a better solution I would appreciate it.

Figure 4

PowerShell script for updating workflows

The other day when working on a project I needed to update some existing Visual Studio workflows. I had a Visual Studio project which contained a variety of workflows which were used across several SharePoint sites. While keeping all the workflows in one solution works it is not ideal for deployment as you can’t auto associate the workflows as part of the deployment.

I decided to try and automate the deployment so started thinking what my options were. Being a developer my first thought was code in a feature receiver on activated and deactivated, however I decided that this would be another opportunity to practice some PowerShell. This gave me the flexibility of changing the script if need be without having to alter code.

Before starting to write the script I went through in my head what it need to do and came up with the following:

1. Get the list to which the workflow should be deployed
2. Check to see if the workflow was already deployed
1. If it was already deployed check to see if there were any running instances
1. If there were running instances set the workflow to not allow any new instances but allow existing ones to finish.
2. If not remove the workflow and go to step 3
2. If not move on to step 3
3. Associate the new version of the workflow to the list

 

With these steps in mind I started to create my script. I will cover each of the above and go through the script required to achieve the goal.

Point 1

First I wanted to get the list the workflow was associated with. This is very easy and can be accomplished by getting the web object then using that to get the list object much the same way you would if you were writing C#

Get Workflow List

1. Write-Host 'Get web'
2. $hrWeb = Get-SPWeb $siteURL
3.  
4. Write-Host 'Get Lists'
5. $list = $hrWeb.Lists[$listName]
6. $taskList = $hrWeb.Lists[$taskListName]
7. $workflowHistoryList = $hrWeb.Lists[$historyListName]

 

Point 2

With the list the next task was to see if the workflow is currently deployed to the list. This presented a problem as my plan was to automatically deploy the workflow with a name and the date of the deployment i.e. “Workflow 22/12/2011’. This caused problems as I wouldn’t know the last deployment date so I had to use a like comparison on the name. I wasn’t comfortable just using a like comparison as I felt this wasn’t always going to get me what I wanted so I added an additional check to validate the name of the workflow but also if it was of the correct base workflow type.

Try and find workflow

1. Write-Host 'Get base template'
2. $basetemplate = $hrWeb.WorkflowTemplates.GetTemplateByName($workflowTemplateName,$culture);
3. Write-Host $basetemplate.Id
4.  
5. #set up variable to hold workflow instance if we find it
6. $existingWorkflow = $null
7.  
8. Write-Host 'Get workflow association'
9. #loop through all associations on list
10. foreach($tempWorkflowAssociation in $list.WorkflowAssociations)
11. {
12. #check if the base template id matches the base template of the current WF associaton
13. #in additon check the name of the current WF association against the one we are interested in
14. if($tempWorkflowAssociation.BaseTemplate.Id -eq $basetemplate.Id -and $tempWorkflowAssociation.Name -like $workflowName +"*" )
15. {
16. $existingWorkflow = $tempWorkflowAssociation
17. break
18. }
19. }
20. #check we have a workflow
21. if($existingWorkflow -ne $null)
22. {

 

Point 2.1

As you can see the last line in the previous snippet makes sure we have a workflow returned so at this point we know there is a version of the workflow we are interested in currently deployed to the list. Now I need to know if there are any running instances for this.

Check running instances

1. Write-Host 'Got workflow associated with list'
2. if($existingWorkflow.RunningInstances -ge 0)
3. {

 

Point 2.1.1

If there are running instances these must continue working until they are complete but not allow any new instances of this workflow to be initiated. This can be done by setting the ‘No new instances’ option through the workflow settings in the UI or by the following command.

No new instances

1. Write-Host 'There are running instances so set to allow no new running instances'
2. $existingWorkflow.set_Enabled($false)
3. $list.UpdateWorkflowAssociation($existingWorkflow)

 

Point 2.1.2

If there are no running instances then we want to remove the current version. This can be done by the command below

Remove workflow

1. Write-Host 'No running instances so remove'
2. $list.RemoveWorkflowAssociation($existingWorkflow)

 

Point 3

We are now in a position where we can try and associate the new version of the workflow to the list. As I mentioned above when attaching the new workflow I am adding it with the name and the date so I first build up the name then create an new workflow association object the add it to the list.

Associate new workflow

1. Write-Host 'Create workflow association details'
2. $date = Get-Date
3. $workflowName = $workflowName + " " + $date.ToShortDateString()
4. $newWorkflow=[Microsoft.SharePoint.Workflow.SPWorkflowAssociation]::CreateListAssociation($basetemplate, $workflowName,$taskList,$workflowHistoryList)  
5.  
6. $newWorkflow.AllowManual = $allowManualStart
7. $newWorkflow.AutoStartChange = $autoStartChange
8. $newWorkflow.AutoStartCreate = $autoStartCreate
9.  
10. Write-Host 'Add workflow to list'
11. $list.AddWorkflowAssociation($newWorkflow)

At this point the new version of the workflow should be attached to the list. I started creating this with some hardcoded values but then extracted it out into a reusable function. The full function is below.

Fully assocaition function

1. function AssocaiteWorkflow([string]$siteURL, [string]$listName, [string]$taskListName, [string]$historyListName,
2.     [string]$workflowTemplateName, [string]$workflowName, [bool]$allowManualStart, [bool]$autoStartChange, [bool]$autoStartCreate)
3. {
4.  
5. Write-Host 'Start WF assocaition for ' $workflowName ' on list ' $listName ' and site ' $siteURL
6.  
7. #get culture
8. $culture= Get-Culture
9.  
10. Write-Host 'Get web'
11. $hrWeb = Get-SPWeb $siteURL
12.  
13. Write-Host 'Get Lists'
14. $list = $hrWeb.Lists[$listName]
15. $taskList = $hrWeb.Lists[$taskListName]
16. $workflowHistoryList = $hrWeb.Lists[$historyListName]
17.  
18. Write-Host 'Get base template'
19. $basetemplate = $hrWeb.WorkflowTemplates.GetTemplateByName($workflowTemplateName,$culture);
20. Write-Host $basetemplate.Id
21.  
22. #set up variable to hold workflow instance if we find it
23. $existingWorkflow = $null
24.  
25. Write-Host 'Get workflow association'
26. #loop through all associations on list
27. foreach($tempWorkflowAssociation in $list.WorkflowAssociations)
28. {
29. #check if the base template id matches the base template of the current WF associaton
30. #in additon check the name of the current WF association against the one we are interested in
31. if($tempWorkflowAssociation.BaseTemplate.Id -eq $basetemplate.Id -and $tempWorkflowAssociation.Name -like $workflowName +"*" )
32. {
33. $existingWorkflow = $tempWorkflowAssociation
34. break
35. }
36. }
37. #check we have a workflow
38. if($existingWorkflow -ne $null)
39. {
40. Write-Host 'Got workflow associated with list'
41. if($existingWorkflow.RunningInstances -ge 0)
42. {
43. Write-Host 'There are running instances so set to allow no new running instances'
44. $existingWorkflow.set_Enabled($false)
45. $list.UpdateWorkflowAssociation($existingWorkflow)
46. }
47. else
48. {
49. Write-Host 'No running instances so remove'
50. $list.RemoveWorkflowAssociation($existingWorkflow)
51. }
52. }
53. else
54. {
55. Write-Host 'No workflow associated with list'
56. }
57.  
58. Write-Host 'Create workflow association details'
59. $date = Get-Date
60. $workflowName = $workflowName + " " + $date.ToShortDateString()
61. $newWorkflow=[Microsoft.SharePoint.Workflow.SPWorkflowAssociation]::CreateListAssociation($basetemplate, $workflowName,$taskList,$workflowHistoryList)  
62.  
63. $newWorkflow.AllowManual = $allowManualStart
64. $newWorkflow.AutoStartChange = $autoStartChange
65. $newWorkflow.AutoStartCreate = $autoStartCreate
66.  
67. Write-Host 'Add workflow to list'
68. $list.AddWorkflowAssociation($newWorkflow)
69. }

 

You can then call this in the standard way i.e.

Call function

1. AssocaiteWorkflow "http://sharepoint" "Test List Name" "Tasks List Name" "Workflow History List Name" "Visual Studio Workflow Name" "Workflow Instance Name" $true $true $true

 

As with all PowerShell scripts while they are very useful please always test them on a development environment before running them on live. As always I can’t be responsible for any issues that arise so if you use this you do so at your own risk.