Question

How To Create Auto-Increment Number Based on Day?

Forms

Updated May 11, 2017

asked on May 4, 2017

I have a need to assign a unique id to a field on a form when it is submitted based on how many of been submitted that day. I was looking at the Forms Help documentation about how to auto-increment a number and it looks great, but I need it to start over at 1 each day. For example, the first form submitted today would have an id of 0504201701, then 0504201702. Then tomorrow it would start over with 0505201701, then 0505201702. How would I alter the Stored Procedure to account for that?

1 0

Answer

SELECTED ANSWER

replied on May 5, 2017 • Show version history

Hi Blake,

You could use some javascript or use this stored procedure instead of the previous one:

USE [LaserficheIntegrationTables]
GO

/****** Object:  StoredProcedure [dbo].[sp_IncrementCounter_Forms]    Script Date: 05/05/2017 22:57:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_IncrementCounter_Forms]
	(
	@KeyName nvarchar(30)
    )
AS
	begin transaction
		declare @KeyVal int
		select @KeyVal=keyvalue  from ingencia_Counters where keyname=@KeyName
		if @KeyVal is null
		begin
			insert into ingencia_Counters values (@keyname,1)
			set @KeyVal=1
		end	
		update ingencia_Counters set keyvalue=keyvalue+1 where keyname =@KeyName
		select @KeyVal as [KeyVal],@keyname+right('00'+convert(nvarchar(2),@KeyVal),2) as [BlakeVal]
	commit 
GO

Then in the Lookup Rule, replace "KeyVal" with "BlakeVal"

Are you sure 99 is enough? Will you need 100 or more per day? That code will break when the counter gets to 100. If you are using SQL2012, you should create the BlakeVal with this line instead:

select @KeyVal as [KeyVal],@keyname+convert(char(2),FORMAT(@KeyVal,'0#')) as [BlakeVal]

The above line is still only gong to give you two digits BUT it won't break when the counter gets to 100.

The more I think about it, the more I would concatenate the string in javascript because I'd rather keep the SQL script generic and modify the form and the javascript as required.

0 0

Replies

replied on May 5, 2017 • Show version history

Hi Blake,

I have a TSQL based solution, so no good for Oracle users. You feed it a string and it returns the next digit in the sequence for that string. The next day, you feed it a new string and it starts from one. It can track and increment multiple counters. Is that what you're after?

1 0

replied on May 5, 2017 • Show version history

first create the table to hold the values...

USE [LaserficheIntegrationTables]
GO

/****** Object:  Table [dbo].[ingencia_Counters]    Script Date: 05/05/2017 12:08:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ingencia_Counters](
	[keyname] [nvarchar](10) NULL,
	[keyvalue] [int] NULL
) ON [PRIMARY]

GO

then create the stored procedure

USE [LaserficheIntegrationTables]
GO

/****** Object:  StoredProcedure [dbo].[sp_IncrementCounter]    Script Date: 05/05/2017 11:57:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[sp_IncrementCounter_Forms]
	(
	@KeyName nvarchar(30)
		)
AS
	begin transaction
		declare @KeyVal int
		select @KeyVal=keyvalue  from ingencia_Counters where keyname=@KeyName
		if @KeyVal is null
		begin
			insert into ingencia_Counters values (@keyname,1)
			set @KeyVal=1
		end	
		update ingencia_Counters set keyvalue=keyvalue+1 where keyname =@KeyName
		select @KeyVal as [KeyVal]
	commit 
GO

then test it...

exec [dbo].[sp_IncrementCounter_Forms] N'05042017'

Sample Form configuration:

-Ben

1 0

View 1 previous reply

replied on May 5, 2017

No worries; let me know how you go.

0 0

replied on May 5, 2017

Ben,

This is close to what I need. I need the KeyVal to include the keyname in its value. So it would be 0505201701, 0505201702, etc.

1 0

SELECTED ANSWER

replied on May 5, 2017 • Show version history

Hi Blake,

You could use some javascript or use this stored procedure instead of the previous one:

USE [LaserficheIntegrationTables]
GO

/****** Object:  StoredProcedure [dbo].[sp_IncrementCounter_Forms]    Script Date: 05/05/2017 22:57:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_IncrementCounter_Forms]
	(
	@KeyName nvarchar(30)
    )
AS
	begin transaction
		declare @KeyVal int
		select @KeyVal=keyvalue  from ingencia_Counters where keyname=@KeyName
		if @KeyVal is null
		begin
			insert into ingencia_Counters values (@keyname,1)
			set @KeyVal=1
		end	
		update ingencia_Counters set keyvalue=keyvalue+1 where keyname =@KeyName
		select @KeyVal as [KeyVal],@keyname+right('00'+convert(nvarchar(2),@KeyVal),2) as [BlakeVal]
	commit 
GO

Then in the Lookup Rule, replace "KeyVal" with "BlakeVal"

Are you sure 99 is enough? Will you need 100 or more per day? That code will break when the counter gets to 100. If you are using SQL2012, you should create the BlakeVal with this line instead:

select @KeyVal as [KeyVal],@keyname+convert(char(2),FORMAT(@KeyVal,'0#')) as [BlakeVal]

The above line is still only gong to give you two digits BUT it won't break when the counter gets to 100.

The more I think about it, the more I would concatenate the string in javascript because I'd rather keep the SQL script generic and modify the form and the javascript as required.

0 0

replied on May 10, 2017

Hi Blake, how'd you go with this?

0 0

replied on May 11, 2017

Ben,

Thank you for all of the information. It seems to be working great so far. I am having to get some clarification from the client about when they want the number created during their process, but looks great!

1 0

You are not allowed to follow up in this post.

Question

Question

How To Create Auto-Increment Number Based on Day?

Answer

Replies

Sign in to reply to this post.