Generating an auto increment sequence number is a pretty common requirement for applications but often times you would find that something as simple as that might not be that straightforward to achieve.
However AgilePoint NX platform makes it very easy to achieve this functionality. There are infact more than 1 ways of doing it which I have explained in the article below.
I am going to make scenario a bit more complicated where auto incrementing field needs to be prefixed with specific text for e.g. Task_1, Task_2 etc.
Options 1 – Use Sequence Number control in eForms – This control lets you create an alphanumeric string with a number that increments each time a new instance of an form starts. You can use the sequence number in this form control for tracking purposes, like a request number. Please note that this approach will be best suited if you want t generate the sequence number on the form instead of through the process.
Please note that if a form user starts a new instance of an eForms, and then cancels it without submitting the form, the sequence number created for the eForm instance becomes unusable. The next time an eForm instance is created, a new sequence number is created.
What happens in the background is that when you publish such application, AgilePoint engine checks that if a sequence does not exist in database, it automatically creates it and utilizes this for any subsequent form submission. The big advantage this brings in is that you do not need to have access to SQL Server or database knowledge to create a sequence. Also you are guaranteed to always generate a unique sequence.
Configure the Sequence Number control
A short process to capture data from form and submit it to SharePoint list as target system
Open a new instance of form which shows auto generated task #
Here is the final result in SharePoint list as expected
Option 2 – Make use of database sequence using process – In this scenario you would generate a unique sequence number through the process model instead of handling it on the form. This approach is specially useful in non-eForms applications.
In this case an DBA could quickly define a Sequence on the database and then you can use a query database activity to call the sequence and get next sequence number.
This is guaranteed to generate a unique sequence number as well.
Here is the configuration of the Query Database activity
If you drill down into the query configuration, it looks like this
The final process model might look like this where update process instance activity is used to append a prefix “Task_” and then push the data back to SharePoint using Create List Item activity.
Option 3 – Generate Sequence Number using SharePoint List –
This typically is the least preferred option compared to the the ones mentioned previously but you might have a valid scenario of managing generating sequence # using SharePoint list. Creating auto incrementing column in SharePoint is not so straightforward but AgilePoint workflow can help you achieve that.
Here are the steps to be followed
You need is a supporting list that tracks the next number to use. This list will have one item in it that holds the next number value. My list is called “Sequence Number List” and one column has been created to store the next number which is called Next Number. Here is the list configuration
Next you would create the list where this auto generated number would be stored. For demo purpose I created a list which has 3 columns, title, sequence number and task #. Sequence number shows the current sequence number and task # shows the prefixed sequence number.
Next we would create a workflow which would be kicked off when a new list item is created and we will make use of AgilePoint NX SharePoint ListForm functionality to hide the sequence number and task # column as they should not be entered by the user
If you need a quick refresher on how AgilePoint ListForm works, please refer to the following article
The workflow logic would be that when the user opens ListForm, all he sees is the Title field and once he submits the form, an AgilePoint NX process is kicked off in SharePoint which reads the next sequence number from supporting list and increments it and immediately writes it back to the list to avoid duplicates. Next it formats the number to add few leading zeros and finally prefixes that with “Task_” and finally writes it back to the columns in parent list where the list item belongs to. Workflow might look something like this
Next I would drill down into configuration of individual activities
Get Next Sequence Number (CAML Query Activity)
Column configuration
Where clause configuration
Mapping of the output to a process variable
Increment Seq Number (Update Process Data Activity)
Configuration
The formula reads as Convert.ToInt32(${/pd:AP/pd:processFields/pd:NextSeqNum})+1
Update Sequence Back in SharePoint (Update Metadata Activity)
Configuration of column to update next available sequence number
Pad Next Number with Zeroes (Inline Functions Activity)
PadLeft configuration
Add Prefix (Inline Functions Activity)
String concat configuration
Update Generated Task Number in SharePoint (Update Metadata Activity)
Configuration
Here is the final result as expected where sequence number is incremented
New row with sequence number and formatted task # is inserted into the list
One drawback of the SharePoint approach is that there can be chances of duplicate if 2 request came in at precise milli-sec where both read same variable before the other got a chance to update it but that will be extremely rare as chances of concurrent requests is less and the workflow executes very fast but can still happen sometimes in a extremely loaded environment. That would however be the case for any workflow product which generates the number using workflow if the source system is not transactional in nature. This is the reason I always would have preferred option #1 and #2 but I still covered option #3 for sake of completeness.
Also option #1 and #2 are far more easier to configure.