Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 31, 2021 05:09 AM
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)
Aug 31, 2021 07:51 AM
Aug 31, 2021 09:54 AM
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.
Aug 31, 2021 09:22 PM
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
Sep 05, 2021 06:30 PM
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.
Sep 06, 2021 12:26 AM
@Rose_K , is this something that could/should be added to the Airtable documentation for the SWITCH()
function?
Sep 06, 2021 05:56 AM
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.
Sep 06, 2021 06:12 AM
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.
Sep 07, 2021 08:22 AM
@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!
Sep 21, 2021 10:14 AM
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: