Skip to content
+1-888-319-3663

HELP ARTICLE

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. http://www.eonesolutions.com/blog-post/next-dynamics-gp-document-number-smartconnect/

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

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N’dbo’

AND SPECIFIC_NAME = N’E1_Get_Next_Assembly_Document_Number’

)

DROP PROCEDURE E1_Get_Next_Assembly_Document_Number

GO

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

AS

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

BEGIN

SELECT @AssemblyDocument = TRX_ID FROM BM40100 WHERE SETUPKEY = 1

 

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

WHERE SETUPKEY = 1

— make sure the document doesn’t already exist

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

BEGIN

SELECT @Loop = @Loop + 1

SELECT @AssemblyDocument = ‘Err’

END

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

BEGIN

SELECT @Loop = @Loop + 1

SELECT @AssemblyDocument = ‘Err’

END

ELSE

BEGIN

SET @Loop = 1000

END

END

SELECT @AssemblyDocument AS Number

GO  

GRANT EXECUTE ON E1_Get_Next_Assembly_Document_Number TO DYNGRP

GO

 

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

Field

Description

MsGPRollingDocumentId

Use NEWID() to generate a random GUID

DocumentType

Display name when picking the document from GP Rolling Column field

DocumentTypeNumber

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>’

DocumentTypeIdQuery

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

ExecuteCommand

SQL Statement SmartConnect will execute to get the next document number

MsGPRollingSeriesId

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.

Field

Description

MsGpRollingSeriesId

The text to identify the series

SeriesName

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

SeriesNumber

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.

Field

Description

{CompanyId}

Replaced with the Dynamics GP database

{seriesId}

Series Number from the MsGpRollingSeries Table

{documentType}

Document Type Number from the MsGpRollingDocument table

{documentId}

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]

GO

— 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’)

 GO

 

 

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.

 

RECENT POSTS


Use the REST Connector to parse nested Value Arrays
SmartConnect email task fails with error: An invalid character was found in the email header
Does SmartConnect import into Avalara Avatax software
Could not initialize a collection when launching SmartConnect
ERROR [HY000] [Microsoft][ODBC Excel Driver]General error Unable to open registry key when you open a SmartConnect map.

POPULAR POSTS


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

CATEGORIES

TAGS

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 partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com 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.