I have two tables:
Nestle Log Results
Risk Table by position
I need the position column in Table 1 (Nestle Log Results) to reference Table 2 (Risk Table by postion). I did this by referencing it - it works fine.
Then i need [Risk] in Table 1 (Nestle Log Results) to give the corresponding risk value according to Table 2 (Risk Table by position) - this i cannot seem to get to work.
Screen shot of table one (Nestle Log Results):
Screen shot of reference in Table 1 in column [position]:
Screen shot of table 2:
Screen shot of the app - I need column values in [Risk] column:
Screen shot of the raw data in table 2:
My goal:
To assign a risk factor to each employee depending on their position.
Solved! Go to Solution.
The formula will only work for new entries. Unless you use a virtual column where it will compute for you every sync.
Hello Tiger, you can use a lookup or any formula. See a sample below.
Any(
Select(table 2[risk], [position] = [_THISROW].[position]))
Or if your are referencing the table 2 in the log table then I believe a dereference expression will do
[Position].[Risk]
I’ve tried that in the “Formula” - It did not work? Is it my key and/or label in table 2?
The formula will only work for new entries. Unless you use a virtual column where it will compute for you every sync.
Ok. So make it a virtual and then it should work?
That worked great. Thanks guys for all the help.
I need to now go a step further. I need to give a text value based on those numbers [Risk] - column along with referencing. But i cannot seem to get it:
AND([position].[risk],
IFS(
([Virtual Risk] = “0”), “Zero Risk”,
([Virtual Risk] = “1”), “Very Low Risk”,
([Virtual Risk] = “2”), “Low Risk”,
(Virtual Risk] = “3”), “Average Risk”,
([Virtual Risk] = “4”), “High Risk”)
)
Please check the formula below
That works - but i need to also have it reference:
[position].[risk]
I cannot picture what you wanted. Can you please elaborate it? Because [position].[risk] is dereferencing
Don’t i put the dereference in the “formula” as well?
If you just want to get the value, then nope.
But if it is not referencing Table 2 - then how will the IFS statement work?
Wait,
So in Table 1 - [position].[risk]
In Table 2 - IFS(
([Virtual Risk] = “0”), “Zero Risk”,
([Virtual Risk] = “1”), “Very Low Risk”,
([Virtual Risk] = “2”), “Low Risk”,
(Virtual Risk] = “3”), “Average Risk”,
([Virtual Risk] = “4”), “High Risk”)
I’m quite confused.
You will get the value the risk from the Table 2
There is a Risk column in Table 2, what is your formula for your Virtual Risk?
I am very sorry.
Table 1:
Virtual column [Virtual Risk] - Formula = [position].[risk]
Table 2:
Column [Risk] has a formula -
IFS(
([Risk] = “0”), “Zero Risk”,
([Risk] = “1”), “Very Low Risk”,
([Risk] = “2”), “Low Risk”,
([Risk] = “3”), “Average Risk”,
([Risk] = “4”), “High Risk”)
This still does not give me the text instead of the numbers
I am using [Virtual Risk] in my view.
Table 2:
This will not work in Risk column. Try creating a virtual column with that formula because you are trying to “change” the data.
Oh ok. Let me try that.
Thanks
worked!
Thanks. I am sorry. I know i am using text instead of those numbers. But i need that - because i am going to use those numbers to random test.
Thanks
You are such a big help - I was just going to ask you.
Do you know how I can do a random raffle - using these numbers?
So if the employee has a risk factor of = 4, he gets in the raffle 4 times. Then i want the app to be able to force a random drawing. Does that make sense?
I’m sorry but I haven’t done this before.
Ok. Thanks
As you have shown if “Position” is key of the table 2 and if you are referencing it in [Position] of log table , then dereference expression I believe will work for the{Risk] column.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |