0

Look up value in table based on two other values

I'm new to Ninox and am trying to set up a database for a distillery's production records. One thing I need to accomplish is a formula that can look up a value in another table based on two inputs. The data in the table looks something like this:

Density    Temp    ABV

1                1            1

1                2            2

1                3            3

2                1            4   

2                2            5

2                3            6

If the density is 2 and the temperature is 3, I want the function to return 6 as the value for ABV.

Unfortunately, I don't have the option of using a formula to accomplish the same task. The data has to be pulled out of a table.

3 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Alex

    In your main table have Density and Temp numeric fields and a formula field for ABV with a Lookup table with numeric fields for Density, Temp and ABV populated with your lookup data. Then set the ABV formula field in the main table to 

    let a := Density;
let b := Temp;
first(select Lookup where Density = a and Temp = b).ABV

    If you have a large dataset you might want to make ABV in the main table a numeric field and put the following code in the Trigger after update: of the main table 

    let a := Density;
let b := Temp;
ABV := first(select Table2 where Density = a and Temp = b).ABV

    Regards John

    • Alex_Mumm
    • 2 yrs ago
    • Reported - view

    Thanks John! I got both of your suggestions to work! This is what my actual code looked like in the end: 

    let a := Density;
let b := Temperature;
DesiredABV := first(select 'Alcoholometry data' where 'Hydrometer reading' = a and Temperature = b).ABV

    The table with the data is called 'Alcoholometry data', both tables have Temperature fields, and the density in the Alcoholometry data table is called 'Hydrometer reading'. I put this code in the Trigger after update section of the Edit Fields page of my Distillation table.

    John, the reason for using a numeric field is for database performance I assume? So if I had thousands of records with a formula field, they would all be trying to update at once each time the tale is accessed? Or would the formula only run when the record is first added?

    If I use a numeric field instead of a function, I can't change the colour of the box to show that it's being used for calculation in the same way that a function box is white instead of grey by default, can I? Are there any workarounds or layout tips to let the form users know to not enter data into that box?

    Another solution to avoid storing formulas in a large table would be to have fields in forms that only display the results of a calculation and don't actually store those results in the corresponding table. Is there any way to do that?

    • Alex_Mumm
    • 2 yrs ago
    • Reported - view

    Can anyone point me in the direction of any resources on database performance for Ninox? Mostly just curious. I'm not experiencing any performance issues yet.

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 3Replies
  • 542Views
  • 2 Following