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

Question

Question

Save multiple row table data to sql in one row

asked on September 21, 2022

Is there a way to move data saved in multiple table rows in LF forms into one row in a sql DB?  I have done this before with token formatting, creating a token for each field on each possible row a user could add but it doesn't seem to be working the same.  The For Each Row will create individual rows for each line but I need to capture each piece of data and put it in the designated column on the same row.

0 0

Answer

SELECTED ANSWER
replied on September 22, 2022

Luckily one of my old co-workers told me how we did it before.  The test results don't work like they used to but the process works.  Forms collected each row on top of each other, but if you edit the variables in Monitor it shows them individually, they get their own member ID per row.  The trick is to index the token at the start and increment for each possible row.  So if 4 rows of data come back you create a token for each row and index per row.  so tkDataRow1 (index start at 1), tkDataRow2 (index start at 2).  Then in the Insert activity, match the right token with the right column.  Then they all go on one row in SQL.  

Forms data collection.png
Token capture.png
Apply Index.png
correct token setup.png
sql wrong and correct view.png
Apply Index.png (370.96 KB)
0 0

Replies

replied on September 21, 2022

You would need to collect the data in a workflow token and store that token into the column.

Best way is probably to use a multivalue token, which you can populate in the for each row loop.

Then put the database insert/update activity outside of the loop. When you set the column, use the token editor and to set "all values separated by" so you can format values as a list separated by commas, semicolons, or whatever you prefer.

0 0
replied on September 22, 2022

Yes that is what I used to do, but now the data seems to be stored by an individual member ID so its stacked in the variables instead of separated by a comma.  So I get this:

 

09

10

11

12 

instead of 09,10,11,12

 

Is there a token formatter that will identify the row or member ID that I'm missing?

0 0
replied on September 22, 2022

I'm not sure I understand what you mean by "individual member ID"

If you were copying the values to a multi-value token, the token would allow you to choose the formatting in the token editor.

By design, For Each Row is going to iterate the values individually so you need a token within the workflow to gather the values.

Can you explain in more detail, possibly with screenshots, what you used to do, versus what you are doing now?

I understand you're getting different behavior, but I need more information about the workflow structures to be able to explain why.

0 0
SELECTED ANSWER
replied on September 22, 2022

Luckily one of my old co-workers told me how we did it before.  The test results don't work like they used to but the process works.  Forms collected each row on top of each other, but if you edit the variables in Monitor it shows them individually, they get their own member ID per row.  The trick is to index the token at the start and increment for each possible row.  So if 4 rows of data come back you create a token for each row and index per row.  so tkDataRow1 (index start at 1), tkDataRow2 (index start at 2).  Then in the Insert activity, match the right token with the right column.  Then they all go on one row in SQL.  

Forms data collection.png
Token capture.png
Apply Index.png
correct token setup.png
sql wrong and correct view.png
Apply Index.png (370.96 KB)
0 0
replied on September 22, 2022

Forms does not collect rows on top of each other, it stores an array of values for each variable in the table. That first screenshot is just a user friendly way to display the data because there's more than one value in the object.

In your second screenshot, the test results aren't going to do what you're expecting because what you have in the test area is just 09 with a line break followed by 10; this is not the same as an array of multiple values.

Based on your screenshots, the problem is that you're creating a new token with every iteration of the loop, which means everything from the previous iteration would be lost when the next iteration occurs.

What you want is a multi value token created before the loop, then inside of the loop you don't create new tokens, you Append the values to the multi value token you created before the loop.

1 0
replied on September 22, 2022

I understand the array vs the actual view.  That is what I meant by when you click on the edit of the variable, you can see its separated out individually and in the Forms DB it has a unique member ID per data capture.  The mutli-token option didn't work even without the For Each row...although I hadn't tried appending it. 

The index on each individual token for each row works though, even with the For Each Row, but you are right that it isn't necessary.  Its a lot to enter but it gets every thing value from the table on one line.  

What was throwing me off was that the Test Result area doesn't have a way to show that that actually works.  

 

Here is how it should look:

 

 

 

0 0
replied on September 22, 2022

Now that I can see the insert activity things makes more sense. You don't really want a multi-value because you're storing each row to a separate column.

Since all you need to do is assign a specific row to a specific column, technically you could skip the extra tokens and just set it directly in the column value.

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

Sign in to reply to this post.