It's often desirable to generate a unique ID when a form loads that is included with the submission. 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.
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.
"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;"
}
]
}