Get Data from Dynamics GP using eConnect Requester

eConnect is a Dynamics GP API to get and write data using GP business logic. eConnect Requester is way to get retrieve from Dynamics GP.

To use the requester, install eConnect, and then browse to the Requester Setup folder, for GP 2016 located at: C:\Program Files\Microsoft Dynamics\eConnect 16.0\Requester Setup

Then, open the RequesterSetup.exe:

Select Connection Settings->Setup Connection:

Enter your database connection information:

Click Connect, and the window will refresh with information regarding the document type, alias and table name. Note some document types such as “Sales_Transaction” will have multiple tables:

Check a row you would like to monitor, e.g. when the Customer Master table RM00101 is populated:

Note the other tabs, update and delete. Check these if you would like to monitor these events:

Message queues allow us to configure where the messages will be created.

Once you have your settings selected, press Update:

Now, in SQL Server Management Studio, you will see a new trigger has been created on the RM00101 table in the database:

The trigger code monitors insert on the table and inserts changes into the eConnect_Out table in the same database:

CREATE TRIGGER [dbo].[eConnect_CustomerRM00101_InsertTrigger] 
ON [dbo].[rm00101] 
FOR INSERT 
AS 
    DECLARE @required CHAR(50), 
            @DRI      INT, 
            @CUSTNMBR VARCHAR(50) 
    DECLARE econnect_customerrm00101_inserttrigger CURSOR FOR 
      SELECT CONVERT(VARCHAR(50), custnmbr, 121) 
      FROM   inserted a 

    SET nocount ON 

    OPEN econnect_customerrm00101_inserttrigger 

    FETCH next FROM econnect_customerrm00101_inserttrigger INTO @CUSTNMBR 

    WHILE ( @@FETCH_STATUS <> -1 ) 
      BEGIN 
          IF ( NOT EXISTS (SELECT 1 
                           FROM   econnectouttemp (nolock) 
                           WHERE  doctype = 'Customer' 
                                  AND index1 = @CUSTNMBR) ) 
            BEGIN 
                SELECT @DRI = 0 

                SELECT @DRI = Isnull(dex_row_id, 0) 
                FROM   rm00101 (nolock) 
                WHERE  custnmbr = @CUSTNMBR 

                IF ( @DRI > 0 ) 
                  BEGIN 
                      SELECT @required = custnmbr 
                      FROM   inserted 

                      IF @required <> '' 
                        BEGIN 
                            DELETE econnect_out 
                            FROM   econnect_out b (updlock) 
                            WHERE  ( b.doctype = 'Customer' 
                                     AND action = 1 
                                     AND index1 = @CUSTNMBR ) 

                            INSERT INTO econnect_out 
                                        (doctype, 
                                         tablename, 
                                         dex_row_id, 
                                         index1, 
                                         index2, 
                                         index3, 
                                         index4, 
                                         index5, 
                                         index6, 
                                         index7, 
                                         index8, 
                                         index9, 
                                         index10, 
                                         index11, 
                                         index12, 
                                         index13, 
                                         index14, 
                                         index15, 
                                         action) 
                            SELECT 'Customer', 
                                   'RM00101', 
                                   @DRI, 
                                   @CUSTNMBR, 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   '', 
                                   1 
                        END 
                  END 
            END 

          FETCH next FROM econnect_customerrm00101_inserttrigger INTO @CUSTNMBR 
      END 

    DEALLOCATE econnect_customerrm00101_inserttrigger

You can see the eConnect Out table is generic, holding the table name and several index fields:

Now in Dynamics GP, if we enter a new customer and save it:

We will see the record is populated in the database:

From here, you can use this table to integrate Dynamics GP records with other systems.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin