Breadcrumbs

Generating Unique and Sequential ID Values

Give each form a sequential ID, such as a request number, as soon as the form opens, so users can see and refer to it before submitting.

An ID is used up whenever the form is opened. If the user never submits, that number is skipped. If numbers must not have gaps, assign them on submission instead, with a SQL Query action. See SQL Query Form Actions.

Step 1: Create a counter table

This SQL Server table issues numbers starting at 1000:

SQL
CREATE TABLE dbo.GenericCounter (
  ID BIGINT IDENTITY(1000,1) NOT NULL,
  AllocationDate DATETIME NULL DEFAULT (GETDATE())
);

Test it: running INSERT INTO GenericCounter DEFAULT VALUES; SELECT SCOPE_IDENTITY() AS SequentialID; twice should return 1000, then 1001.

Step 2: Add a route

Add this route to the Resquel routes in config\local.json, then restart the GlobalForms service:

JSON
{
  "method": "get",
  "endpoint": "/counter/id",
  "query": "INSERT INTO GenericCounter DEFAULT VALUES; SELECT SCOPE_IDENTITY() AS SequentialID;"
}

Resquel connects to one database. If the counter table is in a different database, name it in full, for example CounterDB.dbo.GenericCounter. See Working With Multiple Databases.

Step 3: Add the ID to your form

Import the Sequential Counter example form, Sequential Counter.json, and drag its components into your own form, using Existing Resource Fields in the builder. The example requests a new number when the form loads and shows it to the user.