Have a formula resulting in a number. Must search the number in another table and return word related to that number

[RogerioPenna]

I would do that easily in Excel, but I am failing miserably at Grist.

Basically, I have a column where person choose Risk Probability and another column where you choose Risk Impact. I have another column that multiplies one by the other.

Then I have another table (RiskDegree)where first column is Numbers and second column is Risk Degree.

So if for example, person choose High Impact and High probability, the result is 25.

Then I must searc for 25 in the RiskDegree table first column (Number) and get the corresponding value of the second column, which for 25 is “Dangerous Risk”.

[natalie-grist]

Hi there!

In Grist, you can use lookup functions to find values in other tables. Grist has lookupOne which finds a single record that matches the parameters given and lookupRecords which finds a list of records that match the parameters given.

Our References and Lookups article goes over these two functions in depth.

Because you are looking for a single record where the RiskDegree (Number) match, we will use lookupOne.

I created an example (based on your explanation) for you: Community #1545 - Grist

The ‘Risk Degree - Text’ column of the ‘Risk Probability’ table contains the following lookupOne function:

Risk_Degree.lookupOne(Number=$Risk_Degree_Num).Value

The general format for a lookupOne formula is:

[Table_Name].lookupOne([A]=$[B])

[Table_Name] is the name of the table you want to lookup data in. In the example, the table we are looking up data in is the ‘Risk Degree’ table.

[A]=$[B] is what two columns are we trying to match in order to find the correct record.

[A] is the column in the table being looked up (named at the beginning of the formula). In our example, we are looking up the ‘Number’ column in the ‘Risk Degree’ table

[B] is the column in the current table / the table you are entering the formula in. In our example, that’s the ‘Risk Degree - Num’ column. We want to match the value in the ‘Risk Degree - Num’ column to a value in the ‘Number’ column of the ‘Risk Degree’ table.

LookupOne function finds a record. So in this case, it finds the entire record in the ‘Risk Degree’ table where the value in the ‘Number’ column matches the value in the ‘Risk Degree - Num’ column of the ‘Risk Probability’ table.

At the end of the formula, we have .Value. We use dot notation to pull the value from the Value column for the record we found with our lookupOne function.

Let me know if you have any questions.

Thanks,
Natalie

[RogerioPenna]

Thanks a lot Nathalie. It seems the only reason I failed is because the last .value doesn´t show options, so I wrote by hand AND missed a capital letter… it should be GraudeRisco and I wrote Grauderisco… bummer. Anyway, your explanation is great (better than the help file which I had already read but found it less it lacking)

[RogerioPenna]

Natalie, in another document… I have this formula

Orcamento.lookupOne(id=Orcamento_Concreto.lookupOne(id=$id).OrcamentoFK).Tabela_de_Precos

It’s returning me the position of the record on the table… “Tabela_Precos[2]” instead of the value.

Actually, that is part of the formula. I wrote it in a different column to see if I could get the value to be used in another formula.

The whole formula needs to return me a single value.

I am at a record from the Orcamento_Concreto table.

This table has a column referencing a type of concrete (table Concreto, column Tipo_Concreto)

And another column referencing the table Orcamento.

Table Orcamento has a column referencing Tabela_Precos.

Table Orcamento_Concreto has a column referencing Tabela_Precos and a column referencing Concreto and a column for price.

Thus, at Orcamento Concreto, when I select a type of Concreto, I need to get the PRICE value from Orcamento_Concreto table, where Concreto column matches Concreto column at Orcamento_Concreto AND Tabela_Precos column matches the Tabela de Precos value of the Orcamento table record referenced by the Orcamento_Concreto record I am at…

Gosh, even explaining this created a knot in my head.

Anyway, at Orcamento_Concreto table I created two fields, each with a formula

=Precos.lookupOne(
Concreto=$Tipo_Concreto,
Tabela=Orcamento.lookupOne(id=$OrcamentoFK).Tabela_de_Precos
)

AND

=Orcamento.lookupOne(id=Orcamento_Concreto.lookupOne(id=$id).OrcamentoFK).Tabela_de_Precos

Both return me the positions in the tables where they found the values, instead of the values.

I guess mixing the two would return me the correct value for the Price of Concrete in the PricingTable (Tabela_de_Preco)??

Ok… now I understand… if I don´t specify the column I want to see, it shows me the position. Like Excel Index.

This is the final formula I used and it’s working

Precos.lookupOne(
    Concreto=$Tipo_Concreto,
    Tabela=Orcamento.lookupOne(id=$OrcamentoFK).Tabela_de_Precos
).Preco