JSM Automation - nested lookupissues inside FOR EACH sends duplicate emails

[Alik Levin]

Overarching goal - send last month's report to customer.

Logic:

1. Get last months JSM tickets

2. Iterate for each related Organization associated with the ticket.

3. Filter tickets for current Organization 

4. Inside the branch send email - summary of tickets for each Organization.

5. The email content generates just fine!

6. PROBLEM - the automation sends same amount of emails as the number of tickets for that org. For example, if I have Org A with 1 ticket, Org B with with 2 tickets, and Org C with 3 tickets, I am getting 1 email for Org A, 2 identical emails for Org B, and 3 identical emails for Org C.

7. Problem of dups disappears when I change Advanced Branching Smart Value

from:

{{lookupIssues.Organizations.flatten.distinct}}

to:

{{lookupIssues.Organizations.flatten.name.distinct}}

How to fix and avoid getting duplicate emails when using {{lookupIssues.Organizations.flatten.distinct}}?

Reasoning for why I cannot just fall back to {{lookupIssues.Organizations.flatten.name.distinct}} which works fine, no dups: I also need the Org's Id and I don't see the way to extract it when iterating over Org's name, I need to iterate over Org's object.

Thanks!

[Bill Sheboy]

Hi @Alik Levin 

Without seeing the specifics of your entire rule and audit log details...

I suspect the problem is the flatten and distinct functions are having difficulty because Organizations is an object and has several attributes...rather than a single value.

Thus a workaround would be to use advanced branch over a single, flattened attribute, such as the Organization ID, and then within the branch, repeat the lookup and add the branch variable (i.e., the ID) to the JQL:

UPDATE: It appears the Organizations smart value is one which is loaded just-in-time, and thus cannot be used as I first suggested.  I confirmed this with testing both my original and the revised approach.  Either the entire object must be referenced and it will be looked up later; or all the orgs must be extracted into a created variable, and then parsed into a list for iteration.  The first approach is easier:

{{lookupIssues.Organizations.flatten.distinct}}

and within the branch, perhaps using the branch variable named varOneOrg added to the JQL:

AND Organization = {{varOneOrg.id}}

Kind regards,
Bill

[Alik Levin]

[Alik Levin]

Downstream I have Send Customized email with this snippet that builds the content:

{{#lookupIssues}}
<tr>
<td><a href="{{url}}">{{key}}</a></td>
<td>{{summary}}</td>
<td>{{customfield_10144.asCurrency()}}</td>
</tr>
{{/lookupIssues}}

[Bill Sheboy]

Hi @Alik Levin 

Thanks for your rule image, and after testing I found Organizations appears to be one of the smart values loaded just-in-time, the same as subtasks and issuelinks.  Thus it cannot evaluate fast enough for the id to be parsed out before the flatten and distinct happened in my original suggestion.

My original suggestion would only work if all of the orgs were first parsed into a created variable...forcing their evaluation.  And then flattened and examined with distinct.  As that seems unnecessary as the name and id are not needed until later, using the object directly in the branch seems to work.

For the updates you show, are they working as you expected?

[Alik Levin]

I am failing to see how to flatten on both name and id for the Organizations. The loop iterate over flattened and distinct names - no dups. The moment I reference organization's id, things get duplicated.

The closest I am getting to the solution is getting the string of pairs as in name1:id1|name2:id2 which includes all the dups and then I need to parse it with some wild regex with name as parameter and get first match to extract the id. Working on it now. 

That will keep me in the flattened name loop w/o referencing id that triggers dups.

[Alik Levin]

OK NAILED IT!!! GAH!

Inside the FOR EACH LOOP:

1. Create variable pairsString = {{#lookupIssues.Organizations.flatten}}{{name}}={{id}}|{{/lookupIssues.Organizations.flatten}}. It creates a string of org=id pairs with dups divided with I.

2. Parse it as the following orgId = {{pairsString.substringAfter(distinctOrganization).substringBefore("|").substring(1)}}. First gets what's after the matched Org name, then trims the tail leaving =Id, the substring(1) removes the = sign leaving me with the resulting orgId.

3. Now I have org Id in hand so I can issue REST request to get the customer email.

Gosh, unfreakingbelieavable! To get organization's customer email I needed to jump through insane hoops of string concat's and parsing then issue REST requests (now I need to manage tokens that will break my automation when they expire).

Is it really such a corner case? Here is Org name, give me the Org's customer's email.

Thanks for support and the ideas Bill!

[Bill Sheboy]

Well done, and quite curious!

When I did an advanced branch over the flattened and distinct Organizations, I was able to get the id directly.  I wonder what is difference for our usages.

• ...
• advanced branch
  • variable name: varOneOrg
  • smart value: {{lookupIssues.Organizations.flatten.distinct}}
    • inside the branch, needing the {{varOneOrg.id}} to make the REST API call...
    • send the email...

I suspect it was the timing / just-in-time read thing I noted, where the branch did not get the data fast enough.  This can also happen in the Send Web Request action when the request URL or message body uses smart values; to solve that, one also uses a created variable to pre-evaluate them and then use the variable in the later action.

[Alik Levin]

Yes, that's EXACTLY what I did and I was able to extract both Org name and the id inside the loop, but when I tried sending out Emails or Web Requests it all "unlfattenned" and sent duplicate emails/web requests.

[Bill Sheboy]

Without seeing your entire rule, I wonder if the cause is the rule trigger has multiple work items (e.g., scheduled with JQL) and thus the duplication is from the trigger iteration and not the branching over distinct items.

[Alik Levin]

It's triggered as scheduled automation, not by any work item, I test it running it manually 

[Bill Sheboy]

Does the scheduled trigger have JQL?  If so, that is the cause of the repeated items.