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

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, http://opensource.org/licenses/ms-pl.html.)

Comments (6)

  1. VJ says:

    Hi David – Thanks for your article on requester. It was is interesting to configure using the requester tool – especially if you haven’t done it before. Well, I worked on an integration project not too long ago where we had similar situation. We needed to filter out the data based on the vendor class. We obviously thought about changing the trigger but decided against it because someone said that we would have to maintain this trigger every time we did a service pack or upgrade. We would have to recreate this trigger because it would be overwritten. Is that really the case? Is there any other approach you can think of to accomplish this? Thanks, VJ

  2. emily - msft says:

    Hi VJ,

    I’m glad you enjoyed my article.

    As of Dynamics GP 10.0, the eConnect updates should not overwrite triggers in the SQL database: *the GP updates would*

    ANY customizations should be backed up, whether they are changes to the eConnect Pre/Post procs, Requestor Triggers, or any other tweaks for SQL, Dexterity, etc.

    After an update, you would then need to check objects you’ve customized to see if they changed since the previous update.  If they have not, you can simply reinstall your customizations.  If they have changed, you have some work to do.

    We do not currently have anything automated for this comparison process.

    Regards,

    Emily

  3. brent Olsen says:

    Thanks for the article, so little on eConnect out there!

    Can I use this method to join to another table to check a value?  So in your example I would join to my table and then use a column from my table instead of the @SOPTYPE and @SOPNUMBE variables in your example.  

    Is it easier to use a view?  Can't seem to get that to work…

  4. David Musgrave says:

    Hi Brent

    This example is using the variables passed into the script in the if statement. You can take the variables and perform whatever select statements you want and grab the results and use them in an if statement.

    This would not specifically be a join, but would the conditional decision can be made on data from another table.

    David

  5. Derek Dahl says:

    Hi Emily:
    I’m working with a developer who is using eConnect 2013 to post Sales Orders to GP from their SQL based software application. They are able to post the base transaction data, but they say that eConnect is very strict and cannot (doesn’t allow) map data to the ‘user defined’ fields, or fill in the ‘Ship to’ address information when creating a Sales Order.
    my simple question is whether that is truly a limitation of eConnect, or if perhaps they’re just not familiar enough with eConnect to program it.
    Thank you for any clarification you can offer.

    1. Hi Derek

      eConnect has customer pre and post stored procedures for each object which are called automatically and receive the same parameter lists. You can easily use a post stored procedure to map the additional parameters to the required fields.

      David

Skip to main content