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
Community moderators have prevented the ability to post new answers.
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:
https://<yourinstanceurl>/rest/api/2/issue/<issuekey>?expand=names
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Let's try the simplest thing that should work first...Create a test rule to just list the values:
{{#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:
{{#lookupIssues}}{{customfield_13892.format}}; {{/}}
That should look the same, preserving any factional delimiter.
And then this should sum them:
{{#=}}0{{#lookupIssues}}+0{{customfield_13892.format.replace(",",".")}}{{/}}{{/}}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.