How to test each value from a select list?

[mateus_oliveira]

I'm trying to check, when a task is created, how many other tasks I have with the same value for a field. However, this field is a list, and most of them (including the trigger one) have multiple values selected.

What I'm trying to do is write a JQL for Lookup Issues using "(Lookup Issues).Select List ~ (contains) {{#triggerissue.Select List}}, so I can get the number of similar tasks, save it on a smart variable and evaluate the number so I can decide what to do. I saw some iterations like {{#issue.Select List}}, but no matter what I try I just get errors when I try to start the automation mentioning "Failed to close issue.Select List tag". 

What's the correct syntax I need to use so I can get the automation to test each value for this field on the triggerIssue? If this is not a possible, do an automatic iteration on JQL, do you have any workaround?

I'm not on Cloud, we use the Data Center, we're not allowed to install any plugin/add-on, and some settings are also unavailable (like the creation of schemes)

Thanks!

[Bill Sheboy · Accepted solution]

Hi @mateus_oliveira 

To find the issues with one (or more) of the same values selected as the trigger issue, you could use JQL like this:

project = yourProject
 AND yourSelectionField IN (value1, value2, etc.)
 ORDER BY Key ASC

To obtain the values from the trigger issue for the IN expression, use a list iterator over the selected values:

project = yourProject
 AND yourSelectionField IN (
  {{#triggerIssue.yourSelectionField.value}}"{{.}}"{{^last}}, {{/}}{{/}}
 )
 ORDER BY Key ASC

Please verify the correct smart value for your field before trying this expression.  You may do that with this how-to article: https://confluence.atlassian.com/automation/find-the-smart-value-for-a-field-993924665.html

Kind regards,
Bill

[mateus_oliveira]

Hi @Bill Sheboy

Thank you so much for your help, as always! In the automation log, I can see it did iterate through the values of the trigger issue and brought them together. I assume that, despite having multiple values in what looks like a string, it's used by the automation as an array with multiple values, right..?

But now, for some reason, I can't pass the lookup size to the smart variable... Would you know what could be the problem?




I'm under the impression the lookup was successful (since the log passed through it), but maybe it's not saving the number of issues found - or maybe it did not find any issues, when I'm sure they exist..?

You'll see I tried both the field name and also the cf[id], but none of them worked - at least, not in the lookup side... any of them works fine to get the trigger Issue values.

[Bill Sheboy]

Looking at the audit log, the JQL is not correct.  

Is your custom field cf[30902] a multiple-select option field?

If so, please try the IN syntax and not the CONTAINS ~ one:

project = BCC AND cf[30902] IN ( {{#triggerIssue.Target Specialties.value}}"{{.}}"{{^last}}, {{/}}{{/}} )

[mateus_oliveira]

Thank you Bill!
I'm sorry, I haven't realized that the SQL needed adjustments in addition to the Smart Values iteration. But now, that worked flawlessly!

This took care of my most essential needs. Before finishing, would you know if it's possible to get the info of WHAT "Target Specialties" value had the most occurrences, or what had more than X occurrences?? I guess this would not be possible to do on the same lookup issues step, but is there any alternative? This would just be a plus, though.

Anyway, once again, thanks! You saved our team operation.

[Bill Sheboy]

Getting the maximum occurrences of specific values in a multiple-select field, or the count over a threshold, would be challenging with an automation rule.

With the current features of rules, I believe this could be done by saving the counts for each possible value and then using a series of comparisons.  There may be other approaches using entity properties that are repeatedly updated, but that sounds like a brittle solution to me.

[mateus_oliveira]

I would ask how to achieve the first option, saving the counts for each possible value and then comparing, but I believe the value would have to be stored in a created variable, right? And thus, that means the comparison/testing could not be dynamic anymore, right?

Considering that the number of values selected is completely different in every issue created, in the best scenario the variables to save the counts would also have to be created dynamically, which I don't think is possible without any plugins... so, to make this work, I believe I would have to create an IF/Else block for every possible value in the list, selected or not in any issue... That would require too much work, specially to maintain.

The solution we have right now solves the most urgent problem, so that'll be enough for now.

Once again, thank you Bill!

[Adolfo Casari]

@mateus_oliveira  Should be cf[30902] IN ("a", "b", "c" ) , not ~ "a", "b", "c"