In your Jira projects, you might need to perform calculations based on data in your Jira custom fields. Yet, Jira doesn’t give you the ability to perform on-the-fly calculations, even reasonably straightforward ones.
In this article, I show an example of how a calculation can be done – with step-by-step instructions – using Jira Misc Custom Fields (JMCF) for Jira Server and Jira Data Center.
Of course, this calculation example can be adapted to many of your business scenarios, such as calculating cost across various projects and calculating estimates for future projects based on related projects.
In addition, you’ll also see how simple it is to use JMCF’s Editor and Tester, even if you are a Groovy novice. Let’s get started.
What is the Business Need?
Jira comes with a number of standard system fields. It also gives you a flexible and broad means of creating your own custom fields. But what it doesn’t do is give you the ability to create custom fields that will perform calculations. Don’t bother looking or experimenting, you just cannot.
How does JMCF fill that need?
To me, this is pretty simple. If you need the ability to calculate fields, then you need Jira Misc Custom Fields, commonly known as JMCF.
How does the business benefit?
If your process needs calculations, then JMCF will speed up the work process as you can now calculate things automatically rather than having to do the calculation on other devices, apps, or calculate manually. This frees up your team member to do more productive value-added work, reduces manual calculation errors, and probably improves morale.
In addition, as it probably takes, at a maximum, 1 (one) hour or less to build this solution. The cost/benefit is incredible.
Create a “Work Cost Estimate” calculated field
Here, I want to demonstrate a “work cost estimate” calculation operation. I will have a Cost per Hour field, a Number of Hours field, and a Total field, which will multiply the cost and hours fields.
We need to start by creating three custom Number fields.
Create three custom fields
The first thing to do is create two custom number fields (you probably have a “Sandbox” project somewhere, so I recommend you use that for this exercise).
- Create a custom Number Field, name it “Hours Work Estimate”
- Create a custom Number Field, name it “Hourly Fee”
- In the Advanced area of the Custom Fields screen, Create a custom Calculated (scripted) Number field (JMCF app)
- Make sure to associate them to a screen, etc.
We have now created our three custom fields. Two are standard Jira fields, and one is the JMCF Calculated (scripted) Number Field. The first two are our ‘base’ fields, and the JMCF field will show the results after calculating the two base fields.
Create custom field formula
Next, we are going to create a formula (script) for our JMCF custom field and then test it.
When creating this formula, we need to know three things:
- The custom fields involved
- What to do with each of those fields
- The operator involved (e.g. Add(+), Subtract (-), multiply (*), or divide (/)
- Navigate to the Main Menu Bar > Administration Cog, and select Issues
- In Administration > Issues, select Custom Fields in the sidebar
- In the Custom fields screen, find the Total Quote field and click on the Cog in the Actions column
- In the Cog drop-down, select configure
- In the Calculated (scripted) Number Field, click on Edit Groovy Formula:
6. In the Configure Formula screen, click on Issue Fields:
7. In the drop-down that appears, click on the Select a field drop-down arrow:
8. In the drop-down field, enter Hours estimate and select from the list:
9. Click on ID in the Refer to the issue field by options
10. In the list of value options that appear select Issue .get(“customfield_#####”)
NOTE: ##### represents the field ID for the “Hours Estimate” field in your version of Jira:
11. As a result, you should see this:
What we did
Let’s take a short break here and look at what we did.
First, we entered the name of the first custom field we need in our calculation. By the way, you could have first picked Hourly Fee, it doesn’t matter the order.
Second, we decided to have the formula use the field ID instead of the field name. I did this for two reasons. If the formula includes the Name, then any time you change the field name (it happens more than you might expect), you need to go back in and change the formula name as well. By using the field ID, you don’t need to do anything if you change the field name. In addition, if you are in a company with many Jira or Project Admins you may end up with many fields named the same thing. If you use field name in the formula, and someone else creates a custom field with the same name, your formula won’t work, because it won’t know which field to choose.
Finally, we get to the intimidating part. All those expressions that look like I.T. coding. In this case we chose Issue.get(“customfield_10402”). The formula means, “In this issue, get the value that is in custom field 10402“. In our exercise custom field 10402 is the field ID for Hours Estimate in my version of Jira. Your ID is probably different.
We are one third of the way home, so let’s continue.
12. In the formula bar enter ” * ” after the formula (just enter the asterisk, not the quote marks):
13. Repeat steps 6 to 11 and in this case select Hourly Fee as the field
14. When done you should see this:
Let’s finish by testing our formula and saving the script.
15. Click on the Test Groovy script…. link:
16. In the Test Groovy Script pop up, enter the Issue Key you wrote down in the Issue Key field and click Test
17. If everything is good, you should see something like this:
18. If everything worked, click Save to save your script Formula.
If the script didn’t work, you probably got enthusiastic and rushed through the instructions. Don’t worry, it happens often. Go back and start over, being very careful to follow the instructions as written.
Format calculated field
Our calculated formula works, but looks somewhat odd without a currency symbol. How do we know if the result is US currency, Euro, Yen, Pound, Yuan, Rupee, or that of any other country? Or maybe it is indeed just supposed to be a number.
Let us format our calculated field to include a $ sign.
1. Navigate to the Main Menu Bar > Administration Cog, and select Issues
2. In Administration > Issues, select Custom Fields in the sidebar
3. In the Custom fields screen, find the Total Quote field and click on the Cog in the Actions column
4. In the Cog dropdown, select configure
5. In the Calculated (scripted) Number Field, click on Edit Format Expression:
6. In the configure Number Formatting screen, find numberTool.format (‘currency’,value). If you don’t see the image below, click on Expected Value in the Help line.
7. Copy and paste it into the expression line
8. In the expression, replace ‘currency’ with ‘$’.
9. Click Test Groovy Script, and you should see this:
NOTE: if you get an error message, replace ‘$’ with ‘/$’
10. Click Save
What we did
When we opened the Configure Number Screen, we were shown a quantity of formula snippets. I find it easiest simply to copy and paste (making sure to leave no spaces before and after the formula), and then edit the formula.
This particular formula has four parts:
- numberTool is the object we want to work with in the calculated issue field
- format is what we want to do using the numberTool
- ‘currency’ is the place holder for the currency symbol
- value means that the field will show the value of the field after the currency symbol.
Make sure to include the single quotation marks before and after the currency symbol, otherwise you will get an error message.
The first time you try this formula you may get an error message. That’s because the $ symbol means something specific in software coding, and the program might get confused. In this case you need to add another symbol to show the software that you want to use it for its original purpose: currency symbol. If you do get the error, then change ‘ to ‘/ . Typically, for some strange reason, the next time you format a similar number you just need to type the original ‘ . The program seems to recognize what you are trying to do.
Last updated: 2023-03-21