Question

If Record not Found in Update Data Activity, Insert Row?

Workflow Version 9

Updated May 19, 2014

asked on May 15, 2014

I am setting up a workflow that will perform a custom query on our HR database to pull back all active employees in the system. I would then like to perform an Update activity based on the employee number in another database. That part I can figure out without any problems. After that, if the employee is not already found in the database I would like to insert them into it.

I was thinking I could use a conditional decision and look at the Update Data activities results to see if there was a token that was created for a lookup that was not found, but no luck. Any ideas on how I can accomplish this?

0 0

Answer

APPROVED ANSWER

replied on May 19, 2014

You could use a Custom Query and this MSDN solution. Or give the MERGE command a try (requires SQL 2008 or higher).

0 0

Replies

replied on May 15, 2014

Blake,

I could be oversimplifying but my first thought is that you could just do a SQL SELECT COUNT(*) Custom Query Activity to see if the record exists in the HR table. Something like this as the query;

SELECT COUNT(*) AS RecordCount FROM People WHERE ID = 4110

Assign the returned field 'RecordCount' to a token then use that token in the workflow. If the RecordCount returned is 0 then the record doesn't exist in the HR table.

0 0

replied on May 16, 2014

Blake,

Checking for the existence of an employee record is effectively an oversimplification:

- in multi-user/multi-threaded environments such as Laserfiche, there is no guarantee that in between 'your' check and the following insert/update activity, someone else may invalidate the check precondition

- you should instead force an update activity then try-catch the absence of the employee record with an insert activity, provided the HR Table has a primary key enforced by SQL

0 0

You are not allowed to follow up in this post.

Question

Question

If Record not Found in Update Data Activity, Insert Row?

Answer

Replies

Sign in to reply to this post.