Daryl Rasmussen's Not-so-random Thoughts about SharePoint Here in my world

Archive for the ‘SharePoint 2010’ Category

Intro to Declarative Workflows

On March 24, 2016 I presented at the Calgary SharePoint and Office 365 Users Group on the subject of Declarative Workflows in SharePoint.  A copy of the presentation can be found on their site here.

Topics included what a workflow is, why you might want to consider setting some up in your SharePoint environment (from 2007 to 2016, whether on premise or in O365), and some tricks and tips that I have discovered over the course of the last 8 years of building declarative WF solutions for multiple clients.

The deck also includes some links to a few more “complex” tasks such as how to get a value from another list based on a value in this workflow (lookups), create a workflow with elevated permissions by using the SharePoint 2013 Workflow platform, and how to Start a SharePoint 2010 Workflow Within a SharePoint 2013 Workflow.

Tweaking the built-in Survey List

My client wanted to be able to create their own survey that respondants could fill out on-line, and the built-in survey list in SharePoint seemed a likely candidate for doing this (though we did briefly toy with InfoPath as an option as well).

There were a few things about that built-in survey that needed to be tweaked.  Here’s some links that proved useful:

Create List Instance without List Definition

This worked (mostly):


But:  in SP Here at my client’s office, I had to muck about with the Target location (step 6) for the Schema.xml file.   I ended up setting it to the name of the visual studio project, not the name of the list contained there-in.  Also in step 6, file I edited was SharePointProjectItem.spdata, not SharePointItem.data.

Finally, I had to rename the .stp to a .cab not a .zip before I could open the manifest.

In case the link above ever breaks (and also so I have the instructions handy for myself in future), here’s the content of it, replicated for posterity.  Credit is hereby given to the original author, Saurabh Khatri, for a great time saving idea.

Assumtions: I am assuming that we have already created SharePoint Project in Visual Studio 2010.

Preparing Solution Structure

1. In your visual studio instance, Right Click on your sharepoint project and click on Add > New Item.
2. From the Wizard, choose the SharePoint List Instance.
3. a) Give some name your List Instance(Display Name), I will give this ‘MyNewCustomList’
b) Choose a List Template, You can choose any List Template, and choose a List Instance, Let me choose Custom List to keep it simple.
c) Give List a URL, “Lists\MyNewCustomList”
d) Click ok to close the wizard.
4. After step 3, we will be having a SharePointItem  ‘MyNewCustomList’ of type List Instance with a element.xml file, instantiating it from TemplateType=100, that will create a custom list with a Title Column in it. But we wanted to add the fields to this custom schema.(In SharePoint 2010 we can create a List Instance without creating a ListTemplate by specifying CustomSchema property for ListInstance. http://msdn.microsoft.com/en-us/library/ms476062.aspx

<ListInstance Title=”MyNewCustomList” OnQuickLaunch=”TRUE” TemplateType=”100″ Url=”Lists/MyNewCustomList” Description=”My List Instance” CustomSchema=”MyNewCustomList\Schema.xml” />

5. Now Lets Add a Schema.xml File to your SharePointItem.
6. Also include this file into your SharePointItem.data file.
This is how we do it. Click on the your ‘MyNewCustomList’ in Solution Explorer in Visual Studio. Right click Open Folder in Windows Explorer. Open SharePointItem.Data in notepad

<ProjectItemFile Source=”Schema.xml” Target=”MyNewCustomList\” Type=”ElementFile” />

We are done with Solution Preparation steps.

We will now create a Custom List in SharePoint with ”MyNewCustomList”. Go to List Settings and Add the columns, as per the requirement. After doing with all changes whether it is a addition of List Column, Creating a View, addition of some content Type etc.

1. Save this list as Template. Go to Template Gallery, and Download your list template. This file will be a .stp file.\
2. Change the File Extenstion to .zip.
3. Open the zip file, it will contain a manifest.xml
4. Open Manifest.xml file in some nice editor like notepad++.
5. Carefully, Locate node “<List>” in this xml. Copy this entire section of <List>…</List> and now paste it in your schema.xml file we created in above steps.

Few Final Modifications and we are done in Schema.xml file.

1. Remove the name attribute containing List GUID of your previous List.

<List Name=”SomeGUid” …> remove this Name attribute, we don’t need this in our schema

2.Replace the following  nodes <View>

<View BaseViewID=”1″ Type=”HTML” Level=”1″ ContentTypeID=”0x” WebPartZoneID=”Main” OrderedView=”TRUE” DisplayName=”All Links” DefaultView=”TRUE” MobileView=”TRUE” MobileDefaultView=”TRUE” SetupPath=”pages\viewpage.aspx” ImageUrl=”/_layouts/images/links.png” Url=”AllItems.aspx”>

Also replace the Forms Node with the below one.


<Form Type=”DisplayForm” Url=”DispForm.aspx” SetupPath=”pages\form.aspx” WebPartZoneID=”Main” />
<Form Type=”EditForm” Url=”EditForm.aspx” SetupPath=”pages\form.aspx” WebPartZoneID=”Main” />
<Form Type=”NewForm” Url=”NewForm.aspx” SetupPath=”pages\form.aspx” WebPartZoneID=”Main” />


Great we are done, Now we can include this ListInstance in a feature and create a package. Activating that feature will create a ListInstance.

The first client I used this technique at was on SharePoint 2010.  It worked as well for me in SharePoint 2013 at the next client as well.

Coding External Content Types

I was trying to follow the example given here:


However, it didn’t work for me. This link looked slightly more promising:


But I still was having trouble figuring out in SP Here whether the “External System” shown in Central Admin on the Manage BDC Services page was the LOB Name, or the LOB instance name, or the Model Name, and therefore, confused about what to put in the properties in our specific instance in order to get the Holiday list working from a client’s Dynamics (DAX) based database on the above examples.

To make matters worse, both of the above links, and the post here


say that the format for the EntityNamespace property is supposed to have “dot” notation.

Trial and error and a bit of dogged persistence, and I finally figured it out on my own. The final post above needs 1 correction to make it work (I replied to the author’s post on-line in 2012 when I encountered the issue, but as of Feb 2015, my comments are still not visible there). It took me a few hours to figure this out, but the fact that the documentation here does not mention any prefix or dot-notation should have been my first clue. Basically, you need to change

listDataSource.SetProperty(SPListDataSource.BDCProperties.EntityNamespace, “YourBdc.EntityNamespace”);


listDataSource.SetProperty(SPListDataSource.BDCProperties.EntityNamespace, “EntityNamespace”);

In other words do not prefix the EntityNamespace with “YourBdc.”, or you will get an error that says: Entity (external Content Type) cannot be found with Namespace= “YourBdc.EntityNamespace” , Name = “YourBdcEntity” The error is thrown on the call to list.Add.

Why the “Next” Button Doesn’t Work for Me in SharePoint 2010 Surveys

Recently I was helping a client put together a survey in SharePoint 2010, showing them all the various types of questions, and things you can do with them.  They were having a hard time visualizing it, so I ended up building their survey for them so they could see the overall effects in their desired question set.  About 65 questions in all, and to my dismay the “Next” button (which shows up at the top and bottom of the form if you have either a Page Separator field or if you implement branching logic in your questions) wasn’t working.

The first “page” of the survey would load, but when the “Next” button was clicked, that page would just re-load.  It would NOT move on to the next “page” (after the Page Separator or branch).  I searched everywhere on the internet for a possible reason.  Nada.  Nothing.  I can’t believe I’m the only person who ever encountered this.  And I certainly can’t show it to the client in this state.  WTF?

I thought maybe the client had re-ordered the questions in the branching logic, resulting in some kind of circular reference.  I painstakingly went through every question (hint – click the “Change the order of the questions” link from the Survey Settings page to see a summary of what is branching where).  No luck.  Everything seemed fine.

I then thought perhaps the use of special characters like quotes and apostrophes in the question title were mucking things up.  So I removed all of them (Changing questions like “Please enter any comments regarding the president’s address” to something like “Please enter any comments regarding the presidential address”).  No joy.  Still it would not leave page 1.

Then I thought maybe my survey list was corrupt.  Hey, lets face it.  Sometimes that happens with SharePoint.

So I rebuilt it (all 65 questions of it!) from scratch, stopping after every page separator question was added, and after every branch section was set up to test the new survey.  63 questions in, and everything is looking good.

The answer came when I added my 64th question (which was a plain text field for people to enter their phone number if they wanted to be contacted regarding their submission).  The fly in the ointment was that it was using the column validation capabilities of SharePoint to ensure the phone number was entered in a standard format.

The field was called “Optional – Phone” and had the following Column Validation formula:

=AND(LEN([Optional – Phone])=14,IF(ISERROR(FIND(“(“,[Optional – Phone],1)),FALSE,(FIND(“(“,[Optional – Phone])=1)),IF(ISERROR(FIND(“)”,[Optional – Phone],5)),FALSE,(FIND(“)”,[Optional – Phone],5)=5)),IF(ISERROR(FIND(” “,[Optional – Phone],6)),FALSE,(FIND(” “,[Optional – Phone],6)=6)),IF(ISERROR(FIND(“-“,[Optional – Phone],10)),FALSE,(FIND(“-“,[Optional – Phone],10)=10)),IF(ISERROR(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))),FALSE,AND(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))>1000000000,1*MID([Optional – Phone],2,3)<>911,1*MID([Optional – Phone],7,3)<>911,1*MID([Optional – Phone],7,3)<>555)))

It also had the following User Message:

Please enter the phone number in the format (###) ###-####

Do you see the problem?  I didn’t at first, but here’s what I now believe:

When the user is finished answering the questions on Page 1 and clicks next, the above validation rule fires, and fails.  The “Optional – Phone” field is on a different page that the user has not gotten to yet, so of course they haven’t filled the “Optional – Phone” field in yet.  And even though the field is not mandatory, the first part of the validation rule says that the length must be 14 characters.

The length at this point is still zero.  The User Message is probably being displayed under the “Optional – Phone” field, but since the field isn’t visible on the current page, the user gets no feedback and the “Next” button just reloads the current page.

Of course this is just a theory, because I can’t find anything in Microsoft’s documentation to say that this is what’s happening, however when I change the Column Validation formula to allow either zero or 14-character length strings, the problem goes away, so it seems a valid assumption.

For those who are interested, here’s the corrected formula:

=OR(LEN([Optional – Phone])=0, AND(LEN([Optional – Phone])=14,IF(ISERROR(FIND(“(“,[Optional – Phone],1)),FALSE,(FIND(“(“,[Optional – Phone])=1)),IF(ISERROR(FIND(“)”,[Optional – Phone],5)),FALSE,(FIND(“)”,[Optional – Phone],5)=5)),IF(ISERROR(FIND(” “,[Optional – Phone],6)),FALSE,(FIND(” “,[Optional – Phone],6)=6)),IF(ISERROR(FIND(“-“,[Optional – Phone],10)),FALSE,(FIND(“-“,[Optional – Phone],10)=10)),IF(ISERROR(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))),FALSE,AND(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))>1000000000,1*MID([Optional – Phone],2,3)<>911,1*MID([Optional – Phone],7,3)<>911,1*MID([Optional – Phone],7,3)<>555))))

After many days of cursing and frustration, the Survey is working again.

The moral of this story is  “Be careful with Column Validation in Survey Lists”.

That’s SP Here.  I would bet a case of doughnuts that the same is true in MOSS 2007, SP 2013, and O365.

Colour your Webpart in SP 2010 with the Theming Engine

Recently I was asked to take over a webpart under development from another programmer.  The goal was to provide a Q&A list that could be displayed on a page, “Accordian” style, so that when the end user clicks on a question, the answer appears below it, similar to how the jQuery UI “Accordian” widget behaves (see http://jqueryui.com/accordion/).

As the client consisted of multiple companies under a single corporate umbrella, and each company had its own SP site with a different look and feel, they wanted the Questions displayed in a colour that complemented the current site’s colour scheme.

Disclaimer: I am Canadian. We spell the word “colour” with a “U” in it. As I’m writing this blog mostly for my own future reference, anyone not familiar with this convention should substitute the word “color” whenever they see the word “colour” throughout this post.  I will use “color” only when it is a the key word (for example, in cascading stylesheets), but not in everyday language.  For those who care, “behaviour”, “harbour” and “neighbour” are also spelled with a “U” in Canada (and I believe the UK).

Ok, so the webpart I was given to work on (remember, it was already half-written by someone else) constructs a set of HTML Div tags using a string builder in the “CreateChildControls” event, and then appends the resulting string to the webpart’s output stream, something like this:

StringBuilder sb = new StringBuilder();
Literal litContent = new Literal;
sb.Append(“<div class=’ms-rtestate-field’ style=’display: inline;’>”); 
sb.Append(“<div class=’faq-content accordion’>”);
sb.Append(“<div class=’faq-holder’>”);
// get list content and create data tags here via a call to loadAccordianList()
sb.Append( “</div>” );
litContent.Text = sb.ToString();

The loadAccordianList function retrieves data from a SharePoint list.  Each list item is converted to an html description list set of tags:  the Header (where the question will be posted) is in the “dt” tag, and the Content (containing the answer to the question), is in the “dd” tag.  Each pair is wrapped in the requisit “dl” tag.  The “dt” tag has a class of “acc-item” assigned at this point as well.

A similified version of the loadAccordianList() function follows.  ProvisioningList is a string property of the webpart indicating the name of the list in SharePoint to get the data from.  Of course, in the real procedure, error checking and handling exists for such things as list not found, list empty, and so on.  It has been omitted here to keep the code simple, and because what we are mostly interested in for this discussion is the html tags being generated, not the contents that is being placed within them and how.  The actual code for retrieving the field values for the header and content of each item has similarly been ommitted from the sample below and replaced with comments indicating what is being retrieved and when.

private string loadAccordionList()
  StringBuilder sb = new StringBuilder();
  SPWeb web = SPContext.Current.Web;
  SPList list = web.Lists.TryGetList(ProvisioningList);
  SPQuery qry = new SPQuery();
  qry.Query = @”<OrderBy><FieldRef Name=’SortOrder’ Ascending=’TRUE’/></OrderBy>”;
  SPListItemCollection nItems = list.GetItems(qry);
  foreach (SPListItem item in nItems)
      sb.Append(“<dt class=’acc-item’>”);
      // Append Header (question text) here
      sb.Append(“<dd class=’scriptHideOnLoad’>”);
      // Append Content (answer text) here
  return sb.ToString();

Notice the class that is being applied to each “dt” tag.  The project when I took it over also included a custom CSS file, being deployed to the SharePoint Layouts folder.  It had a lot of stuff in it, but the important piece was the following:

.faq-content dt.acc-item { color: #ff0000; }

So the header items in the list were being shown in red.  The task at hand was to change this so that the colour was dynamically selected based on the current site’s colour scheme.  Since each site that the wepart might be deployed in could have its own Master Page, and each Master Page could reference different CSS, I had no easy way of knowing what colours might be in effect through any kind of custom branding.

However, SharePoint 2010 provides a Theming Engine. And regardless of what additional styles may or may not be being applied by the site’s Master Page, the colours from site’s current “Theme” are always available {even when the current theme is the out of the box “Default (no theme)”}, and even if those colours are being over-ridden in the Master Page for certain page elements such as the ribbon, top nav bar, etc.

To see what Theme colours are currently in effect on your site, go to your site’s “Site Settings” page, and from there, click “Site Theme”. You should see somthing similar to this:


Notice that every site has an associated set of colours (named “Text/Background – Dark 1”, “Text/Background – Light 1”, “Text/Background – Dark 2”, and so on).

It is possible in SharePoint 2010 to reference these “named” colours in your CSS. The process of buiding your CSS is fairly well documented on the Microsoft SharePoint Designer Team Blog in their Working with the SharePoint Theming Engine post.

Based on that, the first order of business is to modify the project’s existing CSS file, as follows:

.faq-content dt.acc-item { /* [ReplaceColor(themeColor:”Dark2″)] */color: #ff0000; }

I chose the “Dark2” colour from the choices listed in the above mentioned post, but could have chosen whichever of the “named” colours available I wanted to use.  As a side note, the information in the above mentioned post on re-coloring images worked as well and was employed in this webpart, but to keep this post straight forward, I’ll omit precisely how that was done.  Suffice it to say the code in the Webpart CreateChildControls that added an image to the webpart for a button to close (collapse) a section once it had been expanded, and the CSS had a line later on that looked like this to recolour that image dynamically using the Theming engine:

.faq-content .btn-close { /* [RecolorImage(themeColor:”Dark2-Medium”,method:”Tinting”)] */ background: url(/_layouts/Images/BaseAccordionWebPart/faq-arrow.gif); }

The next order of business is to move the CSS file.  The original developer was deploying this file to the SharePoint layouts folder, and while this is a perfectly acceptable approach under normal circumstances, it will not work if you want the theming enging to do the substitution and achieve a dynamicly assigned colour.  It needs to be in the “Style Library” in a “Themable” folder as described in the Microsoft post mentioned earlier.  They talk about how to put the file there using SharePoint Designer, or via the web front end.  How to do that in Visual Studio is covered well in this post in the SharePoint Tools blog.

In my case, the resulting Elements.xml file looks like this:

<?xml version=1.0encoding=utf-8?>
<Elements xmlns=http://schemas.microsoft.com/sharepoint/>
  <Module Name=StylesUrl=Style Library>
  <File Path=Styles\expandable_collapsible.css”  Url=Themable/expandable_collapsible.cssType=GhostableInLibrary />

Third, as noted in the Working with the SharePoint Theming Engine post, the CSS must be properly registered.  The post mentions how to do this through SharePoint Designer, but again, not how to do it in Visual Studio.  For me, the project I took over was already registering its CSS correctly, so when I moved the file from the SharePoint “Layouts” folder to the “Style Library\Themable” folder, I just needed to update the location.  For future reference, the following call to register the CSS was invoked from the webpart’s “OnInit” event (don’t forget to do this after you invoke “base.OnInit(e)”).

CssRegistration cssRegistration = new Microsoft.SharePoint.WebControls.CssRegistration();
cssRegistration.Name = “/Style Library/Themable/expandable_collapsible.css”;
cssRegistration.After =“corev4.css”;

Lastly, its time to test your webpart.  If you are like me, you will hit “F5” in visual studio to build and deploy your code to the development server, and like me, scratch your head why the text in the header is still red.  The trick here is to know that the SharePoint Theming engine only does the substituion of the selected theme colour in the CSS when the current theme is applied.  Of course, when you deploy your webpart, the theme is already applied, and so nothing happens.

To get the colours to show up, you have to re-apply the current theme.  From the front end where your webpart is deployed, go to “Site Settings” and then “Site Theme”.

On that page, click (any) one of the “Select a color…” links. Cancel the dialog that appears (so you aren’t actually changing anything) and then click Apply. The current theme will be (re-)applied, including calculating the dynamic colour for the CSS in the Style Library/Themable folder that was installed as part of the .wsp.

Also, when you are finished developing your webpart, and you are ready to deploy it to your production environment via the .wsp file, you will need to re-apply the current theme in your production environment!  You only have to do this once (when the feature containing the webpart is activated).  I do this manually…a way to do it programmatically in the feature_activated event should in theory be possible, but the client in question wasn’t interested in that and I didn’t implement it.

You CAN Count On It (Part 3)


In “You CAN Count On It (Part 1)“, I showed how you can keep a counter of items in a list and use that counter to compose a field value.  It was a simple example, done in MOSS 2007.  The actual problem I encountered in the real world was somewhat more complex, and I am going to walk you through its solution, this time in SharePoint 2010.

Detailed Problem Statement

The client wanted a list of projects, with typical project-related info including who was assigned to each, the status (in progress, on hold, complete, cancelled, etc.), the start and end date (estimated if not yet complete), and so on.  They also wanted each project in the list to have a particular type (such as sales, engineering, development, and so forth) as well as a location (their actual values aren’t important – for this example, we’ll use “Central”, “Southern”, “Eastern”, “Western” and “Northern”).  For reasons only they know, they wanted the “Project Identifier” to be composed of the year the project was intitiated, followed by a 3-character abreviation representing the type (SLS for Sales, ENG for Engineering, etc.), 3 characters representing the region (we’ll use “CEN” for Central, “SOU” for Southern, and so on), and finally a count of projects of that type in that region in that year. So the first Sales project in Northern Region for 2012 would get a Project ID of “12SLSNOR01”, the second sales project in 2012 would be “12SLSNOR02”, and so on.  But if the next project was in a different region, or of a different type, the counter needed to start over, for example “12ENGNOR01” or “12SLSCEN01”.

Also, because the company was going through a major corporate change, an IT Systems freeze was in effect, and although we could create anything we wanted using the SharePoint UI, nothing could be installed on the server, so a no-code solution to this problem had to be found.

As I mention in my earlier post, an exhaustive search of the internet on how to to count items in a SharePoint list consistantly led to the same two answers:  some said it couldn’t be done, while others suggested writing a custom workflow in Visual Studio to achieve the task.  Neither of these answers was acceptable to the client at the time.  The solution I came up with involves keeping track of how many projects of each type and region you already have in the current year, using another SharePoint list.  It builds on the idea presented in  “You CAN Count On It (Part 1)“, but for this post, I am going to build the solution in SharePoint 2010 (although it could just as easily be built in MOSS 2007 with few changes).

First:  Create the lists

We are going to need 4 lists for our solution:  one to hold the project types and their abbreviations, one to hold the regions and their abbreviations, one for the projects themselves, and one to keep track of the number of items that exist in the Projects list of each type and region in a particular year.

For the Project Type list, create a new custom list called ProjectType, and add a 3-character text column to it called “Abbreviation” (leave the built in “Title” column, but make it unique).  Similarly, for the Project Locations, we will build a new custom list called Regions, and add a 3-character text column to it, also called “Abbreviation”, and also make the Title column unique.  You will not need attachments for either of these lists, so feel free to disable them.  You probably also want to make the Abbreviations columns mandatory and enforce unique values on them.  Your end results (with data populated in it) will look something like this:

ProjectType List

ProjectType List

Regions List

Regions List

Next, create the Projects list. Add columns of the appropriate type for each of the required data fields (Date Time for “Estimated Start Date” & “Actual Start Date”, Person Or Group for “Assigned To”, Choice for “Status”, and so on). I also changed the built-in Title column to “Project Name”, and made it enforce unique values.  For the Project Type and Region columns, create “Lookup (information already on this site)” columns bound to the Title column of the ProjectType and Regions lists respectively.  You do not need to add columns to show any additional information (but you can if you want to), however you do need to make these columns mandatory.  I also recommend that you enforce the “Restrict Delete” relationship behavior, so in the future your Project Types and Regions don’t become orphaned.  Finally, you are going to need a Text column of length 10 to hold the ProjectId field, which we will populate using a workflow we are going to build in SharePoint designer.  The list’s design will look something like this when you are done:

Projects List

Projects List

Finally, we are going to need a list to keep track of what ProjectId’s we have already used, as folows:

    1. Create a new Custom List called “ProjectsCounter”. Set “Display this list on the Quick Launch?” to No.  Alternately, you could modify the ListCounts created in the first post of this series to serve a dual purpose of counting cutomers in the customer list and projects in the project list.  I’ll leave doing that as an exercise for the reader.
    2. Navigate to the Settings page for your newly created “ProjectsCounter” list, and edit the “Title” column, renaming it “ProjectPrefix”, and setting it to Enforce Unique Values.
    3. Create a new required column called “ItemCount”, of type Number with zero decimal places, a minimum value of 0, and a default value of 0, and also setting its Enforce Unique Values property to yes.
    4. Start SharePoint Designer, and open the site in which your lists exist. You may optionally change the properties of the ProjectsCounter list to Hide it from Browsers

Second:  Build the Workflow

You are now ready to create a workflow in SharePoint Designer that will set the “ProjectId” as items are added to your “Projects” list. Start by creating a new list workflow in SharePoint Designer, and bind it to the Projects list.  I called my workflow “Set Project Id” and gave the following description:  “Sets the Project Id based on the Year, Project Type and Region.”.  You will want to set your workflow to start automatically when an item is created.  For now, you may also want to be able to run the workflow manually (useful during testing and debugging).  You might also want to allow the workflow to be run manually later, if someone wants to delete a list item and you need to re-use the Project ID.  Deleting and re-numbering list items will be discussed in a subsequent post.  If deletes aren’t required, or you aren’t sure, its probably safest to turn off the ability to run the workflow manually when you are done building it.

The logic we are going to use to set the Project Id goes something like this:

  1. Figure out the current year, and convert it to a 2-digit format, and save that value to a variable called “yr”.
  2. Look up the ProjectType Abbreviation in the ProjectType List for the Project Type selected in the project, and save that value to a variable called “pType”.
  3. Look up the Region Abbreviation in the Regions List for the Region selected in the project, and save that value to a variable called “region”.
  4. Concattenate yr, pType and region, and save that value in a variable called “prefix”
  5. Lookup the ItemCount in the ProjectsCounter list for the row with a value in the ProjectPrefix column that matches what we have in our “prefix” variable and put it in a new variable called “counter”.  Note that if you don’t find it, “counter”`will be zero, which is perfect.
  6. If  “counter” is 0, create a row in the ProjectsCounter list for the ProjectPrefix “prefix” with a value of 0.  There is no else condition, because if “counter” is anything other than zero, an item with a ProjectPrefix matching our “prefix” variable already exists.
  7. Increment the counter variable.
  8. Update the existing ProjectPrefix item to the new value in “counter”.
  9. Concatenate prefix and counter to get the Project Id we need.
  10. Write the resulting value into the ProjectId column of the item in the Project list that we are looking at.

Building the workflow for step 1 is pretty straight forward.  We know the current date is saved in the built-in “Created” field of the current item, so extract that value as a short date and parse out the last two characters to get the 2-digit year value, as shown below.

Workflow Step 1

Workflow Step 1

Step 2 is also fairly straight forward. Click the fx button in the “Lookup for String” dialog to select the ProjectType in the current Item to match on, resulting in something similar to what is shown below.

Get Project Type Abbreviation

Get Project Type Abbreviation

When you click OK on the dialog, you will be prompted that the lookup you defined is not guaranteed to return a single value.  This is fine.  In fact it will return a single value (although SharePoint doesn’t recognize this fact), because we put a unique constraint on the ProjectType list’s Title and Abbreviation columns, and we forced the Project Type in the Project list to select only from this list (so click “Yes” to that prompt).

Repeat the process for step 3 to get the Region’s abbreviation from the Regions list, with the following result:

Get Region Abbreviation

For step 4, concatenate the year, project type abreviation and region abbreviation to get the prefix for the project Id.  Click the elipses (three dots) button in the “Set Workflow Variable” action to open the “String Builder” dialog.  Use the “Add or Change Lookup” button at the bottom of the dialog to select fields to include.  You can optionally include literal characters such as spaces, hyphens, the ampersand (&) character, etc.  (I did in the screenshot below, but in truth the solution does not call for them in this instance.  They are included in the picture for information only. My actual value in the String Builder window was [%variable: yr%][%variable: pType%][%variable: region%] with no spaces between the variable references.)

Determine Prefix

Determine Prefix

In step 5, we use the “Set Workflow Variable” action to set a new variable called counter to the value of the ItemCount field in the ProjectsCounter list where the ProjectPrefix matches the “prefix” workflow variable we constructed at step 4.

Find count of projects for a given prefix

Find count of projects for a given prefix

For step 6, test the returned value of “counter”.  If it is zero, its because the ProjectCounter list was found to not contain any items with a ProjectPrefix that matches the “prefix” we have constructed, which means there are no projects with that type and region created yet this year.  So we need to create a new list item in ProjectsCounter, and set the ProjectPrefix equal to the value in the “prefix” variable and the ItemCount to the value in the “counter” variable to record this fact.

Add ProjectsCounter item

Add ProjectsCounter item

Next, increment the counter in step 7 to reflect the fact that we have added a new project to the Projects list with a particular type and and region value this year.

Increment the counter

Increment the counter

Step 8: Now we should write the revised “counter” value back into the ProjectsCounter list. Use an “Update List Item” action, and fill in the values as shown below.

write counter back to ProjectsCounter list

write counter back to ProjectsCounter list

Again, you will have to click “Yes” when prompted about the fact that there might not be a unique match. We know there should be.

Step 9 involves concattenating the prefix with the counter. In my case, the users wanted to always express the counter portion as a 2-digit number, so I check if the value in “counter” is less than 10, and if so, I stuff a zero into the string.

Build ProjectId out of prefix and counter

The last thing to do is to write that projectId back to the ProjectId field in the current item in step 10.

Update ProjectId in Projects

Update ProjectId in Projects

And there you have it. Save your workflow, check it for errors, and if there are none found, publish it to SharePoint. Some testing and tweaking may be required, which you can do by adding a dummy project and running the workflow manually. You can also manually edit the value(s) in the ProjectsCounter list and re-run the same workflow on the same dummy project a few times to check all the different paths through the workflow. During development, I sometimes wrote the value of one of the variables into the ProjectId field as a way of checking the progress. You can also add any number of “Log to History List” steps to output some tracing info to help you fine tune your process, but in a nut shell, I hope this post has helped you see that you although you can’t easily count items in a SharePoint list dynamically at run time, you can use a list and a workflow to keep track how many items you have so far in another list that match a particular pattern, and you can use that same data on insertion of new items.

I hope you found this post useful.  As mentioned at the end of my first post, you may want to prevent users from editing the ProjectId field now that you have filled its value in via the “Set Project Id” workflow.  See my second post for info on how to do that.

Tag Cloud