Sol Web Solutions Blog

Sol Web Solutions Blog is aimed to help, inform, educate and enable our site visitors.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Login
    Login Login form

SharePoint Autonumber for document numbering request list

Posted by on in SharePoint
  • Font size: Larger Smaller
  • Hits: 154725
  • 1 Comment
  • Subscribe to this entry
  • Print

I had a request to create an autonumbering column in SharePoint 2010 for document numbering, and here is how we got the solution to work. I will use elementary school documents as an example.

The user could select one of up to 15 different school subjects when requesting a new document number, and the autonumber should stay in sync with the school subject...so there is no loss in the next available number for that particular school subject.

This is why I can't simply use the ID column that's associated with every list and append that to the eventual variable in a workflow.

Let's say we have the following autonumbers already in the list:
•DEMO-MATH-001
•DEMO-MATH-002
•DEMO-ENG-001
•DEMO-ENG-002
•DEMO-SCI-001

If a user submits a request for a MATH document, the next number they should get to use is DEMO-MATH-003.
If a user submits a request for a SCI document, the next number they should get to use is DEMO-SCI-002.

First, there are two lists:
1. Acronymn_Lookup_List
2. The Request List that fired the workflow

Say, we have four subjects we are looking at now:
Math
Science
English
Physics

Each of these 4 subjects would be defined in the "Lookup List" with 3 specific columns:
1. The acronym for each subject (MATH, SCI, ENG, PHY) >> "doc_type"
2. The Long Description/or title for each subject >> "course_description"
3. An integer fields starting @ 0 which will hold the counter >> "Current_Number_ID"

My Request List, which fires the workflow, would have the following columns defined, but for sure would need to show (in our purposes):
1. The acronym for the subject requested (MATH, SCI, ENG, PHY) >> A lookup field to go to "Acronymn_Lookup_List" ...also called "doc_type"
2. The long description for the school subject acronymn >> This column is auto-created b/c I selected "Add a column to show each of these additional fields: " and selected the "Title" checkbox ...when creating the column mentioned directly above
3. New_ID_Column >> this holds the new autonumber such as DEMO-MATH-003, DEMO-SCI-002, etc.

Now, the workflow:

When you are viewing the Request List that fires everything off in SharePoint itself, from "List Tools" in the ribbon, click the "List" menu item. Go to the far right, and click on "Edit List" which will bring up SharePoint Designer.

When SharePoint Designer opens, it will basically be focused on this particular list.

  • Click "List Workflow" in the upper left section of the ribbon.
  • Add a name and Description >> Click "OK"


------------------------------

1. Create a variable to hold the Acronym value

In the SharePoint Designer Ribbon go to Action >> Set Workflow Variable
Click "workflow variable" link
Select bottom most option called "Create new variable..."
Give variable name of "varAcronym"
Select Type value of "String"
Click "Ok" for this "Edit Variable" modal window

For the 2nd part of this action, click "value" link
Click the "fx" button
In the "Lookup for String" modal window:
"Data source" dropdown list item = Acronymn_Lookup_List
"Field from source" dropdown list item = "doc_type"
"Field" dropdown list item "doc_type"
"Value" >> click the "fx" button as this will bring up a "Lookup for Single line of text"
"Data source" dropdown list item = Current Item
"Field from source" dropdown list item = "Doc Type" (this it he column header name for the column "doc_type")

***VERY IMPORTANT STEP BELOW!!!***
"Return field as" should be "Lookup Value (as Text)" and NOT "As String" >> "As String" selection will concatenate the ID of the lookup value + ";#" + the acronym value. This took me several days to figure out and finally I used the email for to test everything and see why the behavior wasn't working

Click "OK" and answer "YEs" to the warning message to save everything and move to the next action below


------------------------------

2. Create increment of the "Acronymn_Lookup_List.Current_Number_ID" column by 1

In the SharePoint Designer Ribbon go to Action >> Do Calculation
For the 1st part of this action, click the "value" link
Click the "fx" button

In the "Lookup for Number" modal window:
"Data source" dropdown list item = Acronymn_Lookup_List
"Field from source" dropdown list item = "Current_Number_ID"

"Field" dropdown list item "doc_type"
"Value" >> click the "fx" button as this will bring up a "Lookup for Single line of text"
"Data source" dropdown list item = Current Item
"Field from source" dropdown list item = "Doc Type" (this it he column header name for the column "doc_type")


***VERY IMPORTANT STEP BELOW!!!***
"Return field as" should be "Lookup Value (as Text)" and NOT "As String" >> "As String" selection will concatenate the ID of the lookup value + ";#" + the acronym value. This took me several days to figure out and finally I used the email for to test everything and see why the behavior wasn't working

Click "OK" and answer "Yes" to the warning message to save everything and move to the next action below

The second link in this action should have a value of "plus"
Click the third "value" link
Enter in 1 as the value
Click the "Output to" link
Select "Create a new variable..." option
In the "Edit Variable" modal window enter a variable name of "varIncremented_Current_Number_ID"
For the "Type" select "Integer"
click "OK" to save everything and move to the next action below


------------------------------

3. Update the incremental value in "Acronymn_Lookup_List" which holds the number used for that particular school subject

In the SharePoint Designer Ribbon go to Action >> Update List Item
Click "this List" link
From top "List" dropdown list items, select "Acronym_Lookup_List" in the "Update List Item" modal window

Go to the bottom where it says "Find the List Item".
***We could have just updated every field in the Acronym list, but the update should only be for that one school subject in this case.

For the "Field" dropdown list item, select "doc_type"
Click the "fx" button for "Value"

You are now in the "Lookup for Single line of text" modal window
For "Data source" select "Current Item"
For "Field from source" select "Doc Type"
For "Return field as" select "Lookup Value (as text)"
Click "Ok"

Now back in "Update List Item" modal, click the upper right "Add" button
In the "Value Assignment" modal, select "Current_Number_ID" for "Set this field"

In the "To this value", select the "fx" button
In the "Lookup for Number" modal window:
"Data Source" should be "Workflow Variables and Parameters"
"Field from source" should be varIncremented_Current_Number_ID
click "OK" 3xs and "Yes" to the popup warning to save everything and move to the next action


------------------------------

4. Create a variable to hold the final autonumber

In the SharePoint Designer Ribbon go to Action >> Set Workflow Variable

Click "Workflow variable" link
Select the bottom most option called "Create new variable..."
For "Name" enter "strFinalVar"
For Type select "String"
Click "Ok"


Click the "value" link of this action
click the "..." / elipses button
The "String Builder" modal window comes up

Click the "Add or Change Lookup" button in the bottom left corner

In the "Lookup for String" modal window:
"Data source" will be "Workflow Variables and Parameters"
"Field from source" will be "varAcronym" ...this was the variable create in item #1 above
"Return field as" can be "As String" at this point

Click "OK" because now you've got a portion of the auto number >> the middle basically

In the "String Builder" modal window type in the string value of "DEMO" as the prefix so all number begin with "DEMO"...or for your particular case, whatever you'd want.

Repeat this step to pull in the ending portion/suffix as the autonumber...the actual 1, 2, 3, etc ending part

Click "OK" to save everything and move to the next action below


------------------------------

5. Update the current item in the list that fired this whole workflow

In the SharePoint Designer Ribbon go to Action >> Update List Item
Click "this list"
"Current Item" should be in top "List" selection

Click the "Add" button to bring up the "Value Assignment" modal
For "Set this field" select "New_ID_Column"

For "to this value", click the "fx" button
"Data source" should be "Workflow Variables and Parameters"
"Field from source" should be "strFinalVar"
"Return field as" can be left as "As String"

Click "OK" to close all windows


------------------------------
6. We used the Action "Send an email" to send the final variable to the person that created the list item
The email action actually helped me test things out and solve the issue of picking the variable in as "Lookup Value (as Text)" and NOT "As String"
Use this email action to test every step and make sure your variables are pulling exactly what you need.

***One last item, you'll notice the numbers were simply incremented as 1, 2, 3...not 001, 002, 003, etc.

The customer asked for this and an easy resolution was to use Conditions from the ribbon.
The condition would check for the incremental value of:
1. Less than 10
2. Less than 99 but greater than 9
3. Greater than 99

Depending on what condition was true, you would then build a new variable to say something like the following results:

  • Oh, the new number is 8, so tack on "00" in the beginning and via a string builder add number 8 since it's already in varIncremented_Current_Number_ID
  • Oh, the new number is 11, so tack on "0" in the beginning and via a string builder add number 11 since it's already in varIncremented_Current_Number_ID
  • Oh, the new number is greater than 99, so just tack on varIncremented_Current_Number_ID


You’d put actions 2-3 into that condition.

Actions 4-6 can be put outside these conditions since they can wait till we look at the 3 conditional statements for the incremented number length/value:


Thanks...hope it works for you and helps to save some time!

0

Comments

  • Guest
    vikpr Monday, 15 December 2014

    Thanks very much for the article..Well explained!!!

Leave your comment

Guest
Guest Wednesday, 17 January 2018

Help Today

Send an email here!

Sol Web Solutions will be glad to help you anytime with general or technical questions, suggestions or comments.

Website Hosting

Web Hosting

Sol News

We will not share, trade, or sell any of your information in any way. Your privacy is completely respected.