Get the sum from multiple rows to populate metadata

asked on November 29, 2016 • Show version history

Hi,

My customer has an existing workflow that populates metadata with the Assign Field Values activity using the Query Data activity based off of a PO number. An issue has been noticed due to a particular PO# that returns 4 rows. Each row has a different currency value in it, and the customer would like the workflow to calculate the values from each row and populate it into a field called 'Total.' The Query Data activity is already returning 'All rows', so what activities would I need in order to make this happen? I tried the 'For each row' activity with that 'Query Data' activity for the total, and in the Assign Field Values activity I choose the token '%ForEachRow_Iteration', and then I use the Token editor to apply the Sum function, but that doesn't return the right total.

Thanks in advance,

Brian

0 0

replied on November 29, 2016

Thanks, Chris. It looks like you might be running v10 of Workflow because I don't have the append option in my Assign Token Values activity. Do you know if v10 workflow is backwards compatible with v9.2.1?

0 0

View 4 previous replies

replied on November 29, 2016

Version 9.2 has the append option. Be sure that when you create the initial token you check the box to make it multi-valued. Then, when you create another 'Assign Token Value' activity, click 'Modify...', and select the multi-valued token you made earlier, you will see the 'Append to existing values' option.

1 0

replied on November 29, 2016

WF 10 is backwards compatible with LF 9.2. Everything Chris does is possible in WF 9.2. The token calculator will have a lot less functions available, but SUM works the same way: SUM(%(Total))

1 0

replied on November 30, 2016 • Show version history

Thanks, everyone. I think I'm on the right track, but I'm confused about the screen shots above. How are you getting the '%(ForEachRow_vacation_hrs)' token in the Assign Token values activity? Shouldn't that be done in the 'Assign Field Values 2' activity? And how come you didn't select the iteration? It looks like you're selecting the db column for vacation hours.

Thanks again to all.

Configure Token.png (6.28 KB)

| Download

0 0

replied on November 30, 2016 • Show version history

Here's what I got so far. Am I close? In the initial Assign Token Values token I haven't entered any value.

What I have so far.JPG (24.29 KB)

| Download

0 0

replied on November 30, 2016

I just gave it a shot with this config. Please let me know if you spot anything that I should change, but it appears to be working. Can't thank you all enough for the help. You're all awesome.

Seems to be working with this.JPG (25.77 KB)

| Download

0 0

replied on November 30, 2016

As for your question "How are you getting the '%(ForEachRow_vacation_hrs)' token"

You can simply select that using the token selector at the end of the line. What you are doing is basically declaring a multivalue variable. Then you want to populate that variable with all the numbers you wish to sum. So you want to update the multivalue token with each and every value returned. If you just did QueryData_vacation_hrs it would only populate the token with the first result returned. Doing a For Each allows each value in all rows to be populated in the token.

1 0

replied on November 30, 2016

Thanks again, Chris. I was asking that question because it seemed that the order of operations was incorrect because that token would not be available in the first Assign Token Values activity because it precedes the ForEachRow activity.

0 0

Question

Question

Get the sum from multiple rows to populate metadata

Answer

Replies

Sign in to reply to this post.