Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
W
Wenjia_QianNew Participant
posted in Formulas
Filterbyformnula python Airtable API

Hello people, I’m really new to all this, so this might be a stupid question. I want to retrieve AirTable data from my table, filtered by two variables (problem, severity). Based on these two values, the code should give back individual help offerings. But it seems that I can only filter by one variable using “filterbyformula”. Using two it gives back that the request is invalid. Is my syntax just wrong or is it even possible to filter by two variables? data = { "filterByFormula": [f"problem = '{mood}'", f"severity = '{severity}'"], "fields": ["help"] } thank you so much!

414
GavinA
T
TechArkNew Participant
posted in Formulas
Emoji color is not appearing in windows 7 chrome/edge old browser

Hello Airtable Team, I have made conditional formula with emoji icons. But I have got one issue in the old OS/Browser. some old OS in the chrome/edge old browser icon color is not appearing. Can you please provide a list of supported browsers lists and OS? OR Any alternative solution for that? Thanks

10
T
B
Bartek11New Participant
posted in Formulas
The formula IF there is something it is supposed to show “✔️” or “❌”

I would like the formula to be able to identify the client by field {Rodzaj usługi}. If the client would be (in the {Rodzaj usługi}) “Fotografia ślubna” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Odbitki}, {Album}, {Galeria internetowa}, {Selekcja zdjęć}, {Zwiastun}, {Obróbka zdjęć} fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ". When the client would be (in the {Rodzaj usługi}) “Film ślubny” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Film wrzucony na dysk}, {Teledysk ślubny}, fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ”. This formula works for two {Rodzaj usługi}. I would like to add one more “Service Type” on the same terms as the previous ones. Namely, “Ślub cywilny” If the client would be (in the {Rodzaj usługi}) “Ślub cywilny” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Galeria internetowa}, {Selekcja

03
TheTimeSavingCo
P
Paul_LNew Participant
asked in Formulas
Finding the Top 5 values from several fields

Hi I’m creating a tournament ranking system. I have a table for each tournament played and within each table, I list all the players and their individual scores and then their ranking position and ranking points for that tournament. On a separate “rankings” table, I list all the players and lookup their score, ranking points from each of the tournament table. I can then add the total ranking points across all tournaments and sort them by male/female/adult/senior categories. However, the end of season rankings after 8 tournaments will be based on a player’s best 5 results/points scores out of the eight events. Therefore, I need to take the 5 largest (not necessarily unique) points from the 8 fields. I’ve looked at various formulas to try and find the answer (similar to LARGE function in Excel), but I’m stumped. Does anyone know how to do this? Is the answer to structure my tables differently? I feel database design is always my downfall :frowning: Thanks in advance.

264
P
C
CunwalkerNew Participant
posted in Formulas
How to combine or calculate two multiple-select values

Is there an easy way to ‘calculate’ or combine Tags and Score, so I get row 1: “A7, B6, C5, D4, E3, F2, G1” and row 2: “C7, G6, F5, A4 E3, D2, B1”

31
Kamille_Parks11
M
Marijke_CortnebNew Participant
posted in Formulas
If cell contains 'search', 'google' 'bing' (etc) then return 'search engine'

Hey All, I’m struggling to build a formula in airtable for the following. I have a list of free text data in my column Source, where customers have put in on where they have found us, ranging from ‘Search engine’ to ‘Google’, 'Bing, ‘My friend James’, ‘A friend’, etc… I want to create a column that groups several of these free text entries together, so for example for ‘Search engine’ and ‘Google’ we return ‘search engine’ for all entries that contain the text ‘search’ or ‘google’ in column Marketing channel. (I know I won’t be able to capture all, but I can continue to build on this formula to include all) I tried a Switch formula, but that requires the text to be exact, and not ‘contains’. I tried an If Find formula, but i’m struggling to get the format right and I get a lot of errors. Could anyone help?

161
Kamille_Parks11
P
petebockenNew Participant
posted in Formulas
Vehicle Fuel Log - calculating miles driven between fill ups and MPG

I’m creating a vehicle fuel log. I’d like to calculate the miles driven between fill ups (trip). After that, then I will be able to easily calculate the miles-per-gallon (MPG). I know I could easily accomplish this by adding a column for “start odometer” and “end odometer” and then simply subtract the two numbers. But that is extra work each time I create a new entry. I’d rather just add the odometer reading at the time of the fill up and have it auto-calc based on the previous (above) entry’s odometer field. I know that since Airtable is not just a spreadsheet you can’t do what you’d do in Excel with something like “=sum(b2-b1)”. Also since the sort order could change, etc. So I was thinking, what if I setup an auto-increment ID, that way I can reference to the previous ID’s odometer field. Since the previous ID would be one less than the current, that would work, unless I delete an entry. So that may not be the best option. Is this possible or am I just stuck with manually typing the

6312
B
C
C-B_2021Participating Frequently
posted in Formulas
Formula that modifies after a set date

Hello everybody, I have been using a formula to generate invoice numbers, but is has been producing some overly similar looking numbers which confuses clients. Current formula is: “INV” & DATETIME_FORMAT(CREATED_TIME(),‘msw’) What I’d like to change it to is: “INV” & DATETIME_FORMAT(CREATED_TIME(),‘wms’) The only problem is that if I change it now, all of my previously issued invoice numbers change too. Is there a way to perform the below? If date is on or before 18.04.2022 then “INV” & DATETIME_FORMAT(CREATED_TIME(),‘msw’) else “INV” & DATETIME_FORMAT(CREATED_TIME(),‘wms’) Your advice would be very helpful! Thank you.

84
ScottWorld
A
AtomChat_SupporNew Participant
posted in Formulas
Getting error while adding formula field as "Formula fields should be configured in the field menu dropdown"

Getting error while adding formula field as “Formula fields should be configured in the field menu dropdown”. Please find attached video link for the same - drive.google.com formula-field-error.webm Google Drive file.

1021
ScottWorld
J
Jeff_MooreNew Participant
posted in Formulas
How to Remove Empty Value Option in Multiple Select Field Using a Formula

I have a multiple Select field that takes entries from a form. Often times it comes in with one of the options being empty or null in value. See Screen shot. I’m trying to create a formula that removes this empty Value and the “,” for it. I’ve used all of the ARRAY formulas and they aren’t working. Example: Multiple select Has: ,17,18,19 I want the Formula Field to be: 17,18,19 (removing the first null value and the one between 18/19) Screen shot shows both fields and that the empty field remains.

901
Alexey_Gusev
D
David_AlonsoParticipating Frequently
posted in Formulas
Number of attendes to an event till now

Hi There!!! I am trying to figure out how to solve an issue that it doesnt seem very difficult but i am not able to. I organize groups to go to the theatre. At the moment someone fills up the form to attend an event, I would like to check if there is still tickets available and if not, send him / her to the wiating list. For that i woukd need the summary of tickets sold at that moment and this would be te trigger of an automation. Is that possible? Just want to have a field called tickets sold that summarizes the number of tickets sold until the moment of a new record is created.

21
TheTimeSavingCo
Karlstens
KarlstensBrainy
asked in Formulas
Parse JSON File via Formula

Hi All, I’m parsing JSON files into Airtable Long Text fields, and then setting up Formula Fields to relay the contents of that JSON. For example, the JSON data looks similar to this; {"NAME":"Colour","VAL":"Red","ID":"1"}, {"NAME":"Shape","VAL":"Square","ID":"2"}, {"NAME":"Size","VAL":"500","ID":"3"}, {"NAME":"Scale","VAL":"2","ID":"4"} At the moment, I’m using a fairly hacky method to display the data in a formula field, for example; ABS(SUBSTITUTE( RIGHT(MID({Log Content},FIND("Size", {Log Content}),17),4) , '"','')) But this is buggy as. Obviously if Size is more or less than 3 digits, the output fails or works simply by chance. What would be the correct way to write a formula that retrieves the entire value between the inverted commas, regardless of the length?

17113
kuovonne
M
Mary_WilliamsParticipating Frequently
posted in Formulas
Is it IF, SWITCH, or Count?

I apologize ahead of time if I’ve made this way too hard; I’m a newb. I tried to search for this question already being asked, but I don’t think my search terms are right. Feel free to put this under another topic if it fits there. I’m trying to create a database that automatically ranks the nutritional quality of commercial pet food. I have 4 columns indicating the nutrient levels in each product (protein, carb, fat, & phos), with a number hand-entered as a percentage of calories (doesn’t need to display the percent sign). I created 4 IF function columns to categorize various number ranges into “Good”, “Bad”, and “Borderline”. So now each row contains 4 values in any combination. (eg: Good, Bad, Good, Borderline) So here’s where I’m lost; now I’d like to evaluate something like the following: If there are 3-4 “Good” returns, and 0-1 “Borderline” returns, I want it to display “EXCELLENT” 3 “Good” & 1 “Bad” = “GOOD” 2 “Good” & 2 “Bad” = “FAIR” 3-4 “Bad” & 0-1 “Good” or “

113
kuovonne
V
Vlad_MorosanParticipating Frequently
posted in Formulas
Formula for displaying members of a household

I’m working to develop a directory in Airtable that will also export pages to PDF, and I need some help with a “conditional formula” (don’t know if that’s the correct terminology) that will pull in certain records in what I think is a self-joining relationship (but I’m not sure). I started by converting a Filemaker Pro solution to Airtable, but then for various reasons set forth in this article https://support.airtable.com/hc/en-us/articles/360007520454-Combining-multiple-tables-into-one-table-with-multiple-views, I decided to combine the several tables into one. There are 3 essential views: Individuals Households Directory display (grid and gallery views) Each household has a unique number and {householdname}, which identifies to which household an individual belongs. In the household, there are “positions” - head, spouse, child, and other adult(s). In the Directory display view I would like the following fields: Photo Household occupants: ({lastname} {, " {firstname} [head] & "

208
V
A
Anna_MoralesNew Participant
posted in Formulas
How to rollup records in different branches of an Org chart?

I want to assign a value to each record based on the 2nd level record it is related to in the org chart. This question is similar to the question posed here but instead of adding salaries, I want to have a column where everyone under “Tamara Muro” is assigned “Tamara Muro”, everyone under “Keith Shearin” is assigned “Keith Shearin” and so on. One of the commenters suggested using a script but I wouldn’t know where to get started with that. Is there not a way to use a lookup with a looping function to keep climbing the org chart until it lands on one of these names? I also tried this solution but with all the levels in my org chart, this isn’t sustainable. Thank you for your help!

43
TheTimeSavingCo
D
DalkatNew Participant
posted in Formulas
Compare comma-separated emails across two cells and identify the ones without a match?

Hey all, I’ve looking through a few others threads and haven’t found a solution so I’m hoping the hive mind can help: a bit of a tricky scenario, but I’m essentially looking for a way to compare comma-separated emails across two cells and identify the ones without a match. Scenario: I have an airtable of events, where each event has: a column called “invitees” (people invited to an event), where multiple email addresses are separated by commas within a cell – the values are a string a column/cell of emails called “RSVP’s” (people who have already replied ‘going’), where multiple emails are separated by commas as well – the values are a array that rolls up data from another table. So we’ve got two comma separated cells, one is a string and one is an array. I would like to send reminder emails only to the people on the invitee list who did not already RSVP (aka, remove RSVP emails from the invitees list, then email only the ones left over, aka the ones without duplicates). Is there a w

143
ScottWorld
S
Samuel_DuncanInspiring
asked in Formulas
Formula with formatting for cemetery database

Airtable Grid view - Airtable Explore the "Grid" view on Airtable. I have created this database to catalog those buried in a cemetery. I understand that I can’t use formatting in a formula–unfortunately. In a perfect world, I need to format “short name” in quotes (or bolding) and “maiden name” in italics. Could any of the experts suggest a different approach if indeed formatting is not available in formulas? Thanks, Sam paynegap.info

342
S
K
Khuned_SachdevInspiring
asked in Formulas
DATETIME_DIFF Incorrect Months

Dear Community, Need help in trying to calculate the difference in months and days between two dates. Q1: is it even possible to calculate difference and return result in month and days? Or is it only possible in either months OR days? Q2: I entered a formula to only display difference in months, but choosing Jan 1 to Dec 31 is showing 11 months, and not 12 months. I get it that is subtracts and does not include Dec 31 in it’s calculation, but selecting the employement period dates (in our case) to be Jan 1 to Jan 1 would technically create confudion to the employee, as the period ends Dec 31. Any work around this. Super thanks in advance.

157
Databaser
M
Melissa1Inspiring
posted in Formulas
Quantity x price formula in the same row

Hello Airtable community! I would like to be able to choose the quantity of a product A or B ON THE SAME ROW and multiply this quantity with their indicated unit prices. Is my approach right? do you have another solution? How to multiply the quantities (by products entered) and unit price (by products entered) to obtain a TOTAL Is there a formula? Thank you

204
M
R
Robert_PaigeNew Participant
asked in Formulas
How to merge many fields with separators, but remove separators when cell is blank?

Hello Airtable Community, I am new to Airtable and I am struggling with a simple CONCATENATE formula: The DESIRED RESULT column is what I am trying to achieve. You can see that anytime I try to add “" as separators, but if one of the cells is blank then not to have the "” in it’s place. I tried this formula: CONCATENATE(CompName,"_", Length,"_", Dimensions,"_", Date,"_", Code) Not sure if CONCATENATE is the right solve. I have been able to do this in Google Sheets with this formula: =JOIN("_",FILTER(B2:F2,B2:F2<>"")) Been racking my brain for a whole week on this. I appreciate any help or guidance.

284
R
G
gretchen11New Participant
asked in Formulas
IF Formula with two options for returning a given value

I’m hitting a wall, but got at least halfway to the solution! I have two scenarios that I’d want to return a given statement. Option A: Someone has a “Yes” in FCIHSEP and a “Fall 2021” in Cohort Option B: Someone has a “Yes” in FCIFEB and a “Spring 2022” in Cohort Either of those should meet the criteria to return the “ :white_check_mark: Paid” statement. I got it to work for Option A, but I’m a bit stuck on how to build in Option B as an alternate way to satisfy the statement. What I have so far: IF(AND(FCIHSEP = “Yes”, Cohort = “Fall 2021”),“ :white_check_mark: Paid”) Any help is greatly appreciated!

30
G
E
Elizabeth_StockNew Participant
posted in Formulas
If statement for blank dates, else countdown days to due date

Heya, Can someone help me with writing if statemtent for my blank ‘deadline dates’, if there is a no date, I want my ‘Time Left Helper’ to say ‘On Going’ deadline rather than error. ‘Days’ is my countdown field, so i think I need to say if deadline date is blank then “on going” else, countdown days. Then I’m not sure how to transfer this part to the Time Left Helper? Maybe best to leave it to appear as blank and if blank then the time left helper writes ‘ongoing’ i am not sure… Days formula: (WORKDAY_DIFF(NOW(),{Deadline Date})-1) Time Helper formula: IF(Status = "Complete", "✅", IF(Days = 0, "Due today ⏰", (ABS(Days) - (2*(Days < 0))) & IF(Days > 0, " days left ⏳", " days overdue 💥"))) thanks!

31
Databaser
S
Steven_WhitingNew Participant
posted in Formulas
Extracting a Username from a Social Media Platform URL

Hi I’m quite new to formulas, but I’m trying to find a formula that would extract a username from a Social Media Profile URL and return the username with an “@” in front of it. E.g. Taking - https://www.instagram.com/originalsteve/ and returning @originalsteve I’ve been reading up about REGEX functions, but it’s a little but it seems quite complicated. Would anyone be able to help? Thanks

1923
TheTimeSavingCo
W
WildfireSnowNew Participant
asked in Formulas
Adding Formulas to come up with final score

Hello all I have a single formula field called Threat Score I have multiple single select fields with answers of Yes,No, Na. I am trying to have a final threat score after all the single select fields are answered. For example Switch({Is there leaves, needles or other vegetation on roofs, gutters, decks, porches, stairways, etc.?}, ‘Yes’, 0, ‘No’, 4, 'Na’, 4 ) Switch({Is there any dead and dying trees, branches and shrubs or other plants adjacent to or overhanging buildings?}, ‘Yes’, 0, ‘No’, 4 'Na’, 4 ) If the user chooses no for both fields the formula field should have a sum of 8. How would I combine multiple fields into a single formula to come up with a final sum. Thank you Josh

122
W
M
Mike_TulloNew Participant
posted in Formulas
Split multiple items in lookup field into separate text columns

Hello! I am looking to add a formula that would split this lookup field that can contain multiple items and separate or split into separate text columns for each “planning unit”. The planning unit column can have no items or up to 10ish items. Thanks in advance for the help!

41
V

Badge winners

Show all badges
Terms & Conditions