Question

How to insert data into SQL Server from a table with repeatable rows?

Forms Workflow

Updated April 18, 2022

asked on March 1, 2016 • Show version history

I'm trying to insert data captured from a table that has repeatable rows. I'm running into a couple problems:

I seem to only be able to insert the first row of the table. I see most people use "For Each Row" to fix this, but I don't think I'm able to use that since I'm retrieving all the data from "Retrieve Field Values" instead of "Retrieve Laserfiche Forms Content".

Additionally, the form has a few fields that are not apart of the table, but need to be inserted with each row into SQL. For example - the user enters one sales order number and then enters all the lot numbers associated with that sales order. So there will be multiple lot numbers entered, but only one sales order.

When inserting the data, I would like that sales order number to be associated with every lot number - so each row inserted needs to have both the sales order and each respective lot number.

Thanks!

0 0

Answer

SELECTED ANSWER

replied on March 1, 2016

You can use For Each Value to iterate through a multi-value field token generated by Retrieve Field Values.

I'm sort of confused though. If you do have a form, then why not use the form data? Why use Retrieve Field Values?

2 0

View 1 previous reply

replied on March 1, 2016

Miruna,

I switched to Retrieve Laserfiche Forms Content to use For Each Row. It is now inserting the correct number of rows, but it is just repeating the first one for each. Any idea how to make it move on to the next row?

edit: Never mind I got it. Just had to use for each row within the insert data.

Thanks

0 0

replied on July 15, 2016 • Show version history

David,

I'm experience the exact issue you describe (repeating first row). However, I don't understand you description of the fix "use for each row within the insert data". Can you elaborate?

As a side note, in the Retrieve Business Process Variables activity it says: "Note: Collection and table fields (marked with an asterisk above) can only be accessed by using a For Each Row activity." Only the table itself is marked with an asterisk, the columns/fields within the table are not marked.

Thanks,
Rob

edit: Nevermind, I figured it out too.

0 0

replied on June 22, 2018

It took me a minute to figure this out so thought I would add what threw me off. Firstly the Retrieve Laserfiche Forms Content activity has been renamed to Retrieve Business Process Variables. When you set that up make sure you are selecting the forms table (it will have a type of Table* in the dialog) as well as any of the variables within the table that you want to use later. Then use a For Each Row activity and set it to Get Rows From your Retrieve Business Process Variables and then the table. Then you can use an Update Data, Insert Data, etc... activity within the For Each. In that 'manipulate' Data activity make sure the Rows to Update section is set to use a value/token from the For Each Activity, it should look something like, "%(ForEachRowinTable_YourTableValue)" I assume the same would apply for the New Values section, but my workflow didn't actually need that.

2 0

replied on April 18, 2022

Thank you Alon, Miruna, and David!! That was exactly what I was missing!!! Just fixed and now all rows are updating :) Thanks again

0 0

Replies

replied on July 3, 2020

I am trying to insert multiple rows of data from a form to a database table using workflow.

I have Retrieve Business Process Variables Activity and For Each Value Activity ( not able to use For Each Row). But again it is creating multiple rows but with the data from the first record is repeated.

How did you resolve your issue?

0 0

View 1 previous reply

replied on July 6, 2020

I have attached the workflow screenshot with the issue I am having when I use For each Row

For Each Row.PNG (75.97 KB)

| Download

0 0

replied on July 6, 2020

What does the Retrieve Business Process Variables activity looks like? As Alon said above, you need to make sure you selected the table or collection, not its individual columns.

1 0

replied on July 6, 2020

Miruna,

I overlooked that part, and was selecting individual columns. For each Row can now see that collection.

Thank you!

0 0

You are not allowed to follow up in this post.

Question

Question

How to insert data into SQL Server from a table with repeatable rows?

Answer

Replies

Sign in to reply to this post.