Combine multiple values selected in LookupTable into a field

Ryan Buffa

I have a lookup table linked to a multi-select custom field. 

I want to combine all the matching values from the lookup table that were selected into a single field. 

I have added a branch and used {{lookuptable.get()}} to log the different values. This adds them to the log exactly how I want them in my field. However when I try to populate the field I only get the last value. 

 

 

Bill Sheboy

Hi @Ryan Buffa 

Short answer: please try a method like dynamic list searching to solve this as branching and list iteration will not work.  If you want to try this, please continue reading this response.

 

There are two ways that do not work to solve your scenario:

• A rule branch which could be on more than one thing / issue executes in parallel and asynchronously, with no guarantee of processing order or when the branch will complete...up until the last step of the rule.  And so looping over the field values will not help build up as single result.  What you see in the audit log is an "artifact" of the way rule write value to the log as they loop.
• Another option would be the long format of iteration over the value in multiple-selection field.  However once inside of the iterator, the lookup table would not be visible.  This is a known, scoping problem with rules where only information at the iterator scope, and lower, can be accessed.

 

One possible way to solve this is to "flatten" the table into a delimited string of key / value pairs, using the entries function for lookup tables, create a dynamic regular expression to find the selections from the field, use inline iteration and the match() function with that expression, and then parse out the values found using text functions.

For example, let's assume your field is customfield_12345 and the lookup table name is named varValueReplacementTable.  I recommend writing each variable to the audit log to understand how this rule works.

• action: Create Lookup Table
  • name: varValueReplacementTable
  • row -- key: abc; value: some substitute for abc
  • row -- key: def; value: some substitute for def
  • ...
• action: Create Variable (This is the flattened out table, with a ~~ delimiter between rows)
  • name: varFlatTable
  • value: 

{{#varValueReplacementTable.entries}}{{key}}:{{value}}{{^last}}~~{{/}}{{/}}

• action: Create Variable (This will be the regular expression from the field)
  • name: varSearchExpression
  • value:

({{#issue.customfield_12345}}{{value}}:.*{{^last}}|{{/}}{{/}})

• when the replacement values are needed, use this expression:

{{varFlatTable.split("~~").match(varSearchExpression).substringAfter(":").join(", ")}}

That expression works by splitting the flattened table back into rows, searching for a match, and then extracting the values found, finally joining them together as a comma-separated values list.

 

Please note well some assumptions...

1. The multiple-select field options do not contain colons or tildes
2. If the multiple-select field options contain other non-alphanumeric characters, they may need to be escaped in the varSearchExpression.  That is a bit complicated, so please let me know if that is needed.

 

Kind regards,
Bill