Can custom formula fields contain variables and/or use values from other ideas?

[Juniper Emnett]

Hi!

I have a project set up following the hierarchical example seen here.

So for example, I have:

• A field called "Category", where the options are Opportunity or Solution
• A field called "Opportunity", where the options available are our 7 or so different primary opportunities

I have "parent" tickets for each Opportunity -- I have an idea titled "Data Visualization" where Category field = "Opportunity", and Opportunity field = "Data Visualization". This is the "parent" ticket.

Then I have several "child" tickets for solutions that fit under the Data Vis opportunity -- they are set to Category field = "Solution", and Opportunity field = "Data Visualization".

In this way I can create high-level views of major opportunities, as well as drill-down views of the associated solutions (the video explains better than I can with text, and the screenshot below illustrates it fairly well). The parent/child implication is really all facilitated through views with various filters.

The actual question:

My "child" Solution tickets have Annual Recurring Revenue values (extracted from Salesforce-integrated insights on each ticket). I would like to roll up the ARR from all ideas with the same Solution so that it can be displayed in views when I'm looking at the "parent" Opportunity ticket.

Essentially I need a formula that says "display the sum of all of the ARR from all other tickets with the same Opportunity value as this ticket".

Rollup Revenue = sum (ARR where Opportunity = {this idea's Opportunity value})

Current state:

Desired state:

If not possible with the built-in formula fields, can anyone think of a way to accomplish this with automation functionality, or some other solution? I was also contemplating copying all insights from child tickets to the parent ticket, either manually or through automation, but that sounds rough to maintain.

[Bill Sheboy]

Hi @Juniper Emnett 

If you can write a JQL statement to find the "child" issues based on the field values, the answer is "yes".

To do this, please try creating an automation for Jira rule, triggered on the conditions for your scenario.  This means you would either need several rules for an "always accurate" Rollup Revenue, or use a scheduled rule to reduce complexity.

One consideration: how does the Salesforce data get set in the idea issues: manually, through an integration, etc.?  If it is through an integration, this may not be possible.  Experimentation with a test rule can verify that.

An example rule could be...

• trigger: field changes for Total ARR
• condition: Category equals Solution
• action: lookup issues with JQL, to gather the sibling issues with the same Opportunity and which are also Solution items
• branch: to the parent item
  • action: edit your field with the sum, using {{lookupIssues.Total ARR.sum}}

This is just one example rule.  Please consider what can change that leads to a need for a re-rollup: add an issue from the solution, remove one, edit an issue's "parent", etc.

Please check the field names and smart values to confirm that match what you need.  And...you are showing the Rollup ARR stored in each issue, so a different branch may be needed for that.

To get you started on creating your rule, please review this documentation and examples:

• https://www.atlassian.com/software/jira/guides/expand-jira/automation
• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-issues/
• https://www.atlassian.com/software/jira/automation-template-library

I recommend chatting with your Jira admin as they can probably help you implement this rule.

Kind regards,
Bill

[Juniper Emnett]

@Bill Sheboy I have no words for how helpful you are! The logic totally makes sense, I will try implementing this week and report back with details in case anyone else has a similar question in the future.

I assume some of this may be a moot point if/when a more formal hierarchy concept is rolled out, but until then this structure is working well, and the flexibility of JPD allows for experimentation without making me feel like I'm going down a path that can't be revised later.