I want an automated weekly table of open tickets for each field dropdown selection

Question

I need help with an elegant solution for creating a table in which each value in the first row consists of a custom field's dropdown list and the second row consists of the amount of open tickets that full under each custom field's dropdown value. I also want it to be easy to add new dropdown values.

My first idea was to create a variable "SiteList" that contains each Sitethen a branch rule for each Site 
that will lookup issues for each site and then a lookuptable would've stored each lookupissue 
such that i would be able to reference (the size of) each lookupissue

I would like the table to be something like:

but cannot use smart values in lookuptable name

Bill Sheboy · Answer

Hi @Makai Baker

Lookup tables cannot be dynamically created as you tried.

And even if tables could, the advanced branching would not help as it would create a new table with each loop item, and then the table vanishes at the end.  (Branches which could be on more than one thing are executed in parallel and asynchronously, in separate processes.  And so the branch may not even finish before the next step after the branch in the rule starts.)

If you know the Site values when the rule is created / updated, you could use smart value, list filtering and the lookup table, entries function to iterate the values:

* https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588
* https://community.atlassian.com/t5/Automation-articles/Update-Create-lookup-table-action-improvements/ba-p/2427798

For example, create the lookup table for the current values, using the hardcoded site name as the key and in a filter to count the issues with a math expression:

* action: lookup issues to gather the data
* action: create lookup table, perhaps named tblSiteCounts* row* key: Site1
* value:

{{#=}}0{{#lookupIssues}}{{#if(equals("fat site[dropdown]".value,"Site1"))}}+1{{/}}{{/}}{{/}}

* repeating to add all rows for the sites...
* action: some action* when you need to iterate over the values in table, use

{{#tblSiteCounts.entries}}
* {{key}}: {{value}}
{{/}}

To add / change sites later, just update the table keys and value filters.

Kind regards,
Bill

Trudy P Claspill · Answer

Hello @Makai Baker

Have you considered using a Dashboard instead, with a Two Dimensional gadget? The X axis could be your dropdown field and the Y axis could be Status. If you want to see only the issues that are In Progress, you can create a filter to select all the In Progress issues only, and base the gadget on the filter.

The gadget will automatically split out each of the values in the dropdown field.

The output would not be sent to you in any automated way, though.