Question

Looking for ways to group multi value field values

Workflow Version 11

Updated April 11, 2023

asked on April 11, 2023

I am working on fixing an issue we run into when processing invoices. A single invoice can have multiple GL codes assigned to it. Sometimes we end up with 3,000+ GL lines for a single invoice. When we look into these scenarios, we find that some of the GL codes are the same, but just have different amounts.

What I would like to do is combine the GL codes that are the same to cut down on how many lines there are. The GL values are assigned to a document in a multi-value field. The value has the GL code and the amount being assigned to it at the end of each code.

I have thought about inserting the values into a SQL database table that is truncated each time the workflow runs and then running a SQL query to group the values. My concern is the amount of time it would take to insert the values into SQL. Can anyone think of a better way to group them using Workflow?

0 0

Replies

replied on April 11, 2023

I think you could do it in Workflow by making a token of the GL codes with duplicates removed, and then looping through each value from the metadate and comparing to each code in the token with the duplicates, and then summing up the new values.

But honestly, I would do it with the database. I know you are concerned about time for the database entries, but I think the flexibility that the database will give you to consolidate is worth it.

I wouldn't truncate the table however, unless I knew for sure the workflow would only ever be running once at a time. I would include an identifier on the table to match the entry or workflow instance I was working in, and then just delete the matching entries at the end of the process.

If you are really concerned about the time to insert the records in the database - it is tricky - but it is possible to create a large token of data that can be inserted into the database table in one Custom Query activity instead of a bunch of different Insert Data activity. It's a weird set-up, so I wouldn't recommend doing it unless you're talking about trying to 5 to 10 (or more) minutes from your workflow processing.

1 0

replied on April 11, 2023

Interesting ideas. If doing it in Workflow, I think the duplicate function would probably be the way to go as you and Steve mention.

I think I will do a test run on how long it takes to insert that many rows into the database table. There will only be one instance of the workflow running at a time as it is set to single instance mode, so no concerns there. We would also only invoke this new workflow if there were over X amount of GL Codes based on a threshold. We will do some testing to figure out what threshold makes sense.

1 0

replied on April 11, 2023

Good luck!

0 0

replied on April 11, 2023

Hi Blake, we went down this road and ended up using DB tables to manage all of the GL data as we generate reports and output our Invoice/GL data from the tables. We have two tables, one for the Invoice info and the second holds the GL Data. There is unique key that ties the invoice to the GL data.

We manage the complete processing and posting of invoices through forms, and only use the repository for storage. We choose not to store the GL data in the Repository as it wasn't required once we have updated the ERP with the data.

My 2 cents, I think you have more flexibility with SQL then attempting to use Metadata and Workflow to manage all of that data.

0 0

replied on April 11, 2023

The problem is this is a current process and moving away from using metadata at this point would be a huge undertaking, so I'm trying to work with what we currently have to find the best solution until we can change the process as a whole.

0 0

replied on April 11, 2023 • Show version history

Hi Blake, in Workflow, you can use a Function with a Token to remove duplicates. If you did this, you would then have a list of unique GL's and then loop through the values to accumulate the values for each GL.

0 0

You are not allowed to follow up in this post.

Question

Question

Looking for ways to group multi value field values

Replies

Sign in to reply to this post.