Skip to content
+1-888-319-3663

COMMUNITY FORUM

Update Data Source

Brian asked 8 years ago
I want to update an integration status field in my source SQL table after each document is processed.  Would this be accomplished using a Document Task?  If so, would the task used be Run Script, Run Map, or Run SQL Command?  Does anyone have a sample they could share?
Lorren replied 8 years ago

Brian,

I would use a Document Task when it succeeds and that task would be either Run Script or Run SQL Command.  If it's a SQL table, then using Run SQL Command would be the better option in my opinion.

Lorren

Brian replied 8 years ago

Thanks Lorren

Sherry replied 8 years ago

If I run a script that says "update [table] set integrated = 1', is that going to update ALL records in [table] or just the doc that was just successfully integrated before I ran this Document task/success?  Or do I need to qualify WHICH record it needs to update?  If I need to qualify with a where clause, how do I tell it what doc just ran?

Lorren replied 8 years ago

Sherry,

You would need to specify the document restriction in a where clause.

Select a SQL Task in the Document Succeed Event
Get the Source document by click on the Insert Variable button.

Your SQL Statement would be something like

update [table] set integrated = 1 WHERE document = '_Document'

if your document is a string you must include the single quote.  Smart Connect will replace the variable name with the actual value when the task executes.

Hope that helps

Answers
Best Answer
Sherry answered 8 years ago
Lorren,   Thanks for your reply.  It does help.  I accomplished the same thing yesterday by passing the value of _Document to a global variable and then calling that global variable in my where clause.  Is there a performance difference between your way and my way?  We are going to be submitting 80k+ transactions like this, so every little bump in speed will help us.  Thanks again!
Chris answered 8 years ago
The difference between the way you are doing it and the way Lorren described should be negligible. You will just end up having a couple extra steps of setting and retrieving that value from the global variable which is a very quick operation in relation to actually executing that update statement, so you should be fine with how you have it.

H

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