Automation for JIRA - Sum of numeric (decimal) fields doesn't work with lookupissues

[Patrick Schmidt]

Hi,

I've got a custom numeric field set up in JIRA, which contains decimal numbers, e.g. 8,5.

The comma is used as a decimal separator instead of a dot, as this is the setting for Germany.

{{lookupIssues.My custom field.sum}} returns 0 although the fields are filled with values. I think it has something to do with the decimal separator.

Using issueFunction in JQL returns the expected number, e.g. 57.2

issueFunction in aggregateExpression("Sum of xyz", "MyCustomField.sum()"

Any idea?

Thanks a lot,

Patrick

[Bill Sheboy]

Hi @Patrick Schmidt 

First thing, the {{issues}} smart value (note the plural) is only defined / supported for Jira Server and Data Center versions, not for Jira Cloud.  I would not rely upon that smart value for Jira Cloud.

Next, you note that a numeric custom field is not available in the results of Lookup Issues.

Smart values are name, spacing, and case-sensitive.  And often the smart value does not match the displayed field name on the issue views.  When an incorrect smart value is used in a rule, it evaluates to null and often fails silently.

The how-to article below helps to identify the correct smart value (and custom field id) for any supported fields for automation rules.  The essential steps are:

• identify an issue which has your field(s)
• call a REST API function with a browser to search for your issue:

https://<yourinstanceurl>/rest/api/2/issue/<issuekey>?expand=names

• search on the page for your field, such as with CTRL-F
  • if you find the field, you will also find the smart value, custom field id, and structure / attributes it has
  • if you do not find the field, it is not supported by automation rules

https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

If you custom field is from a marketplace addon, that may explain why it is not supported by automation rules.

Kind regards,
Bill

[Patrick Schmidt]

Hi @Bill Sheboy

thanks for your reply!

I forgot to mention that I'm using JIRA Data Center. I'm aware of the case sensitivity and checked the correct field name through the rest API. What I noticed is that I can't call specific custom fields with their ID, e.g. {{lookupIssues}}{{customfield_123456}} after a JQL lookup, also {{lookupIssues}}{{customfield_123456.sum}} doesn't work. I tried this for several numeric custom fields. I guess I need the correct smart value call with {{issues}} that provides me a sum of my custom field. {{issues}}{{My Custom Field.sum}} only returns one value of the list of issues not a sum of all.

Thanks a lot,

Patrick

[Bill Sheboy]

Thanks for the confirmation of your Jira version.

To sum a numeric field for a Lookup Issues result set, please use this syntax:

{{lookupIssues.customfield_123456.sum|0}}

I added a default value of 0 to the end to handle the case of all values are empty / null.

[Patrick Schmidt]

Hi @Bill Sheboy

thanks again! Unfortunately this doesn't work either. I tried several versions, all return a 0. :(

{{lookupIssues.customfield_123456.sum|0}}

{{lookupIssues."customfield_123456".sum|0}}

 {{lookupIssues.My Custom Field.sum|0}}

{{lookupIssues."My Custom Field".sum|0}}

{{issues.customfield_123456.sum|0}}

{{issues."customfield_123456".sum|0}}

{{issues.My Custom Field.sum|0}}

{{issues."My Custom Field".sum|0}}

The custom field contains decimal values separated by ",".

In JIRA the JQL issueFunction in aggregateExpression("Sum", "MyCustomField.sum()") returns Sum: 32.6.

[Bill Sheboy]

The syntax I provided would work for a numeric field with decimal points for the factional separator. 

Let's try this one, using a math expression and a list iterator, replacing any stored commas with a decimal point:

{{#=}}0{{#lookupIssues}}+0{{customfield_12345.text.replace(",",".")}}{{/}}{{/}}

If that works, the symptom you are seeing seems like a defect to me if the rule handling cannot detect the commas with international numeric formats.

[Patrick Schmidt]

Hi @Bill Sheboy

unfortunately this also results in 0. I tried various versions, like with {{#issues}} or using My Custom Field instead of customfield_12345. I also tried your smart-value with a different custom field where {{lookupIssues.My other custom field.sum}} is working. That one only has integer numbers. Your smart-value also returns 0 for that one.

 {{#=}}0{{#lookupIssues}}+0{{My other custom field}}{{/}}{{/}} returns the expected sum.

[Bill Sheboy]

I am puzzled why these techniques would not work...let's level set with some context:

What are the types of each of the fields you are trying to sum: numeric, text, select, something else?

Please post some images for:

• an image of your complete automation rule,
• images of each action where the rule tries to sum the fields, and
• an image of the audit log details showing the rule execution when it does not work as expected.

Please post images of example issues with their field values.  I am particularly interested if there can be empty fields and / or a mix of fractional delimiters (i.e., decimal point versus comma).

[Patrick Schmidt]

Hi @Bill Sheboy

thanks for your patience :)

My screenshots are in German, but I hope that shouldn't matter too much.

The custom field in question is a numeric field (Zahlenfeld = numeric):

The content of the field after running the JQL is either 0, some integer value or a decimal value, separted by comma and only comma (no dot). There are no empty fields.

Here's the complete automation rule:

...and where the variable is created:

...and it's output:

The audit log shows a successful run without errors:

Thanks again!

Patrick

[Bill Sheboy]

Let's try the simplest thing that should work first...Create a test rule to just list the values:

• trigger: manual
• action: lookup issues with your JQL
• action: log the field with this:

{{#lookupIssues}}{{customfield_13892}}; {{/}}

Run that and review the audit log.  What do the values show: with commas or decimal points?

Then a slight adjustment: after I checked the docs and tested, I found that the text function does not work with number fields.  Instead we need to use format...So then adjust the rule to this one:

• trigger: manual
• action: lookup issues with your JQL
• action: log the field with this:

{{#lookupIssues}}{{customfield_13892.format}}; {{/}}

That should look the same, preserving any factional delimiter.  

And then this should sum them:

• trigger: manual
• action: lookup issues with your JQL
• action: log the sum with this:

{{#=}}0{{#lookupIssues}}+0{{customfield_13892.format.replace(",",".")}}{{/}}{{/}}

[Patrick Schmidt]

Hi @Bill Sheboy

{{#lookupIssues}}{{customfield_13892}}; {{/}}

...shows a successful run in the audit log. Unfortunately the generated output looks like this: Test Story Points: ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;

{{#lookupIssues}}{{customfield_13892.format}}; {{/}}

...also runs successful but with the same output.

{{#=}}0{{#lookupIssues}}+0{{customfield_13892.format.replace(",",".")}}{{/}}{{/}}

Here the output is 0 without errors in the audit log.

I limited the JQL to only issues with integer values in the custom field.

{{#lookupIssues}}{{customfield_13892}}; {{/}}

...produces this output: Test Story Points: ; ;

So sorry to bother you with this. :(

Thanks again,

Patrick

[Bill Sheboy]

To reset a bit, I just re-read this conversation thread from the beginning, and I noted you are using Jira Data Center...Custom fields are not yet supported for Lookup Issues with that version of Jira automation: https://jira.atlassian.com/browse/JIRAAUTOSERVER-877

When the Lookup Issues action was implemented for Jira Cloud, it only had about a dozen fields, and about a year later they added all the other fields.  Perhaps that will happen for Jira Data Center as well.

At this time, I believe a work-around would be to use the Send Web Request action to call the REST API for a JQL search instead.  That should return the custom fields, and the response can be iterated over instead of the lookup.  Here is a how-to article on that approach if you want to try it: https://community.atlassian.com/t5/Jira-Software-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828

I apologize for the detours on this one!

[Patrick Schmidt]

Hi @Bill Sheboy

thanks - I guess that I will wait and hope for a DC update, which will add the field. As I'm not the only JIRA admin I don't want to use the base64 API-key, which will be needed for using the REST-API.

Thanks again for all your support!

Patrick

[Patrick Schmidt]

Hi,

I might be a little closer to a solution. It turns out that {{lookupIssues}} is not able to read the content of my numeric field but {{issues}} does. {{issues}} should also be able to parse a list of issues provided by JQL. {{issues.sum}} doesn't return the expected value.