Use the SmartConnect Change Tracking Table as your Data Source

The great thing about a SmartConnect Changes Only integration is that it sets up the SQL Trigger and tracking table for us. To make the process in SmartConnect work, we have to have a field that marks the change table as processed. This extra field, called Sequence or DEX_ROW_ID (for extender tracking) is required as a Key Field for any Changes Only integration.

There may be times when we want to not include the sequence as part of our data grouping. To do this, we can create a second map to handle the updates of the Change Tracking table.

In this example, I have created a simple Extender form for entering SOP documents. Whenever my line items change, I need to ensure my Dynamics GP SOP document is created or updated and due to the number of Extender updates, the grouping that includes the DEX_ROW_ID as a key field does not work as I would like

Set up the Change Tracking Table

We can use SmartConnect to create a new map to generate the tracking table and triggers to track the changes to my Line data. Then we will create a second map that will use the SmartConnect tracking table as the data source and generate the SOP document within Dynamics GP.

Here is my simple Extender form with really just the basic data elements I would need for a SOP document.

We will create our first map to generate the tracking table against our Extender form which will be the standard process within SmartConnect to create any Changes Only integration.  However, with this map, we can actually remove the Sequence (DEX_ROW_ID for Extender) since we are not ever going to execute this map. We will use the SOPNumber and Sequence as our key fields as if we were going to export the data.

The destination doesn’t really matter in this scenario since we are only creating this map to generate the change tracking table. But we do require a destination for any map so a simple File Export will do.

Tracking Table Data Source

The next step is to create a map that uses our tracking table as the data source where we can generate our own query and use the key fields we need to keep our data flowing as expected.

The tracking table in this scenario is SC_EXT01200_Track, since the main Extender table for my data is stored in the EXT01200 table. I will need to join this table to our Extender View we created in Extender that contains the flattened version of our Simple SOP Extender data. In addition to the Extender View I will join to the SOP Header and SOP Line tables to see if this SOP document and SOP Line already exist so I can update the existing document.

The query below will give us the data we need to create our SOP document when this map runs and is only that data that hasn’t been processed yet. We will use this query as our ODBC Data Source Query.

The Key Fields is only the SOPNumber since I want to create only one document per SOP Number from our data source.

We map our Sales Transaction Header as we would normally. Note the Update If Exists option is set to our HeaderExists field as we have determined whether the document already exists in our source query.

Be sure to group by the same value as our Key Fields so we get the right number of header documents.

Now we map our Sales Line fields, doing the same thing for handling an existing SOP Line. We do need to group on the Item and SOPNumber for the line due to the number of tracking records created for the Extender Changes Only integration. If we don’t group our records, we’ll get too many line items. Depending on your data source, you may or may not need to group this node.

Update Processed Flag

The last piece for this map is to create a Document Success SQL command to update the processed flag for those documents that hadn’t been processed yet. Typically, this process would be handled with the Changes Only data source, but we are bypassing the normal SmartConnect processing and doing this part manually in our map. The _SOPNumber variable place holder will be replaced by the actual value when we run the integration ensuring we update all change tracking records for our SOP document.

Here is what our Task window will look like.

Process the SOP Data

Go into Dynamics GP to pull up the Extender form and enter our simple SOP information. When we enter the data into the Extender window and save, it will trigger the tracking table inserts.

Because of the number of updates that are done to the Extender Header record, we will end up with more records in our tracking table than just one per save as we can see when we do a preview of the data within SmartConnect using our CREATE_SOP_DOCUMENT map.

Since, our key field is set to SOPNumber in this case so it will only try to create one SOP document per SOP Number from our data source.

Now, we will run the SmartConnect map to generate our SOP Document in Dynamics GP and upon success, we have our new SOP Document. The way we have designed this integration, any updates to the Extender form will also update or add lines to the existing document.

Previewing our data within the map should now show no records since they will have been marked as processed.

This map could be put on a schedule to run every 5 minutes or whenever required to get the data into Dynamics GP.

This process can be followed for any Change Tracking source and any destination.

Happy Integrating.