Question

Extract values from forms table rows

Forms Forms Layout Designer Workflow

Updated March 26

asked on March 26

I have a use case where we would like to extract data from a table on a form, and insert it into a database. Now, each column in a Forms table appears to be stored as a single variable, but not at the row level, if that makes sense. I have previously been able to index tables like this when I create tokens in a workflow, but that was with a known number of rows. In this new scenario, we do not have a known number of rows.

I did a test where the workflow will index at least five rows, and when I ran a test with a form with just one row, it transposed that row into the 5 unique tokens, essentially duplicating the row 5 times.

Is there a way to dynamically determine the number of rows in a Forms table, then extract the data from each row as individual tokens to write to another table?

0 0

Replies

replied on March 26

Hi Dillan,

I think you should be able to use the For Each Row activity and point it at the table from the Retrieve Business Process Variables activity. That will iterate through each row, so you don't need to worry about how many rows there are. Then, within the For Each Row activity, you should be able to use an Insert Data activity to insert a new row in your table for each row in the table from Forms.

Does that help at all?

1 0

replied on March 26

Jacob,

I didn't realize that works for tables in forms also. I have used that activity for other processes when querying data in external tables.

I presume when I create a token for each value in each column, I would need a way to assign a row number? Basically as a way to identify each row and the fields within each? Or I guess in theory I could write the data in real time per row within the For Each row activity with a Insert Data activity....

0 0

replied on March 26

If you want multiple rows in SQL of the data pulled from the table in forms, @████████listed your solution.

If you want to index them out so you can put them all in one row in sql with multiple columns, you can create as many tokens as you think would be the maximum (ex: 20) and if there are less than the number of available tokens, those columns will just be blank.

Is the table in your form being appended by data from another source, or does your end user really not have a maximum number of rows they can add?

0 0

View 1 previous reply

replied on March 26

Angela,

There is a practical limit of likely 25 or so rows. I did attempt this already with an example form that only had one row. It however, seemed to insert the same data into each indexed tokens I created in the workflow. I was expecting the first one to be populated, but not the others, similar to what you described.

0 0

replied on March 26

I posted an answer with screenshots here: Save multiple row table data to sql in one row - Laserfiche Answers

You could create the max number you are expecting, but that could be a lot of tokens. Just make sure your index is correct in the setup.

You don't need to create tokens to insert the data into SQL using the For Each Row, however if you have data that applies to each row of the table, you may want to use a pivot in a view or sp to arrange your collected data to one row. It really just depends on what you are wanting to accomplish with the data once collected.

0 0

replied on March 26

Essentially I would like the data to be captured and written to certain columns in an external database. There are some columns in the database that don't appear on the table in Forms, so it isn't one for one relationship in terms of columns. There are also some variables elsewhere on the form I would likely need to insert into each row.

0 0

replied on March 26 • Show version history

As Jacob mentioned, you can use the Insert Data activity to match the fields in the form to the columns in SQL. You can use the For Each Row activity to define the table columns to the SQL columns, which will create a SQL row for each row in the table.

The initial insert will create the first row. If you create the first row in SQL with specific data from the form, you can save the table data to a different table that stores a unique ID (such as Employee ID) for each row to reference. Then, you can utilize SQL to organize the data as you want to display it. If you want to store the initial data and the table data all in one row, you will need to create tokens for the maximum number of columns/rows that could be added to the table in the form (refer to the previous post where this is shown). You can't index table field values in the For Each Row activity because the field is storing multiple values that cannot be indexed.

You can also use Update Data activity in the workflow to add additional data to the originally created row if there are other areas that need to be added after a conditional decision is made (e.g., adding an address, Yes or No).

This approach will allow you to define which fields in the form you want to save to which columns in your SQL table. If there are conditions (IF AND/OR), just use the update activity in the workflow.

Hopefully these options make sense.

0 0

You are not allowed to follow up in this post.

Question

Question

Extract values from forms table rows

Replies

Sign in to reply to this post.