With an Extender form, you can create standalone data within the Dynamics GP environment. With this standalone data, there are many times where the users should not enter their own ID/Key value so the numbering sequence stays consistent. In this case, we can set the ID field on the Extender Form to Auto Increment. The issue with this method when integrating data using a tool such as SmartConnect, the auto-numbering process is not automatic since it only takes works when in the Dynamics GP environment.

There is a solution to this process by writing a few lines of T-SQL to increment the next form ID and use it within SmartConnect.

Set up the Extender Form and mark the Form ID to Auto-Increment. I downloaded our Software Contract Extender Template and imported it. We can adjust the ID Field Prompt for the CONTRACT_TMP form to auto increment.

When the Extender form is opened and we click into the Contract ID field, the Next ID Value will default, which is the behavior we want. However, when integrating this data, it does not default the ID number so we must provide it. Using the Creating your own Dynamics GP Rolling Column blog article as a starting point, we can create a Stored procedure to get the Next ID value from Extender. Then use that stored procedure in a GP Rolling Column.

 

— Procedure to get the next Extender ID number

 IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N’dbo’

AND SPECIFIC_NAME = N’E1_Get_Next_Form_ID’

)

DROP PROCEDURE E1_Get_Next_Form_ID

GO

 

CREATE PROCEDURE E1_Get_Next_Form_ID

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

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

@DOC_ID CHAR(21) = ”, — Extender Form Name

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

AS

 

DECLARE @FormID 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 @FormID = Next_ID_Field_Value FROM EXT40305 WHERE Extender_Form_ID = @DOC_ID

 

UPDATE EXT40305 SET Next_ID_Field_Value = dbo.E1_fnIncrementDecrement(Next_ID_Field_Value,0)

WHERE Extender_Form_ID = @DOC_ID

 

— make sure the ID doesn’t already exist

IF EXISTS(SELECT 1 FROM EXT01200 WHERE Extender_Form_ID = @DOC_ID and UD_Form_Field_ID = @FormID)

BEGIN

SELECT @Loop = @Loop + 1

SELECT @FormID = ‘Err’

END

ELSE

BEGIN

SET @Loop = 1000

END  

END

SELECT @FormID AS Number

GO

GRANT EXECUTE ON E1_Get_Next_Form_ID TO DYNGRP

GO

 

SmartConnect MS GP Rolling Series

With the stored procedure created, we will create the SQL scripts to create the GP Rolling Column numbering pieces that can be used in SmartConnect.

Insert the Extender Series object. This uses the next available Series ID.

DECLARE @SeriesID INTEGER

SELECT @SeriesID = MAX(SeriesNumber)+1 FROM SmartConnect..MsGpRollingSeries

insert into MsGpRollingSeries(MsGpRollingSeriesId, SeriesName, SeriesNumber) values(‘Extender’,‘Extender’,@SeriesID)

 

Insert the logic for calling the Next Number stored procedure we created above.

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

values(NEWID(),‘Form ID’, 1,‘select ”Blank” as DOCID’,‘exec {CompanyId}.dbo.E1_Get_Next_Form_ID {seriesId}, {documentType}, ”{documentId}”’,‘Extender’)

 

Mapping

We won’t cover the entire processing of mapping but will show creating the new GP Rolling Column that will use the Extender Form Next Auto-Number sequence when the map is executed.

We choose the Extender and Form ID for Series and Document Type respectively. For the Document Id, we need to select either a Source Column, as I have done in choosing my FormName source column, or we can choose a calculated field that returns our Form Name.

Once you have the NEXT_FORM_ID GP Rolling Column, map it to the Record ID on the destination columns of the mapping window.

Running the map will use the next available Next ID Value set for the given form.

Be sure the FormName on the Document Id for the GP Rolling Column Lookup and the FormName on the Form ID mapping field match. If they do not match we will end up with invalid data in our Form ID.