Question

Workflow: Create a delimited record from values retrieved form a Forms table

Workflow

Updated September 8, 2023

asked on August 2, 2023 • Show version history

I'm using Retrieve Business Process Variables to get records from a Forms table. The end goal is to get tokens that include delimited values. Some parts of the record will be single value, such as vendor number and GL Date, but other parts of the record will contain multiple values, separated by a delimiter.

If I loop through the table using For Each Row, the data looks like this:

I only care about \account and \total and the goal is to get values that look like this:

Account: 2230.8620/2230.8515

Total: 4995.00/17.76

I'll use these values in tokens to pass to a SQL stored procedure:

@VendorNumber: 999999

@GLDate: 8/2/2023

@Account: 2230.8620/2230.8515

@Amount: 4995.00/17.76

So, my question is how to update the token values for Account and Amount (Total) to include multiple values separated by a delimiter?

Thanks!

0 0

Answer

SELECTED ANSWER

replied on August 2, 2023 • Show version history

You can do that with the token editor using the Index options.

Just retrieve the column(s) you want, then you can either create a new token to store the delimited values or use the formatted token directly.

When you right-click or use the menu to open the token editor, check the box for Apply Index, select "All values separated by" and select, or type in, your desired delimiter.

1 0

replied on August 3, 2023

Thanks for the reply Jason...I didn't realize this functionality existed. I learned something new!

0 0

Replies

replied on September 7, 2023

@████████

I now also need to concatenate values from two additional fields, Subledger and Subtype (which may not always have values).

Account: 2230.8620\29297\A|2230.8515

For example, in the first account number there is a subledger number and a subtype, separated by a backslash. What would be the best way to accomplish this?

0 0

View 1 previous reply

replied on September 7, 2023

Once you bring another list/column into the mix, you'll have to use a different approach.

The best option is probably going to involve the following:

Create a multi-token at the start of your workflow
Do a For Each Row on the Table
Add/append the concatenated values to the multi-value token
Use the same approach as before to flatten/delimit but with the token instead of using the column directly

0 0

replied on September 7, 2023 • Show version history

Ok, so I'm at this point in the process and I'm not sure how to delimit with \ between the account, subledger and subledger type. Thanks in advance!

0 0

replied on September 7, 2023 • Show version history

You would not add those as separate values in the list, you would add the single completed/concatenated value to the list like:

New Values:

%(Token1)\%(Token2)\%(Token3)

The idea is that you're not delimiting values of the same type, you're building a multi-part string like you would if you were combining pieces of address info.

If you don't want the "\" when those secondary fields are blank, then you'll need conditional logic to build the string the way you want.

For example

Create a separate single value token inside the loop
Start it off with the "Account" token value
Add a Condition that checks if Subledger is blank
1. Set the single value token = "Account\Subledger\SubledgerType"
Add that token to the multi-value token

Or another option would be to just create a conditional branch:

If Subledger is blank - append "Account" to multi-value token
If not blank - append "Account\Subledger\SubledgerType"

But the idea is that you're not adding all those values to the list separately, you're adding your concatenated value to the list.

1 0

replied on September 8, 2023

Got it working...thanks again Jason!

0 0

You are not allowed to follow up in this post.

Question

Question

Workflow: Create a delimited record from values retrieved form a Forms table

Answer

Replies

Sign in to reply to this post.