How to create a number sequence

Create a number sequence from Site Manager

In the Site Manager, go to Tools > [Administration] Options > Number sequences tab

Here you see a list of all the number sequences in the Site Manager. You can edit a number sequence or create a new one.

This is the dialog you get when you want to create a number sequence:

You give it an ID and Description and then you select the Highest number that the sequence can store. When this number is reached the Format will get an extra # in the last place and the highest number will be multiplied by 10.

This is done so that you do not have to have more zeros than necessary. This is explained with an example later on in the chapter.

Next, we have the Format of the sequence. This format can be anything you like and the only thing that matters here is that # signs will be replaced by the current number. This means that if you create a sequence with the following format

ITEM####

Then the sequence string will be:

ITEM0001

ITEM0002

…

ITEM9999

ITEM10000

Lastly, we have three bool parameters. The Can be deleted will allow a Site Manager to delete this number sequence if checked. Embed store ID and Embed terminal ID will append the ID of the store and/or terminal you are working from onto all strings that the sequence produces. So if we were on store with an Id of S0001 and a terminal with an ID of T0001. The sample sequence above would instead produce:

S0001-T0001-ITEM0001

S0001-T0002-ITEM0002

Create a number sequence using a SQL script

To create a new number sequence, first you need to create a new SQL script, using the version of the last update script and incrementing the partner version, as described in How to Create/Edit a Database Table.

After you have created the SQL script, to create the new sequence you need the add the following lines to the script:

USE LSPOSNET
GO
					
IF NOT EXISTS(SELECT * FROM dbo.NUMBERSEQUENCETABLE where DATAAREAID = 'LSR' and NUMBERSEQUENCE = 'MYSEQUENCE')
BEGIN
	INSERT INTO dbo.NUMBERSEQUENCETABLE (NUMBERSEQUENCE, TXT, HIGHEST, FORMAT, INUSE, EMBEDSTOREID, EMBEDTERMINALID, CANBEDELETED, STOREID, DATAAREAID)
	VALUES ('MYSEQUENCE', 'My sequence', 99999,'SEQ#####', 1, 1, 1, 1, 'HO', 'LSR')

	INSERT INTO dbo.NUMBERSEQUENCEVALUE (NUMBERSEQUENCE, NEXTREC, STOREID, DATAAREAID)
	VALUES ('MYSEQUENCE', 1, 'HO', 'LSR')
END
GO

In this example, first we add the new number sequence, named MYSEQUENCE. This is the text that you need to use in your corresponding data object as the SequenceID.

The next line adds the starting value of the sequence, from which IDs will be generated.