Update: Create lookup table action improvements

[#1]

Hey all,

Just an update to let you know that my favourite Swiss army knife style component, aka the "Create Lookup table" action, is having some improvements shipped this week, of which there are three changes coming.

Changes to .get

Firstly, the .get method will try really hard to map your key to the right value. As an example:

The first row is "5" mapping to "hello". You would need to provide a string value of "5". If you used a number field or the results of a math operation that wasn't then turned into a string, you wouldn't get a result. As an example, this would not work:

{{example.get(5)}}

But this would:

{{example.get("5")}}

This has now been fixed! Yay!

But it has also been supercharged as well. The .get method can now sometimes figure out when you're using a value that cannot work, such as issue or assignee:

{{example.get(issue)}}
{{example.get(issue.assignee)}}

Neither of these would work as you cannot make an issue object a key nor a user object. Instead, the .get method will use the display value instead. What do I mean by that? Well, if you did a Log Action and logged out {{issue}} you'd see the issue key, eg RAGE-234, or in the case of {{issue.assignee}} you'd see the account ID. The .get method will now try that instead.

New method .size

There is a new method called .size which will tell you how many rows are in the table:

{{example.size}}

You can get all the rows as a list

A few people had asked to be able to get the rows in the table as a list. You can now do that with the .entries method:

{{exampple.entries}}

which returns a list of pairs. For the above example you would get:

key=5,value=hello
key=RAGE-234, value=bananas
key=RAGE-7996, value=First
key=RAGE-7997, value=Second

If you wanted to show all the keys you could do a log action with the following smart value:

{{example.entries.key}}

Oh and about limits

We've upped the limit on the number of rows in the table to 200. So the table will now be paginated at 20 rows per page!

Thats all folks

These changes will start rolling out over the next week. Big thanks to @Julia Foden and @Bill Sheboy for their help with the changes!

Happy automating folks!

[Matthias Gaiser _K15t_]

Thank you, @Simmo.

I really like lookup tables to keep my automations tidy so that I don't need multiple variable declarations in the beginning.

[Eric Rosche]

This, in my opinion, fixes/adds all the major things still missing to the lookup tables (size, entry looping, etc.)

I really appreciate the time spent to fix/add this! 

Thank you 😃

[Julia Foden]

Thank you @Simmo this is great.

Can I put in a request for the next time you have time to spend on this - when I need to add a new row I would like to be able to insert it into the existing table rather than adding at the end so that I can keep the rows in alphabetical order. If possible this would be nice to have :) 

[Sami Ahmed Shaik]

@Simmo ,

Thanks for an update. Is this available for “Automation for Jira” server/DC? 

[Erki Tammik]

This looks useful; however, I haven't tried it myself yet.
Is it possible to use that functionality to set chosen assignees for certain customer request types?

For example, when Form 1 is submitted on the portal, it needs to be assigned to a certain agent. Using a table format instead of many if-else blocks would make rules much tidier. First column: customer request type and second column: to whom to assign.

I'm trying to find use cases for that. It would be nice to see an instructional video from Atlassian demonstrating some use cases and explaining how to set it up.

[John Funk]

Thanks @Simmo  - This looks like it's going to add a lot of power. Are you some examples you can post either here or in the Automation playground that uses each of the new items in a more "realistic" example? (Now that you have done all of hard work, mate, you are probably bored and looking for something to do).

[Arkadiusz Inglot]

Its 07.08.2023 and my automation still has:
Each Key will return its corresponding Value. The Value that you define here can be a constant or a smart value. You can add up to 20 entries.

How to force an update for my instance so we can have limit of 200 records fot that?

[Jordan Wilkinson]

Fantastic updates! I'm really eager to know when we can expect them as I'm on a cloud migration project that could really use these features, and it's crunch time.

[Ashli Foust]

Hi there! This looks like an incredibly helpful thing - so thank you for your work on this. One question: is it only available in certain versions or instance-types (Jira Cloud vs Jira Datacenter)?

[Doug Levitt]

@Simmo 

This is awesome.

The combination of..

- User Input on Manual Triggers

- Use of Lookup Tables to clearly describe Name/Value Pairs

- Ability to easily iterate through those Name/Value Pairs

...opens up a whole range of possibilities.  Thank you so much.

Doug

[Bill Sheboy]

Yes, and...

What do people think of dynamic creation of a Lookup Table, such a from a webrequest response?  (e.g., providing a list of key/value pairs to a creator)  This could mitigate the problem where often smart values (particularly created variables) cannot be used in functions, allowing the table to be used as a source instead with get() calls.

Thanks and keep up the great work, @Simmo and the automation team!

[Doug Levitt]

Bill,

That sounds like a very interesting idea.

It makes me wonder.  Does anyone else store information in Jira (that is not related to Projects, Issue Types and Users)?  As an example, I have a "special" Jira project whose purpose is to stores attributes (in Custom Fields) for each of our Scrum Teams (e.g. who the Product Owner is, what is the average Cycle Time, count of items in Backlog that are Sprint Ready, etc.).  Some of these attributes are manually set (e.g. who is the Product Owner for that Team).  Some are auto-calculated (by Automation for Jira).  I can then expose this information in Dashboards, etc.

It would be interesting if a Lookup Table could be populated from this Jira Project (I suppose by invoking JQL).

[matt.parisi]

I'm still seeing a limit of 20 entries in Cloud... Am I missing something?

[Simmo]

@matt.parisi the roll out has been slower than I had anticipated. Mainly, I forgot to increase it. Its currently at 75%. If you don't have it now you should by the end of the week. Same for @Jordan Wilkinson 

@Ashli Foust, @Sami Ahmed Shaik, I'm afraid that it is Cloud only at the moment. I'm not sure if the DC team will have the bandwidth to pickup the idea or not.

@John Funk, mate! Yeah, I'm probably not going to post examples. I don't really have any! I built it because there were a few asks for it and it was a good chance to fix the number as a string vs number as a number problem! @Doug Levitt would be a good one for some example use cases.  

@Bill Sheboy now that is a crafty idea. Do you have a use for it? :)

[matt.parisi]

Wow, thanks @Simmo , that's fantastic news. I spent some time fooling around with the lookup table automation last week and can see it coming in handy. Allowing for 200 entries vastly opens up possibilities. Color me excited. 
Thanks for the update!

[Bill Sheboy]

Hi, @Simmo 

I do not personally have a use case for dynamic loading of tables yet...although I have seen community posts where people had data in other systems which they wanted to lookup in Jira.  Even with those, they are limited by the smart value, function parameters behavior I noted. 

A few possible use cases would be getting a holiday list from another system for date calculations, getting a list of available assignees who are in-office (i.e., not on vacation), or calling the Jira REST API to store and then update issues from other projects (as a work-around for the limitations on the number of global / multi-project scope rule executions per month).

[Simmo]

I'll keep it in mind @Bill Sheboy. We've got shipIt coming up, so that might be something. Although I have another project in mind too :)

[matt.parisi]

@Simmo I checked this morning and have noticed the increase to 200 values in our instance. Thank You! 

Not sure if you'd be interested in my use case scenario, but here it is just in case:
We have about 180 services in our JSM cloud instance and our IT Change Manager really likes the feature included with the Incident Management's Affected Services that lets you inform Stakeholders, but the Change Management module doesn't include that and I couldn't find a way to call the stakeholder attribute for a service in non Incident Management request types. The lookup table allows me to set a distro group as the value for each service and set up a workaround. I've set up an automation that's currently manually triggered and has the lookup table action with each service and a distro as it's key value pair. I've named my table tableAffectedService. I then have a Send Email action, where under To: I have put:
{{tableAffectedService.get(issue.affectedServices.name.get(0))}}
{{tableAffectedService.get(issue.affectedServices.name.get(1))}}
{{tableAffectedService.get(issue.affectedServices.name.get(2))}}

The content gets sent to the distros of the services listed. I'll use smart values to include the information to these "stakeholders" for issues they should be updated on.

So far I've got this working I think, but I'm stuck with 1 issue... it's that I don't know if it's possible to go through each service dynamically rather than statically setting it's index placement i.e. {{table.get(issue.affectedServices.name.get(0))}}. Not sure if you know the answer to this, but it can't hurt to ask... Is there some way I could implement a "foreach" statement with a field that outputs a list like Affected Services? I saw in documentation that it's possible to iterate over a list by adding pound, like {{#list}}{{/}}, but I couldn't seem to figure it out with the lookup table. I'm pretty new at a lot of this so it's possible I'm missing something really basic.

Anyways, again, thank you for your work on this and rolling out the updates. It's a very cool feature.

[Ryan Henze]

@Simmo throwing my hat in to also say my org would also greatly appreciate DC support if you are able to help convey the appetite for folks here to the DC team.

[Simmo]

@matt.parisi you should be able to use it with the iteration logic, you just need to use the .entries property which returns a list you can iterate over. It has two properties, key and value.

[Jennifer Luo]

Hi Simmo, love the update - just wondering if we can force the order of .entries to be what we see in our lookup table - I'm currently parsing through and the output looks like it's out of order.

Thank you,
Jennifer

[Simmo]

Hi @Jennifer Luo, 

I'm afraid that the order isn't guaranteed at this point. What is the issue with it being out of order?  

@matt.parisi you can't use it inside a list traversal operation currently. I'm hoping that should get changed pretty soon!

[Jennifer Luo]

@Simmo, my use case is that I'm parsing through the responses to return a table of values in a specific order, which will be sent out for users to be able to refer to / read.

[Simmo]

@Jennifer Luo and the order in .entries doesn't match what you see in the UI?

[Jordan Wilkinson]

@matt.parisi I also have a use case like this. The way I do it is create a string variable with comma separated keys, like "1,2,3,4,5,..." Then I use an advanced FOR loop and use a split string syntax on my key list to iterate through the keys and in my case create sub tasks that reference a couple of different tables constructed with the same keys. A table for summaries, one for descriptions, one for assignees, etc.

I don't have any examples that are more than about 30 keys at the moment, so this may not work for a larger dataset like yours. What's nice about this approach though, you can create different conditionally constructed key lists inside IF branches. IF BLUE, then 1-5. IF GREEN, then 6-10.