Starting from Dynamics GP 18.3 (GP Fall 2020 Release), Microsoft added a user-defined field in the general ledger transaction entry window for Dynamics GP.
For a video explanation, see below or click here.
https://community.dynamics.com/blogs/post/?postid=3cc22276-2164-488f-b0f5-f8c826b1e44e
Issue: Field mapping for user-defined fields under the GL Entry Header node in “Create Journal” is not visible in SmartConnect.
Please follow the steps provided below, but first, kindly review the notes for additional context and information.
All the eConnect stored procedures utilized by SmartConnect are developed by Microsoft. Most of these procedures include 4–5 user-defined fields designed to pass data. These fields are typically used when custom code has been added to eConnect pre- or post-procedures to perform specific calculations or to handle actions such as pre- or post-processing activities.
In summary, the “User Defined Fields” visible in the journal entry window (specifically in table GL10000) are not available for mapping in eConnect. These fields were introduced by Microsoft, likely in GP 18.3, but the eConnect procedures were not updated to support passing data into them. As a result, it’s not possible to import data into these fields using eConnect out of the box. This leads us directly to the reason why the “developer-only user-defined fields” exist—because Microsoft did not provide a way to map the actual user-defined fields within the main eConnect node.
Here is a direct link to the Microsoft SDK documentation for the GL Transaction Header node/schema available on the MSDN website.
https://learn.microsoft.com/en-us/previous-versions/dynamicsgp/developer/jj193353(v=msdn.10)
If you scroll to the bottom of the list, you’ll find all the user-defined fields labeled as “User-defined field—developer use only.” While you can map any data to these fields, they won’t have any functional impact unless you implement your own custom logic within an eConnect procedure, such as taGLTransactionHeaderInsertPost.
Solution:
- If you have a SQL Connection set up for your company database under Connection, proceed with Option 1 – This is the recommended approach.
- If a SQL Connection is not available, then choose Option 2 instead. like in extreme case, creating a SQL connection may not be possible for you.
** Using SQL Task as a Post Document Task >>>
This is a simple method to update the field using an SQL task triggered by a post-document task, avoiding the need to modify any code or write custom logic.
Right-click on Document Succeeds > New Task > Run SQL Command > Select your SQL connection and choose your company database as shown below.
UPDATE [dbo].[GL10000] SET [User_Defined_Text01] = '_UserDefined1', [User_Defined_Text02] = '_UserDefined2' WHERE [JRNENTRY] = 'GlobalRollingColumn'
** Adding custom code within the taGLTransactionHeaderInsertPost procedure >>
01. Add through Node Maintenance to enable mapping, making the “developer to use” field available for you.
Go To >> File > Node Maintenance >> Go To General Ledger >> Transactions >> Create Journal
Add the following fields as shown below.
USRDEFND1
USRDEFND2
USRDEFND3
USRDEFND4
USRDEFND5
02. Go To SmartConnect Integration >> Go To Mapping >> Create Journal
Map USRDEFND1 to the source field userdefined1, USRDEFND2 to the source field userdefined2, and USRDEFND3 to a local constant, such as SmartConnect.
03. Modify the taGLTransactionHeaderInsertPost script to incorporate custom code, etc., against your company database.
For Example:
ALTER PROCEDURE [dbo].[taGLTransactionHeaderInsertPost]
@I_vBACHNUMB CHAR(15)
,@I_vJRNENTRY INT
,@I_vREFRENCE CHAR(30)
,@I_vTRXDATE DATETIME
,@I_vRVRSNGDT DATETIME
,@I_vTRXTYPE SMALLINT
,@I_vSQNCLINE NUMERIC(19, 5)
,@I_vSERIES SMALLINT
,@I_vCURNCYID CHAR(15)
,@I_vXCHGRATE NUMERIC(19, 7)
,@I_vRATETPID CHAR(15)
,@I_vEXPNDATE DATETIME
,@I_vEXCHDATE DATETIME
,@I_vEXGTBDSC CHAR(30)
,@I_vEXTBLSRC CHAR(50)
,@I_vRATEEXPR SMALLINT
,@I_vDYSTINCR SMALLINT
,@I_vRATEVARC NUMERIC(19, 7)
,@I_vTRXDTDEF SMALLINT
,@I_vRTCLCMTD SMALLINT
,@I_vPRVDSLMT SMALLINT
,@I_vDATELMTS SMALLINT
,@I_vTIME1 DATETIME
,@I_vRequesterTrx SMALLINT
,@I_vSOURCDOC CHAR(11)
,@I_vLedger_ID SMALLINT
,@I_vUSERID CHAR(15)
,@I_vAdjustment_Transaction SMALLINT
,@I_vNOTETEXT VARCHAR(8000)
,@I_vUSRDEFND1 CHAR(50)
,@I_vUSRDEFND2 CHAR(50)
,@I_vUSRDEFND3 CHAR(50)
,@I_vUSRDEFND4 VARCHAR(8000)
,@I_vUSRDEFND5 VARCHAR(8000)
,@O_iErrorState INT OUTPUT
,@oErrString VARCHAR(255) OUTPUT
AS
SET NOCOUNT ON
SELECT @O_iErrorState = 0
/* Create Custom Business Logic */
IF @I_vUSRDEFND3 = 'SmartConnect'
BEGIN
UPDATE [dbo].[GL10000]
SET [User_Defined_Text01] = @I_vUSRDEFND1,
[User_Defined_Text02] = @I_vUSRDEFND2
WHERE [JRNENTRY] = @I_vJRNENTRY
END
/* End Create Custom Business Logic */
RETURN (@O_iErrorState)
GO
Have a question? Please reach out to us at support@eonesolutions.com
