Lookup Mapping in Jira using Jira itself

[#1]

The problem:

Say you want to set the value of one or more fields based on a lookup of other fields. (What does that even mean, Darryl?)

Well, let's say that you use Components to not only assign tickets to the Component Lead when a ticket is created, but you also need to designate an Executor per Component that will carry out the ticket after it is approved. You want the ticket assigned to the Executor automatically at that stage.

Or in a more complex example, you might have several "key" fields (Release, Phase, Build Area) that in various combinations map to "target" fields: primary and secondary approvers from Engineering and Build teams. Whenever one of the "key" fields changes, you'd want to update the approvers accordingly.

With on-prem Jira you could accomplish this ScriptRunner or Power Scripts, and if you use Automation, you could create a rule with multiple IF-ELSE blocks, or multiple rules for each variant.

But it would be even better to delegate management of these mappings to project owners (leads, managers, and so on) without having to edit a complex Automation Rule. Jira Admins would not have to handle those requests, and changes could be implemented faster.

The solution:

If you create a separate project to hold your mappings, you can create a simple Automation that looks up the mapping based on your "key" fields, and can take actions based on "target" fields, like assigning to the looked-up values or updating the issue with the newly looked up values.

Step 1 - the mapping project

You'll need to set up a separate project to hold your mappings.

The steps are:

1. (Optional) Set up the Lookup Mapping issue type.
2. Create a new project using the "Task Tracking" template. (You can use any template, but this one keeps things simple.)
3. (Optional) Edit the issue type scheme to use the the new Lookup Mapping issue type
4. Update the screens to remove any fields you don't need to map, and add your "key" and "target" fields.
5. Update memberships in the project roles, limiting access to Jira Admins and any users that you will be delegating mapping to.
6. Populate the entries as issues.

It's a good idea to make custom Screen and Field schemes in case you need to create other mappings restricted to different sets of users. You can simply create a new project that shares settings with the existing mapping project.

Step 2 - set up Automation (details in examples)

Simple example - Lookup for assignment

Here's a simple example and rule. The scenario is we're already assigning an issue to Component Lead for Review. When the issue moves to In Progress, the issue needs to be assigned to a user known as the "Executor".

Issues in the SAMLOOKUP project have just two fields: Components and Executor. (I have automated having the Summary being rewritten to "Executor - {{issue.component}}" whenever Component is updated, but it will overwrite any existing Summary and on creation we can't leave it empty. See Simple Notes.)

So the issues look like this:

When an issue’s status changes to In Progress we want to assign the issue to the "Executor" value which is looked up in an SAMLOOKUP issue based on the Component. Here's what that rule looks like:

Automation Rule Notes: 

• We check to make sure that the issue being assigned only has one component, as the lookup relies on that.
• We also check that the SAMLOOKUP project only finds one matching issue.
• Once we're sure only there's only one component and one issue returned (using the .size function), we can confidently use the .first function to get the first component and first looked up issue's Executor value. (Reference: Jira smart value - list functions)
• This rule needs to be scoped to multiple projects: the one that needs to do lookups, and the one providing the lookups:
  

Hence we have the additional check to make sure the transition happened in the New Bugs project. (Initially I set this up as a Global Rule but we lose a lot of efficiency doing that.)

• Components are not shared across Jira projects, so I had to manually duplicate components to the SAMLOOKUP project. (Somebody ought to write a script for this.) If your "key" field has a custom context, or the context can be shared with your mapping project, then you don't have to do any extra work!
• Rule for updating Summary:
  
  The problem with this rule is that upon issue creation, Summary cannot be empty, and then the rule overwrites the field. So I've added a field description telling users they should just put a single character in the field before submitting. This is not optimal.
  
  

Complex example - Lookup multiple fields to set multiple fields values

Here's a more complex example.

For this case, we want to catch when any of the fields in blue change. So we need to trigger on a Field Change for the project in question.

But other than that, the logic is very similar to the Simple Example above, and equally as uh, simple.

TODO

• It might be interesting to see if you could write automation that checks for duplicate "keys" and if it finds one, notifies the Project Users. It could be triggered whenever a LOOKUP mapping issue is changed, or maybe on a schedule.
• Even better would be if you could write a validator that prevents you from even submitting a duplicate entry, but I have not had to deal with the Boolean logic of Jira Expressions for some time now. I guess it would have to do some kind of Lookup against existing issues. Can any add-ons actually do that?

Notes and Context

• I previously accomplished the complex example using Project Entity Properties and Automation, which was and is, very very painful. (And I guess kind of fun.) In my defense, the Lookup Issues feature was only added in June 2020 and the ability to access all of the fields wasn't added until November 2021.
• Akeles' Lookup Manager provided the inspiration for this project, but it didn't work for us, because I realized I wanted to trigger lookups when fields change, not just after transitions. Lookup Manager only provides a Workflow Post Function for its lookups.
• In our simple use case, I'm trying to replace functionality provided by the defunct Component Watcher for Jira add-on. (Praise be to the Internet Archive.) We were actually leveraging the custom Component Watchers field that it provides.
  
  Sidenote: if you need to migrate data from Component Watcher, the old documentation mentions a very handy REST API for exporting your watchers. 
• We initially looked at Redmoon Software's Watch It Cloud, since it seemed to have support for setting "Component Watchers", and docs mention a Custom Field. Unfortunately the Custom Field can only be used to set system watchers at issue creation. It does not include the "Watch It Watchers". There is a REST API (available by request) where the "Watch It Watchers" could be accessed, but creating an Automation Rule to query that API and match the Component seemed overly complicated.
• One suggestion was to add the "Lookup Mapping" Issue Type (with its own workflow) to a given project rather than a dedicated project for config to allow you to place the config alongside the data it is impacting. I like this idea, but one concern is permissions. Depending on the project, I wouldn't want regular users being able to change the mappings.

Thanks

Many thanks to @Matt Doar, @Boris Berenberg - Modus Create, @Mikael Sandberg, @Huw Evans, @Dave Liao, @Susan Hauth _Jira Queen_, and @Jack Brickey for their invaluable edits, suggestions, and encouragement.

[Darryl Lee]

Now that I've seen this implemented "for real", I realize that I was wrong about having to duplicate components.

Instead, you could simply use Summary to store Component Name, and your Assign action would use:

 {{lookupIssues.first.summary}}

This avoids the whole problem of having to duplicate Components to your *LOOKUP project or keep them in sync.

Components in Summary. This is the way.

[Ben Fahlgren]

This was a genius idea. I was able to get it working with a Cascading Select List field.

Some places I ran into troubles:

• It has to be a Global rule. Scope = Global
• The "Field value changed" for the Jira Automations doesn't work with a Cascading Select List. You have to use something like "When Issue Updated"
• JQL for a Cascading Select List is a bit tricky. This is what I landed on which worked:

project = "PROJECT" AND "CASCADING-SELECT-LIST-FIELD-NAME" = "{{issue.CASCADING-SELECT-LIST-FIELD-NAME.value}}" AND "CASCADING-SELECT-LIST-FIELD-NAME" = "{{issue.CASCADING-SELECT-LIST-FIELD-NAME.child.value}}"

• If you're trying to use a custom user field, you need to add ".accountId" after the field name like this: `{{lookupIssues.first.AssigneeName.accountId}}` in order for it to be parsed correctly

Here's a screenshot of my automation rule: