Auto Number form

What happens when the form is submitted? Does a workflow run on the form submission? Are you saving the data to a data source like SharePoint online?

You could utilize an action like the “Create a text string” action within the workflow to dynamically build your example string and update the data source.

@bamaeric ,

Hi! So the Nintex Automation Cloud form I am creating will be used with SharePoint Online, yes. I don’t have a workflow built yet, just a simple email control applied but for the form I was seeing the options available to handle this kind of auto population.

Can this only be done via workflow? or can this be done within the form itself? Like with using formulas, variables etc?

I’m trying to also figure out how it would know to restart the numbering scheme once a different year approaches...Is that possible?

I am not aware of a way to do this on the form. The concept to think about is that a number should not be assigned until the item is created. So the “001” would not exist on a new request form until after the form is submitted. That’s when the workflow could create the unique tracking and update the list item. Then the tracking number would show up on the form when it is opened.

As far as restarting the numbering, you can modify the workflow actions when that time of year comes around with the new format.

Check out this post for details on how to configure this in a workflow: Auto Incrementing in NWC | Community (nintex.com)

Hi @jpacheco 

It is possible to do this but not without some kind of system to track the number of forms logged in a year.

The first parts of the ID are very easy, you can do it with a simple formula like this:

The hard part is getting the unique yearly limited number.

To do this we would need the following:

Somewhere to track the records kept for this year.

A data source variable to return the count of the items currently created this year only.

To start lets create a variable that will always return 01/01/[current year]

Now we use that to filter our external data records to only return those created this year:
 

Now we have a list of all records created this year we can use the form to count them in another variable, because we need the count +1 (including the current form) lets add it here also:
 

Now we have the count we can add it to the Unique ID:

(Please note that data source variables don’t always work in preview so you might see an error) First just try applying and saving the form and re-opening, it should then work.

If all went well you will see this:
 

If we look at my list there are indeed 8 items not yet including the one I am going to add:

Important:
This method of approach when it comes to generating unique form IDs is not without its faults. 

Primarily an issue will arise if 2 forms are submitted at the same time, both of them will return the “Request-23-9” on form submission but only one of them can in be that, the other would have to be Request-23-10. 

It is typically not recommended to pre-populate IDs like this until the record is stored on the server (SharePoint) ultimately you have to think about how displaying this ID on a form serves a purpose that sending it to someone after via an email after creation if ensuring the end user knows the request ID.

Even SharePoint will not predict the ID of a list item for you in the form as there is always a chance it will be incorrect. 

To ensure a wholly unique identifier and return it back you need to perform similar actions to the above within the workflow after you create the item, below are steps to do this using workflow:

 

Lets start by creating our new item with a placeholder Title storing the output to “New Item”
 

Now lets count the number of items in that list that were created this year:

Lets store that output in “No of Records”
​​​​​​​

By default for Sharepoint the number of records is returned in the output.
 

Now we can use a ‘create a text string’ action to combine the values.
 

Before we can finish it however we are missing one variable (that returns the year in 2 digit format) we can use the form to create this for us, again being the most efficient method as it doesn't require any workflow actions so go back into the start form an create a variable like this: 

Then add this and the record count to the create a text string action:
 

As the count includes the record we created we do not need to add +1 storing the value in a workflow variable named Unique ID

Now we just set the title of the item we created to the new value using the output “New Item” ID
 

REALLY IMPORTANT!

If it works you will see the new record with the correct ID:
 

@Jake,

Wow and thanks for your response! I will attempt this and get back to you with the result!

Thanks!

@jpacheco , if you have the ability to use SQL, that will be the quickest and most robust method:

Step 1: Create a Reference Number Table:

CREATE TABLE [Lookup].[ReferenceNumber](
	[Code] [nvarchar](50) NOT NULL,
	[CurrentNumber] [int] NOT NULL,
	[Prefix] [nvarchar](50) NOT NULL,
	[LengthWithoutPrefix] [int] NOT NULL,
	[Suffix] [nvarchar](50) NULL,
 CONSTRAINT [PK_ReferenceNumber] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2: Create a Stored Procedure:

CREATE PROCEDURE [Lookup].[ReferenceNumberGetNextForYear]
(
    @Code nvarchar(50)
)
AS
BEGIN
	declare @NextNumFull nvarchar(100), @NextNum int, @currentDate date = getdate();

	update [Lookup].[ReferenceNumber]
	set @NextNum = [CurrentNumber] = [CurrentNumber] + 1
	where [Code] = @Code + FORMAT(@currentDate,'yyyy')

	select @NextNumFull = ISNULL(Prefix,'')+'-'+FORMAT(@currentDate,'yy')+'-'+REPLICATE('0', [LengthWithoutPrefix] - LEN(@NextNum)) + CAST(@NextNum AS nvarchar)+ISNULL('-'+Suffix,'')
	from [Lookup].[ReferenceNumber]
	where Code = @Code + FORMAT(@currentDate,'yyyy')


	select @NextNumFull as NextNumber
END
GO

Step 3: Populate Reference Number Table:

insert into [Lookup].[ReferenceNumber]([Code],[CurrentNumber],[Prefix],[LengthWithoutPrefix],[Suffix]) values ('LEAVE2023',0,'LVE',3,null)
insert into [Lookup].[ReferenceNumber]([Code],[CurrentNumber],[Prefix],[LengthWithoutPrefix],[Suffix]) values ('LEAVE2024',0,'LVE',3,null)
insert into [Lookup].[ReferenceNumber]([Code],[CurrentNumber],[Prefix],[LengthWithoutPrefix],[Suffix]) values ('LEAVE2025',0,'LVE',3,null)
insert into [Lookup].[ReferenceNumber]([Code],[CurrentNumber],[Prefix],[LengthWithoutPrefix],[Suffix]) values ('LEAVE2026',0,'LVE',3,null)
insert into [Lookup].[ReferenceNumber]([Code],[CurrentNumber],[Prefix],[LengthWithoutPrefix],[Suffix]) values ('LEAVE2027',0,'LVE',3,null)

Step 4: Call Stored Procedure From NAC: