New Automation action - Create lookup table

[#1]

Hey all!

Simeon here, one of the developers on Automation. Just writing a quick heads up that a new Swiss army knife style component is coming your way, the "Create Lookup table" action.

This was my Innovation week project that I've been dogfooding for while and am now ready to make available. It allows you create pairs of keys and their associated values that you can reference later on in other actions. I personally use it to map Jira project keys to Slack channels but you can do so much with it. The classic example that I've been using to explain it is shown below:

Now, you then use this in smart values like so:

{{departments.get("HR")}}

and the result you get out is:

Human Resources

This should hopefully go a long way towards reducing the complexity and number of If / else if blocks you need. And before you ask, yes, you can use smart values to create your values. They evaluated when the "Create lookup table" action runs and creates the table (not when you call .get). And, you can have the smart value store an object that you can access with dot notation (I might have nudged @Sam Harding to do the same thing for the Create Variable action, which should make lots of people happy) - it is pretty cool!

It does have one limitation; that the value you provide to .get must be a text value; numbers or anything else won't return a value even if they match what is in the key.

Finally a shout out to the eagled eye @Ryan Schamp who noticed this in our docs when we published a little early :D

It is rolling out over the next few days, so happy Automating folks!

[Dave Mathijs]

@Simmo Will this lookup table be stored in each automation rule individually?

[Simmo]

Hey @Dave Mathijs,

At the moment, yes. It is on a per rule basis.  

[Julia Foden]

Hi @Simmo thanks for this. I had noticed it on Friday and couldn't believe this new thing had snuck in without me seeing any article about it :) 

I look forward to trying it out. I'm a bit disappointed that it's limited to 20 rows - the use case that I have in mind would require a lot more.

[Simmo]

Hi @Julia Foden,

What is the use case you have in mind that exceeds 20?

[Julia Foden]

Hi @Simmo 

In my Access Request process, the user selects what they want access to from a list of over 100. The ticket then goes to their manager to approve and then automation fills in a group field with the group that provides access to the thing they selected. Only members of the group in the group field can do the next transition. 

At the moment I am doing that with if/else. There are some groups who provide access to multiple things but then there are some that provide access to just one or two. Until recently I was able to fit all of these if/elses in one rule but now I have had to split it into two.

So I would love to be able to use a table with up to 200 rows. 

Thanks!

[Doug Levitt]

@Simmo 

This looks like a very useful feature.  Some enhancements I would like to see:

a) Supporting more > 20 entries in a list

b) Allowing a key to be associated with multiple values

c) Allowing the lookup table to be shared across multiple projects

I already -- in effect  -- simulate the Lookup behavior by maintaining an "Administrative Project", which stores meta-data (for use in downstream automations).

We have ~ 30 Agile Teams, and I store information about each Team (Average Cycle Time, Average Velocity, Product Owner, etc.) that then gets references in automations.

As I think about it, the "killer feature" would be to be able to import the Lookup Table from a Jira Project, mapping keys (Admin Project, Custom Variable #1) to a set of values (Admin Project, Custom Var #2; Admin Project, Custom Var #3).  It would significantly simplify the "code".

Thanks,

Doug

[Simmo]

Hey @Doug Levitt 

What is your use case for more than 20 entries?

We did deliberately limited it due to the fact that it is allowing storage of objects, as the complexity and amount of data could spiral quickly.

@Julia Foden, you mentioned 200, it sounds like you wouldn't be storing much in there? 

[Julia Foden]

Hi @Simmo 

In my case the keys would be values from a single-select list and the values would be group names or group IDs.

[Doug Levitt]

@Simmo 

When I initially read about this feature, my first thought was to create a Lookup Table entry name-value pair for each of our Scrum Teams.  Currently, we have ~ 30 Teams.  I don't see that # exceeding 40 anytime soon.

Thanks,

Doug

[Hana Kučerová]

Thank you!

[Kelley R]

@Simmo I'm currently working on a multiproject automation that creates cloned issues across projects to allow devs to work on service desk tickets on their home software project boards.
When: Issue created (in service desk)
If Specific Issue Type
Creates a issue in specific project, adds to current sprint, Links to trigger issue as Clones

That parts done and stable, although I can definitely see the utility of the lookup table in the If selection once I'm past proof of concept and into full build out. 

What I'm struggling with now is that I want the status's to mirror back and forth, but the projects are on different workflows. Would this be something the lookup table would be able to resolve, for example could I map as below and if so, how?
Service Desk Project : Cloud Project

To do, Team Lead Approval Required, : To Do

Waiting for support: Ticket Submitted

Up Next : Up Next

Waiting for Customer, Blocked: Blocked

Investigating, In Progress : In Progress

Review/QA : Review/QA

Resolved : Done

Canceled : Canceled

Thank you for your time!

[Nicolas Philip]

am also unable to find a match using a smart value on a custom Jira field...

`{{table.get(issue.Custom Name)}}`

Great new feature by the way!! I was not looking forward to build the switch logic by hand.

Also, it might worth clarifying in your documentation that the returned values are strings only and one can use the asNumber function to convert the values as numbers if needed.

[Bill Sheboy]

Hi @Nicolas Philip 

Would you please clarify what you meant by this:

Also, it might worth clarifying in your documentation that the returned values are strings only and one can use the asNumber function to convert the values as numbers if needed.

My testing indicated that lookup table values contain objects, and so entire issues (or parts of them) can be stored, and accessed later by key, and then the object structure.  For example, storing {{issue}} in a table allows it to be accessed as an issue later.

Kind regards,
Bill

[Nicolas Philip]

Hi @Bill Sheboy

Indeed, I was able to validate that the value returned by a lookup table can be an object, a string or a number. Thanks!

I found a typo in my math expression and was able to directly apply the value returned by my lookup table:

{{#=}}{{table.get("No")}} + 20{{/}}

Regarding the issue about using custom fields, I ended up putting the value in a variable first and then do the lookup on this variable:

One action putting the value of my custom field in a variable:

{{variable_name}}  ==  {{issue.is this real?}}

Another action to get the value from the mapping table:

{{table.get(variable_name)}}

[Bill Sheboy]

Thanks, @Nicolas Philip 

When I originally tested lookup tables, using a created variable as a parameter in a table.get() it did not work, and it rechecked and it does work now.

What is the specific variable name you are using for the created variable?

And...if "is this real?" is in fact your issue field name, I wonder if that could be causing issues.  Have you tried using the custom field id (or confirming the smart value name) to check this?

[Eden Eliyahu]

amazing feature!

few questions:

1- When it will be possible to add more than 20 rows?

2- I have multiple rows that need to get the same value. are you going to support "in" function? Now I added 6 rows with the same value, but only the key is different.  

thanks

[Christopher Little-Savage]

I'm trying to use this for the described use-case of mapping to slack IDs, except I'm mapping assignee email addresses to slack user IDs so that I can @ them in reminders. I've come across what I think is a bug: the resolution doesn't work correctly inside a list. Using this template (for a "Send Slack message" action):

{{#lookupIssues}}
Inside: {{usernames.get("demo")}}
{{/}}

Outside: {{usernames.get("demo")}}

 Results in the following message being sent:

Inside:
Inside:
Inside:
Inside:

Outside: FOO

Where the lookup table contains `{demo=FOO}`.

On a more positive note, I've enjoyed using this as a bit of a hack to be able to store objects in a variable. My rule looks like:

1. Lookup Issues

2. Create lookup table with a single entry ("values" = {{ lookupIssues }})

3. Action where I can retrieve the full objects using `myLookupTable.get("values")`. 

This is particularly useful when I want to repeat steps 1 and 2 for multiple issue types, since I can store them all in differently-named lookupTables and re-combine them all in a single message at the end.  

[Bill Sheboy]

Hi @Christopher Little-Savage -- Welcome to the Atlassian Community!

I believe you are seeing the problem/behavior that once inside a list iterator, scoping seems reduced and so other objects are not visible for use in functions, math, filtering operations, etc.

And I agree that feature of lookup tables preserving objects in values is quite helpful.

Kind regards,
Bill

[Doug Levitt]

Hi @Simmo 

I am experimenting with the Lookup Table.  I was wondering if there is a way to transform the Lookup Table into an actual list, so I can do standard list operations on it (like Advanced Branching) ?

I was thinking of creating a list of Name/Values Pairs:

Squad1:Blue

Squad2:Green

Squad3:Yellow

And then iterating through the list, performing operations on my Squads (named Blue, Green, Yellow).

I realize I can do similar, by loading the above values in a variable, but using the Lookup Table would make "the code" easier to read/support.

Thanks,

Doug 

[Ingo Wenke]

Same issue here.

We need to mask issue status of related issues and update a custom field with a complete set of those related issues.

Findings

• lookuptable is limited by 20, our underlying workflow with >20 status can't be mapped 
• no "wildcard/nomap", unmapped status will be empty instead of a possibility to keep the original value
• the status field has to be wrapped with an variable, mylookuptable.get(issue.status) does not work, but with custom variable instead, everything is fine ( {{statusmapping.get(realstatus)}})
• lookuptable does not work within an lookup issues loop

For a limited scope, it's a great feature and we're looking forward.

[Simmo]

@Doug Levitt , an interesting suggestion. I want to fix the input has to be a string and not a number issue when I can, so i'll see what i can do with your request as well. This is my personal project so I don't get a lot of time to work on it.

@Ingo Wenke , issue.status is actually passing in the status object (eg { id: ... name: ... }), which is why it doesn't work. You need to tell it which part of the status to use.

You're also suggesting a getOrDefault(key, defaultValue) style option?

Can you expand on "lookuptable does not work within an lookup issues loop"

[Ingo Wenke]

@Simmo 

I've tried to add the interesting screenshots, but it's not possible due to limitation of this forum (Your response must be fewer than 100000 characters.)

The automation looks like 

1. specify lookup table with a mapping of status ID to new name
2. make a lookupissues
3. Update the description with following statement:

|| Key || my Status || Jira Status || Jira Status ID ||

{{#lookupIssues}}

| {{key}} | {{statusMapping.get(status.id)}} | {{status.name}} | {{status.id}} |

{{/}}

Result is, that {{statusMapping.get(status.id)}} is always empty.

[Bill Sheboy]

That seems to match "as implemented" as the keys passed to get() for lookup tables must be text (as opposed to the get function for lists which are numeric).

[Ingo Wenke]

ok, next try:

|| Key || my Status || Jira Status || Jira Status ID ||
{{#lookupIssues}}
| {{key}} | {{statusMapping.get(status.name)}} | {{status.name}} | {{status.id}} |
{{/}}

Don't work, too

[Bill Sheboy]

Sorry, I should have clarified earlier...I believe with the expression you showed originally you were encountering two problems:

• get takes text parameters for lookup tables, and
• the scoping problem in list iterators, and so it cannot "see" the table.  You could verify this by hard-coding a status id (or name) as text in the get() call: if it works, it is not the scoping problem.

What do you think, @Simmo ?