Inequality Join

[Linette]

Hi guys,
First time using Airtable, been trying to set it up for a nutritionist clinic and I ran into an issue. Hope I can receive some guidance here.

I have to tables
table1 has user_height, user_weight and user_bmi (calculated based on the height & weight)
table 2 has the bmi ranges min_bmi, max_bmi, bmi_status.
E.g. min_bmi = 1, max_bmi = 18.49, bmi_status = underweight
min_bmi = 18.5, max_bmi = 22.9, bmi_status = normal
min_bmi = 23, max_bmi = 27.49, bmi_status = overweight
min_bmi = 28, max_bmi = 100, bmi_status = obese

In order to retrieve the bmi_status, the calculated bmi needs to fall within the range of min_bmi and max_bmi.

How do I join table1 and table2 to get the bmi_status? Is it possible?

Expected output:
table1: user_id, height, weight, bmi(calculated), bmi_status (extracted from table2)

[Databaser]

Hi @Linette and welcome to the community!

Have you considered using a formula field to calculate the BMI status? Eg by using a SWITCH()?

[kuovonne]

Welcome to the Airtable community!

It sounds like you are coming from a traditional database viewpoint. While Airtable is a relational database, it does not perform joins the same way. Instead, all joins must be created with linked record fields. You can fill in linked record fields manually or with custom code, but not with a traditional query.

I agree with Databaser’s suggestion to use a formula field. However, a SWITCH() will not work in this situation because SWITCH works with exact values not ranges. This case calls for converting a number to a range, which can be done with a nested IF. If you are unsure how to create a nested IF formula, my app Ready Made Formulas can build the formula for you.

[Alexey_Gusev]

Hi,
You can use SWITCH() in following way (сonsidering TRUE=1, FALSE=0)
SWITCH(
(weight>=18.5)+
(weight>=23)+
(weight>=28),
0,‘underweight’,1,‘normal’,2,‘overweight’,3,‘obese’)

But that depends on future table users. Nested IF is better for reading and understanding

[kuovonne]

Alexey_Gusev:

You can use SWITCH() in following way

This is an interesting and creative way of using SWITCH. Thank you for sharing it. There are many different ways of doing things in code, and I enjoy learning new methods, even if I don’t see myself using them.

[Databaser]

Alexey_Gusev:

You can use SWITCH() in following way (сonsidering TRUE=1, FALSE=0)
SWITCH(
(weight>=18.5)+
(weight>=23)+
(weight>=28),
0,‘underweight’,1,‘normal’,2,‘overweight’,3,‘obese’)

@Rose_K , is this something that could/should be added to the Airtable documentation for the SWITCH() function?

[Dominik_Bosnjak]

It might be a good candidate for some tutorial content but this is vintage JS in all its glory, first-order functions and whatnot.

You wouldn’t even have to dance around this kind of usage in the actual language. You’d just write:

switch(true){
  case(x > 2 && x < 10):
  //stuff
  break;


}

-and so on.

[kuovonne]

Databaser:

is this something that could/should be added to the Airtable documentation

I’m not Rose, but based on my experience as a technical writer, I think that this specific example would not be a good one for the documentation.

The current documentation already has two examples, which is more than most functions have. The documentation also already states that the first parameter is an expression. Maybe the documentation could be altered to include a first parameter that is a more complex expression, but then you are moving away from documenting the function and into teaching how to code. Teaching how to code is helpful, but not at the expense of cleanliness of the documentation.

[Rose_K]

kuovonne:

Maybe the documentation could be altered to include a first parameter that is a more complex expression

@Databaser @kuovonne
I think you both raise good points. Our current documentation briefly explains what an expression is, but could definitely be expanded to provide some examples of more complex expressions. Thank you both for the suggestion!

[Rose_K]

Just wanted to follow up here and note that our documentation has been updated with an expanded “Expressions” section. Hope that helps highlight what an expression is, and how folks can go about building them :star2: