SharePoint lists come pre-equipped with a unique naming convention for each list item. The unique ID on any SharePoint is a GUID, a “Globally Unique ID”, which identifies the item as unique from anything else in the system. (Lists also have GUIDs, as do Sites, Views, Workflows… everything has its own GUID, which is how SharePoint identifies all of its pieces. It is also why workflows built-in SharePoint Designer aren’t portable – when its start is triggered by the updating of an item in a list, it is actually triggered by the update of an item in the list with the GUID that was specified on creation. But I digress…)
The problem with GUIDs is that, in order to be truly unique, they are long and not really human-readable (nor are they intended to be).
I recently had a requirement to create a unique ID auto-incrementing for items in a list, in a particular format. (In this case it was the ListNameAbbreviationYear-IDNumber, for example LNA2010-0001, LNA2010-0001, LNA2010-0002, LNA2010-0003, etc.).
There were a number of approaches I could have taken with this, as this particular list was an InfoPath form library.
Approach One – “Pure” Workflow
This approach works for both InfoPath form libraries and all kinds of SharePoint Lists. I actually used this approach on a subsequent request (once word got around that one group had an auto-incrementing list, everyone wanted one!). The unique numbering system requires the creation of a SharePoint list (which for convenience sake we will call “Unique Number List”), that has only a few fields. Since workflows can’t manipulate strings worth a damn (see my post Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution), for this approach I use a calculated column in the list to create the name string. I changed the Title field name to NextNumber, added a calculated column called FollowingNumber, and a calculated column called NextUniqueID.
Calculation for FollowingNumber:
Calculation for NextUniqueID:
When you create a single item in the list with a value of “1” in NextNumber, this code yields a value of LNA2010-000001.
You could use either the CONCATENATE function, or you could join the strings with an ampersand. The result is the same, which you use is up to your personal preference.
Next, let’s examine
TEXT(NextNumber,"0"), which is used twice. NextNumber is, of course, the value of the NextNumber column of the list item. The “0” is the format parameter of the TEXT output – in this case, an integer. If we had wanted a dollar value, the format would be “$0.00”; if we had wanted a decimal, would could have made the format “0.0”. So this would take a number like 1 and turn it into the string 1, or the number 53 and turn it into the string 53.
From the beginning then: we have a string “LNA”, to which we append the value of the YEAR the list item was modified, followed by a dash. Then comes the tricky part.
We want the number value to be padded with zeroes so that the number is X characters long (in this case I have set the length to 6). LEFT() gets the leftmost characters of the first argument (“000000″). The number of characters is the second argument, 6-(LEN(TEXT(NextNumber,”0”))). This argument starts with the total length we are going for, and reduces it by the length (LEN) of the TEXT string I explained before.
So, for the examples I gave above for the TEXT strings, 1 and 53, the LEN values are 1 and 2. Subtracted from 6, this leaves 5 and 4. The LEFT() function returns “00000” and “0000” respectively, for the examples. This string is appended to our “LNA2010-” string, followed by the number (1 or 53 from the examples).
You can tell my programming background, since I explicitly recast the numbers as strings. You could get away with a slightly simpler
="LNA"&YEAR(Modified)&"-"&LEFT("000000",6-LEN(NextNumber))&NextNumber, but then you are relying on the SharePoint environment to handle things gracefully, which can rarely be depended on…🙂
So now we have our Unique Number List, which has the next number, the unique name, and the number
The workflow is configured to start on item creation in the list you want unique numbering. It gets NextUniqueID and saves it as a workflow variable, then updates list item in the Unique Number List, setting NextNumber equal to FollowingNumber. Then the workflow sets the Title (or other field) of the new item to the workflow variable (the value of the NextUniqueID from Unique Number List from before we changed it).
Approach Two – Pure InfoPath
This uses a similar “Unique Number List”. The only difference is it does not use the calculated column called NextUniqueID. It still has the Title field name changed to NextNumber, and the calculated column called FollowingNumber.
This approach requires setting Submit Options Rules (Tools >> Submit Options >> Rules >> Add) and/or rules for your own custom submit button (if you have one), as well as a data connection to the “Unique Number List”. You will also need a field in the form data source for FormNextNumber and FormID.
Set up the data connection (Tools >> Data Connections >> Add >> Create a new connection to >> Receive Data >> Next >> SharePoint Library or List >> List URL >> Next >> Unique Number List >> Next >> select the NextNumber field >> Next >> uncheck “Store a copy” if checked >> Next >> Name your connection >> Finish).
The Rule for the Submit button(s) has the condition FormNextNumber is Blank.
The first action is to Query using data connection: ConnectionName (Add Action >> Query using data connection >> select Connection Name >> OK). There is a reason to do this at submit rather than on frm opening. Suppose it takes an hour to fill out the form (beacuse the user is slow or got interrupted). How many others will open forms in the meantime? Better to make it happen on submit, where you are unlikely to have a “collision”.
The second Action is to set a field’s value: FromNextNumber = @NextNumber (Add Action >> set a field’s value >> Field: FormNextNumber >> Value: fx >> Insert Field or Group >> Data Source: ConnectionName >> myFields >> datafields >> Unique Number List >> : NextNumber >> OK >> OK >> OK >> OK)
On the FormNextNumber field, set a Rule (Properties >> Rules and Merge >> Add). The condition is FormNextNumber is not blank. The Action is set a field’s value. The field is FormID. Click for fx button for the value.
Here is the formula:
concat("LNA", substring-before(Date, "-"), "-", substring("0000", 0, 5 - (string-length(FormNextNumber))), FormNextNumber)
Look familiar? Once you have selected the FormNextNumber field, the UI replaces the field name with an underlined period “.“, so don’t be surprised by that. It is basically a shorthand for “self”.
You create by starting with Insert Function, selecting concat, then replacing the fields with the text and functions to complete the formula. One thing to note, “Date” in my formula is a read-only form field called Date, which has a default value of today().
For a pure InfoPath solution, you will also need to change the value of the item in the Unique Number List from the InfoPath form, so you would need to do some code behind to make that work, which I won’t go into. It is much easier to use the third approach…
Approach Three – Hybrid
You can combine these approaches to have the workflow do some of the work, and InfoPath do some.
For instance, as I just mentioned, you can set the workflow to update the Unique Number List, which is easier than doing the code behind in the InfoPath form, with the InfoPath form doing the rest.
Other Variations and Considerations
You can also make the unique name field on the target list a calculated column like the NextUniqueID by feeding the NextNumber (either via a workflow or the InfoPath data connection) to another column in the target list.
One thing that it is important to remember is the SPD workflows run in the context of the user filling out the form or adding the list item, so anyone who had rights to add list items needs to be able to edit the item in the Unique Number List. But what you don’t want is for users to go to the list and manually change the number!
One way to control this is to set the default view to use the version of the NextNumber field without the “(linked to item with edit menu)” or “(linked to item)”. In order to lock this down fully, only users you trust to not mess things up should have either the Manage Lists or Manage Personal Views permissions on the list.
To get rid of the Manage Personal Views permission, you will have to either remove the permission from the Contribute permission level (which will affect your site collection), or create a custom LimitedContribute permission level (give it your own name if you like) and apply that to the Unique Number List.
Finally, the unique number as I have it needs to be reset each year. There are a number of ways to accomplish this as well, from a manual process (ummm, no) to having the workflow check the Last Modified date and compare it to today(), check to see if the years match, and, if they don’t match, reset the number.
Hopefully this will get you going in the right direction. Get creative, as your business needs may vary! If what I have said is unclear, or you run into problems, post a comment below, and I’ll do my best to help. And if you get value from this, you can comment on that below, as well!