Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday : One SmartConnect Tracking Table to Track Multiple Table Changes


I have a scenario where I want to run an integration when either the Dynamics GP vendor or Dynamics GP vendor address changes but I don’t want two change tracking maps. We can combine the change tracking processes to simplify our integration, for example, triggering a Vendor Master table (PM00200) and Vendor Address (PM00300) in GP to store the fact the specific vendor changed. If you create a new address for a vendor, the vendor master table is not updated but we want to trigger the integration anyway. By using one tracking table, we can use a single map to handle this type of scenario.

To do this we will need to add triggers to the Vendor Address table to update the Vendor tracking table when either the vendor master or vendor address table is updated.

Set up a map with the basic information to track the Vendor Master changes. This is the tracking table and map that will be used for the integration itself.

After you set up the data source and enable tracking, click validate. The key field Sequence will be added, the change tracking table will be created and SQL Database triggers created.

If we look in SQL Server Management Studio in your GP database, we will see the change tracking table created.

Look under the Triggers folder of the table being tracked, we see the triggers that are created. Tick the box for both Create and Update under Enable Change Tracking in the map and we see two triggers.

We are going to copy these 2 triggers and add them to the Vendor Addresses table. Right click on one of the triggers and select Script Trigger as / Create to / New Query Editor Window. This script is also included.

Additional Tracking

In the first 3 circled regions below, change PM00200 to PM00300. In the 4th circled region, replace the single quotes with ADRSCODE. Execute this query and repeat these steps for the Update Trigger. Now, both of the triggers are added to the Vendor Addresses table.

Back on the map, click modify on the data source pane.

This will open the query editor window. The relationship between the tracking table and the Vendor Master table is already defined. We need to add the Vendor Address table to this query Drag table PM00300 onto the grid from the table list on the right. Create a left join from SC_PM00200_Track.Index1 to PM00300.VENDORID. This will add the address fields into the output for anything with a changed address. Choose the fields from the vendor master and vendor address that need to integrate to the destination and click OK.

We’re not concerned with the destination right now so we will output the destination to a .csv file for now.

Click save on the map.

Create / Update some records in GP in both Vendor Maintenance and Vendor Address Maintenance and run the map. Now we are getting creates / updates for both the Vendor Master and Vendor Addresses tables flowing into the same tracking table and thus through the same map.

This same process can be used for any SQL table and could include additional related tables if so desired.

 

Happy Integrating!

 

Leave a Comment





RECENT POSTS


Popdock Data Lake Upload Tool - Free Training on April 5th
eOne Sessions at Directions North America 2023
Connect with eOne Solutions at Directions North America 2023!
Tech Tuesday: Creating a Summarize List by States Between Two Systems
eOne Sessions at Days of Knowledge UK - Live from Birmingham!

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.