I cannot seem to link a table to another and get the value i want

[Tiger1]

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.

[JuneCorpuz]

Hello Tiger, you can use a lookup or any formula. See a sample below.

Any(
Select(table 2[risk], [position] = [_THISROW].[position]))

[Suvrutt_Gurjar]

Or if your are referencing the table 2 in the log table then I believe a dereference expression will do

[Position].[Risk]

[Tiger1]

I’ve tried that in the “Formula” - It did not work? Is it my key and/or label in table 2?

[JuneCorpuz · Accepted solution]

The formula will only work for new entries. Unless you use a virtual column where it will compute for you every sync.

[Tiger1]

Ok. So make it a virtual and then it should work?

[Tiger1]

That worked great. Thanks guys for all the help.

[Tiger1]

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”)
)

[JuneCorpuz]

Please check the formula below

Tiger:

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”)

[Tiger1]

That works - but i need to also have it reference:

[position].[risk]

[JuneCorpuz]

I cannot picture what you wanted. Can you please elaborate it? Because [position].[risk] is dereferencing

[Tiger1]

Don’t i put the dereference in the “formula” as well?

[JuneCorpuz]

If you just want to get the value, then nope.

[Tiger1]

But if it is not referencing Table 2 - then how will the IFS statement work?

[Tiger1]

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”)

[JuneCorpuz]

I’m quite confused.

Tiger:

Table 1 - [position].[risk]

You will get the value the risk from the Table 2

Tiger:

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”)

There is a Risk column in Table 2, what is your formula for your Virtual Risk?

[Tiger1]

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:

[JuneCorpuz]

Tiger:

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 will not work in Risk column. Try creating a virtual column with that formula because you are trying to “change” the data.

[Tiger1]

Oh ok. Let me try that.

Thanks

[Tiger1]

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

[Tiger1]

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?

[JuneCorpuz]

I’m sorry but I haven’t done this before.

[Tiger1]

Ok. Thanks

[Suvrutt_Gurjar]

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.

[Tiger1]