You are viewing limited content. For full access, please sign in.

Question

Question

Insert Forms table rows into SQL from Rules [Cloud]

asked on September 22, 2022

Hi,

Anyone know how to insert all the rows in Laserfiche table into SQL table?

I've tried using Workflow with Insert Rule but its only insert 1 row.  It doesn't iterate thru all the rows.

Please help!

0 0

Answer

SELECTED ANSWER
replied on September 26, 2022

You can use a For Each Value on of the "column" tokens and use the iteration to index the values in the other "column" tokens to insert each "row" into the SQL table.

Alternatively, you can write a custom script that will use the tokens and insert them into SQL using remote agents.

0 0

Replies

replied on September 22, 2022

I use self-hosted rather than Cloud, however, I'd assume the issue is that you have to use a loop in order to insert multiple rows because there's no "bulk" insert activity.

Cloud workflow should have a For Each Row loop activity, which you would configure to iterate through all the rows of the form table.

Then, inside of that loop, you would have your database insert activity; that way you can iterate the form table and do an insert for each row.

0 0
replied on September 22, 2022

Hi Jason,

Agree, thats how we do it on the premise. However in CLoud, there's difference on how they passing variables value (No retrieve bp activity in cloud) to workflow.

You have to create parameter's token in wf first in order to pass variable value. Issue is, I can not use For Each Row on this token. It doesnt appear in the selection of variable to iterate. 

Inserting every row from forms table to sql should be possible in Cloud as I think this feature quite important. 

0 0
SELECTED ANSWER
replied on September 26, 2022

You can use a For Each Value on of the "column" tokens and use the iteration to index the values in the other "column" tokens to insert each "row" into the SQL table.

Alternatively, you can write a custom script that will use the tokens and insert them into SQL using remote agents.

0 0
replied on September 26, 2022

At the moment, this suggestion: "For Each Value on of the "column" tokens and use the iteration to index the values in the other "column" tokens to insert each "row" into the SQL table" It works totally erratic.

 

Does anyone know when it works stable?

0 0
replied on September 26, 2022

We have had no reports of this behavior. Please open a case with Tech Support and provide the process and details of what is not working in expected ways.

0 0
replied on September 26, 2022

My case is from Form Table to Data Management.

I already opened a case.

0 0
replied on September 27, 2022

Support helps me find the issue in the configuration for my case:

"indexing the multivalued tokens with the For Each Value>Iteration token instead of Current Value".

0 0
replied on September 29, 2022

Hi, 

Support help me go thru all steps and its working! well, sort of.

Basically all the steps are same as what Miruna & Gerado said.

The problem I'm having now is the workflow iterate more that number of rows.

For example, I only add 3 rows in the Forms. When insert this value to SQL from workflow, it inserted 28 rows. The first 3 inserter rows are correct. But The rest of 25 row basically repeating the 3rd row value. 

Any idea?

0 0
replied on September 30, 2022

Hi all,

 

Update on this question. I manage to figure it out. 

In the For Each Value activity, make sure to choose only one column (any column i believe?) when creating the token.

Then inside For Each Value Activity is your Insert Rule. For the Rule, select every column you want to insert into SQL with Index - For Each Value - Iteration.

Also, make sure the Input Parameter for passing forms table variable is set to Multivalue .

Thanks everyone.

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.