How to Find Highest Value in Lookup Table

[Vikrant Yadav]

Hi Experts,

Need your help in setting up an automation rule.

Problem Statement:
We have a multi-select field where each option has a ranking or score stored in a lookup table.
For example:

• Client 1: 100

• Client 2: 200

• Client 3: 300

If multiple values are selected in the multi-select field, we want to pick the option with the highest score.

Example:
If Client 1 and Client 2 are selected, the smart value should return 200.

I am not able to find a way to pick the highest value from the lookup table.

Kindly suggest a solution.

[Duc Thang TRAN · Accepted solution]

Hello @Vikrant Yadav 

For me, if we have fewer than 10 values of client name to manage, we can user if / else conditions.

I will start with the condition for the highest-priority value (for example, Client 10).
Then, as an action, I will use: {{tablename.get("Client 10")}}

If that condition is not met, I will go down step by step to the next client (Client 9, Client 8, etc.) until the lowest one, and for each match I will return the corresponding value from the lookup table.

:)

[Vikrant Yadav]

Hi @Duc Thang TRAN  
Thanks for your suggestion! 

But here it's multi-select field having 8 options 

how you decide which option to pick when three option selected in a field ? 

Out of these three options, we need to select the value from lookup table which have highest ranking out of these three.

Is any other way to do this, other than lookup table.

[Duc Thang TRAN]

@Vikrant Yadav 

That is why I suggest using if / else conditions.
For example, if we have the following matrix:

Client 1 → 100

Client 2 → 200

Client 3 → 300

We start with the highest value in the matrix.

First condition:
Work item field condition → Client name contains any of Client 3 (highest value)
Then action:

{{tablename.get("Client 3")}}

This means that if the ticket contains multiple values and one of them is Client 3, it will automatically be considered as the highest value.

Then in the else:

Check if it contains Client 2
→ {{tablename.get("Client 2")}}

Then in the next else:

Check if it contains Client 1
→ {{tablename.get("Client 1")}}

This is a manual ranking, not a variable
That is why I think it is easy to manage when we have fewer than 10 values.

We could even simplify it further by not using a lookup table at all and directly mapping the values in the actions (for example, setting 300, 200, or 100 directly).

[Vikrant Yadav]

Hi @Duc Thang TRAN  Thanks for the solution!

This solution works for me.

[Bill Sheboy]

Hi @Vikrant Yadav 

Short answer: you could solve this using dynamic list searching methods.

To get the maximum score, the solution approach is:

• assuming the Lookup Table has clients as keys and scores as the values
• assuming the multiple-selection, option field has the clients as the values
• create a variable containing the table entries flattened into delimited records as text, for example:
  
  • Client 1:100,Client 2:200,Client 3:300
• create a variable containing a regular expression based on the custom field selections using list iteration, for example:
  • ^(Client 1|Client 2):.*
• use the match() function with the regular expression to get the matching records
• extract the scores from the matches with text functions, convert with the asNumber function, and use the list max function to get the answer

To learn more about this method, please see this article I wrote on it.

Kind regards,
Bill

[Vikrant Yadav]

Hi @Bill Sheboy  Thanks for the solution!

I'll try your solution as well. Let me setup flow and get back to you.

[Marc -Devoteam-]

Hi @Vikrant Yadav 

Lets approach this theoretically.

Don't use a lookup table.

What if you have a custom select field with just number options like 1, 2 and 3 .

Create a work item with multiple values in customfield_xxxxx

Then in the automation use {{issue.customfield_xxxx.max}}

Add this to a log action, what is the result?

If this this is 2 ( if option 1 and 2 where used)

The you could use this value.

[Vikrant Yadav]

Hi @Marc -Devoteam-  

Custom Field options are Client Names. 
It's not a number options. In Lookup table i am giving score to each Client. 

From lookup table we'll get which Client have high score. 

[Marc -Devoteam-]

Hi @Vikrant Yadav 

This can't be done with a lookup table as already mentioned by @Andrea Tagliabue 

[Andrea Tagliabue]

Hi @Vikrant Yadav,

Unfortunately, Jira Automation does not natively support retrieving the maximum value from a lookup table for a multi-select field.

To achieve this, you would need to use a Marketplace app like ScriptRunner to script the logic, or manually create conditional smart values for each option, but there is no built-in function to automatically pick the highest score.