Automation for Jira: Copy value of Elements Connect field to number custom field

[Joelle Güfel]

Hi there,

I am trying to copy the value of an Elements Connect field to a standard Jira custom field of the type "number". I am using Jira on Server and the app "Automation for Jira".

When using an automation rule, I don't get any results from advanced field editing (More options > Additional fields) with the following code (as suggested here):

{
"fields": {
"customfield_11121": {{issue.customfield_11400.asJsonStringArray}},
}
}

Here's what i noticed:

The Elements Connect field (of the type "Snapshot" btw) gives me a number in the format "1.069.754,49". Via Rest API I get the following information:

customfield_11400: {
required: false,
schema: {
type: "array",
items: "string",
custom: "com.valiantys.jira.plugins.SQLFeed:nfeed-unplugged-customfield-type",
customId: 11400

While the info on the Jira custom number field is:

customfield_11121: {
required: false,
schema: {
type: "number",
custom: "com.atlassian.jira.plugin.system.customfieldtypes:float",
customId: 11121

The format of this number field is "862,000" or "862.000", depending which language is used (English or German). The format of the Elements Connect field is always as mentioned above.

I understand that the main problem is that the Elements Connect field has a string value while the custom field expects a numeric value. I have tried to play around with different smart values (.asNumber, .remove("."), .format("###")) but haven't had any luck so far.

Does anyone have a solution for this? Any feedback and suggestion is appreciated  :)

Cheers,
Joelle

[Simon Laffont · Accepted solution]

Hello @Joelle Güfel

I'm part of the Elements Support team.

That's an interesting question. I have just checked on my end and I don't have any problem to populate a custom Jira number field from a Connect Snapshot field that contains a valid number, as shown below: 

This is what I wrote in the Additional fields section:

{
"fields": {
"customfield_11121": "{{issue.customfield_11400}}"
}
}

Can you please remove the asJsonStringArray function you wrote after issue.customfield_11400 and see if it make a difference?

If further help is needed, I invite you to raise a ticket on our Support platform.

Kind regards,
Simon.

[Joelle Güfel]

Hi @Simon Laffont 

thank you for the quick reply! Unfortunately, when I remove the asJsonStringArray I get the same error as before: Error while parsing additional fields. Not valid JSON.

Thanks for the link to the support platform, I will raise a ticket there.

Cheers,
Joelle

[Bill Sheboy]

Hi @Joelle Güfel 

Did you note that @Simon Laffont added quotation marks around the value on the right side?  Did you try that format after removing the asJsonStringArray value?

Best regards,
Bill

[Simon Laffont]

Hi, 

After further analysis with @Joelle Güfel, the problem was due to the format of the number used in the Snapshot field which was not understandable for a classic Jira number field.

For a Connect field value to be passed to a Jira number field, the following format must be respected:

1069754.49

So, there should be no thousands separator, and the decimal separator should be a dot.

For example, in the case of a Snapshot field connected to a MSSQL database and using the following query:

SELECT numberColumn as number FROM mytable

Here is how the number should be formatted:

SELECT REPLACE(FORMAT(SUM(numberColumn),'N','en-US'),',','') as number FROM my table

(!) This solution comes from the request created by @Joelle Güfel on our support platform and matches a specific use case. It may not be applicable to other cases. 

(i) For your information, we have created the following Change Request: CO-1814 - Elements Connect custom field of type Snapshot Number 
For those who are interested, we advise you to vote for it to give it some weight and add you as watcher to be informed of any news.

Kind regards,
Simon.