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

Archive for the ‘Counting Items’ Category

You CAN Count On It (Part 3)

Overview

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.

Advertisements

You CAN Count On It (Part 2)

In my previous post, I discussed how to use a SP-Designer workflow to build a simple counter for items in a list.  At the end of it, I mentioned that once you have a field created that contains the auto-generated counter, you might want to hide that field from end users on insert (since it will be generated by the workflow), and disable it on update (since you don’t want your business clients mucking with your numbering scheme).

I really like the OfficeToolbox.SharePoint.Lists v1.0 (Custom Forms) tool for exactly that purpose in MOSS 2007 / WSS 3.0.  Once you have downloaded and installed it per Sharad’s instructions, follow these steps to hide/disable your new “CustomerNumber” column:

  1. Navigate to the Settings page for your Customers list
  2. Click the (newly added) Form Settings link
  3. Configure the CustomerNumber column to be hidden on the New form, to be shown on the Display form, and to be shown as a read-only label in the Edit Form, as shown below

Form Settings for CustomerNumber
Click OK to save your changes, and then check out the behavior on your Customer List’s Add, View and Edit pages.

I’ve also downloaded and and attempted to install this tool in SP 2010, including re-naming the “Form Settings” to “Field Settings” as described here.  I was not successful.  I encountered a correlation error when I tried to deploy the feature.  It may work for you, but in the mean time I will see if I can download the code from CodePlex and figure out how to make it to work with SP 2010.

Up next:  a bit more complicated real world counting example.

 

You CAN Count On It (Part 1)

Several years ago, I needed to create a column in a SharePoint list that included a count of the items in the list – much like an auto-number field. Much searching on the internet led me over and over to the same two answers:

  • “use the ID column of the list” as part of a calculated value, or
  • “there is no way to count columns in SharePoint without coding a custom workflow”.

The first option, using the ID column, is not effective. As items are added and removed from a list, the ID column quickly develops “gaps” in the sequences, and can not be reliably used to determine how many items are in the list.

The second option, writing a custom workflow, was not an option at the time for that particular client.  I have decided to make how I solved this problem, and how it could be solved today the subject of my first set of posts.

After many hours of surfing the net looking unsuccessfully for an answer, it finally occured to me that if I knew how many items there were already, I wouldn’t have to count them. And, when a list is new, you know for a fact that it has zero items in it. When you add a row to the list, increment the “counter”. When you delete one, decrement it.

The following procedure (and screen shots) are for MOSS 2007, but a similar process could easily be followed in SP 2010.

  1. Create a new Custom List called “ListCounts”. Set “Display this list on the Quick Launch?” to No.
  2. Navigate to the Settings page for your newly created “ListCounts” list, and edit the “Title” column, renaming it “ListName”
  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
  4. In the list that you want to have a “counter” column for, add a new column of type “Number” of type Number with “Number of Decimal Places” set ot 0. Set the formula to “0” (without the quotes). For this example, I named this column “CustomerNumber” in the list “Customers”.
  5. Go back to your “ListCounts” list, and add a new item. In the “ListName” column, enter the name of the list you want to have a counter in (“Customers” in my example), and a value of 0 in the “ItemCount” column.
  6. Start SharePoint Designer, and open the site in which your lists exist. You may optionally change the properties of the ListCounts list to Hide it from Browsers

You are now ready to create a workflow that will set the “CustomerNumber” as items are added & removed from your “Customers” list. Start by creating a new workflow in SharePoint Designer.
Create a new workflow...
Give your workflow a meaningful name.  Select the list (Customers) it should operate on, and set it to run both on demand and on insert of new items.
Name your workflow
Click Next.  On the screen that appers, give step 1 a meaningful name.  Click the Actions button, and add a “Set Workflow Variable” action.
Step 1
Click the “Workflow Variable” link, and select “Create a new variable…” of type Number called “Counter”.
Set Workflow Variable
Click the “” link. Click the fx button, and fill in the form as shown below to retrieve the current count of customers from the ItemCount field of the ListCounts list.
Get ListCounts.ItemCount

Click OK.  You will be prompted with a warning that the lookup may not return a unique value.  This is fine.  Click Yes.  Click the link (top right) to add a new Workflow Step.  In the screen that appears, give the step a meaningful name and click the Actions button to Add a “Do Calculation” step.
Add Do Calculation Step

Add 1 to the current value of the “Counter” variable, and save the result back to the “Counter” variable.
Increment the counter
Click the link (top right) to add a new Workflow Step.

Counter Incremented

In the screen that appears, give the step a meaningful name and click the Actions button to Add a “Set Field to Value” step. Save the newly calculated “Counter” variable’s value to the “CustomerNumber” field in the current item.  Click “Add workflow step” one last time.
Set Field to Value
Add an “Update item in this list” action. Click the “this list” link, and use the dialog that appears to set the ItemCount field to the value in the Counter variable (click the Add… button), for the row with a “ListName” of “Customers”.

Update ListCounts

Click OK to close the dialog box.  Click Yes to the warning that this might not return a unique value.

Click the Variables button, and remove all but your Counter variable.

Click the “Check Workflow” button to validate the steps entered.  Fix any errors encountered (there should be none if you followed these instructions).  Click Finish to save the workflow.

If your list already had items in it, you will need to run the workflow on each item in the list (one after the other) manually to assign values to CustomerNumber fields in the existing list items.  Once all existing items have a value, you can edit your workflow if desired to remove the ability to run the workflow manually.  If you have LOTS of records, you may want to change to “Edit in Datasheet” view, and just manually enter the numbers yourself.  If you do that, be sure to edit the ItemCount in the ListCounters list to match the last row entered manually.

At this point we have an “aut-number” field in the Customers list that is incremented by 1 for each new list item.  Admittedly, this does not buy us much when compared to using the built-in ID column of the list.  But we could have used any increment we wanted (for example, adding 10 for each new item).  We also have a list called ListCounters, with a row for “Customers” containing the count of items in the Customers list.  If you decide to increment by ten, you may want to add another column to ListCounters to keep track of the next index value to be used (or amend the formula in step 2 of your workflow to work it out at run time).

In a later post I’ll build on this basic idea to make a much more complex set of automatically generated identifiers in a list (which is what I actually needed to do originally when this process was first conceived).

Up next you may want to hide or disable the CustomerNumber field in the insert and edit forms for the Customers list so your users don’t inadvertently muck with it.

Third in this series:  a more complicated real world example, implented in SP 2010.

Eventually, I plan to explore options to decrement the counter.  Watch for additional posts coming soon…

Tag Cloud