Automation concepts – Randomly Selecting N Work Items

[#1]

Estimated time to read: 15 minutes

TL; DR:

Let's find a random sample of work items, such as for an internal audit, from a lookup result set. There are many internet sources for spreadsheet tools to randomly sample. This article is not trying to replace those. Instead, we have an example of how a problem may be split into pieces and solved one chunk at a time.

As a disclaimer we will use complicated, list operations, building upon techniques from two earlier articles on dynamic list searching and using created variables as numbers. So, "enjoy" or "beware": take your pick ;^)

---

What is the problem, and why is it difficult to solve?

What if my team leader wants to review completed work from the last few months...with a random sampling of 10 items:

GIVEN a set of N work items completed in the last few months
WHEN it is the first day of the quarter
THEN provide a review list of 10 work items randomly selected from the completed ones
SO THAT internal audit can compare the work to prior quarters

With the spreadsheet approach I noted earlier, one could identify and export the work items, and use a random selection without replacement approach. That is, we want 10 distinct work items from the set without duplicates.

How might we do that with an automation rule; what do we need?

1. A rule could use a Scheduled trigger for the quarterly cycle
2. The Lookup Work items action could gather the work items (assuming there are 100 or fewer)
3. The RANDOM() math function provides a floating-point number between 0 and 1, but we want an integer 1 to 10. We could multiply values to get that range.
4. The RANDOM() function will not help with random sampling without replacement from a list, and there no list functions to do that either
5. We need a single sample list when done. Rules can branch over lists of things; however, such branches run in parallel and make it difficult to create a single list of work items. Instead, we will need to use list iteration.
6. Once we have a review list, it could be sent as a JQL expression in an email or Slack message to the requestor

We see some of our needs are directly supported with rule components, while others require us to get creative. Let's outline the approach:

Rules do not have a random sampling without replacement feature. We mitigate that by creating a large pool of random numbers within the range of our lookup results size (i.e., the count), removing any duplicates, and grabbing the first 10 of them for our sample. Those numbers represent an index into the lookup results. To get the actual work items' keys, we build a dynamic regular expression to extract the "winners". Finally, we compose that into a JQL expression for use by the requestor.

Walking through the rule

Using the rule for context, we explain how the pieces work, particularly the Create Variable actions. First, let's see the entire rule.

Other than the trigger and Lookup Work Items action at the beginning, the steps are essentially a series of Create Variable actions. These incrementally use each prior result to get to the answer.

Let's step through our earlier "needs" list to meet the goal.

Select source work items from which to draw the sample

We create and test the JQL for the source work items. My example rule only looks for completed items for a single project:

project = TOOLS AND statusCategory="Done" ORDER BY Key ASC

You could add additional criteria, such as the work items completed within the last quarter using JQL functions, such as startOfMonth() and endOfMonth() with adjustment parameters.

Remember this rule action can return up to 100 work items. Please see "Exercises for the reader" at the end of this article when you have more than 100 items.

Create a source list with index values

We gather information from the lookup results, keeping only the work item key and the position in the list: the index. That is stored in a variable for the final filtering later.

• action: Create Variable
  • variable name: varLookupResultIndexAndKey
  • smart value: 

{{#debug}}{{#lookupIssues}}{{index}}:{{key}}{{^last}},{{/}}{{/}}{{/}}

How does that work...

• We iterate over the lookup results
• Including the list index and work item key, with a colon delimiting them. The {{index}} smart value only exists when iterating a list, with a value of 0 up to N-1, where N is the size of the list. We are saving it to help with grabbing the "winners" later using a regular expression.
• Each above record has a comma delimiter to help split back into a list
• And finally, we wrap it all in a {{#debug}} ... {{/}} function so everything gets written to the audit log without the need to add another rule step

This produces a value such as this:

0:TOOLS-3,1:TOOLS-7,2:TOOLS-8,3:TOOLS-9,4:TOOLS-11...

Generate a large pool of random numbers

 We will need two variables to generate the large pool of numbers...

• action: Create Variable
  • variable name: varNull
  • smart value:

{{#debug}}{{null}}{{/}}

Variables cannot be created empty in the rule editor, but we need an empty one as a starting point for our next expression. There is no smart value {{null}} and when an unknown smart value is used, that evaluates to an actual null value. And that then creates an empty string: a string with a length of 0. We covered this technique in the earlier article on using variables as numbers.

• action: Create Variable
  • variable name: varOneThousandRandomNumbers
  • smart value:

{{#debug}}{{#varNull.rightPad(2000, "X,").substringBeforeLast(",").replace("X", lookupIssues.size.format("###")).split(",")}}{{#=}}ROUND(({{.}}-1)*RANDOM(),0){{/}}{{^last}},{{/}}{{/}}{{/}}

How does that work...

• Adding to our empty varNull string, we rightPad() a series of X followed by a comma for 2000 characters; that will produce 1000 "X," entries
• We drop off the last comma
• And then immediately replace the "X" character with the number of items in the lookup results.  Assuming there were 50 work items, that will look like: 50,50,50,50...
• Now we split() into a list and iterate the values
• Using that string of "50" values, we subtract 1 and multiply it by a random number, producing a value between 0 and 49, including fractional values in between
• We clean that up with the ROUND() function
• Finally adding a comma delimiter between records

Whew! Yes, that is a complicated expression, and we used the same technique in the article on dynamic list searching. It produces a value such as this:

12,9,7,2,2,8,3,18,0,5,1,17,11,18,4,22,9,17,18,5,3,21,9,15,1,3,20,2,20,0,11...

QUESTION: Why generate 1000 random numbers to sample 10 work items?

Although my testing indicates the built-in RANDOM() function produces a good-enough distribution, if your lookup result contains 100 work items, I would not recommend generating only 100 random numbers. The cost is small to generate a larger pool, and assuming the distribution is valid, you could easily reduce that range to a few hundred. Personally, I erred on the side of avoiding the possibility of too many duplicates, which would reduce the sample size.

Remove duplicate numbers from the pool

The sample we created has no limitations, and as we see above, it contains duplicate values. We need to eliminate those with the distinct function, and then get just the first 10 distinct values.

And so, we run into the next challenge: although the {{index}} smart value would seem to help accomplish this, it cannot be used inside of a list filtering, conditional expression. We will defer this: check if a number is in the sample, but do no filtering yet.

• action: Create Variable
  • variable name: varDistinctRandomNumbersWithIndexTest
  • smart value: 

{{#debug}}{{#varOneThousandRandomNumbers.split(",").distinct}}{{index.lt(10)}}:{{.}}{{^last}},{{/}}{{/}}{{/}}

How does that work...

• We split() our last variable back into a list
• Allowing us to use the distinct function to remove duplicates
• We then test if index is less than 10, and within the sample index of 0-9, which will add either "true" or "false" to the result
• And we include our random number, with a colon delimiter between the values
• Finally adding a comma delimiter between records

That produces a value which looks like this:

true:12,true:9,true:7,true:2,true:8,true:3,true:18,true:0,true:5,true:1,false:17,false:11...

Gather the sample index values

 Then with one more variable, we get the actual list of indices in the sample:

• action: Create Variable
  • variable name: varSelectedRandomList
  • smart value:

{{#debug}}{{#varDistinctRandomNumbersWithIndexTest.split(",")}}{{#if(equals(substringBefore(":"),"true"))}}{{substringAfter(":")}},{{/}}{{/}}{{/}}

How does that work...

• Once again, we split() to iterate
• Now performing a list filter on that earlier result for index values less than 10 by extracting it from the left of the colon delimiter
• And finally extracting the sample index from the right side after the colon, we produce a list like this:

12,9,7,2,8,3,18,0,5,1,

Build a regular expression to filter for the work item keys

 We have our list of indices, and we need to filter for the work item keys. We do that with a regular expression and the match() function.

• action: Create Variable
  • variable name: varRegEx
  • smart value:

{{#debug}}(^({{varSelectedRandomList.substringBeforeLast(",").split(",").join("|")}}):.*){{/}}

How does that work...

• Splitting our list of sample numbers, we re-join them with a pipe | delimiter
• Wrapping that with some additional regex syntax, we end up with an expression that looks like this:

(^(12|9|7|2|8|3|18|0|5|1):.*)

This will find the 12th, 9th, 7th...and so on records and the work item keys.

Filter keys to build a JQL expression

Applying that regular expression to our very first variable from the Lookup Work Items result, we get what we wanted:

JQL for Random Sample: 
key IN ( 
{{varLookupResultIndexAndKey.split(",").match(varRegEx).substringAfter(":").join(", ")}}
 )

How does that work...

• We use one final split() into a list
• Using the match() function with our regular expression to filter the list
• Extract the work item keys
• And join() them with commas to create a well-formed JQL expression, such as this:

JQL for Random Sample: key IN ( TOOLS-3, TOOLS-7, TOOLS-8, TOOLS-9, TOOLS-16, TOOLS-50, TOOLS-51, TOOLS-86, TOOLS-155, TOOLS-217 )

The example rule only writes the JQL to the audit log. Your own sampling rule could instead send it as an email, Slack message, or perform additional processing on the sample work items.

Checking an example rule execution

Our rule is complete, and we can test it immediately (as it has a scheduled trigger). Here is a sample of the audit log with the results. I added the names of the Created Variables so you can see what the intermediate results look like...all the way up to the final JQL expression to return our sample. And, I expanded each debug section to show the details; by default, those are not visible in the log.

 

Wow, are you still reading? Awesome! If so, here are some additional things to try as exercises.

Exercises / discussion topics for the reader's team

Would you really ever use this rule, or only review it to learn techniques to help with other rules?

Hint: Only you know the answer to that one :^)

How could I find more than 10 work items in the sample? 

Hint: You may change one single number for the variable varDistinctRandomNumbersWithIndexTest to impact the sample size. Can you find it?

How could I make the sample size (e.g., 10 items) configurable, such as with a manual trigger with user inputs?

Hint: Consider the method with variables to dynamically generate the list of random numbers using rightPad() as a text string. Now consider the filtering done with a regular expression and match(). What if the configurable size of the sample was used to generate a regular expression with rightPad(), and that was then later used to filter instead of using the technique with true / false in varDistinctRandomNumbersWithIndexTest?

How could I sample from more than 100 work items?

Hint: This rule uses the work item keys and the Lookup Work Items action. What if instead the work item ID values were used with the new REST API endpoint for enhanced search to get up to 5000 ID values? How would the rule change?

Wrapping it up

Yes, this was a very long, and very complicated rule scenario. I hope you found something of value to help your rule problem solving, even if you did not actually need to sample anything. I welcome any feedback you have on the scenario and article.  And as always...

Happy rule writing!