Formulas

HiI am setting up a zap connecting stripe to airtable.I want the zap to find a record in Airtable that matches the stripe data in 2 fieldsTo do this I need to enter an AirTable search formula into the zapThe 2 Airtable columns I want it to search are “Email Address” and “Subscribed to”So let’s say that a change is made in stripe that fires the Zap.The zap will search Airtable for a record where both the “Email Address” field and the “Subscribed to” field match the data in both those fields in stripe. The combination of the 2 fields indicates it is the correct record to be updated with the changes made in Stripe.What search formula would get this result for me in my Zap?ChatGPT helped me get to this formula:"{Email Address} = {Stripe Subscription}[customer.email] AND {Subscribed to} = {Stripe Subscription}[items.data.0.price.nickname]"but I'm getting the message"Failed to find or create a record in AirtableError from halted execution: The formula for filtering records is invalid: Invali

I looked at the posts about progress bar formulas and I have used them but my result is not looking correct. In the post, they said you need to use chrome and not safari I am using the latest version of chrome and its sill not working. Can some one please help me.

 Hello everyone,I have to create a calendar of course sessions and I have a table in which I enter in one column the date, in one column the start time and in another column the end time because different days might have different times.I use the formula DATETIME_PARSE(CONCATENATE(DATESTR(Date),' ',{Start Time})) to create a date & time that can be displayed on the calendar. However, the result of the formula returns the wrong time, increased by one hour.Am I doing something wrong or is it a bug?Thanks for any assistance!

Hi, new to Airtable and I'm trying to parse out specific records from a lookup field and have Airtable display them in a new column. In one table called "Downloads",  I have a lookup field called "Guests" that is pulling records from a separate table called "Guests". The lookup field returns the names of all the "Guests" for a given entry. There a multiple guests per each entry. In the Guests table there is a variety of data about each individual guest (demographics, affiliation type, etc.). In my Downloads table, I want to create a new column that displays all the Guests names of a certain type (e.g. only Guests that have "Affiliation Type" = "Media"). How can I do this in Airtable? My attempts to create a formula aren't working as I can't get Airtable to return the Guests names. Thanks!

Hi everyone, I’m trying to figure out how to calculate the Mode (ie. the most frequently occurring number in a series). I need this for the purposes of turning crowdsourced data into something useful. For example, if I ask 10 employees of one company how many days of holiday they get a year, I want to return the value most frequently cited, not the average (mean) because that would be inaccurate. In google sheets, it’s simply the function: MODE() but Airtable doesn’t have this. Does anybody know how I might do this?

Hello, Is there a way to configure this formula to only return a time during specified business hours (7:30-4PM)? DATEADD(WORKDAY({Last Modified},1)) Thanks :slightly_smiling_face:

Dear Community,I am faced with the following problem:I want to pull data from a different table when the status in each table is the same.I am creating a text.Say the status in Table 1 is "First Text" then it should check in Table 2 for the row named "First Text" and pull the information from a specific cell in that roll.In sheets it looks like this: =IF(A4=Table2!A2;Table2!B2;IF(A4=Table2!A3;Table2!B3);0)How can I replicate that in Airtable?Thank you

I'm trying to clean up my text with Regex. Some ranges are listed as 3.0 - 3.0 which I want simplified to just 3.0 whenever the two numbers match. I tried using regex101 and MDN references, but Airtable seems to use some different syntax than either of those.The following expressions should be matching but aren't. Does Airtable not support these "back reference" expressions such as \g{n} or simply \n(\d.*) - (\g{1})(\d.*) - (\1)Desired Result:3.0 - 4.0 ==should remain 3.0 - 4.03.0 - 3.0 ==should change to 3.01.5 - 1.5 ==should change to 1.51.0 - 3.5 ==should remain 1.0 - 3.5 

Hi everybody !I would like to know how to add in column that features only the similar values found in columns A and B ?(In my case the A and B columns are Lookup from two différents tables but I guess it works the same).Thanks a lot for your help !

Hey guys, I've got a new challenge in my quest to create a base for tracking personal finances.  A quick review:TablesCategories (I call them buckets)TransactionsDesignationsTransactions are either entered manually or bulk imported via csv from my bank.  Each transaction is assigned at least one category via the designations join table.In the Categories table, I have assigned each category a monthly budget.  Then I roll up the designations amounts filtered by month (this is my current setup - I'm working on a different option).  Now, you would think that a simple third formula field for [ budget - total spent (roll-up) ] would give me the balance for each of my budgets for the month.  Should tell me whether I've received all my salary for the month or if I've gone over budget for eating out, for example.Here's my confusion:  there are situations in which categories may have counter-intuitive balances.  For instance, we have a category for gifts receiv

Hi there, I have a single select field name "Département" and I want to concatene the 2 first letters (later for automation).I have a problem with this formula :IF(  OR(    {Département} = "75",     {Département} = "78",     {Département} = "77",     {Département} = "91",     {Département} = "92",     {Département} = "93",     {Département} = "94",     {Département} = "95"  ),   LEFT({Département}, 2),   "")Erreur : Missing opening parenthesis "(".Can someone help me ?

Hello!I have created an estimates / intakes base for a client. One of the goals of creating this was to have an automation that sent a confirmation email to the customer once their estimate was scheduled. When I tried to insert the Estimate Date/Time field directly into the email automation, it was not working. Someone suggested creating a different field that makes the formula more 'readable'. However, for some reason, the date is not converting properly:As you can see, while the time converts properly, the date is way off. Here is the formula I'm currently using: Can anyone identify the problem for me and offer a solution? Thanks in advance! 

Hi there,We recently decided to use Airtable as our payroll software, everything is fine but there's one problem and it's about the automatic calculation of overtime per week.We found a way to calculate those hours but it involves manually copying the sum of all the hours worked per week in the "Total heures travaillées" (total hours worked) column, we find this number of hours in the "sum" of the column "Durée" (duration). Thus, copying this sum in the column "Total heures travaillées" (total hours worked) allows us to see the total of overtime hours per week in the other column "Heures supplémentaires" (Overtime hours).In conclusion, we have to manually copy all the total hours worked per week to have the number of overtime hours so I wanted to know if there's a way that overtime can be calculated automatically without having to do anything and that we can delete the column "Total heures travaillées" (total hours worked)?For your information, in France employees can work a maximum of

Hello, I am having to add hours ("duration") to a field and outputting to a new field where a formula would be developed, however, I am not sure how to format the formula for the duration; here is my formula: IF(FIND("Algebra I", {Requires Additional Hours?}), {30:00},SUM({Total Hours Required}, {30:00})) "{30:00}" in both instances comes out red, meaning it is wrong, however, what I need to do is add 30 hours to the "{Total Hours Required}" field. Basically, if Algebra 1 is found in this drop-down menu "{Requires Additional Hours?}", it takes the value within "Total Hours Required" and is supposed to ad 30 hours and output it to this field. If Algebra 1 is not found, it stays at 30 hours, hinting at the first "{30:00}" in the IF() formula. Thanks in advance!

I have to regularly place Facebook/Google pixels on webpages and was wondering if there's a way to track via a formula, button, or script whether the pixel on that webpage is working or not. I have a field in the table for the URL the pixel would be placed on. 

I have a formula field that creates an email address for me, but when there's an extra space after someone's last name it returns an extra "." and breaks my automation connection to create the email. Ex. sandy.castellano.  --- it should be sandy.castellano I thought adding TRIM to the last name would fix the issue but I keep getting an error code. Can anyone help with removing extra spaces in the last name? Current Code: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(  (REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(LOWER(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(first_name & " " & last_name, ",", " "), "'", ""), "  ", "&nbs

Hello,sorry, if I ask a question that might have been asked before. I am creating a simple private bookkeeping table and would like to build a SWITCH function that stamps the DATE when a value changes ("paid" / "open").Furthermore, I have tried the TODAY() function, but it updates every day. The LAST_MODIFIED_TIME() works, but if I change the value of the field, it will automatically update again. I would like to build a function that stamps the date only once, after the value "paid" / "open" changed for the first time.This is the formula I used so far:SWITCH(Status, "bezahlt", LAST_MODIFIED_TIME()) Thank you very much!

Does anyone else keep tracks of birthday on Airtable? How do you filter it so you can see them in the new year? I typically filter them out by months but just realized that everyone that filled it in last year will now show up this year. TIA!

I am trying to make a formula that takes the date/time from a previous column, and if the time is in the p.m, display the current date, however, if the time is in the a.m, to display the previous day's date. Attached it my current formula, which works for the p.m. times, but shows a mixture of 'Invalid Date' and an oddly specific time (see photos)Any ideas what I'm doing wrong?  

Hi allI only know basic HTML so the Functions field in Airtable to me looks like hieroglyphics.In my events company, we require people to complete forms to update their existing data. I followed this tutorial: "Use a form to update an existing record" https://www.youtube.com/watch?v=BkAY7OHEgdc - and got the 2 tables working with automation.However - if I want to apply this method to other bases, I'm worried because I don't understand the purpose of any of the code I've written. Can someone explain what is happening/the purpose of the following (broken down to baby level please eg. what does '' do in the code? what about &?):'https://airtable.com/shrPd1axJx1k3Yg6u'&'?prefill_Guest%20ID='&RECORD_ID()&'&prefill_Guest%20Name='&ENCODE_URL_COMPONENT(Name)&'&hide_Guest%20ID=true' Happy to read material if anyone wants to point me to any resources. Thank you.

Either I've stumbled upon a great bug or I'm a complete idiot. If history is anything to go by let's assume the latter.In a greater context I'm using a formula to check if TODAY() is in the range of some dates.Sometimes the date is from an automatic Created Time field, other times it's from a regular date field. When I check the Created Time (doesn't matter if the field is with out without a timestamp) on a record created moment ago against TODAY() I get a false.I used it in a long formula, but boiled down to it's minimum it just doesn't work. IF(TODAY()={Created Time},"1","0") The above returns 0 on a record created 15 minutes ago.If I ask it to check a date field with today's date inserted, then it returns 1.What's going on and how can I work around this?

Hi all,I am trying to extract the mean/average of a set of dates through roll-ups. I am able to calculate the min or max date through the use of MIN(values) or MAX(values), but AVERAGE(values) returns NaN.Any suggestions? (thanks in advance!) 

Hi all, I'm trying to set up a HTML button with a link that includes a variable "Name" (name of the product) like this: I tried adding " " to make everything except &Name& green but it didn't work. Is there a way to put HTML in the formula and use variables? Surely I'm missing something. Thanks!  

I can't seem to figure out how to loop a concate formula. I have 2 columns: Show Name and Episode Number. Show is single select and Episode is multi. My desired output is a string that loops the show name before the episode number; instead I only get Show Name, then a list of episodes with a comma deliminator.The desired outputs are the names of linked records. Example base below. Any help truly appreciated.https://airtable.com/shr07v7lc15RGEaj5 Show (single)Episode (Multi)FormulaDesired OutputThe Boys301, 302, 303The Boys 301, 302, 303The Boys 301, The Boys 302, The Boys 303The West Wing401, 502, 302The West Wing 401, 502, 302The West Wing 401, The West Wing 502, The West Wing 302Archer201, 303, 105Archer 201, 303, 105Archer 201, Archer 303, Archer 105

Hello! I have a Typeform that is connected to an Airtable base.There is one question that in the Typeform that is a multiselct (the user can pick more than one option).When it translates to the Airtable multiselect field (the field is call "Select Dates"), it comes through as a massive long option that is super hard to read:  YIKES - I am wondering if there is a formula that can separate this out into distinct values, such as - Tuesday: February 28th at 12p PT / 3p ET Wednesday, February 15th at 10a PT / 1p ET Thursday February 9th at 11a PT / 2p ET Tuesday, February 14th at 10a PT / 1p ET Thursday February 9th at 11a PT / 2p ET Tuesday, February 14th at 10a PT / 1p ET (there are also duplicates in there.... but thats not the worst thing / I don't mind... )My plan is to then take the output of that formula and use an automation to re-submit the cleaned up values into the multi-select field named "Select Dates" in Airtable.The formula se