Question

Add single row back to SQL table that is retrieved from forms.

Workflow Forms Version 10 Laserfiche

Updated April 27, 2016

asked on April 25, 2016

Hi all, I am having some trouble with hopefully a simple resolution. Any ideas are graciously received.

I have a basic table I have uploaded into SQL. The main fields are Customer, Part and Description.

Using Forms 10 and Workflow;

In forms there is a drop down that contains a list of customers. Once a customer is selected it then displays the list of corresponding parts to that customer (from the SQL Table). The operator can edit what they like and any changed fields (once submitted) then get updated back to the SQL table (via workflow), the problem I have is if they add a new part, it doesn't save back to the SQL table.

Once the form is submitted, workflow initiates gets the forms business process instance and forms/table values etc, it then has no problem updating the fields that are already in the table. But I have no way of saving the new row details.

How can I make it only add the new row?

I have tried several things but to no avail. Every time I try and insert data it inserts the whole table.

1 Customer List.png (18.18 KB)

| Download

2 Form.JPG (83.05 KB)

| Download

3 Filled.JPG (96.26 KB)

| Download

4 Final.JPG (53.15 KB)

| Download

Fields.JPG (104.78 KB)

| Download

Lookups.JPG (204.67 KB)

| Download

sql.JPG (103.94 KB)

| Download

0 0

Replies

replied on April 27, 2016

Thanks for your reply. It was very helpful.

I had to declare the variables, and then this worked great. Thank you.

Final used query below.

DECLARE @company VARCHAR(50), @part VARCHAR(50), @des VARCHAR(500);
DECLARE @date VARCHAR(50), @location VARCHAR(50);

SET @company = ?;
SET @part = ?;
SET @des = ?;
SET @date = ?;
SET @location = ?;

if not exists(SELECT * FROM Parts WHERE [Company]=@company and [Part]=@part)
SELECT * FROM Parts;
insert into Parts ([Company],[Part],[Description],[DateInsp],[Location])values(@company,@part,@des,@date,@location)

1 0

replied on April 27, 2016

Final WF.

SiteAudit.png (19.63 KB)

| Download

1 0

replied on April 25, 2016

Correct Lookups table...

Lookups.JPG (225.5 KB)

| Download

0 0

replied on April 25, 2016

How does you design your workflow part for update/insert data to SQL table?

0 0

replied on April 25, 2016

See workflow attempts. (The top part works fine. It just updates the date the part was last inspected based on a value in a drop down and updates any edited field) All the greyed out components are failed and disabled and half attempts at getting it worked out.

I have tried comparing the submitted forms data to a new sql query and what wasn't found then insert data.

I have tried a multi value token with a default of 'NotFound' and mark any found parts as 'found' and only insert the multi value token with a value of 'NotFound'

I tried for each row of forms data to each row of sql data and if it doesn't exist then create it.

I thought it would be much easier to just add a single row? It would be really easy if forms could just do it... or do I have to use workflow?

Cheers

Workflow 1.png (74.17 KB)

| Download

0 0

replied on April 26, 2016

Instead of using the Insert Data activity to insert data, you should use Custom Query to insert data, in the query for the custom query activity, you can only insert when the item not found, something like this:

 if not exists(select * from Parts where [Company]=@company and [Part]=@part)
insert into Parts ([Company],[Part],[Description],[DateInsp],[Location])values(@company,@number,@des,@date,@location)

0 0

You are not allowed to follow up in this post.

Question

Question

Add single row back to SQL table that is retrieved from forms.

Replies

Sign in to reply to this post.