How do i do a 2 way lookup

Help Center
1

Hi folks, I probably should be able to work this out but its got me stumped. Any pointers gratefully accepted.

I’m trying to set up a risk matrix. One axis is Likelihood, One axis is Impact. Depending on where they intersect we derive the risk rating. Something like this:

image
image461×298 26.1 KB

I need a way for a user to enter Likelihood (L, M, H) and Impact (L,M, H) which will give risk rating of LOW, MEDIUM or HIGH. e.g. L Likelihood & H Impact >> MEDIUM (bottom right corner)

I’m stumped with how to set this up in Knack. I know I could set up a field with some really ugly and longwinded validation rules, but is there a smarter way by using connected tables or similar?

2

I’m not sure I understand the risk matrix graphic. Maybe reading it at 10:30 PM is not the best time for me cognitively. :crazy_face:

Can you express the options in a spreadsheet layout as it will be easier to create a table.

Not sure what you mean by a two way lookup :thinking:

3

It’s a simple 3x3 box.

Each axis has 3 options low medium or high. Pick a position on each axis and the coloured cell at the intersection is the answer.

So low impact + high likelihood = MEDIUM.

It’s a 2 way lookup because 2 inputs give the output.

4

Hi Leigh

That looks like a H&S Risk Matrix.

We assign a value of 1-5 for the Likelihood and 1-5 for the Impact and multiply those values for the Risk rating so that 5 x 5 = 25 or Critical or 1 x 1 = 1 or Low. You’ll just need to work out your bands for the 4 levels you have.

Depending on the user requirements you might work it the other way and assign values to Low, Medium, High, & Critical Likelihood & Impact buttons for them to select which will calculate the risk and display the appropriate Risk rating button/icon.

Dean

5

Ok, after reading through a bunch of old posts and watching one of Carl’s videos, I got there.
Just describing what I did in case it helps someone else later. Its not all that intuitive, but it works.

Create a simple one column table called LIKELIHOOD - add three (short text) rows Low Med High
Create a table called IMPACT & RATING. Connect it to LIKELIHOOD. Display is IMPACT.
Create 9 rows, so that it looks like:

|short text|connection|short text|

Impact (short text) Likelihood (connection) Risk Rating (short text)
Low Low LOW
Low Med LOW
Low High MEDIUM
Med Low LOW
Med Med MEDIUM
Med High HIGH
High Low MEDIUM
High Med HIGH
High High CRITICAL

Then create the RISKS table with (at least) 4 columns:
Risk Name (short text)
Likelihood (connection to LIKELIHOOD)
Impact (connection to IMPACT & RATING)
Risk Rating (short text)

Then on the associated add or edit form for RISKS, use cascading drop downs
LIKELIHOOD (from Likelihood table, all options)
IMPACT (from IMPACT & RATING, connected to this forms LIKELIHOOD)

And create a record rule that sets:

image
image465×120 6.78 KB

Convoluted but it works. And it isn’t buried in some formula that requires access to the builder to change - this can be made available to an admin user to adjust as they wish, including expanding it to a bigger grid if needed.

2 Likes
6

Glad one of my videos was of some inspiration :rofl:

1 Like
7

Thank you for sharing your solution.