How can I refer values in multiple linked tickets at once and add them up?

[sergio.peschiera]

I have a main ticket where an internal budget has been approved for X amount. Later, overtime, multiple tickets with invoices are linked to this main ticket. I'm then adding up the invoice values and comparing it to the main ticket's approved amount to identify when we are on budget.

However, as very often these invoice tickets are linked all at once, the automations do not run in succession and some invoice values are overriden and not accounted for. I want to run a scheduled automation that runs for each main ticket, goes through each invoice ticket and adds up the invoice amount of all and then stores it in the main ticket.

eg.

Main ticket

Invoice Ticket A (invoice amount: 25)

Invoice Ticket B (invoice amount: 17)

Invoice Ticket C (invoice amount: 8)

I want to run an automation that goes to the Main Ticket, then evaluates all linked tickets that meet a certain condition and then add up all the invoice amount and stores the result in the main ticket so that I get invoice amount: 50 in the Main Ticket.

[Trudy Claspill · Accepted solution]

Hello @sergio.peschiera 

How many of these "main" tickets are there to evaluate?

You could run an Automation Rule with a Schedule trigger that use JQL to retrieve the main tickets.

When using a JQL in a scheduled trigger the actions after that in the rule will be executed for each issue returned by the JQL in the trigger.

Then use a Lookup Issues action to look up the linked issues. 

Follow that with an Edit Issue action to edit the Invoice Amount in the main ticket with the sum of the invoice amounts in the issues returned by the Lookup Issues Action:

{{lookupIssues.<your custom invoice field>.sum}}

[sergio.peschiera]

Hey Trudy,

Gee, thanks! This could actually work! How do I use lookup issues to return the tickets linked to the trigger ticket? I don't think I've ever limited a lookup search based on the trigger ticket.

If I'm using the scheduled trigger, how do I refer to the "trigger" issue id? Would it be something like:

issue in linkedIssues({{issueid}})

I guess it would take the reference from the scheduled trigger but I'm not so sure.

[Trudy Claspill]

When using a Schedule trigger with a JQL statement to pick the primary issue, the rule will iterate over each issue in the result to execute the remaining actions in the rule. To refer to the issue in the current iteration use the smart value {{issue}}. You then can tack on to that smart value the field you want to reference and use that in the JQL.

So, in the Lookup Issues action you would use a JQL like this:

issue in linkedIssues({{issue.key}})

...if all the issues linked to the primary issue are Invoices. If they are not all invoices then you would have to add additional criteria to get only the linked Invoices.

[sergio.peschiera]

awesome, this works perfectly. thanks again!

[Iryna Komarnitska_SaaSJet_]

Hi @sergio.peschiera ,

I recommend you use the app developed by my team Cost Tracker for Jira Cloud.

Recently, we added an invoice generation feature to it.

In it, you can generate informative cost reports where everything will be calculated correctly.

Examples of reports: