Issue with Conditional Custom Formula Field

Question

Hi JPD Community, I’m working on a prioritization use case in Jira Product Discovery and have encountered some challenges with custom fields. Here's my scenario: * I have a custom field called RICE, which contains a calculated RICE score. * I also have a custom field called Weighted Score, which is another custom formula field. * I want to calculate a new custom formula field called RICE x Weighted using the following logic:* If Weighted Score is 0, the value of RICE x Weighted should be equal to the value of RICE. * Otherwise, the value of RICE x Weighted should be RICE * Weighted Score. Here’s what I’ve tried so far: * Custom Formula Field: I attempted to use the formula for the RICE x Weighted field: IF(CAST({RICE} AS NUMBER) * CAST({Weighted Score} AS NUMBER) = 0, {RICE}, {RICE} * {Weighted Score}) This resulted in an "Invalid expression" error. I suspect it might be due to {RICE} and {Weighted Score} being custom formula fields themselves. * Automation with Edit Issue: For the automations, I created a new RICE x Weighted field that is a number field. I set up an automation rule with the trigger "When: Value changes for RICE or Weighted Score" and tried to use smart values in an Edit Issue action to calculate the value of the RICE x Weighted field. The format of the line I used was: {{#if(issue.fields["Weighted Score"] == 0)}} {{issue.fields.RICE}} {{else}} {{issue.fields.RICE}} * {{issue.fields["Weighted Score"]}} {{/}}. Instead of the names, I used the Smart Value of the custom field columns, so for Weighted - {{issue.fields.customfield_11111}} and for RICE - {{issue.fields.customfield_22222}} (I used these numbers in this question so that it is easier to follow). * Automation with If then else: * Trigger: when Issue is updated * If: Used a {{smart values}} condition with {{issue.fields.customfield_11111}} equals 0. * Edit issue: Selected RICE x Weighted and added {{issue.fields.customfield_22222}} in the input box (the value of RICE). * Else * Edit issue: Selected RICE x Weighted and added {{issue.fields.customfield_22222}}* {{issue.fields.customfield_11111}} in the input box (the value of RICE). * For this automation, I get a success, yet I cannot see the value. I also created a separate automation with a Log action with the Log message: "Weighted: {{issue.customfield_11111}}, RICE: {{issue.customfield_22222}} Weighted: {{issue.fields.customfield_11111}}, RICE: {{issue.fields.customfield_22222}}" and it did not display the values of the columns and I am now very confused. I'm new to Jira Automations so there must be something I am missing, that's why I am looking forward to your help!

Jeny Stoeva · Answer

@Maria Barac ,

This will be a long one and I hope you will have the patience to read and understand everything since it will help you achieve your goal :)

JPD Automation should be used. Let me rephrase the objective below. No worries, it will still achieve what you need.

Maria:

* I have a custom field called RICE, which contains a calculated RICE score.
* I also have a custom field called Weighted Score, which is another custom formula field.
* I want to calculate a new custom formula field called RICE x Weighted using the following logic:* If Weighted Score is 0, the value of RICE x Weighted should be equal to the value of RICE.
* Otherwise, the value of RICE x Weighted should be RICE * Weighted Score.

Jenya:

* I have a custom field called RICE, which contains a calculated RICE score.
* I also have a custom field called Weighted Score, which is another custom formula field.
* I want to calculate a NEW FIELD called TEST which is calculated like this RICE * Weighted. TEST filed calculation follows this logic:* If Weighted Score = 0, then TEST =  RICE
* If Weighted Score > 0, then TEST = RICE * Weighted Score

What is very important to understand:

1. The values of the calculated custom fields are not stored in the backend. They are evaluated on the frontend side from the fields included in the formula. I checked over the API and could see the full Jira issue Object. Fields like RICE and Weighted Score are NULL. This is why you must not reference them in formulas and should instead use directly the fields from your formulas e.g. Reach, Impact, Confidence, ...! Those are stored in the backend.

2. Trying to populate a custom calculated field from the automation won't work! It gets evaluated on the frontend and will be overwritten even if you try to change it from the automation. This is why for your case I created a simple TEST filed from type: Number. This one you can easily populate with automation.

With this in mind here is how the automation looks like. I created two separate rules for the calculation logic in points 3.1 and 3.2. Pay attention that I am not using RICE to detect changes but the fields behind the RICE calculation! My Weighted field is from type: Number, since I  had no idea how you calculate it. You should use the fields which are in the formula of your Weighted Score whenever you refer to it!

You can take the fields IDs from the UI (I have shown in one of the screen shots how to do it). Below are the JSONs (using field names didn't work for me, so I used the IDs).

RICE when Weighted = 0
{
"fields": {
"Test": 
{{#=}}{{issue.customfield_10104}} * {{issue.customfield_10099}} * {{issue.customfield_10077}} / {{issue.customfield_10097}} {{/}}
}
}

RICE*Weighted when Weighted > 0

{
"fields": {
"Test": 
{{#=}}{{issue.customfield_10104}} * {{issue.customfield_10099}} * {{issue.customfield_10077}} / {{issue.customfield_10097}} * {{issue.customfield_10106}} {{/}}
}
}

In the IF statements (in the "First value") the  asNumber {{issue.customfield_10106.asNumber}} is not needed. It works perfectly fine when like this: {{issue.customfield_10106}}

And lastly, Jira automation takes several seconds to reflect in the UI, I always forget that :)

Bill Sheboy · Answer

Hi @Maria Barac -- Welcome to the Atlassian Community!

It is unclear when / if calculated JPD fields may be used in other calculations, so let's focus on your attempts with an automation rule...

First, your trigger on Issue Updated seems to broad, and I recommend changing that to specifically check the fields using the Field Values Changed trigger.

Next, I do not believe calculated JPD fields may be accessed in rules as smart values.  Instead only issue fields may be used.  If you have the source fields which calculate the RICE and Weighted Score fields, the calculation could be replicated in your rule.  Let's assume that is possible, and you store those with the Create Variable action as varRICE and varWeightedScore.

Next, smart values are name, spacing, and case-sensitive.  When an incorrect one is used, it returns as null.  To confirm you have the correct smart values (or custom field IDs) for your fields, please use this how-to article: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

Finally, there are several conditional expression formats, and you appear to be trying this one:

Please note there is no pound sign # before the if in this format.

For your case, and substituting in the variables I noted above, that could be this:

{{if(varWeightedScore.asNumber.eq(0), varRICE.asNumber, varRICE.asNumber.multiply(varWeightedScore.asNumber))}}

Kind regards,
Bill

Jeny Stoeva · Answer

Hi @Maria Barac ,

Your solution 1 won't work because custom formula fields in JPD accept only simple operations, when configured in the UI, like: Addition, Subtraction, Multiplication, Division and there is no support for logical operators such as: if statements. This is the reason you are getting the "Invalid expression" error.

In the next post I will explain in details how you can achieve what you want.