Skip to main content

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)

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.

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

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



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.


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?


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


switch(true){

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

  //stuff

  break;





}

-and so on.


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



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.


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!


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

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 🌟


Where was Airtable when I needed to learn a bunch of higher-order function patterns? Tricking me into just writing them would have worked way better than reading books with unnecessarily mean titles. 🥲


Either way, this new section is a super convenient reference for anyone.


EDIT: @Rose_K Seems like a good candidate for a featured resource spot, as well? Unless it’s already there and I’m just not seeing it.

Where was Airtable when I needed to learn a bunch of higher-order function patterns? Tricking me into just writing them would have worked way better than reading books with unnecessarily mean titles. 🥲


Either way, this new section is a super convenient reference for anyone.


EDIT: @Rose_K Seems like a good candidate for a featured resource spot, as well? Unless it’s already there and I’m just not seeing it.


@Dominik_Bosnjak Thanks for the call out!


This article is included in the featured resources currently (it’s the “Formula field reference” article). I just updated the description for that article to include a reference to expressions for those searching for that term. 🙂

Reply


Terms & Conditions