Working with and presenting data pulled from Lookup action

[David Quiram]

Preamble / Context

We have a form for when employees want to request equipment/furniture. When this request comes in, we are wanting a comment placed on the issue letting the assignee know if the employee has made a similar request before. 

I know there is a "Similar Request" feature, but it's not always exact and for this we need to be painfully precise. I understand I will need to use the Lookup Issue action and possibly the Create Lookup Table action?

Here is the automation we have been working around. 

• Trigger: When Issue Created
• IF: Issue matches JQL
  • issuetype = "A" AND Custom_Field_1 = "B"
• Then: Lookup Issues
  • issuetype = "A" AND reporter = {{issue.reporter}}

So that alone works perfectly. We can see that it is pulling the correct issues in the audit log. 

For a little bit of context for those that caught it. Yes, our Issue Lookup is much broader than the requirements for the automation. This is because the Custom_Field listed wasn't being tracked on previous issues and now it is. (Form Field vs. Jira Field). We actually have two new custom fields in relation to this. One being used here and the other will be used later.

---

Theoretics 

So in the next section of the automation is where we need some help. We want the results of the lookup to be given in a comment for the issue assignee to review, but we need more than just the issue key and we want a little bit of logic behind it.

As I understand it, at this point we would need to take the Issues collected by the Lookup Search and place them into a Lookup Table? We need to do this because we want additional details from the issues available to be actionable by further steps within the automation flow.

In the end, we want 4 items to be displayed in the issue comment. 

• Issue Key
• Creation Date
• Custom_Field_2 Data (could end up being null)
• Link to Issue

As mentioned before, the Issue Lookup will potentially pull a broader data set than what we might need. So we want to use logic to provide either select issues from the lookup or all issues from the lookup:

• IF: trigger issue has any exact matches based on the Custom_Field_2, then list issues with the 4 points of data available. Displaying data in a table view would be ideal.
  • So this will only be select issues from the lookup/table
• ELSE: list all the matches based on Issue Type, with the 3/4 points of data available. Displaying data in a table view would be ideal.  
  • So this will be all issues from in the lookup/table

---

Questions and Guidance

1. Is this even possible or did I understand something incorrectly? 
   
   
2. Is using the Lookup Table the correct method for this?
   • Assuming I am on the correct path, setting up the table confuses me a little and I'm uncertain if I understand it correctly. Key can be literally anything, but Value is where we need to use Smart Values to access the issues from the Lookup and scope down to the exact detail we are looking for?
     
     
3. Assuming all the above is possible and correct, can I display that data in a table in an internal comment?
   • If not, what would be the least confusing method of displaying the information? 

Thank you in advance for any suggestions and brainstorming around this. I am still very green when it comes to understanding and working with data in this fashion. 

[Kalyan Sattaluri · Accepted solution]

Hello @David Quiram 

I am not sure I full understood the requirement but my take is below:

If you need, in your comment, a table of data from lookupissues, After your if condition, you can put below in your comment section (see screenshot below). Modify according to your fields.

||*Key*||||*Summary*||||*Reporter*||||*CustomField*||||*CreatedDate*||
{{#lookupIssues}}
|{{key}}|{{summary}}|{{reporter}}|{{if(customfield, customfield, "NO-VALUE")}}|{{created.jiraDate}}|
{{/}}

Gives below table in comment:

NOTE: field names are case sensitive.

Summary is wrong and will not give you data. summary is correct.

In my table I have a if condition to check if value is null, if so replaces with NO-VALUE.

You can do the same for your "Else" condition.

Hope it helps.

[David Quiram]

@Kalyan Sattaluri This will certainly help with presenting the data in a clean way! Thank you very much for this suggestion. 

[Kalyan Sattaluri]

Great, def try and let us know if questions or any syntax issues.

[David Quiram]

So, I have been trying a few ways and can't seem to get they syntax right to use the IF condition. Here is what I have:

||*Key*||||*Date*||||*Equipment Requested*||
{{#lookupIssues}}
|{{key}}|{{created.shortDate}}|{{if(customfield_10338, customfield_10338, "Data Unavailable")}}|
{{/}}

When working with custom fields in other automations, I always had to use the format customfield_###. Is that potentially getting in the way? I know that the fields have values in them for the lookup issues. I can confirm that, but the code as written always returns the third option of "Data Unavailable". 

Also note, this custom field is a multi-select type, so it will have up to three items listed in it. Would that be causing an issue. 

[Kalyan Sattaluri]

hello @David Quiram 

To access the multi-select values, you need to use {{customfield_10338.value}}

We need to check if you lookupIssues has access to this field.

So, Can you, before you put in a comment, put below 2 log statements.

{{issue.customfield_10338.value}}    <-- If trigger issue has equipment field selected, it should return you a list

{{lookupIssues.customfield_10338.value}}  <-- If lookupIssues has access to equipment field, should return you an array.

Can you check and get back if above 2 smart values are returning expected values?

[David Quiram]

Just tested and yes, both values returned the expected data. 

I was trying to anticipate how to use this new knowledge and updated the Comment action. 

I tried it numerous different ways, but no combination seems to work. It still only returns "Data Unavailable" for all entries being pulled in my test. Issue 325 has values in those fields, confirmed with the earlier log actions, and issue 4 does not. 

My last attempt was

{{if(customfield_10338, customfield_10338.value, "Data Unavailable")}}

I thought that would work because it should read as

If lookup issues contain True values for customfield_10338, then print those values, else print "Data Unavailable", right?

[Kalyan Sattaluri]

Hello @David Quiram 

I have observed this too in my tests, looks like smart value "else" condition does not work as I expected...

BTW.. for my custom field multi check box, i am not even able to retrieve data in lookup issues...

So, If you Equipment requested field, which is customfield_10338, retrieves data for lookupIssues.customfield_10338.value , try below

||*Key*||||*Date*||||*Equipment Requested*||
{{#lookupIssues}}
|{{key}}|{{created.shortDate}}|{{customfield_10338.value|0}}|
{{/}}

Limitations with this approach, you can only provide numeric substitution (0) in my case. any alphanumeric substitution is not resulting in data.

Please try above syntax and let me know.

[David Quiram]

That worked! Thank you so much!

I have some more development to do around further improvements to my automation, but this was the first huge hurdle. 

I'll update here with further development questions if I hit another block for this particular flow I am building. 

[David Quiram]

Disregard this post/update. My information was incorrect. Apologies. 

[Kalyan Sattaluri]

@David Quiram  Hello, if your fields are not being returned by lookupIssues, you can instead make a REST call with the same search JQL as your lookup criteria and do the same with webresponse you get back.

[Kalyan Sattaluri]

Also, if you think the basic premise of displaying lookup issue data in a tabular manner in comment is working, please consider accepting the answer so others looking for similar process can benefit in the future.

As mentioned, if you want to show additional fields not exposed by lookupIssues, you can make a REST call and process is the same once you have the response, so you will reference {{webResponse.body.key}} or {{webResponse.body.fields.summary}} etc based on the field you are interested in.

The end point you will point to will be below and you will use the same JQL as your lookup issues.

https://yourdomain.net/rest/api/2/search?jql=

You can raise a new post and tag me if you run into any issues.