Re: Inequality Join

2439 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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)

11 Replies 11

Hi @Linette and welcome to the community!

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

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.

You can use SWITCH() in following way (сonsidering TRUE=1, FALSE=0)

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

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.

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

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:

  case(x > 2 && x < 10):


-and so on.

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.

@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!

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: