Generating Unique and Sequential ID Values

It's often desirable to generate a unique ID when a form loads that is included with the submission. While this approach allows you to immediately see the ID associated with the data a user is going to submit, it does introduce the ability for ID's to get lost. When the form loads, the sequential ID is generated. If the user never submits the form, the ID does not get returned and is ultimately "lost".

To generate the ID, a database to store the incrementing value is required. The SQL script below will create a table with a unique, incrementing value starting at 1000. The table uses an identity column to control the sequence and value. Adjust the identity properties to fit your specific needs as necessary.

Counter Table

SQL

CREATE TABLE [dbo].[GenericCounter](
	[ID] [bigint] IDENTITY(1000,1) NOT NULL,
	[Allocation Date] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GenericCounter] ADD  CONSTRAINT [DF_GenericCounter_Allocation Date]  DEFAULT (getdate()) FOR [Allocation Date]
GO

With a SQL table created, test the function in a query window with the following SQL command:

insert into GenericCounter default values;select SCOPE_IDENTITY() as SequentialID;

The return should be a number starting from 1000. Executing the SQL again should return 1001.

Once proper operation of the SQL database is confirmed, add the SQL statement (and necessary connection information) to the GlobalForms configuration file (c:\globalforms\config\default.json). Find the resquel section in the file and add the necessary sections from the example below. At a minimum, you will need to add the /counter/id route. After editing the configuration file, GlobalForms will need to be restarted.

Database Warning

GlobalForms only supports a connection to a single database. Make sure in scenarios where you may be using multiple routes that the SQL Commands in the query section account for this.

Configuration

JS

   "resquel": {
      "type": "mssql",
      "db": {
        "user": "mysqluser",
        "password": "mysqluserpassword",
        "server": "myserver\\myinstance",
        "database": "mydatabase",
        "requestTimeout":"30000"
      },
      "routes": [
        {
          "method": "get",
          "endpoint": "/counter/id",
          "query": "insert into GenericCounter default values;select SCOPE_IDENTITY() as SequentialID;"
        }
      ]
    }

The form logic to support incrementing the value and displaying it is implement in the attached resource form. Use this form as an example, or simply drag the Columns component of the resource form into your own form by using Existing Resource Fields in the Components toolbox.

Sequential Counter.json