Question

Forms - Unique Number Generator

Forms

Updated August 4, 2016

asked on August 4, 2016

Hi,

In my forms i am trying to generate a unique number every time form is submitted without using java script like this.

So far i have been able to get date using OPEN Document calculation like this

=CONCATENATE( DAY(TODAY()) , YEAR(TODAY()) , MONTH(TODAY()))

But this is not unique, I would like to include time as well, Like hours and minutes.

Something like this

=CONCATENATE( DAY(TODAY()) , YEAR(TODAY()) , MONTH(TODAY()) , HOUR(NOW()) , MINUTE(NOW()))

but its not working for me.

Any help is greatly appreciated.

Thanks

Junaid Inam

0 0

Answer

SELECTED ANSWER

replied on August 4, 2016

The time related formula functions (HOUR, MINUTE, NOW) are not available in Forms 10.1 (but they will be there in Forms 11.)

And the submission id is not available before submission so you can't use it as default value on a new submission form.

So for now, I would suggest use lookup rules or custom JavaScript to do it.

0 0

Replies

replied on August 4, 2016

If you have a SQL Server back-end database you could use a Sequence to pull a unique ID from a stored proc.

In my case, I created a new Sequence called LFScanID, assigned starting and ending numbers, and then created a Stored Proc that gets the next sequence:

/****** Object:  StoredProcedure [dbo].[sp_LF_GetScanID]    Script Date: 8/4/2016 1:43:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_LF_GetScanID]
AS
DECLARE @ID as bigint
BEGIN
	SELECT @ID = NEXT VALUE FOR dbo.LFScanID;
	Declare @myTable table
	(
	[ID] bigint
	)
	INSERT INTO @myTable ([ID]) values (@ID)
	SELECT TOP 1 * FROM @myTable
END

Add a lookup field to your form that gets the results of the proc and you're good to go, and the great part about SQL Server Sequences is that they are guaranteed to be unique.

1 0

replied on August 4, 2016

Hi Geoffrey

This looks good but i am trying to use only built in out of the box functionalities.

0 0

replied on August 4, 2016

Do you need the unique number to be a date and time? If not, you could just use the submission ID as it is always unique and readily available as a variable.

0 0

replied on August 4, 2016

I am not sure if submission id is available before submission.

I would like to show the unique id on the form as well. I used this and nothing shows up in the form even after submission.

after submission

0 0

You are not allowed to follow up in this post.

Question

Question

Forms - Unique Number Generator

Answer

Replies

Sign in to reply to this post.