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

Question

Question

What workflow activities do I use to populate a database table with selections made from a forms checkbox

asked on May 9, 2023

I have a checkbox listing various documents. I want workflow to update a sql database with only the items checked. I'm using the following Workflow Activities  "Retrieve Business Process Variables", "Find Entry", and "Insert Data" but I know I'm missing something. Not sure if I need "Assign Variables" activity and "For each row"

Lucinda 

0 0

Replies

replied on May 9, 2023

It depends on how you are storing the values of the checkboxes. If you are storing them as separate rows, then you could use a For Each Value to loop through the selected values and perform your database inserts inside of the loop.

0 0
replied on May 9, 2023

I'm storing the different values in columns, doc 1, doc 2, doc 3 etc. 

0 0
replied on May 9, 2023 Show version history

In that case, are you storing them as text values or as bit/boolean columns?

When you retrieve a checkbox field in workflow, there are "Is Set" values for each of the options.

If your columns are bit/boolean you can probably use those values directly, but if you're storing them as text values you'll need to do something more complex with some kind of conditions for each of the values.

Basically, think of columns as 1 dimensional like a line, but checkbox values are 2-dimensional like a set of rows, so in order to "flatten" than into columns for a single row you need to map them to other tokens.

For example, create a token for each column in your row, use the Is Set values in conditions, update the column tokens for the values that are selected, and then use all the tokens in the data insert.

You can use the NULL formatting option to treat empty values as "NULL" if you want them to be null rather than just empty strings in the database.

Token Formatting--Empty Tokens (laserfiche.com)

1 0
replied on May 10, 2023

That is awesome, thank you. I'll make the changes.

Lucinda

0 0
replied on May 10, 2023

Hi Jason, 

I assigned tokens and set the values to "string" but I do not see the"is set" activity. The data is updating the database but with boolean values. I could write sql query to convert the boolean but I would rather it be done in the workflow. 

0 0
replied on May 10, 2023

Can you provide screenshots of the settings for the Retrieve BP Variables, Assign Token Values, and Insert Data. Something is definitely missing based on that setup.

0 0
replied on May 10, 2023

 

 

All the checkbox variables are set to string

 

It works but it is returning a boolean. I could add to the query for the view setting the value of each column based on True or False but I would prefer not to. 

SQL DB 

 

thank you for your help!

Lucinda

0 0
replied on May 10, 2023 Show version history

That's the problem. You're assigning the "Is Set" value to your tokens, so it is actually behaving as expected since that is a true/false value.

To get the actual text value of the checkbox into the tokens is going to be a lot more complicated because you'll need to check the each "Is Set" and find a way to get the corresponding value set.

Like I was saying, this is going to be tricky because you're trying to squash a 2-dimensional list into a 1-dimensional row.

Basically, you should start with all of your column tokens empty, then use each of the individual "Is Set" values in conditions to determine which ones to populate.

 

0 0
replied on May 10, 2023

you are right, you got me thinking - got it 

 

 

 

It worked! Thank you!!

 

0 0
replied on May 10, 2023 Show version history

Looking better.

However, instead of saving the "Is Set" into your tokens, I would leave those empty when they're first created (so they only have a value if the condition is met) and use "Is Set" directly in your conditions rather than the tokens to avoid the true/false in the non-selected columns.

I'd also apply the NULL value formatting I mentioned so you get a "null" in any column that wasn't checked (unless you prefer it be a blank string).

1 0
replied on May 11, 2023

So are you saying to remove the first "Assign Token Values" activity?

and assign the token in the condition like this if True?

 

As for the Null I"m not sure to format the tokens. I'm assuming this is on each token and not a separate token.

0 0
replied on May 11, 2023 Show version history

No, you want to keep that first Assign Token Values to create the tokens so they always exist, but create them without a value.

It's important to understand that Tokens in workflow are variables. They are used to hold values, but they can also be empty, so think of them like containers for the different values used in the workflow.

You need to create all those column Tokens so they exist and can be referenced, but in your case you only need to set a value if certain conditions are met. They always need to be created so you can reference them in the Insert Data activity without any errors, but they can absolutely be blank/empty.

As for the null value, the formatting is used when you use the tokens somewhere. For example, in your "Insert Data" activity, if you right click on one of the tokens where you're setting the value and open the Token Editor, you will see format as one of the options.

The formatting tells workflow how to "interpret" the data stored in the Token.

 

You want to create the tokens so you can use them in the Insert Data activity, but you only want them to have values if the conditions are met.

So you would reference "Is Set" in the Branch conditions.

The idea is that "Is Set" should only be used for the conditions because you don't want to store the true/false, you only want to use it to check if you need to set a value for the Tokens.

As a result, you'd start with your column tokens all empty.

Next, each branch would check "Is Set" to determine if you need to set a value for the associated token.

 

Create the empty tokens

Set your branch conditions

Set the values you want to use if the box is checked (i.e., if "Is Set" is true)

Then, use the tokens you created in the Insert Data.

1 0
replied on June 24 Show version history

@Jason Smith - Thanks for this, I was looking for a solution to do exactly this.

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

Sign in to reply to this post.