Tech Tuesday : Implement Extender Form Next Number for SmartConnect Import
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.