Skip to content
+1-888-319-3663

COMMUNITY FORUM

Smartconnect variable replaces column name

Jothikrishnan Narasimmaraj asked 8 months ago
Hi,
How do I avoid this kind of errors. It replaced _Status which is part of a column name with value of a column called _Status.
I’m trying to integrate Payroll Employees from another system and create Employee Vendor automatically with workflow status as approved.
 

Thank you
Jothikrishnan Narasimmaraj replied 8 months ago

i had a picture attached but not seeing it. so adding the actual query

From:
update PM00200
set Workflow_Status=6,Workflow_Approval_Status = 0
where Workflow_Status<>6 and VENDORID=’_GPEMPID’

To:

update PM00200
set WorkflowActive=6,Workflow_ApprovalActive = 0
where WorkflowActive<>6 and VENDORID=’HAVKY001′

Answers
Patrick Roth Staff answered 8 months ago
Use two scripts for this
1. create global variable such as GBL_TEXT
2 create .NET script in the same spot as your SQL task is
GBL_TEXT = “update PM00200 set Workflow_Status=6,Workflow_Approval_Status = 0 “
GBL_TEXT = GBL_TEXT + ” where Workflow_Status<>6 and ENDORID='” + _GPEMPID + “‘”
MessageBox.Show(GBL_TEXT)
return true
Above we set our global variable to our sql text.  And the interesting thing about .net is that the replacement of the global variables doesn’t happen inside the double quotes so your column names are going to be correct.  The actual data variable – _GPEMPID  – does as it is outside the double quotes due to concatination
We use a MessageBox.show() so we can evaluate our global and see that it is (or should be) correct
 
3 use the global in our SQL Task
Now the SQL Task script is easy – just
GBL_TEXT
we don’t need OTHER code here because GBL_TEXT now contains our complete code for the script and is correct at this point.
 
 
Jothikrishnan Narasimmaraj replied 7 months ago

Great! Thank you very much Patrick.

Regards


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