Automation rules - Lookup tables & empty value

[CEDRIC DEVAUX]

Hi,

I explain my use case, I want to manage a risk table with customfield Impact & customfield probability, these fields are select list single value

Then when a user select values in Impact & Probability, an automation rule is launched with 2 lookup tables:

- One table for Impact to define the value of impact ex: User selects Low Impact so in the table Low Impact matches with 1

- One table for Probability to define the value of probability ex: User selects Minor so in the table Minor matches with 1

Finally I calculate Impact value x Probability value to have the risk scoring

It works well except the case when impact or probability is empty.

No matching with a empty value inside lookup table so no calculation available.

I want to avoid to set Impact & Probability as required.

There is a solution to manage empty values + lookup table?

Below my rule:

[CEDRIC DEVAUX · Accepted solution]

Finally, I find the solution :)

I add a IF Else condition to check where Impact or Probability is empty.

If yes => Risk scoring =0

Else I use table look up and calculate Impact x Probability.

[Bill Sheboy]

Hi @CEDRIC DEVAUX 

Another way to do this is with default values for a field, using the pipe | operator.

For example, {{issue.someFieldThatCouldBeEmpty.value|0}}

This would substitute in 0 when the field is empty.

For your scenario, the lookup tables could contain a value to use when the field(s) are empty, and the lookups would then be:

{{tblMyLookupTable.get(issue.someFieldThatCouldBeEmpty.value|0)}}

This technique eliminates the need for conditional logic in the rule.

Kind regards,
Bill

[tiago.santos]

Thank you so much, Bill, this completely worked (not sure why it's not documented on the respective component yet).

Do you happen to know the behaviour if a value is not empty but the key does not exist in the table? Will it still default to the second value of the OR expression?

[Bill Sheboy]

Hi @tiago_santos 

The default value is for the parameter to the get() function, not for the result of the lookup.  If you need to check for missing values, perhaps either:

• create a table row with a "magic number" (e.g., such as 99999) for the key, and use that value as the default
• save the result of the lookup with get() in a variable and then test that for empty

Kind regards,
Bill

[tiago.santos]

You are absolutely right.

I was able to solve it by using something like:

{{tblMyLookupTable.get(issue.someFieldThatCouldBeEmptyOrInexistent.value|0)|tblMyLookupTable.get(0)}}

Afterwards, created a "0" key which I am using for default value. I'm assuming I could also use "default" as key, but it's actually more readable just using a 0 

I can't thank you enough for this. You are a rockstar.