How to use Automation Lookup Tables for calculated values!

[#1]

Hey community, 

I have seen a few questions / posts on this topic and so I thought I would write an article summarising a few customer use cases I have had recently where I used lookup tables in Automation to calculate and populate a custom field in Jira.

Basically, the two use cases were similar. In one case, the customer had a transition screen where they wanted to evaluate 12-13 areas, which all had numeric values / weightings, and then to sum up all those values into an overall weighting.

The other use case was more simple, looking at the impact and timeliness of the completion of an issue to give an overall score.

In both cases, I was able to create a lookup table in Automation and then reference the custom field value, against the numeric weighting from the lookup, and, using smart values, get the calculation / calculated value I wanted.

Here is a simple example.

The requirement is to produce a Risk Matrix-like score.

The score is made up of the numeric product of two custom field values, let's call these 

• Likelihood (from Rare / Unlikely to Almost Certain)
• Severity (from Insignificant to Severe)

First, let's create the custom fields, if they don't exist already and configure the following options.

Likelihood : Rare, Unlikely, Moderate, Likely, Almost Certain

Severity: Insignificant, Minor, Significant, Major, Severe

Now we have the two custom fields, let's create the lookup tables with the numeric values associated with each option

Likelihood

Custom field option	Number value
Rare	1
Unlikely	2
Moderate	3
Likely	4
Almost Certain	5

Severity (of Impact)

Custom field option	Number value
Insignificant	1
Minor	2
Significant	3
Major	4
Severe	5

Here are what the lookup tables should look like

Note: Make sure you write exactly the same way the options from the custom field as you write them in the lookup table.

Once you have configured the two lookup tables, associating the numbers with the custom field options, we need to write the expression to do the product, in this case, of the Likelihood multiplied by the Severity.

{{#=}}{{Likelihood.get(issue.customfield_10090)}}*{{Severity.get(issue.customfield_10091)}}{{/}}

What we are doing here is to find the custom field values (using the custom field ID) from the issue and then replace it with the number from the lookup table and then to do the product, multiplying the first value for the second one. 

In my case, I am using a manual trigger from the issue, but of course you could use any trigger for this, as long as the custom field values are populated, it should work.

Let's do a test now. I have created an issue and populated the values in the custom fields of Likelihood and Severity.

And here we see the calculated score of Severity of Major (4) multiplied by Likelihood of Almost Certain (5).

While I know there are other ways to achieve this same result, I like the lookup table functionality because it is flexible to grow and adapt with you as your requirements change, especially for something like measuring risk.

I hope this article is helpful for people who have similar use cases. If you have any questions or feedback, please let me know.

Cheers!

[Bill Sheboy]

Hi @Valerie Knapp 

Thanks for your article.  As a "yes, and..." to this scenario, please consider: what is needed when one or more of the calculation inputs are empty / not selected?

For example, if the score should be 0, the default values of 0 could be added to the left of each looked-up number.  But if the score should remain null (or be set to null) conditional logic (inline or with rule steps) would be needed.  The specifics of a customer's need will determine which approach to use.

Hmmm...this made me think of a feature request: adding a default value to the Lookup Table action for cases when get() does not return a match to a key.

Kind regards,
Bill