Aggregate values with Issue Creation Trigger

[Adam Rosenbaum]

I have a form with several questions. Each question will be point weighted. When I hit save and the issue is created I want to aggregate the value assigned to the choice in each dropdown box without having to create if/then/else  statement for many different combinations. I am not of JIRA capabilities, bit I have 2 thoughts on how to accomplish, if JIRA allows:

1. create  an automation rule for a field that aggregates each box. I.E create a field called Points and the equation would be Points=Points + (Field 1 Value, Field 2 Value) etc.

2. assign a numerical value to each choice in a dropdown list and write an automation rule that adds the value of each field.

I will that I am not an expert in coding so if JSM offers this functionality, that is preferable.

[Adam Rosenbaum · Accepted solution]

I found the table Creation as an action after the trigger is invoked. So in theory:
Upon submitting a form (Issue creation trigger) the tables are created
The values are extracted and edit fields action will aggregate those values

Do the tables need to be created every time, or is there a way to create a standalone  automation rule that creates the tables?

[Adam Rosenbaum · Accepted solution]

so if i am understanding Jira's functionality correctly, the variable cannot be created ahead of time and assigned a value after the fact, the variable needs to be baked into the ITE statement.

If the field is not empty and contains choice 1 then create the variable and assign a point value of 10, else if the field is not empty and contains choice 2 then create the variable and assign a value of 20...so on until all choice are accounted for. Then 1 more automation rule that simply aggregates the value of every variable that has been created and there is the number I am looking to get.

[Jack Brickey · Accepted solution]

Hi @Adam Rosenbaum , welcome to the community.

Let me first state that I have not attempted such a solution previously. However, you may be able to achieve this using automation. Here are my thoughts on an approach.

1. you could define/name each drop down such that it includes the weighting. For example, option1 (15), option2 (5), etc.
2. you could use Regex to parse/extract the weighting. Here is a good article that might explain this for you - Extract-Content-out-of-Description-and-Summary-with-Regex . NOTE: I am unsure if Regex will work on a non-text based field. I need to play with that to see.
3. use Automation variables to temporarily store each field's selected weighting. Which is extracted using the Aforementioned Regex method. Here is an article on using variables - jira-automation-actions look for the section on Create variable.
4. Create a custom number field for the total weighting where you can place the aggregated value. You would use an Edit issue action and add all of your created variables using Automation's math function - Jira-smart-values-math-expressions 

granted, the above is not a full solution for you, but a concept of what I would attempt. Hopefully someone who has actually done something similar to this will chime in and provide greater detail. If you do attempt the above, please keep me posted with your progress, and I will do what I can to assist.

[Jack Brickey]

Hi @Adam Rosenbaum , I was playing around with this concept a bit and determined that it might be better to simply use the "split" function within automation. Below is an example of this in action. Basically, you would leverage the field value naming convention I mentioned previously but instead use a dash as the parentheses I believe it's a reserve character will not work.

{{issue.Change reason.value.split("-").last}}

in this example I referenced my Change reason field where I had one value listed as Failure - 7. The below automation properly extracted the 7 and placed it into my variable which I then used within a comment action simply to test. Oh, and the Log action was just part of my test as well but useful when creating automations.

[Adam Rosenbaum]

But I would have to create a variable for each field and use the Edit Issue function to sum all the fields? Lastly, there is no way to hide the number after the "-" I assume.

Can I write automation that creates an empty variable, and assigns a number to it based on the selection. So:

create variable {{CRText}}

If Field 1 = then CRText = 10

Else if  Field 1 = B then CRText = 20

Finally, one more automated rule that uses Edit Issue to aggregate all variables.

What do you think?

[Jack Brickey]

Hi Adam,

But I would have to create a variable for each field and use the Edit Issue function to sum all the fields? --- A: Yes

Can I write automation that creates an empty variable, and assigns a number to it based on the selection. --- A: I don't understand. If you look at the documentation or try to create a variable you will see that you must define the value. As mentioned above, you can use smartvalues for this.

based on your original post, I assumed your goal was to sum up the weighted issues from several different fields that were filled in by the customer. You indicated you wanted to avoid large if then else scenarios so I attempted to present some ideas based on what I thought you were requesting. I suggested the variable approach so as to avoid creating unnecessary custom fields to capture the individual field weighted values.

I think the best course of action is for you to take a stab at creating a rule and playing with the various components to see if you can zero in on what might work best for you.

[Bill Sheboy]

Hi @Adam Rosenbaum -- Welcome to the Atlassian Community!

Yes, and...to Jack's suggestions:

When you create your rule, you may want to try using the new Lookup Tables feature, as that seems a good fit for your scenario: https://community.atlassian.com/t5/Automation-articles/New-Automation-action-Create-lookup-table/ba-p/2311333

With that, you could create a table for each list of options (e.g. tblFieldOne) and then used that in your calculations to sum values.  Perhaps like this:

{{#=}} {{tblFieldOne.get(issue.FieldOne.value)}} + {{tblFieldTwo.get(issue.FieldTwo.value)}} {{/}}

Kind regards,
Bill

[Jack Brickey]

Thanks for sharing Bill. I need to play with that new feature as I have not leveraged as yet.

[Adam Rosenbaum]

Not sure I was replying the correct place so let me try this again:

A. I assume that numbers are still not applicable tot he .get argument. Many of my drop down fields are ranges. I.E. 0-250. according to your limitation disclaimer .get won't retrieve that correct. So I would have to put "Zero-two hundred fifty" instead? Will the hyphen throw it off as well?

B. I found the create table feature as an action item. does that mean the tables are created every time a trigger is invoked or is there a way to create the tables once and just reference them each time a trigger is invoked such as issue creation.

[Bill Sheboy]

Answering your question "B" first: yes, when the rule runs the Lookup Table is created for use by the rule.  (There are open suggestions to allow persisting those for later reuse by rules.)

And for "A", it may help if you show an example of all possible values for that selection field.  Seeing the values may help the community guide you in constructing a math expression to help.

For example, if your selection field values are something like this:

• 0-250 widgets
• 251-500 widgets
• more than 500 widgets

Parsing is more difficult if you try to extract the values due to the formatting differences.  But using the Lookup Table would handle that, as the values could be used directly as the keys.

[Adam Rosenbaum]

extracting value from the table and storing  it as smartvalue, can you have more than 1 smart value and can smart values be aggregated using edit issue like other fields can.

Theory:

Write an automation rule that creates the table then runs through an if then else statement to find the actual value selected by the requester, extracts the point value associated with that selection and stores it as a smartvalue.

Repeat for other fields (lookup table, .get to extract point value and )store point value in a different  smartvalues

Finally one more auto rule that aggregates the sum of all smart values.

Is that pretty much it?

[Bill Sheboy]

The power of lookup tables is "lookup".  If you can get your values then the lookup happens in one step, without the looping or if/else structure you note.

And once that happens for each field, there is no need for another rule: just add the values as needed.  That was what I was describing earlier:

https://community.atlassian.com/t5/Jira-questions/Re-Re-Aggregate-values-with-Issue-Creation-Trigger/qaq-p/2475616/comment-id/591460#M591460

My recommendation is to create a test rule, for just one field and lookup table, to get the concept working.  Then incrementally add the other fields/lookup tables until done as you want.

[Adam Rosenbaum]

So if different fields have different selection choices, build 1 table with all of the selections along with the coordinating point values. As long as the same selection doesnt exist in different fields with different values, 1 table should do it, which that isn't the case.

[Bill Sheboy]

Please try different tables.  Without seeing your fields, let me offer an example...

1st lookup table: tblLocation

• key: beach; value: 30
• key: mountains; value: 50
• key: home; value: 20

2nd lookup table: tblActivity

• key: idle; value: 0
• key: swim; value: 20
• key: hike; value: 70
• key: sleep; value: 10

Two custom, selection fields on the page would offer choices for Location and Activity, and the scoring to add these would be:

{{#=}}{{tblLocation.get(issue.Location.value)}} + {{tblActivity.get(issue.Activity.value)}}{{/}}

[Adam Rosenbaum]

Bill i have some of the basic frame work written out but I think I need a little nudge in the right direction to get it to actually work. What I have is:

Automation Rule to create table:

Trigger: Issue Creation

Actions: when any of 3 fields are not  empty, the table is created (this rule runs successfully)

Automation Rule to get value from table and store it into a smart value variable:

Trigger: When Issue is created

Issue Field Condition: Potential Productivity Loss Internal (MIM) is not empty

For Each: {{Potential Productivity Loss Internal (MIM).get("tblRanges")}} Variable Name = varPPLI

Then: Edit Issue Fields Points (MIM) Value set to varPPLI

Do I have that right?

[Adam Rosenbaum]

correction:

If Potential Productivity Loss Internal (MIM) is not  empty then

create a smart value variable called var PPLI and assign it the value retrieved from tblRanges using the following statement:

{{Potential Productivity Loss Inter (MIM).issue.get("tblRanges")}}

I just want to be sure the spaces and params in my field name are not causing the automation to fail

[Adam Rosenbaum]

I seem to be too quick asking questions because the automation rule does run correctly. My issue appears to be assigning the variables value to a custom numeric field since I will need to retain that value for use after the issue has been created.

If the Then part of the statement is extracting the value from the table and assigning it to the variable, can I have a second then statement assigning the variable to the customer field or does that have to be a separate automation rule? I think that may be my issue? 

[Bill Sheboy]

Without seeing your complete, current rule...here are some ideas to try to help:

Context and scope are key in a rule.  If something is created inside of a branch or if/else condition it may not be visible to other parts of a rule.  And, when you create a variable or a table in a rule, they are only available for that rule's execution: not for other rules or for other executions of the same rule.

So for your scenario, an approach may be...

• trigger: issue created
• action: re-fetch issue (I always add these after the issue created trigger, as it can run so fast that the data is not available to the rule yet.)
• condition: some conditions to decide if we should proceed
• action: create a table for use later in this rule (Lookup Tables are only available in a rule in which they were created, and only while the rule is executing.)
• action: issue edit (this can use the table, as needed)
• condition: some condition... (this can use the table as needed)
  • action: ...
• ...

[Adam Rosenbaum]

Rule 1 (runs successfully)

trigger: issue creation

If field 1 is yes then

Table is created

Rule 2(Runs successfully)

Trigger: Also on Issue Creation

If field 2 is not empty then

.get accesses table to get the value and assigns it to varPPLI (smart value)

Rule 3 (successful)

Trigger: Also on issue creation

If field 2 is not empty then

Edit Issue field: Field 3 (custom numeric field) set to varPPLI

All 3 rules run successfully but when the issue is created Field 3 is empty. I am thinking that there is something to what you said above about multiple rules running at once maybe the information is not there to assign to the custom field etc. I am also contemplating if there is a way to accomplish this with less rules. In other words create the table, het the value and assign the value right to the customer field without use of a variable. I am also not familiar with refetch

[Bill Sheboy]

My apologies if I was unclear, so I'll try again: when a Lookup Table is created in a rule, it is only available in that rule, when the rule is running.  It is not available later to other rules.

One challenge with automation rules is that when a smart value is unknown (like your reference to the table in Rule 2), its value is null and no errors occur.  This can lead to problems like you see where the rule appears to work and the results are unexpected.

Please try creating this in one rule as I suggested earlier.

[Adam Rosenbaum]

That makes more sense. I should be more transparent as well, I have a strong background in ServiceNow, but I am new to JIRA. I think the syntax may be my issue. I have figured out how to create a log action from a manual trigger so I can granularly test. Once I understand the syntax I should be fine. Here is what I have:

I have a dropdown field called PPLIMIM

The value selected in that field will be used to extract its corresponding value from the table

The table is called tblRanges

I intend to create a variable called varPPLIMIM and store in ti the value I extract from the table. I perceive the syntax to be:

{{tblRanges.get("PPLIMIM")}}

If the selection in the field is 1-50 and 1-50 corresponds with 1 on the table the variable stored in the variable should be 1 correct?

[Bill Sheboy]

You almost got it!  To use lookup tables, the value passed to get() should be the keys you set up.  So I suspect your lookup usage would be this:

{{tblRanges.get(issue.PPLIMIM.value)}}

• In your example, someone selects a value of "1-50" from the PPLIMIM drop-down
• This triggers the rule (correct?)
• In the rule, a Lookup Table is created, and one of the rows is this
  • key: 1-50
  • value: 1
• Then the value is selected from the table, as I noted above

[Adam Rosenbaum]

so just missing issue and value around my field name

[Bill Sheboy]

Yup!  The documentation is ambiguous which attribute is returned as "default" when we just select a field.  It could be id, name, value, a list, etc.  Better to be explicit and then if it does not work it is easier to understand why.

[Adam Rosenbaum]

and I assume if i want to save that variable in a custom field so I can use it again, I would Edit Issue, Select the field (Points), and assign the value of varPPLIMIM.value, I can also aggregate several variables and assign that value to Points as well? When assigning the value to a custom field using Edit Issue, do I use .value or just the variable name?

[Bill Sheboy]

Yes, you could save the values after the lookups, as needed.  Often that is only needed if the calculation was expensive / slow or the rule structure requires it.

Created variables in rules are text, and do not have attributes like id and value.  They can be converted using functions like {{varSomeVariable.asNumber}} or {{varOtherVariable.toDate}}

Math operations usually take care of the conversion from text, so if you wanted to edit your Points field, it could be whatever you needed to add up.  For example:

{{#=}} {{varSomeCreatedVariable}} + {{issue.someNumericField}} + {{issue.someSelectField.value}} {{/}}

[Adam Rosenbaum]

Understood and great info and also thanks. what is the significance of {{+=}}{{/}} Is like SQL start and end tags? Also, assigning the variable to a field I use edit issue fields select the field and set it to varName. Do I need to mustache the variable or add .value?

[Adam Rosenbaum]

more specifically: The value is added to the variable and the variable writes to a custom field called PointsMIM using edit issue fields setting the fields value to varPPLIMIM

Next I add a rile with a log action assigning the log action the value {{issue.PointsMIM.value}}

When I run that rule and check the audit log I see Log Action Log

[Adam Rosenbaum]

One other thing I forgot to mention is that PintsMIM is a numeric field and as such  when assigning the variable to the PointsMIM I had to use the syntax:

{{varPPLIMIM.asNumber}} so it converted otherwise I was throwing a conversion error.

[Adam Rosenbaum]

Progress!. I changed the PointsMIM field to short text, using Log Action confirmed the PPLIMIM field is 1-50 as that is what is selected, and confirmed also using log action that a manual value I add to the variable is displayed in log action. I have narrowed it down to the value of 1-50 not being found in the table.  Again here is my syntax you have confirmed to be correct:

{{tblRanges.get("issue.PPLIMIM.value")}}

tblRanges is my table being created and PPLIMIM is the field with the selected value of 1-50. Do I need to convert that field to text before checking it against the table. I believe I read the table stores as text not number correct?

[Adam Rosenbaum]

OK last time I promise because I found the issue:

The order of the logic you suggested does not work. In other words, you cannot access a table using a variable, instead I extracted the value using the value of the PPLIMIM field and from there I can assign it to another custom field (PointsMIM) and leave it there or assign to a variable in order to aggregate with other values. So I accomplished alot with your assistance and was able to troubleshoot the issue on my own as well. Thank you for all your help and I am sorry for being a PITA!

[Bill Sheboy]

No worries, as we are all learning.  And, great progress!

For your earlier question, the {{#=}} {{x}}+{{y}} {{/}} syntax is one of the ways to perform math expressions.  The other way, for simpler operations would be this: {{x.plus(y}}}  Please look here to learn more about the math operations: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

Regarding using fields and variables with a lookup table get() operation, that should work.  If you post an image of your complete current rule, showing the details of the lookup table and the use of get() on the table, we can figure out the cause.

[Adam Rosenbaum]

so aggregating multiple values should use that syntax then?

I honestly am good with this. I am using a value from a dropdown field to get a value from a table and storing in another custom field so that I can retain it aggregate it with other fields etc. Exactly what I wanted to do. Thank you for allyour help and I really appreciate your patience.

[Adam Rosenbaum]

Bill do I have to mark this answered or completed somehow and if so how do I do that

[Bill Sheboy]

It appears you have already marked the responses/threads as "answered".

[Adam Rosenbaum]

reading over that now. I'll let you know if I have anymore ????

Thank you both