Skip to content


Creating your own Dynamics GP Rolling Column

SmartConnect has an Additional Column type for the Dynamics GP Destination types called GP Rolling Column that is used to get the next transaction number from the Dynamics GP setup tables. This allows for maintaining the existing Dynamics GP numbering sequence for all transactions and prevents the potential for duplicate document numbers.

Not every Dynamics GP transaction type next number process has been created within SmartConnect and there are many 3rd Party products that may require the same functionality. However, we have the ability to insert records into SmartConnect tables to add our own numbering process to our SmartConnect integrations. If you are not sure of the current available GP Rolling Columns, review this article first.

The custom next number functionality is available with SmartConnect 20.15 and newer.

To show how we can accomplish this, we will use the Dynamics GP Inventory Assembly process as an example.


Stored Procedure

The first step is to create a stored procedure that retrieves the next document from the Dynamics GP setup table, increments it and puts the new number back into the Dynamics GP setup table. You will want to discuss where the transaction number is stored with the vendor who wrote the module being integrated. Be sure to create the stored procedure in each Dynamics GP Company or an error will occur during the integration where this procedure does not exist.

This SQL Statement gets the next document number from the BOM40100 table and increments it by one, putting the new value back into the BOM40100 table. Then it checks to see if it may have been used already and if so, it will increment the number again. So it does not loop forever, it will check 1000 times to see if it can get a valid number and errors if it can’t find an unused number.


— Procedure to get the next Assembly Document





AND SPECIFIC_NAME = N’E1_Get_Next_Assembly_Document_Number’


DROP PROCEDURE E1_Get_Next_Assembly_Document_Number


CREATE PROCEDURE E1_Get_Next_Assembly_Document_Number

@TYPE SMALLINT, — {series id } Dynamics GP Series/Module ID, if needed

@DOC_TYPE SMALLINT, — {document Id} DocumentTypeNumber from MsGpRollingColumn table

@DOC_ID CHAR(21) = ,

@INC_DEC TINYINT = 1 — 1 is increment, 0 is decrement


DECLARE @AssemblyDocument VARCHAR(21)

DECLARE @I_tInc_Dec tinyint

DECLARE @O_iErrorState int

DECLARE @Loop int = 0

SET @I_tInc_Dec = @INC_DEC  

WHILE @Loop < 1000


SELECT @AssemblyDocument = TRX_ID FROM BM40100 WHERE SETUPKEY = 1


UPDATE BM40100 SET TRX_ID = dbo.E1_fnIncrementDecrement(TRX_ID,0)


— make sure the document doesn’t already exist

IF EXISTS(SELECT 1 FROM BM10200 WHERE TRX_ID = @AssemblyDocument)


SELECT @Loop = @Loop + 1

SELECT @AssemblyDocument = ‘Err’




SELECT @Loop = @Loop + 1

SELECT @AssemblyDocument = ‘Err’




SET @Loop = 1000



SELECT @AssemblyDocument AS Number


GRANT EXECUTE ON E1_Get_Next_Assembly_Document_Number TO DYNGRP



SmartConnect Rolling Document

The next step is to insert a record into the MsGPRollingDocument table so it will appear in our GP Rolling Column list.
The fields for this table are as follows




Use NEWID() to generate a random GUID


Display name when picking the document from GP Rolling Column field


Next available sequence number

Use this SQL statement to get the next available document type number based on the Rolling Series ID.

SELECT MAX(DocumentTypeNumber) + 1 FROM MsGpRollingDocument WHERE MsGpRollingSeriesId = ‘<MsGpRollingSeriesId>’


SQL Statement SmartConnect will execute if additional data is required such as Checkbook ID or SOP ID.


SQL Statement SmartConnect will execute to get the next document number


Dynamics GP module where the document will be selected

Use this SQL statement to view the existing Series within SmartConnect.

SELECT DISTINCT(MsGpRollingSeriesId) FROM MsGpRollingDocument



SmartConnect Rolling Document Series

If the next document you want to create is not in an existing Series, like Inventory or Financial, you can insert a record into SmartConnect MsGpRollingSeries to create a new Series for use with the Dynamics GP Rolling Column.




The text to identify the series


The display text used in the SmartConnect Dynamics GP Rolling Column window


Next available sequence number

Use this SQL statement to get the next available document type number based on the Rolling Series ID.

SELECT MAX(SeriesNumber) + 1 FROM MsGpRollingSeries



SmartConnect Variables

The following replacement variables are available to be used as data to pass to your stored procedure, if needed. You can use these variables in the Execute Command section of the MsGpRollingRolling column as they will be replaced with the values from SmartConnect.




Replaced with the Dynamics GP database


Series Number from the MsGpRollingSeries Table


Document Type Number from the MsGpRollingDocument table


ID result from the DocumentTypeIdQuery in the MsGpRollingDocument table




NOTE: Replace [SmartConnect] with the correct SmartConnect System database if it is not the default.


USE [SmartConnect]


— If we are using a new custom series, insert into the MsGpRollingSeries first

— insert into MsGpRollingSeries(MsGpRollingSeriesId, SeriesName, SeriesNumber) values(‘Custom Series’,’Custom Series Name’,{Series Number})

— Need to insert a record to create the next document number

insert into MsGpRollingDocument(MsGpRollingDocumentId,DocumentType,DocumentTypeNumber, DocumentTypeIdQuery,ExecuteCommand, MsGpRollingSeriesId)

values(NEWID(),‘Assembly Document’,10,,‘exec {CompanyId}.dbo.E1_Get_Next_Assembly_Document_Number ”{seriesId}”, ”{documentId}”’,‘Inventory’)




Dynamics GP Rolling Column

Once we have created the stored procedure and added our Rolling Column to the database we can add the GP Rolling Column to our mapping.

Open the node mapping.

Click on Additional Columns.

Click on Columns->GP Rolling Column

For my example, we select the Inventory series and then our new Document Type of Assembly Document.

Click Ok and Ok.

Then Map your new GP Rolling Column from the Source Columns to the Transaction Number on the Destination Columns.


Now, when we run our integration it will retrieve that next document number each time we send a document to Dynamics GP.



No Products were found in this instance of Great Plains. Please run the resource cache inside Great Plains before setting up a change data source
SmartConnect preview shows extra string characters in the header row.
HTTP Error 500.19 - Internal Server Error when browsing the SmartConnect WCF web service. Inactivity Timeout error running integration
SmartConnect ODBC query error - Restricted data type attribute violation


SSL Security error using OLEDB Connection
This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site



Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.