Is there a way to look up a field in another table, based on two values that are “linked to other records”.
In the example below I have 4 tables
The Risk Matrix defines the output risk based on which likelihood level and impact level has been chosen from the linked records “likelihood scale” and “Impact scale”, under the Scenarioes.
In essence I want to be able to write a function in my scenarios table that looks for a match of both likelihood and impact in the risk matrix, and then returns the associated risk.
You could work with the formula column and link the “Likelihood” and “Impact” columns. Then you can search for it via the Link to other records column and link it to the “Scenarices” table. You can also link the risks accordingly.
I see how this could work, but after concatenating the likelihood and impact, I can no longer use the lookup formula, as it wont take input from an formula collumn:
My understanding was that you have the “Risk Matrix” table, here you link “Likelihood” and “Impact” and also the risks (from the lookup).
Then you create a Link to other records from table “Risk Matrix” to “Scenarioes” and search for the concatenation and can display the concatenated risks.
The idea is that doing an assessment, the assessor will at some point have to choose likelihood and impact on a scenario. Normally I just made this locally with a “single select” field with relevant choices on the impact and likelihood, and then made a formulla which dictated the risk based on the choices.
However this became a problem when needing to alter risk categories, or the the associated levels of the single select, as this was not dynamic.
Therefore I wanted it to be a “link” to another table in which I can allways redefine the names and risk levels if requried.
So in essence I need the Scenarioes table to look at the risk table for both choices and calculation of risk level.
You do not need two columns Link to other records in the “Scenarios” table. In my opinion, one link to the “Risk Matrix” table is sufficient here.
You enter a scenario, e.g. Phishing, click in the Link to other records column (Risk matrix) and can now select from the existing records. You can show the impact and the likelihood using the Link formula.
Thanks for really trying. It seems I have done a bad job at explaining. The way you put it is the opposite of what I intend.
What I intend is:
As an admin I define the relationship between risk and impact/likelihood in the risk matrix table.
The user only gets to pick the impact and the likelihood under the scenario table.
Then the system should “calculate” the risk taking the input from the user on likelihood/impact and matching it with the defined risk in the risk matrix.
Please create a formula column in the Scenarios table for chaining Likelihood and Impact and a column Link to other records in the Risk matrix table.
On the Risk matrix table, you also need a formula column to link Likelihood and Impact.
Now create an automation rule based on the Scenarios table (Please look at the screenshot).
You automatically create a link to the Risk matrix table if the values from the chained formula columns match and the status (very low, low, …) is displayed.
