# Generating *SEQ (LNITEMSEQ etc) column values in Dynamics GP

### How does LNITMSEQ work?

There are sequence columns used to introduce order into rows within Dynamics GP. Examples are such as LNITMSEQ that identifies the line numbers of sales orders in SOP10200 and SOP30300. When new rows are appended to these tables, the sequence column value becomes 16384 * the line number. Basically 16384 is added to the last existing value for the sequence in the table. By having gaps in the numbering, this allows records to be inserted in between the existing records without renumbering the entire sequence, with the performance impact that would have had back in the era when GP was born. This is a similar technique that BASIC programmers used to use, to allow code to be inserted, lines would be incremented in tens.

Line number |
SEQ |

1 | 16384 |

2 | 32768 |

3 | 49152 |

Note that although there is a mathematical relationship between the row number and the sequence number to begin with, if new lines are inserted and/or deleted, that relationship is immediately destroyed. Finding line numbers is better done using SQL windowing functions like ROW_NUMBER() than trying to derive it from the SEQ.

Below is a demonstration, where a line has been inserted after the first line, the sequence value is half way between the adjacent values (24576).

Line number |
SEQ |

1 | 16384 |

2 <inserted> |
24576 |

3 | 32768 |

### Applying that learning

If there is a requirement to “bulk” insert rows after the last record and the table has a sequence column, the next whole multiple of 16384 after the last sequence number is required.

To find the next number in the sequence I wrote the following SQL:

@tmpSEQNUMBER = CEILING(CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) + 1

- CEILING((CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) % 1)

**C#**

int NextLineSequenceNumber =

Convert.ToInt32((Math.Ceiling(MaxLineSequenceNumber / 16384) + 1)

- Math.Ceiling(MaxLineSequenceNumber / 16384) % 1) * 16384;

**VB.NET**

Dim NextLineSequenceNumber As Integer =

CInt((Math.Ceiling(MaxLineSequenceNumber / 16384) + 1)

- Math.Ceiling(MaxLineSequenceNumber / 16384) Mod 1)

* 16384

This is calculating the next

**row number**where the sequence left off.

(ROW_NUMBER() OVER (ORDER BY SOPNUMBE) -- actual row number

+ @tmpSEQNUMBER -- row number offset

) * 16384 -- GP multiplier

### Real world example

**SOPNUMBE**, @

**SOPTYPE**, @

**LNITMSEQ**, @

**CMPNTSEQ**define the sales order line.

DECLARE @tmpSEQNUMBER INT

SELECT @tmpSEQNUMBER = CEILING(CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) + 1

- CEILING((CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) % 1)

FROM SOP10203

WHERE SOPNUMBE = @SOPNUMBE

AND SOPTYPE = @SOPTYPE

AND LNITMSEQ = @LNITMSEQ

AND CMPNTSEQ = @CMPNTSEQ

-- if no pre existing rows @tempSEQNUMBER will be null, so seed with zero

SELECT @tmpSEQNUMBER = ISNULL(@tmpSEQNUMBER, 0)

INSERT INTO [SOP10203] (

[SOPNUMBE]

,[SOPTYPE]

,[LNITMSEQ]

,[CMPNTSEQ]

,[SEQNUMBR]

,[ITEMNMBR]

,[LOCNCODE]

,[BIN]

,[QTYTYPE]

,[QUANTITY]

,[POSTED]

)

SELECT SOPNUMBE

,SOPTYPE

,LNITMSEQ

,CMPNTSEQ

,(

ROW_NUMBER() OVER (

ORDER BY SOPNUMBE --fake order

) + @tmpSEQNUMBER

) * 16384

,--generate seq

ITEMNMBR

,LOCNCODE

,BIN

,QTYTYPE

,QUANTITY

,0

FROM [CA_SOP_PicklistDetailBins] --our bin allocation to apply

WHERE SOPNUMBE = @SOPNUMBE

AND SOPTYPE = @SOPTYPE

AND LNITMSEQ = @LNITMSEQ

AND CMPNTSEQ = @CMPNTSEQ

AND DT_Pick_No = @PICKNUMBER