Question

Using Forms and Workflow to update SQL Table

Forms Workflow Laserfiche Version 11 Self-Hosted

Updated November 30, 2023

asked on November 29, 2023

Hello!

I am trying to wrap my head around how to use workflow to update the SQL table. I currently have a form that starts the workflow. The form has the columns that are in the table. My goal was to have the end user edit or do nothing to each row for each campus and it all updates to the sql table. This is what I have setup below. It does run and update all rows on the sql table but only with the first line on the form table.

Any suggestions? TIA!

0 0

Answer

SELECTED ANSWER

replied on November 30, 2023

You need to specify are above New Values, what rows it needs to find. Your current sql table will need to contain an identifier that matches the data you are pull from the form. For example, do you already have an Employee ID in the table? If so, you could use that to match the row of data you are pulling from the form so it knows what row in sql you want to update.

Here is an example from one of mine. If you want to email me at agoerner@coppellisd.com I can walk you through your WF and figure out if you are using the right activity.

1 0

Replies

replied on November 29, 2023

Hi Kaeli,

What are you using for the for each row? What are you updating the table with?

Under the for each row, it should be from the table in the retrieve business process variables.

Then updating the table you should be using the for each row information.

1 0

replied on November 29, 2023

Hi Katy,

The for each row activity is looking at the table from the retrieve business process variable. I am updating the table with user names, email, and AD credentials.

Do I need to add the rows to update?

(newer to workflow and SQL) ;)

0 0

SELECTED ANSWER

replied on November 30, 2023

Here is an example from one of mine. If you want to email me at agoerner@coppellisd.com I can walk you through your WF and figure out if you are using the right activity.

1 0

replied on November 30, 2023

one hiccup you might come across if that if you are jsut attempting to use Forms to insert/update data to SQL that there seems to be a bug around v11.0.2212.30907 where the InstanceId (which is the best unique ID for the form submission) is not available as a variable.

Our VAR verified what I found and has brought this up to LF Support.

So, to get around this, I save the submission to the repository in a known location, but not as a tiff or pdf file, that just creates an entry which gives me an entryID, Instance ID and submissionID to query the forms system from to get the rest of my data. Then, Workflow can run with that

0 0

replied on November 29, 2023 • Show version history

You need to drop your query activity and move the For Each Row to point to Retrieve. Then select the table form your form you want to grab each row.

1 0

replied on November 29, 2023

You also don't need the Query Data by the sounds of your description.

0 0

replied on November 29, 2023

It always helps me to see a sample, so this is an example where we're adding user names to our SQL table. In the Query activity, we are calling the stored procedure that writes the values; you just need to select the form variable that corresponds to each parameter.

workflow_sample.jpg (11.61 KB)

| Download

workflow_sample_sp.jpg (52.75 KB)

| Download

0 0

View 2 previous replies

replied on November 29, 2023

You'll want to use the For Each Row tokens corresponding to your variables. Retrieve Business Process Variables are always just the first row of the table like Katy mentioned above.

0 0

replied on November 29, 2023

How did you create the Query in the custom query?

0 0

replied on November 29, 2023

This is done in SQL.

1 0

replied on November 29, 2023

@████████Is there a way to find all the fields on a table in the retrieve business process variables?

0 0

replied on November 30, 2023

Yes. Retrieve Business Process Variable will show the table itself as an option to retrieve as well as its fields.

For ex, my form has a table named "Table" with 4 fields. If you sort the field list by "Token name (Variable)" all table column fields will be prefixed with the table name.

You don't need to retrieve the individual fields in order to be able to iterate through the table with For Each Row, just retrieving the table by itself will grab all data and For Each Row will generate individual tokens for the columns.

The same behavior applies to collections as well.

1 0

You are not allowed to follow up in this post.

Question

Question

Using Forms and Workflow to update SQL Table

Answer

Replies

Sign in to reply to this post.