Smart Value Filtering - Comparing Two Lists

[Chris Luck]

Hey there - hoping someone can help me out with this:

As part of a reporting automation I'm making, I'm trying to retrieve a list of users that have issues assigned to them but don't have things that are in progress.

So far I've approached this in the following way:

• Lookup Issues for all issues of a particular type, retrieve list of users with {{lookupIssues.assignee.displayName}}
• Add the resulting list of user names to a smart value - {{openAssignees}}
• Lookup Issues for all issues of a particular type with In Progress status, retrieve list of users with {{lookupIssues.assignee.displayName}}
• Add that resulting list to another smart value - {{inprogressAssignees}}

Now where I'm stumped is how to compare the two lists and return a resulting list where the list contained within {{inprogressAssignees}} has been filtered out. Does the new smart value filtering announced here allow for this or is there another way I can achieve this? https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

[Bill Sheboy · Accepted solution]

Hi @Chris Luck -- Welcome to the Atlassian Community!

That is an interesting use case...

To save you some time, my experiments with smart value list filtering show that other lists cannot be used inside the iterator for filtering...and neither can created variables (as a work-around to the first problem).

My suggestion is to try to create a single JQL filter to get your results, and then use the distinct operator on the Lookup Issues list to reduce to one entry per user (if that is desired).

Kind regards,
Bill

[Chris Luck]

Thanks @Bill Sheboy for the warm welcome and the suggestion! Definitely a bit of a shame that it's not possible to use other lists for filtering but I was half expecting that as an answer so it's not the biggest concern. 

I'm not quite sure if this use-case can be done with a single JQL filter though since I've only been able to get a list of users manually by filtering another filter to get those with work assigned but not with work in progress. If you have any ideas on how to do that, it would be much appreciated!

[Bill Sheboy]

Hi, Chris!

It appears you want to identify: people who have nothing in-progress AND who have non-done work assigned to them. Is that correct?

If so, how often do you need to perform this check, and what will you do with the results?

1. Need this often to provide reporting: investigate marketplace addons for JQL to perform this with a single, nested query
2. Need this infrequently: run your two queries separately and use a spreadsheet export to answer the question...or just visually compare the results
3. Need this often, don't want to buy an addon, and can deal with a "noisy" report: you can do this with a slow automation rule, but not with smart value list filtering.  This rule is slow due to the branching and repeated lookup issues.

For option #3, you could use lookup issues and advanced branching to then notify the people...or send yourself n-emails (one for each person).  Here is an example rule to do this:

• trigger: scheduled with no JQL
• action: lookup issues with JQL to find the not-done issues with assigned people

project = myProjectName
 AND assignee IS NOT EMPTY
 AND statusCategory IN ("To Do", "In Progress")

• advanced branch: on the distinct assignees from that lookup
  • smart value: {{lookupIssues.assignee.accountId.distinct}}
  • variable name: varAssignee
• action: lookup issues with JQL for the specific assignee and in-progress work

assignee = {{varAssignee}} AND statusCategory = "In Progress"

• advanced compare condition: to find people with no in-progress work
  • first value: {{lookupIssues.size|0}}
  • condition: equals
  • second value: 0
• action: lookup issues with JQL one last time to get the person's information

assignee = {{varAssignee}}

• action: log, email, or mention them one-time using data you find in {{lookupIssues.first.assignee}}

Kind regards,
Bill

[Chris Luck]

@Bill Sheboy Apologies for not getting back to you sooner and thank you so much for taking the time to write out those potential solutions. I wound up going with something similar to Option 3 since the requirements were for a daily report and thus frequent enough to merit using a slow automation.

[Lauma Cīrule]

Hi @Chris Luck!

If you would be interested in a third-party app for reporting, you can check out eazyBI.

Here is a sample report showing issues by assignee and status: https://eazybi.com/accounts/1000/cubes/Issues/reports/80616-unresolved-issues-by-assignee-and-status.

Let me know if there is anything else I can assist you with!
Lauma / support@eazybi.com 

[Chris Luck]

Thanks @Lauma Cīrule! I'll take a look but I don't think this fits our use case because I'm specifically looking for a way to do this within automation as a means to broadcast things to the team. However, we might get some utility from your app for other purposes, so I will take a look for sure :)  

[Lauma Cīrule]

@Chris Luck, 

I hope I was helpful! :)

To add - the data to eazyBI is synchronized on a regular schedule. Once you build the report, it will automatically show the latest data. You can share it in various ways - to Jira dashboards, as an iframe to any other page, subscribe to send an export to e-mail or wallboard screens in your office. See https://docs.eazybi.com/eazybi/publish-reports.

Lauma / support@eazybi.com