How can I create an automation for a formula of which variables are in word in front end?

[Saili]

Hello community,

I want to calculate a RICE score for which the formula is (R*I*C)/E.

The challenge is that Each R,I,C,E has a value in words on front end. 

I.e. These are picklist fields where R= High, Medium, Low, Minimal. and same for other 3 pick list field of I C E.

Now High means 100, medium is 50, low is 10 and minimal is 1

I want user to be able to select a word option from pick list field. But on the Automation level the calculation of RICE score should be done numerically on the basis of what these options of High, Medium... signify.

Is it possible?

R, I, C, E are custom fields

And I am in a Team managed project.

Looking forward to your help.

Thanks in advance!

[Trudy Claspill · Accepted solution]

Hello @Saili 

I recommend that you consider the Create Lookup Table action in Automation.

https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Create-lookup-table

This would enable you to use the text values of the fields as keys in a table, and associate those to the numeric values they represent.

Where you want to use the numeric values in calculations, you can use the text values of the options to look up the numeric values in the tables you create as part of the rule.

[Saili]

Hi @Trudy Claspill 

Thanks for this recommendation. I have created a rule but the value is somehow not fetched. Attaching the images for reference.

This is the lookup table I created with key and values. I have also kept log action

In audit log the log action shows that the key is considered and not the values

These are the field which are of single pick list type

Thanks in advance!

[Trudy Claspill]

Hello @Saili 

First, to avoid potential confusion I recommend you give your Lookup Table unique name like tableReach rather than the same name as the field.

Please review the information at the bottom of the Lookup Table action for the proper way to get the Values from the table based on the key.

• You would need to use {{Reach.get(issue.Reach)}} in order to get the number value you want associated with the text value for the Reach field.

You might need to use {{Reach.get(issue.Reach.value)}} since Reach is a selection list field, to specifically use the text/name of the field option.

[Saili]

Hi @Trudy Claspill 

Thank you so much for clarification.

But I have a little bit confusion. If I add prefix table to table name eg. tableReach the how to write this queries: {{Impact.get(issue.Reach)}} and/or {{Reach.get(issue.Reach.value)}} 

Thanks in advance!

[Trudy Claspill]

I had a mistake in my response that I have now corrected. Where I used "Impact" I should've used "Reach".

The "get" function applies to the table, with the information in the parentheses indicating the key value. So the syntax would be 

{{tableReach.get(issue.Reach)}}