Question

There has to be an easier way - SQL read-only table loaded into forms and then inserted into different table with new row of updated data

Workflow

Updated December 22, 2023

asked on December 22, 2023 • Show version history

I am not the most proficient at forms, and I feel in my bones there is an easier way to do this.

FD has requested a form so they can stop hand-filling out cleaning supplies for each of their 17 fire stations. The DC in charge of this also wants to see each order by station and what quantity of each they are ordering. We designed the DB to have each "order" have the 50 cleaning products as a column, and the idea was to simply have the number requested put in the column, and then I can create a word merged document to send to the cleaning company via email.

To make it easier (I thought) we created a table that had all products listed so Forms could simply load the products into a table so the fire fighters can simply put in a number and hit submit. The only way I have found workflow to grab the number of the requested items, is to do a For Each Row with a conditional decision embedded so that as each value is "read" by the For Each Row, it sorts the number requested into the correct column. If I do it this way, I will have 50 conditional evaluations by the workflow engine.

It seems really inefficient. Is there an easier way to grab the rows and insert them, en-masse, into the DB. A "blob" insert if you will, with the values going into their respective columns

Thank you in advance, feel free to flame for my inefficient method- I strive to grow with this software.

User Form loading a read-only inventory table from SQL and diplaying row by row:

Workflow that grabs For Each Row from the table, evaluates based on current value what product it is and then inserts that value into the appropriate column (x50 times once I write the workflow out to completion)

Current end result:

0 0

Answer

SELECTED ANSWER

replied on December 22, 2023

You can simply use the supply name for the current row in your where clause of the SQL update statement

update table MyTable

set Requested = %(ForEachRow_CurrentRow_Requested)

Where Supply = $(ForEachRow_CurrentRow_SupplyName)

If your supply names in the table do not happen to match the terminology you use in the database then maybe have another table that holds the alternative values and look them up with a query

If you were inserting new rows you would comma delimit the values into a single token and insert them in one query.

0 0

View 10 previous replies

replied on December 22, 2023

This looks very promising.

The good news is that the supply names in the table match the terminology as they pull from a standard list table.

Let me try to implement this and I'll report back if it works.

Thank you!

0 0

replied on December 22, 2023

There might be some confusion between my post and the post from Andres.

My setup is that I create a blank row with a random "order ID" then the update clause will update that row with that order id.

Would you still recommend using the supply name for the current row in my where clause of the SQL update statement?

0 0

replied on December 22, 2023

I think I am close, but I am not able to get the requested amounts into the table:

This is my query.

0 0

replied on December 22, 2023 • Show version history

Your setting a variable to the requested amount rather than a column. Change @Product_Name to the Requested Amount column.

Edit: Looking closer at your table screenshot, it looks like your table is the same as Andres' table, with columns for each supply instead of one column labeled Supply. In that case there is nothing to update, you want to insert everything as one row as he is doing. See the comma delimited solution.

0 0

replied on December 22, 2023 • Show version history

I apologize, I am struggling a bit on this.

The way my table is set up is that the product name column is also the requested amount.

So with my query above, i was hoping it would be something like this:

Update Table
Set BLEACH=1
Where ORDER_ID=B669F-F

0 0

replied on December 22, 2023 • Show version history

Oh you already have a row with an order number. Well then do this:

Set %(ForEachRow_CurrentRow_SupplyName) = %(ForEachRow_CurrentRow_RequestedAmount)

Where ORDER_ID=X

Don't use the parameters when using a token value for a column name, just use the tokens in the syntax directly

0 0

replied on December 22, 2023

I do not have an option for %(ForEachRow_CurrentRow_Variable)

Only: %(ForEachRow_FD_SupplyNameRow)

0 0

replied on December 22, 2023

Sounds right, I thought token syntax for current value used to include the word CurrentRow, but whatever the token picker gives you for the value of the column in your current row is good.

0 0

replied on December 22, 2023 • Show version history

Well that is a new one for me, I didn't know you could use token syntax directly in the query window. I thought you always had to have parameters defined.

Ok, so I was able to get one column to populate, the rest remained at 0:

Query:

0 0

replied on December 22, 2023

Always use the syntax directly to define table or column names or to replace entire statements like "Where X = Y"

It seems it is working then, just use Token Tracker to check your values of the tokens on each loop. Putting a token tracker in the loop will allow you to cycle through each loop and look at your token values on the tokens tab.

0 0

replied on December 22, 2023 • Show version history

Yep, I think I have column name issues:

Let me work on renaming some stuff and see if it kicks off correctly. Thank you for pointing me in a great direction. If I end up getting this working, will mark your response as the answer.

0 0

replied on December 22, 2023 • Show version history

Thank you very much. This taught me something new and once I updated my column names to include _ instead of spaces (Im bad at SQL) they started to match up and populate.

0 0

replied on December 22, 2023

Oh I see, spaces in column names is ok, but you have to surround the column name in brackets in your syntax like this

Set [%(ForEachRow_FD_SupplyNameRow)] =

1 0

Replies

replied on December 22, 2023 • Show version history

If it were me and had the flexibility to do so..
I would scrap the 50 column table, assuming I read this correctly, you'd be limiting the supply types of items that could be requested(?) to the shape of the table.
I would use a smaller(less columns) table to house all requests with the necessary columns to describe the supplies and an ID/Key identify the supply. Versus, specifying the column the supply request must go into.

Then the work would just be to insert LF Forms table data into the custom table when a row is not empty.

I know I don't have the most efficient either. So, if someone has better words and method than me...

0 0

replied on December 22, 2023 • Show version history

Oh your original image showed Update activites, this screenshot is showing an Insert statement.

An insert statement should not have a Where clause (no conditional), so there should be no need for 50 conditionals to prepare any conditional clause. Just insert the values using a single insert activity that runs for each row.

For the Item Name column just use

$(ForEachRow_CurrentRow_SupplyName)

Edit: Oh I just realized you said you had a 50 column table. So your looking to only insert 1 row into the database.

Simple, in your for each row, just put the values into a single comma delimited token by using a Set Token Values activity's modify feature. Modify the global token with it's current value plus the new value and a comma character.

Then you can run one insert statement and access all the values with the function split on comma and index. Find this by right clicking your token syntax and choosing token editor.

0 0

You are not allowed to follow up in this post.

Question

Question

There has to be an easier way - SQL read-only table loaded into forms and then inserted into different table with new row of updated data

Answer

Replies

Sign in to reply to this post.