Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
P
Panich_OkayNew Participant
posted in Formulas
Window function, group by

Hello all. I want to do a simple sum on a group by. In my example, I am building a recipe calculator and want to find out the proportion of cost for each ingredient out of the total cost. My formula would be : cost/sum(cost group by recipe) In sql this is call a window function but I am not sure if there is the equivalent in Airtable. I have a screenshot, but seems like I cannot upload it. Must be because I’m a new in the community. Anyway let’s say i have recipe_a ingredient_a $3 recipe_a ingredient_b $6 recipe_a ingredient_c $4 recipe_a ingredient_b $2 I want a new column that would output that ingredient_b stands for 1/3 of the cost of recipe_a A workaround is to do a pivot table but it seems like a lot of trouble for something simple (plus not a very scalable solution) If you have any input, I’ll gladly take some. Thank you Stay safe !

102
F
F
Frederico_NunesNew Participant
posted in Formulas
How to replicate a PARTITION BY count?

Hi everyone, I have a table where user X can endorse user Y on a particular skill Z. The final table will be something like this: John | Carol | Painting Carol | John | Drawing Fred | John | Drawing Bill | John | Writing Then I will have a separate table with user profiles, and I would like John’s profile to display “Drawing (+2), Writing (+1)”. The way I would achieve this using a pure SQL mentality would be to group by name, skill, do a count, and then JOIN it with my user profiles table. The way I’m trying to achieve it with Airtable is to create a new column on every row with the value {Name} & “(+” & something & “)”. This would essentially be doing a row-level PARTITION and GROUP BY COUNT, and would allow me then to link to these records from the Profiles table and select the unique. In this case, we would have repeated Drawing (+2), Drawing (+2) and Writing (+1) for John, and the desired result would be Drawing (+2), Drawing (+2) and Writing (+1). Unfortunately, I cou

50
F
R
RJ_SouthamParticipating Frequently
asked in Formulas
Date_time format not returning correct date

My goal is to have a DOB field concatenate with a name and calculated age field. Currently the concatenate works fine with CONCATENATE(Child_First,", “,Gender,”, ",Age) = Rohnan, Boy, 10M When I add DOB field I get Rohnan, Boy, 10M, 2020-12-15T00:00:00.000Z How do I get rid of the T00:00:00.000Z at the end? When I use the Date_time format I get gibberish. CONCATENATE(Child_First,", “,Gender,”, “,Age,”, ",DATETIME_FORMAT(DOB,‘dd-mmm-yyyy’)) = Rohnan Boy 10M Tu-000-yyyy What I want to see is: Rohnan Boy 10M 15-Dec-2020

122
R
J
John_KruegerNew Participant
posted in Formulas
REGEX Extract from Webflow Data

Hello, I’m writing with a question on how to set up a REGEX Extract. I assume it should be relatively straightforward but as a beginner I’m finding myself out of my depth. Any help would be appreciated! Goal: Extract a SKU number from a Webflow Ecommerce string. String Example: count: 1 height: 0 length: 0 productId: 618573ee195d39c0e1ba4199 productName: Test Board productSlug: test-board rowTotal: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantId: 618573ee82825fd59dc4b5bc variantImage: {‘fileId’: ‘6181a0f79b29435921241715’, ‘url’: ‘https://uploads-ssl.webflow.com/618058264546c2bae56f2188/6181a0f79b29435921241715_Screen%20Shot%202021-10-14%20at%208.46.20%20PM.png’, ‘alt’: None} variantName: Test Board variantPrice: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantSKU: A123 variantSlug: test-board weight: 0 width: 0 Data I want after “variantSKU:” - A123 I’ve already tried this formula but only saw “variantSKU:” returned, not the data I actually want “A1

40
J
M
Myra_RiveraNew Participant
posted in Formulas
Formula to create checkbox if within date rate

Hi! I’m looking for help writing a formula. I need something that will automatically check off boxes in separate fields for 2/9 through 2/21 if individuals will be present based on the entered arrival/ departure dates. “if x date is between the range of arrival date and departure date, insert checkbox, otherwise blank” For example if the arrival date is 2/12/21 and departure is 2/20/21, I’d want it to check off all of the dates in between, but not 2/9-11 or anything after 2/20

61
Justin_Barrett
A
Adam4New Participant
asked in Formulas
Create a Conditional Formula on Form Answers

I’m trying to put together a form for screening, however I cannot figure out a way to create a formula that would enable me to give an automatic output based on the form response. In short, if someone writes a symptom I want the last column to output a false, or a fail message. I have tried doing this but haven’t been able to. Here you can find a look into the form: And a formula I put together to try to get an automatic output:

191
ScottWorld
A
Accounts_AccounNew Participant
posted in Formulas
Conditional sum formula

I would like to create a formula that will: Sum the {Amt} fields only from records that have the identical data in another field {ticket/voucher #}. For example: Records 1, 7 and 14 have the identical data in {Ticket/Voucher #}. The formula would sum the amounts only from those records 1717.40 980.00 250.00 and put the total 487.40 in {Formula field}. The same would go for records 10, 12 and 15 and 11, 16 and 13. See link to image of my table. https://hctny-my.sharepoint.com/:i:/g/personal/office_highclasstravel_com/EesBZqFYxQpIvxnKzbAVLu8Bs6pO4BEkdNQWgzHui4Gj_g?e=4zSeTF

00
A
L
Leslie_MillerKnown Participant
posted in Formulas
Using https://eazyform.app/ for time field

I’m using Eazyform app to get time slots separate from the date/time field. I am having trouble then figuring out what formula to use to calculate total hours for employee scheduling. Additionally I would like to be able to view in a Calendar view.

67
L
WilliamPorter
WilliamPorterBrainy
asked in Formulas
Why does this not return a date value?

This ought to be easy. I want to display records on a calendar by a single date field, but the date field for display is calculated: It’s the DueDate if there is one entered, otherwise it’s CreatedDate. IF( DueDate=BLANK(), CreatedDate, DueDate ) Dates in, dates out, right? Nope. A formula field using the above formula cannot be used in the calendar, apparently because the result of the formula is not recognized as a date. Now the following DOES work: DATETIME_PARSE( DATETIME_FORMAT( IF(DueDate=BLANK(),CreatedDate,DueDate),"MM/DD/YYYY" ),"MM/DD/YYYY" ) Seriously? Is there not an easier way to do this?

157
WilliamPorter
S
Steve_RuqusInspiring
asked in Formulas
How to send an email through Gmail with attachment

I’m using a button with “Gmail” & ENCODE_URL_COMPONENT({Email}) & “&su=” & ENCODE_URL_COMPONENT({Subject}) & “&body=” & ENCODE_URL_COMPONENT({Body}) to send an email through Gmail it it works great, but how do I attach a file from an attachment field on the record? I really like the control of a button. I’d rather not use an automation. Thanks in advance!

382
S
K
Kaleidoscope_LeNew Participant
posted in Formulas
Comparing groups of data

Hi, I’m using Airtable to manage a conference. I have a table which has conference proposals with the authors’ contact info. Then I have another table with people that have subscribed to the conference. I’m trying to send emails to those that sent proposals in but haven’t subscribed. So I’m trying to accomplish that by comparing the emails from the Subscribed table with the ones in the Proposals table. These are the steps I followed so far: I linked the 2 tables and added a lookup field to the Proposals table to look for the emails of the Registered participants. In the Proposals table, the contact info is spreaded out in different columns, so I used CONCATENATE() to join all the emails with a separator. I realize that the lookup field returns an array while the CONCATENATE() function returns a string. Here is the tricky part. I’ve been trying to compare both groups of emails and it hasn’t worked. I need something that loops over the whole array/string and checks for matches and retur

00
K
K
Katherine_Trav1New Participant
asked in Formulas
How to get rollup to ignore NaN

I want to use a rollup to get an average of the existing numbers, but not all the fields in the column I want an average from have an existing number and return NaN. I want the rollup to ignore NaN.

1308
M
E
Ewald_Van_HaverNew Participant
posted in Formulas
How to show column headers in 1 column based on the values in those columns for each record?

Hi all, We have a table with records per customer, with multiple variables and their scores coming from typeform (auto-sync). The goal is to get out the 3 highest scoring variables and send the label of those variables to the customer. I can’t seem to find a way to get inside 3 fields the 3 column-names with the highest value of that record. Another solution I was thinking of, was when the auto-sync worked, to store the variables in different rows in another table but also with this I see no reason than to get the max 3 variables out of this table linked to the original one. Help would be much appreciated, thanks!

60
E
M
Myron_WaldmanNew Participant
posted in Formulas
Delay sending an email based upon another field

Here is what my formula looks like. I can not get Airtable to accept it. Can you say why? Current formula IF( ({Sales Rep}!="" ), AND( NOW()>={Followup Date}), “Delay Notification”) Proposed formula IF( OR( AND( {Sales Rep}!=BLANK(), {Followup Date}, NOW()>={Followup Date} ), AND( {Sales Rep}!=BLANK(), {Followup Date}=BLANK() ) ), “Delay Notification”, “Send Notification” )

00
M
A
Amber11New Participant
posted in Formulas
Calculate Renewal Date Using IF

Hey fellow AT users, I’m trying to set up a formula that calculates the renewal date of subscriptions based on if they are Monthly or Annual. I currently have 3 columns that I’m working with: Frequency, Billing Start Date, and Billing Renewal Date (which is where my formula is). I was testing the following formula: IF( {Billing Start Date}, IF( IS_BEFORE({Billing Start Date}, TODAY()), DATEADD({Billing Start Date}, 1, SWITCH({Frequency}, "/mo.", 'month', "/yr.", 'year')) ) ) The issue is that while it DOES show the next date based on the Frequency (i.e /mo. or /yr.), it’s not current. For example, a Billing Start Date of August 29, 2020, that’s set to renew monthly, is showing as September 28, 2020 instead of October 28, 2021 (since it’s currently 2021 and September is past). Does anyone know how I can get this to work? Do I need more columns for date comparisons or can this be done with just the 3 existing columns?

111
Justin_Barrett
C
Chattanooga_FunParticipating Frequently
asked in Formulas
Formulas not displaying correct math

The formula is simple in that I want field A multiplied by field B to = field C What happens is because field B is rounded due to the limit I put in the field because I just want two places after the decimal, it is calculating based upon the original math and not what the cell shows. For example. 51 x 68.33 = 3,484.83 but the cell shows 3,848.70 bc of the original amount being 68.3275 How do I get the field B to use the actual data in the cell instead of the original so that if someone not in the know can see that 51 * 68.33 = 3,484.83 Thank you in advance!

62
C
C
Claudio_BrenniKnown Participant
asked in Formulas
Counting words in a lookup field

Hi Airtable Community! I’m developing a base to manage a bike polo championship. So far so good, I could implement all the function that are required, but I’m stumbling on a formula that I can’t get to work. On this table I want to get the final score for both Teams. As for shown with the example on the last column which it was achieved with a Count field and a filter with the name of the Team ‘Test1’. That is not a solution for me, because what I want is to filter the count based on the name of the column ‘Team 1’ and not on a particular team name. I’ve tried to combine IF () with FIND(), IF(FIND({Team 1},{Which team scored?}),1,0), but then I get only a 1 or a 0. Does anyone have any idea on how I can then count the frequency of the string in the column ‘Team 1’ and than count it or Sum it? I’ve tried to add SUM() or COUNTA() in the formula, but unsuccessfully. I would very grateful if anyone can help me :winking_face: many thanks, Claudio

162
C
T
Ted_RogersNew Participant
asked in Formulas
Logic Formula for dates based on Date Range

Hello! My use-case may be unique, as I am project managing a movie theater. I have a Calendar view of all confirmed films by Date Range defined by a “Start Date” and “End Date.” Some films are on runs (typically Friday-Thursday ie Start Date 10/29/21, End Date 11/4/21), and others are single events (ie Start Date 10/29/21, End Date 10/29/21). I have written a formula to categorize films by status (“Coming Soon”, “On Screen”, “Closed”) as follows: IF( IS_AFTER( {Start Date}, TODAY() ), "Coming Soon", IF( IS_AFTER( {End Date}, TODAY() ), "On Screen", "Closed" ) ) My issue is that single events will appear as “Closed” on the day they open (duh). This status is easily fixed by setting the End Date to the day after it opens, however this yields two-day-long entries for single events on the graphical calendar, which is obviously misleading for our staff. Any recommendations for rethinking my formula (an IS_SAME function?), date-range definitions for the calendar, or an entirely different ap

133
T
P
Praveen_GNew Participant
posted in Formulas
How to pick multiple values from one table & Store in another Table multiple columns

Hello team, am facing an issue in getting multiple columns values from a table and store in different table with multiple columns For eg: In a legal system , we have an one case number and case was failed by the party. so again the party appeal in high court for the same issue. so different case number issued at high-count so if new appeal is happening on this case, then if user select the old case number then it will auto fill to the columns fields with the previous values, means if old case number is 200/2020 and other field like case subject, claim amount, etc once new case comes with new number (201/20221) when goes to appeal, if user select the old case number as 200/2020 it will select or pull all the fields such as case subject, claim amount etc

21
P
I
Ian_james_DuncaNew Participant
posted in Formulas
Concatenate date field displays other data

I am wondering if anybody knows a way to change the way the field for date created is displayed in concatenate? This formula reveals the date as follows: 2021-10-29T20:28:27.000Z - Task Detail - Task Assigned to - Projects I am looking for something a little cleaner like: 2021-10-29 - Task Detail - Task Assigned to - Projects IF(Projects = BLANK(),“No Project”,CONCATENATE("",{Date created}," - “,{Task Detail},” - “,{Task Assigned to},” - ", Projects)) cheers!

41
J
T
Tashina_ONew Participant
posted in Formulas
Formulas and Attachments?

So I have a base that has a lot of images as a part of it. I’m trying to have it so that in Table-3 if Option-A is selected from Table-2 then attachments from Column-A on Table-1 are displayed but if Option-B is selected then attachments from Column-B on Table-1 are displayed instead. However, I have tried everything I can think of to make this work and I can’t. I’ve tried multiple look-ups, formulas, everything I know how to do. I can not get a single column that displays the attachments and switches according to the selected option. Help, please.

652
T
A
Aysia_SaylorKnown Participant
asked in Formulas
How to configure a conditional, combined DATETIME_DIFF formula

Hello, What would be the easiest way to configure a formula that combines all of these? IF({Request Type}=‘New’, DATETIME_DIFF({DCML Issue Date Lookup},RequestDate,‘days’) IF({Request Type}=‘Major Update (Procedure Change)’, DATETIME_DIFF({DCML Rev Date Lookup},RequestDate,‘days’) IF({Request Type}=‘Minor Update (Formatting, Spelling, Grammar, etc.)’,DATETIME_DIFF({DCML NCC Date},RequestDate,‘days’) IF({Status}=‘Incomplete / Closed’, DATETIME_DIFF({Status Last Modified Date},RequestDate,‘days’) I tried a formula that combined the first two, and the system accepts it, but it doesn’t return anything. Thanks :slightly_smiling_face:

82
A
A
April_KastnerKnown Participant
posted in Formulas
Totals from a field that includes a range

I have a field that includes a number range for each record: “5-10”, “15-20”, etc. On another table, I would like to do a rollup that adds together all the first and last numbers in those ranges, displaying “20-30” essentially. Is there a rollup formula that can find the first and last numbers in a range? The only thing I’ve thought of so far is separating that range into 2 fields and then combining them into the other table with a concatenate, but I’m hoping there’s a solution that keeps the range in a single field

10
A
M
MSN-ISI_EditorNew Participant
posted in Formulas
Merging tables that have autocount field, making new autocount that resets to zero every year

I am managing ad contracts and needing to include the year of their creation date plus the contract number in the first column. I use a formula that pulls data allowing me to have at-a-glance info for tracking our ads. Example: the column is called CAMPAIGNS with the following formula: CONCATENATE({Contract #}, ": “,Account,” - ",{Ad Size}) This results in a field that might read: “21 004-BQEA: Bruce’s Quick Eats - 1/8 page” When blazing through production, I can see the year the contract started, that it was the fourth contract of the year, the account code and full name of the client, and finally the ad size they ordered. We also use the 21 004 to populate part the invoice numbers for billing in each ad cycle. (Contracts generally cover multiple ad cycles). This also informs our file-naming for ad image files, so we can find accompanying assets quickly and easily. I intially built contracts for each year in their own tables, because I needed to reset the contract number to zero every

00
M
N
Nicholas_BoehmNew Participant
posted in Formulas
REGEX Extract - Help with Formula

Hello folks, I am trying to extract two numeric values from text. One of the values is directly after “$” and varies in length. The next is directly before a word “endpoint” and also varies in length. Here is an example of text: • $24,200 + Professional Services ARR • 650 endpoints • Blah came to Blah wanting to move away from Blah • Huge shoutout to Blah for helping with Blah and answering all their technical follow up questions. In this instance, I need 24,200 and 650 each in their own columns. I am totally lost on how to do this and have been trying for hours. Any help is seriously appreciated.

182
B

Badge winners

  • First Reply
    GegHhas earned the badge First Reply
  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
Show all badges
Terms & Conditions