Trying to create a sum field based on a lookup table and "for each smart value"

[Joe Riley]

I'm working on a jira automation flow. every time a value is added to the client field for a ticket, i want the 'Total revenue" number field to update on the ticket adding that client's revenue amount. The revenue amount per client is stored in a lookup table within the rule. I can get my logs to return the list of clients on the ticket, and their related revenue numbers. i can also get my total revenue number to update within the branch, but i can't get that to sum and update the jira field outside of the branch at the end. here's what I have that is the closest I've got:

When: Value changes for Client

Group 1:

Then:

• Create Variable
  • variable name: totalRevenue
  • Smart value: 0

And:

• Create lookup table
  • lookup table variable name: clientRevenueTable
  • Define table entries: Adams | 1000, Kids | 2500

(Branch)

• For each: Smart value
  • Smart value: {{issue.Client.value}}
  • Variable name: eachClient
• Then:
  • Create Variable
  • Variable name: clientRevenue
  • Smart value: {{clientRevenueTable.get(eachClient).asNumber}}
• And: Create Variable
  • Variable name: totalRevenue
  • Smart value: {{#=}}{{totalRevenue}} + {{clientRevenue}}{{/}}

Outside of Branch:

• Then: Edit work item field:
  • Total Revenue: {{totalRevenue}} 

I've also tried creating total revenue in the branch as {{totalRevenue.asNumber.plus(clientRevenue.asNumber)}} 

I've got logs at each of these steps that i didn't include for readability, but essentially they show:

• Clients are consistently pulling into the rule
• The lookup table is working (i.e. if the client is set to Adams and Kids i can return 1000 and 2500
•  I have using the above been able to get it to set total revenue within the branch, but it won't update it outside of the branch

[Ivan Manolov - JXL]

Hi @Joe Riley,

Welcome to the community!

I trust that you’ll manage to find an Automation-based solution based on @Bill Sheboy great answer!

Just for future reference, if you’re open to solutions from the Atlassian Marketplace, your use case would also be easy to solve using the app that my team and I a working on: JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your Jira data that allows viewing, inline-editing, copy-pasting, sorting, and filtering by all your work items' fields, much like you’d do in e.g. Excel, Google Sheets, Smartsheet, or Airtable. It also comes with a long list of further features, including support for work item grouping based any work item field(s) and configurable sum-ups.

With these, you can build a view like e.g. this in just a couple of clicks:

 

This is really just one of a virtually endless number of possible views and reports; you can also view and group by any other work item fields, configure different sum-up styles (like e.g. average or median), etc. etc. This all just works - there’s no automation or scripting whatsoever required.

I should also add that JXL can do much more than the above: From support for configurable issue hierarchies, to conditional formatting, or inline bulk editing via copy/paste.

Any questions just let me know,

Best,

Ivan

[Bill Sheboy]

Hi @Joe Riley -- Welcome to the Atlassian Community!

Short answer: this scenario cannot be solved using rule branching, and so a more dynamic-search method or chained-replacement is required to sum the fields.

Here are details on why branching and long-form iteration will not work, and some workarounds...

Rule branches which could be over more-than-one-thing are executed in parallel and asynchronously, with no defined processing order.  This means anything like a created variable created in a looping step will disappear when that pass through the loop finishes.  And, there is no guarantee of when the branch will complete, up until the last step of the rule.

Putting those things together, it means you cannot create a variable in the branch, update it, and have it available after the branch.  And even if that worked, the steps shown after the branch will likely start processing before the branch completes!

Also unfortunately, one cannot use long-format iteration over the custom field values with the lookup table to sum the values: the table data would not be visible inside the iterator.

There are at least two possible workarounds for your scenario: chained-replacement and dynamic searching.

Chained replacement

First, join all of the custom field values into a delimited text string.  Then add a series of replace() functions, with one for each value hard-coded.  Finally, wrap all of that in a math expression.  And, so, the lookup table is not used.  For example:

{{#=}}0{{issue.customfield.12345.value.join(" + ").replace("Adams", "1000").replace("Kids", "2500")}}{{/}}

This is a brittle, error prone approach, that is difficult to maintain...although it is quite simple to try.

Dynamic searching with inline iteration

A more complicated approach involves keeping the Lookup Table, for ease of maintenance, and using dynamic list searching methods to get the values.  This approach is described in more detail in this community article I wrote.

For example, first iterate the table values to create a delimited string in a Created Variable, such as:

• action: create variable
  • name: varClientRevenueTableData
  • smart value: 

{{#clientRevenueTable.entries}}{{key}}:{{value}}{{^last}},{{/}}{{/}}

That will produce a result looking like this:

Adams:1000,Kids:2500

Then using the values in the custom field to build a regular expression in another variable:

• action: create variable
  • name: varRegEx
  • smart value: 

^(({{issue.customfield.12345.value.join("|")}}):.*)

Finally, use the match() function to get the selected values, remove the client information with a text function, and again wrap it all in a math function, with a 0 default at the front, to create the sum.

{{#=}}0{{varClientRevenueTableData.split(",").match(varRegEx).substringAfter(":").join(" + ")}}{{/}}

Please try one of these and let me know what happens.  Thanks!

Kind regards,
Bill