Automation rule should send email list of unique assignees based on lookup and If Compare Two Values

[David Alexander]

Hi

I want to create an automation rule which will result in an email being sent which lists a unique list of Assignees, the Assignees are pulled from a lookupIssues and further narrowed down in a If. For example,

1. Trigger: Sprint Complete

2. lookupIssues: JQL sprint = {{sprint.id}} AND project = TEST AND issuetype = Task AND "Story Points" is not EMPTY

At this point I imagine I need to create a variable like, {{uniqueAssignee}} which equates to {{lookupIssues.assignee.distinct}}

Then I want to do another lookupIssues, the same JQL as before, but I want to add Status = Done AND assignee = {{uniqueAssignee}}

Thereafter I want to compare the sum of story points for the last lookupIssues, i.e. {{lookupIssues.Story Points.sum}} is less than X

Then I want to pull that information into an email.

The email ideally looks like;

"

uniqueAssignee1

uniqueAssignee2

"

I have tried several variations of this rule and cannot find the right sequence of steps & email format to achieve this result. Does anyone have any recommendations? 

[Bill Sheboy · Accepted solution]

Hi @David Alexander -- Welcome to the Atlassian Community!

What you are trying seems possible, but I am not quite following the scenario, so some follow-up questions are:

• What problem are you trying to solve?
• What is the purpose of that story point comparison?
• What would the completed layout of your email look like?  Is this one mail, or one per unique assignee?

If you already have a rule, posting an image of that with the audit log details may help.  Thanks!

Kind regards,
Bill

[David Alexander]

Hi Bill, thanks for your reply and welcoming message,

To your questions,
1 & 2. Our team has an unorthodox way of identifying velocity, without going too much into it, we have individual (i.e. per assignee) thresholds we want to measure, hence "If Story Points.Sum less than X" from the last lookupIssues containing {{uniqueAssignee}}

We want to be able to identify if an individual threshold has not been past. 

This seems like we are trying to look at individual people's productivity, but without going too much into the weeds, that is not the case, we are looking at SP per account (assignee) as this suits our current setup on our project.

3. This is the trickiest part for me so far. 1 Email with a list of unique assignee from the list.

e.g.

"Subject: Threshold Not Past"
"Body: 
assignee1.displayname

assignee2.displayname

" 

Ideally the body also includes the account's story point.sum for the last JQL (sprint = {{sprint.id}} AND project = TEST AND issuetype = Task AND "Story Points" is not EMPTY AND status = Done AND assignee = {{uniqueAssignee}}).

I think I have lost the draft of the rule that was nearly working, but I basically tried with "For Each" branch at first and realized this would not let me aggregate all the results into a single email, but I did succeed in getting the right names sent. 

Then I tried a "Then lookup" -> "Create variable uniqueassignee" -> "Then lookup" (as above) -> if compare lookupissues.story points.sum less than 18 -> send email 
but I could never get the email to send the right information. It would always send me a * or * * depending on my variation. 

[Bill Sheboy]

Thanks for that information, David.  And thanks for clarifying about the individual measurement part, so I will not comment on that: as the saying goes, "not my circus; not my monkeys"  :^)

Gathering the list of distinct assignees is the easy part:

{{#lookupIssues.assignee.distinct}}
* {{displayName}}
{{/}}

The other part for checking the story point sums, for specific assignees, AND sending that in one email is more difficult.  Smart value, list filtering has some limitations that will prevent doing what you ask.

If there is a defined list of assignees, a couple of possible work-arounds would be:

• Use a lookup table, with the assignee as the key and the sum of their story points as the value.  (This part can be done with a math expression and filtering, so no need to repeat the lookups for each user / account.)  Then iterate and filter that on any values less than your threshold of 18.
• Do the same as above without the lookup table, and do this inline in your email.

An example of this for one specific user would be:

{{#=}}0{{#lookupIssues}}{{#if(equals(assignee.displayName,"Bill"))}} + {{Story points|0}}{{/}}{{/}}{{/}}

How this works, inside to outside:

1. iterate over the looked up issues
2. filtering on the specific assignee
3. and adding the issue story points, with a default value of 0
4. wrapping all of that into a math expression, with a leading 0 for a default if no issues are found

[David Alexander]

Hi Bill,

Thanks! 

I tried both ways but cannot understand how to
1) Iterate and filter on the lookup table. If I use an If block here, it will just look at one {{uniqueAssignee}} and decide to move onto the email, so that's not doing it. In my lookup, I'm using {{uniqueAssignee}} as key and {{lookupIssues.Story Points.sum}} as the value, which comes after my second lookup (sprint = {{sprint.id}} AND project = TST AND issuetype = Task AND "Story Points" is not EMPTY AND status = Done AND assignee in ({{uniqueAssignee}})) 
2) In the second method, it does successfully show the individual assignee's story point sum which is awesome, that feels very close to what I'm looking for, but how can I wrap this inline with if lessthan? I am confused as to how to ask it to only display if the Story Points|0 reaches 18. 

[Bill Sheboy]

My apologies as after some testing I realized neither of those ways will work:

The Lookup Table entries cannot be filtered with a math expression (e.g., value is greater than 10).  Only a literal expression can be matched for (e.g., value=10).

And the inline method can be summed or filtered, but not both.

So the remaining methods (assuming a fixed number of assignees):

• use the lookup table to sum by assignee, and manually filter in the email, like this:

{{#if(myTable.get("Bill").asNumber.gte(18))}}user: "Bill" : {{myTable.get("Bill")}} points{{/}}
{{#if(myTable.get("David").asNumber.gte(18))}}user: "David" : {{myTable.get("David")}} points{{/}}

• use created variables and manually filter, as with the table method

With this level of complexity, you may want to also investigate reporting addons from the marketplace.  

[David Alexander]

I couldn't get this method to work either, but likely because I can't match up the table assignee to a smart variable or my created variable in my table
I guess as you said the best course of action is reporting addons or just using this inline method; 

Bill: {{#=}}0{{#lookupIssues}}{{#if(equals(assignee.displayName,"Bill"))}} + {{Story points|0}}{{/}}{{/}}{{/}}

And manually checking the email 

I'll go for this for now! Thanks a lot Bill :) 

[Bill Sheboy]

Sounds good.  And sorry I could not help more, although I did learn some things about Lookup Table limitations by trying  :^)