Automation for calculating the field value based on values entered for 8 separate fields

[Amal Johnson]

Hi,

I need your support in creating an automation for calculating the field value from other fields. The tricky part is, I have this formula in an excel sheet, this needs to be transferred to Jira. It works perfectly in Excel, but in Jira, I don't know how to set up this. Please see the screenshots attached and that's the formula which needs to be transferred to Jira. 

As you see in the screenshot, I need to calculate the "Evaluation score" from the above 8 fields.

Note: The field values are dynamic for each work items, so the automation should be dependent on the varying values of above 8 fields.

Here are the custom field id's of the fields you see in the screenshot : 12777,12778,12779, 10153, 10154, 10155, 12780, 12781.

Kindly let me know if you need any additional information.

Thanks in advance.

Best,

Amal

[Bill Sheboy · Accepted solution]

Hi @Amal Johnson 

You may do this with three different automation rules:

1. One triggered on Work Item created
2. One triggered on Field Value Changed, selecting all of your input fields
3. One contingency rule, disabled by default.  This rule would use a Scheduled trigger with JQL to identify work items to update, and only be used when Atlassian has an outage that does not trigger rules for the first two cases.

Each rule could use a long-format math expression to calculate the value, and an Edit Work Item action to update the field with the result.  (The inline format math expressions should not be used for your case due to the floating point numbers with decimal values.)

Your calculation uses a table in another spreadsheet tab to help with the Criteria Weights.  I recommend using a Lookup Table for that one to improve maintenance and visibility of the calculation.

One final thought: successfully using automation rules requires learning and experimentation.  I recommend trying to create a rule and when one runs into challenges, post images of the rule and audit log, explaining what is not working as expected.  This will both help ensure the rule better meets your needs and provide context for the community to offer suggestions.

Kind regards,
Bill

[Amal Johnson]

Hi @Bill Sheboy , thank you for your suggestion. Do we really need this lookup field here? Because the Criteria weight is always constant for any tasks. Only the field values in the above 8 fields are varying. And also, I dont understand how I incorporate the 3rd rule you have mentioned, could you please help me with that?

1. Trigger: Issue created
Action: Edit issue → Set Evaluation score to:
{{#=}}(
({{issue.customfield_12777}} * 0.07) +
({{issue.customfield_12778}} * 0.07) +
({{issue.customfield_12779}} * 0.10) +
({{issue.customfield_10153}} * 0.20) +
({{issue.customfield_10154}} * 0.16) +
({{issue.customfield_10155}} * 0.12) +
({{issue.customfield_12780}} * 0.13) +
({{issue.customfield_12781}} * 0.15)
).toFixed(2){{/}}

2. Trigger: Field value changed
Conditions: Any of the 8 fields
Same action as Rule 1

Could you also confirm if the above syntax is correct for 1st and 2nd rules? 

[Bill Sheboy]

No, the Lookup Table is not needed. 

It may help because longer math expressions (particularly when duplicated between different rules) can be difficult to maintain...and could easily introduce errors.  When you expect the Criteria Weight values to be very stable and you have good change management of rules, perhaps skip the table.

For the calculation you show...

Are all of the input custom fields number type?  If not, what are they?

Perhaps with some assumptions:

• the fields are all number type,
• a 0 default may be used when the values are empty / null, and
• you want to ROUND() to two decimal places:

{{#=}}ROUND(
({{issue.customfield_12777|0}} * 0.07) +
({{issue.customfield_12778|0}} * 0.07) +
({{issue.customfield_12779|0}} * 0.10) +
({{issue.customfield_10153|0}} * 0.20) +
({{issue.customfield_10154|0}} * 0.16) +
({{issue.customfield_10155|0}} * 0.12) +
({{issue.customfield_12780|0}} * 0.13) +
({{issue.customfield_12781|0}} * 0.15)
, 2){{/}}

For the third, contingency rule, it is not absolutely necessary, and...

This rule would be the same as your others, except use a scheduled trigger with JQL to update the desired issues.

It would be left disabled by default.  When there is an outage and rules do not run as expected, or you want to bulk-update the field for some work items:

• enable the rule
• run it one time from the editor (rather than waiting for the schedule)
• when it completes, disable the rule again

Although Atlassian added an automation retry capability when rules halt for various reasons, there is no documentation or guarantee what happens when there is an automation outage and work item events still happen (i.e., work item created, field updated, etc.).  Specifically, your first two rules may not run when expected.  And so the scheduled trigger rule is "insurance" to help recover from the outage.

[Amal Johnson]

Hi, yes they are all number type fields

[Jack Brickey]

Hi @Amal Johnson , you could use a single long math expression like below (shortened here to 3 fields just for brevity) or consider using variables for each value and then sum at the end.

{{issue.customfield_xxxxxx.multiply(0.7).plus(issue.customfield_yyyyyy.multiply(0.7).plus(issue.customfield_zzzzzz.multiply(0.1)}}

I recommend giving this a try and then if you run into issues, please post your automation rule screenshot for us to evaluate.

note, you generally can replace the customfield_xxxxx with the actual name of the field except if you have reused names. For example, you show "Priority" which is a system field already so you can't simply use the name here. In these cases, you must actually use the custom Field, ID, which is represented by the xxxxxx nomenclature in my example.

[Bill Sheboy]

Hi @Jack Brickey 

FYI -- The inline math expressions may not work for this case if the first value in the expression is an integer.  That will lead to integer math being used for the remainder of the calculation...probably always resulting in 0 for this scenario.

The long-format math expressions will instead use floating point numbers, and will work better for the fractional Criteria Weights shown.

Kind regards,
Bill

[Deivid Araujo]

Hi Amal

As far as I can tell, all the math operations in the formula can be made using smart values in the automation. Click here to see the syntax for that.

You should know the syntax is not user friendly and can be tricky to work with. Also, if you need to check for changes in all 8 fields to update this value, I recommend to implement any sort of governance to edit the fields, i.e. only let the users edit the fields in a transition screen, this way you know all the fields are updated simultaneously

Let me know if you need help

[Amal Johnson]

Yes, please