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.
- Create a new Custom List called “ListCounts”. Set “Display this list on the Quick Launch?” to No.
- Navigate to the Settings page for your newly created “ListCounts” list, and edit the “Title” column, renaming it “ListName”
- 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
- 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”.
- 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.
- 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.
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.
Click Next. On the screen that appers, give step 1 a meaningful name. Click the Actions button, and add a “Set Workflow Variable” action.
Click the “Workflow Variable” link, and select “Create a new variable…” of type Number called “Counter”.
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.
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 1 to the current value of the “Counter” variable, and save the result back to the “Counter” variable.
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 “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.
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”.
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…