+1-888-319-3663

COMMUNITY FORUM

Database Connection for SmartConnect Task

dhunt@ibgnet.com asked 10 years ago

I have a data source containing records for multiple destination companies in GP. The SmartConnect Map Destination Definition window is configured to identify which company to send the records into based on one of the fields contained in the data source. This works perfectly.

I have the need to run a task after each document succeeds. The task is a Run SQL Command task which needs to run against the specific database applicable to the record that succeeded.

Is this possible? If so, what is the syntax of the SQL update query.

dhunt@ibgnet.com replied 10 years ago

UPDATE — I just resolved this issue so I thought I would post the solution for others to use. Here it is:

My situation is very similar to the eOne KB# KBA-01107-B8B3S2, except for two key areas:

1) my data source contains a field called CompanyID identifying which GP company the record should be imported

2) the SmartConnect task I'm using is a Run SQL Command (not Run Script)

When the task executes I need the SQL query to only execute against the specific GP company to which the related record was imported. To do so go to the "Create Transactions" mapping and set the variable named GlobalFileName equal to the CompanyID source field.

Then the Run SQL Command task contains the following query:

use GlobalFileName

update SY00500

set GLPOSTDT = DATEADD(DAY,datediff(day,0,GETDATE()),0)

where BCHSOURC = 'RM_Cash'

and BACHNUMB = '_BATCHID'

Final Notes:

The DATEADD function returns the current date and time from the computer and replaces the time portion with all zeros
_BATCHID refers to a field in the data source

molsen replied 10 years ago

Great work Darren – really appreciate you sharing the answer with everyone.

Janeece Moreland replied 6 years ago

How did you get the field value into the GlobalFileName?

Patrick Roth Staff replied 6 years ago

Janeece,
He would have used a calculation at some point, or a Restriction script, or “before or after” document script.
GlobalFileName = _COMPANYID (the source field)

then when he ran the SQL, the value would be populated.

If you would like to submit an answer or comment, please sign in to the eOne portal.