Filtering eConnect Requester data

Emily Halvorson - Click for blog homepageI wanted to tell you about a recent case I had where the Partner was trying to restrict the Microsoft Dynamics GP data they had selected with the eConnect Requester Setup Tool.

They were first importing some of their Sales Orders from an external system into GP with eConnect.  Then when they fulfilled and shipped the Order within GP, they used eConnect Outgoing Queue to make the updates available to the external system.  Their use of GP also includes manually entered Sales Orders and they did not want the updates to the manually entered Orders to be queued.

So, the way the Requesters work is that boxes checked on each SQL table in the Requester Setup Tool cause a SQL trigger to be created when you click the Update button in the Tool.  These triggers will fire upon an insert, update, or delete operation on the SQL table and cause rows to be created in the eConnect_Out table.  The eConnect Outgoing Service queries the eConnect_Out table on a regular basis and pulls the data into your queue.  You can look at the triggers in SQL Server Management Studio by right-clicking on the trigger and Script Trigger as Create to New Query Editor Window.

If you have some data values that distinguish the GP entered ones from the eConnect ones, you can use that to filter.  For example, if you use different Document Numbers in the eConnect Sales Orders than you do in the manually entered ones you could put an if statement before the insert into eConnect_Out... statement to make sure that the rows are only created that you want created.  The following example assumes we want Sales Orders with a Document Number starting with the letters "EC"

Example eConnect_Sales_TransactionSOP10200_UpdateTrigger change

 if (@SOPTYPE = 2 and @SOPNUMBE like 'EC%')
begin
    insert into eConnect_Out(
    .
    .
    .
end

The dots in the example are the rest of the insert statement that is already in the trigger, and you put your end statement after that. 

One last thing: if you are using eConnect to update the Sales Orders instead of updating with GP, you will need to set the RequesterTrx element to 1 (one) for this to work.

Enjoy!

Emily
Dynamics GP Developer Support

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)