Look up Smart Value in Lookup Table

[Claim Guardian]

Hi,

Since I cannot pass the Smart value to the JQL, I am trying to loop through the Smart value through the Look up Table and it does not seem to return what I want.

I created a Lookup table with 2 keys: ClaimNum and KeyNum from the JQL. Then I want to loop through each of the ClaimNum, if it match, I would like to return the KeyNum. When I log the action it didn't seem to work. I must do something wrong. Any help would be appreciated.

[Bill Sheboy · Accepted solution]

Hi @Claim Guardian -- Welcome to the Atlassian Community!

For a question like this, please include the following to provide context for the community to help:

• what problem is your rule trying to solve; that is, "why do this"
• your complete automation rule, in one single image
• images of any relevant trigger, branches, actions, and conditions
• an image of the complete audit log details showing the rule execution
• explain exactly what is not working as expected, and why you believe that to be the case

Until we see that information...

What do you mean by "I cannot pass the Smart value to the JQL"?  Smart values can definitely be used in rules to perform dynamic searches, lookups, etc.

Kind regards,
Bill

[Claim Guardian]

Bill, Thanks for a quick respond. Sorry I were not clear enough on my question to the group.

Basically, my goal is to link an incoming email that contain the "Claim number" to a Jira service ticket that have the same claim number. 

1. I created a Variable (Smart value) to extract the claim number from the "summary". - Done

2. Then I want to use the "Lookup Issue" to look up via JQL to pass the "Smart value". However, it doesn't seem to ask I read through the various posts that. 

So I thought maybe I create look up table that contain the "Key" and "Claim Number", then I loop through each of the "Claim Number" on the Lookup table to find out "Key" that contain the "Claim Number". From there, I can link the incoming email to that "Key" ticket.

[Bill Sheboy]

Let's assume the variable you created with the Claim Number is named varClaimNumber, and your field in the other issue is also called "Claim Number".

The JQL inside of the rule could be this:

project = CLM
 AND issueType = Claim
 AND resolution IS EMPTY
 AND status != Lead
 AND "Claim Number" = "{{varClaimNumber}}"

Please try that first to confirm it works as needed.

Once it does, you may use branching or lookup issues to perform the issue linking.

[Claim Guardian]

I had tried the JQL with "Claim Number[Short text]" ~"\"{{claimNum}}\"" and it return "A search during custom value definition found no issues".

However, when I used the same JQL with the exact same number ~ "\"0763590007\"" , it found the ticket.

[Claim Guardian]

From this post, it appears that it will not validate because the values in the smart values are unknown at the time of rule writing. That's reason I tried to create the lookup able and loop through it. Solved: Using smart value or smart variable in Automation ... (atlassian.com).

[Bill Sheboy]

That is correct: when smart values are used in a JQL expression it cannot be validated in the rule editor. 

But if you have already tested your JQL outside of the editor with example values (e.g., using Filters > View All Issues), that should not matter: create your dynamic JQL in the rule, and then test your rule by running it.

If this still does not help, please post:

• an image of your complete rule (in a single image),
• an image of the Create Variable action, 
• an image of where the dynamic JQL is used, and
• an image of the audit log details showing the rule execution.

Those will provide context to explain what you are observing.

[Claim Guardian]

Sorry Bill, I am a bit confused when you say "create your dynamic JQL in the rule, and then test your rule by running it.". I don't know what you mean the dynamic JQL.

Coud you please give me a bit more clue? I searched for Dynamic JQL and couldn't find much?

[Bill Sheboy]

When you use a smart value in the JQL, it is dynamically generated when the rule runs using the smart value.

And so the only way to confirm it works is to run the rule.

[Claim Guardian]

I found the issue why the JQL didn't work when I passed the Smart value to it. The issue was the rule stop at the IF/ELSE statement and exit the rule before it passes to the JQL rule. Thank you for your help, Bill.

[John Funk · Accepted solution]

Hi @Claim Guardian  - Welcome to the Atlassian Community!

Take a look at this post and see if it helps you. 

https://community.atlassian.com/t5/Automation-articles/New-Automation-action-Create-lookup-table/ba-p/2311333