Can workflow check data before inserting data?

asked on March 6, 2023

I have been working on a workflow to Insert Data every time a new work order line in a table is filled in. I was hoping I could get it to check first and only Insert Data if the date was today and then I realized that I also need to make sure it doesn't insert the same data twice.

DESIGN: This form process has a User Task that loops around until the individual overseeing the process decides everything is complete. He can order additional tasks by adding a new line to the Work Order table any time by opening the assigned form that is still on the looping User Task.

PROBLEM: A user might open the User Task and add a line for a new Work Order at 10am and then later in the same day open up the same User Task and add another new Work Order. This would kick off the workflow a second time and it would Insert the data for BOTH lines that have today's date! Not wanted.

HOPING FOR: Can workflow be programmed to check the SQL db first, to see if that ROW ID is already in the db and then only insert new data if it did not find that ROW ID?

0 0

SELECTED ANSWER

replied on March 7, 2023

You can use the activity Conditional Sequence, which works like an if true then statement.

But when it comes to SQL, it also includes if statements. So you can save a lot of overhead by executing your entire request in one transaction.

2 0

View 10 previous replies

replied on March 8, 2023

Interesting, Chad! I will definitely be playing with this! Will let you know how it goes.

Thank you to both individuals that responded! The Query Data, as soon as you mentioned it, I remembered using it before and knew I should be able to set up a query then a conditional activity that would run if it did not find the matching row.

0 0

replied on March 8, 2023

Okay, Chad, could I get your help setting up what you suggested? I did the query before the conditional decision and it works great! My first few tests, it did not find the row so it added the detail and the next test it did find the row so it DID NOT add the detail into the database, as required. HOWEVER

It does not seem to be able to match to the unique ID. It looks like it can only match to one item in the SQL db. I need it to first find the SRM ID and then look for that SRM with a specific unique ID which is rows in the originating SRM table. Day One, one work order could get added to the work order table. Day Two, another work order could get added. When the workflow runs, I need it to NOT add the data for ROW 1 which was added on Day One, but I do want it to add the data for ROW 2 in the table which was added on Day Two.

So, Chad, how did you use that query that you are showing in the black screen above? Is it set up in the SQL studio and then you point the workflow's query activity to that SQL query? I'm not sure how this works.

0 0

replied on March 8, 2023

It is just the Query Activity in Workflow Zhiyong imaged earlier. You can write any transaction in this activity, no need for SQL Studio.

There is an option to populate variables for use in the statement. Just enter a variable name such as @UniqueID and pick a value from your workflow.

Then reference the variable, @UniqueID, directly in the query.

0 0

replied on March 8, 2023

Oh, its the Custom Query! Okay... set that up now and ran a test. No change. The second round in the For Each Row is still finding the "Is It Already In There" condition as Yes, when it should be no.

0 0

replied on March 8, 2023 • Show version history

Okay, so just tried something different... took out the conditional branches and moved the Custom Query up so it is just inside the For Each Row activity. Now I'm getting an error "ODBC SQL Server ... COUNT field incorrect or syntax error.

0 0

replied on March 8, 2023

Your query itself is handling the condition, it will not insert if it already exists. All you need is the query. You can test it manually to confirm it is indeed not entering a new row.

0 0

replied on March 8, 2023

I simplified and just tried this (pasted below) and now its saying "incorrect syntax near 'mow'" yet there is no 'mow' in this query. ??

IF NOT EXISTS (SELECT * FROM dbo.SRM_Inspection_Requests WHERE [SRM ID] = ? AND [Unique ID] = ?)

BEGIN
INSERT INTO dbo.SRM_Inspection_Requests (Comments, Hamlet)
VALUES (%(ForEachRow_Instructions), %(PatternMatching_Which Hamlet))
END

0 0

replied on March 8, 2023

Okay, it's something with the variables I'm using for the values. I hardcoded fake values in and it worked!! the whole thing worked as it should!

0 0

replied on March 8, 2023 • Show version history

Okay, so I added one more of the columns I need (I had removed all the columns with two word names) and tried it this way and it worked!

Thanks, Chad! Looks like I'm on my way to making this work!

0 0

replied on March 8, 2023

That is the best way, since you only need to make one connection allowing the SQL server to do the logic.

Your parameter names do not need to be ?, you should be able to write clear names like @SRMID, @UniqueID, etc

0 0

replied on March 8, 2023

Okay, so... looking at @SRMID (which has a space in there) ... how would you write this same custom query? Mine now looks like this:

0 0

replied on March 8, 2023

I just mean where your entering "?" under parameter name, that is not necessary.

You can enter easy to read parameter names instead of just a question mark. I usually start them with an @ symbol to make it clear that it is a parameter.

Now in your script, instead of writing ?, you can write clearly defined parameter names so that your script is readable and understandable.

1 0

replied on March 8, 2023

I'll have to try that.

I am pleased to report that this is working 100% correctly now! So grateful for all your help!

0 0

replied on March 13, 2023

Thanks, Kevin... you've got me stumped, though. I don't have enough experience to know how to do that. Could you provide details?

replied on March 16, 2023

You'd still have to run a custom query where you call the stored procedure and define the parameters that you're sending it, but the stored procedure is saved on the SQL server itself. You define what the parameters are and what to do with those parameters. It greatly simplifies your custom query and also increases reusability. It also allows the SQL server to perform the logic on what to do rather than anything in your workflow. Here's a decent article on how to get started with stored procedures:

Select, Insert, Update, Delete Using Stored Procedure in SQL Server (c-sharpcorner.com)

Question

Question

Can workflow check data before inserting data?

Answer

Replies

Sign in to reply to this post.