Skip to content
+1-888-319-3663

HELP ARTICLE

Use the SmartConnect Change Tracking Table as your Data Source


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.

Picture1

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.

Picture2

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.

Picture3
Picture4

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.

SELECT b.[SOP Number], b.[Description], b.[Customer], b.[Address Code], b.[Document Date], b.[Item], b.[U of M], b.[Quantity], b.[Site], b.[Batch Number], b.[Price]
CASE WHEN SOP10100.SOPNUMBE IS NOT NULL THEN 1 ELSE 0 END as ‘HeaderExists’,
CASE WHEN SOP10100.SOPNUMBE IS NOT NULL THEN 1 ELSE 0 END as ‘LineExists’
FROM SC_EXT01200_Track a
LEFT JOIN SIMPLE_SOP b ON a.Index2 = b.[SOP Number]
LEFT JOIN SOP10100 ON b.[SOP Number] = SOP10100.SOPNUMBE
LEFT JOIN SOP10200 ON b.[SOP Number] = SOP10200.SOPNUMBE AND b.[Item] = SOP10200.ITEMNMBR
WHERE a.Processed = 0

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

Picture5

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.

Picture6

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.

Picture7

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.

Picture8

Here is what our Task window will look like.

Picture9

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.

Picture10

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.

Picture11

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.

Picture12

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

Picture13

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.

RECENT POSTS


Use the REST Connector to parse nested Value Arrays
SmartConnect email task fails with error: An invalid character was found in the email header
Does SmartConnect import into Avalara Avatax software
Could not initialize a collection when launching SmartConnect
ERROR [HY000] [Microsoft][ODBC Excel Driver]General error Unable to open registry key when you open a SmartConnect map.

POPULAR POSTS


SSL Security error using OLEDB Connection
This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site

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 partners Popdock release Reporting 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.