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.
