Formulas

I would like to flag a new record if it arrives and the email address of the new record matches an email address of a record already in the base. I have gotten as far as lowercasing the email addresses to ensure the capitalization doesn’t throw it off, but I just want to have a visual flag immediately showing as an output in a field. We have the pro version, and the dedupe block, but we want to just be able to see quickly if a record exists before wasting time deduping it or outright ignoring the new record.

Hey everyone, question related to automation…and maybe rollups? Let me explain… In my airtable base, I have 2 tables… one table has the submission data from a form I created. Let’s say it’s an internal company feedback form. The fields are NAME and FEEDBACK_SCORE. In another table, I have columns with NAME and FORM_COMPLETED. I’m wondering if there’s a way to automatically change FORM_COMPLETED from ‘no’ (default), to ‘yes’, when someone submits that form. I’m thinking this requires some type of rollup and other formula, but I’m not experienced enough to know if this is possible. Any ideas? Thanks!

Hello, I use this formula to compute the projected funding date for a borrower. the Funding date is usually 3 days after the borrower signs the final documents. This formula does it just fine, but I need to exclude Saturdays and Sundays from the formula. for example, if a borrower signs on Monday the 15th, then he should be funded on Thursday the 18th. However if the borrower signs on Saturday the 20th then he should be funded on Wednesday the 24th. Please note, signings can be done on saturdays, but funding can not. existing formula. DATEADD({Signing Dt/Tm},3,‘day’) thankyou in advance

IT’S ME AGAIN, I would like to add a column that contain code based on fields. This is the code with the field names inserted. But how would I get airtable to pull those fields and write out this code? <a href=“Instructors page Link”>name medium Date Range FriendlyTime slotsBottomline Thanks to all of you that help us undereducated Amy Brakeman Livezey Acrylic March 25-27, 2021 9-4 $325.00/3-day workshop Results in

I have only 2 numbers 15% 161.80 I have to get after the deal is done $161.80 and the the other guy have to get 15% of the deal. So the total deal will be $190.35 - (161.8 is a 15% decrease of 190.35.) How can I get to this number ($190.35) in Airtable, when I have only the 2 other numbers (1. 15% 2. 161.80) In Excel I will make this as follows =161.8 / (1-15%) but I can not make this work in Airtable Thanks in Advance

I have a base of events where I am tracking event updates. I am trying to write a conditional formula that will display up to 3 different possible date fields based on 11 outcomes using data from fields already existing in each record. I have tried nested IF formulas using IF( AND( as well as IF( OR( and have not been able to correctly solve the problem. I am either getting a formula that produces a date that is not 100% reflective of all criteria or an error message. I looked into SWITCH formulas but got lost… Fields within this base include: Field Type | Field Content Date Field | Original Event Date Single Select Field | Update to the event (cancel, postpone, reschedule) Date Field | Announcement of Update Date Field | Rescheduled event date Single Select Field | 2nd Update to the event (cancel, postpone, reschedule) Date Field | Announcement of 2nd Update Date Field | 2nd Rescheduled Date I want to display the appropriate date for each record based on the following criteria: Return

I’ve been incredibly focused without achieving what a simple VLOOKUP in Excel allows me to do. What i am missing This seems rather simple a priori this table is created by a form, fairly simple Then another table from another form. I just want to have a Y/N in the column “exists on enrol table” I’m ready to use integromat if needed, i just need some guidance for this The only matching key is email and cannot be exposed in the 2nd form for privacy reasons

It appears that the DATETIME_DIFF() function rounds down to a whole integer when specifying hours regardless of it decimal is specified to two places.

Hi all, I have read through the other threads regarding this subject of blank cells in a formula but wasn’t able to successfully implement a solution. In my primary field, I have a formula combining the Sponsor Name with the date the Ad Copy was submitted. The two are separated by a space. I would like to change this so that if a Sponsor Name cell is empty, that part of the formula returns “?”. Same with the “Copy Ready Format.” If that is blank, I’d like it to return “?” too. Can you help me write the formula the way? Thank you in advance!

Hello, I am trying to create a formula to track of the aging of a record. I am using date fields for this formula, the Booked date and the Funded date. I was able to come up with a formula that calculated the age, but it only calculated the age once the funded date was updated. I need to be able to keep track of the ongoing aging of each record from the date it was booked all the way to the date the record is funded. I also need the formula to stop keeping track of the aging once its been funded. for example, if a record was booked on June 1st and today is June 17th,and the record has not been funded I need to see that the record is 17 days old. However if the record was booked on June 1st and funded on June 16th, then even though today is the 17th, the formula should still show that the record is 16 days old.

Hi! Can someone tell me why my formula is not working? IF({Rating}=1, ” :star: ”, IF({Rating}=2, ” :star: :star: ”, IF({Rating}=3, ” :star: :star: :star: ”, IF({Rating}=4, ” :star: :star: :star: :star: ”, IF({Rating}=5, ” :star: :star: :star: :star: :star: ”, ))))) Basically, I want to convert a lookup value of integers into an emoji. Many thanks.

What would be the correct way to have the duration / age of a ticket in Airtable being any status that isn’t “Complete”? In the “Days Open” field I currently have got to this formula : IF(LAST_MODIFIED_TIME(Status), WORKDAY_DIFF( TODAY(), LAST_MODIFIED_TIME({Status}) ) ) What’s the best way to stop it counting once the ticket is completed?

Hello! I am having trouble creating a formula to calculate my service fee. Our fee is 10% of the order total (min fee $10, max $25.) We charge and extra $20 IF the order is for delivery. Please Help!!

I am trying to manage a data dictionary and generate XSD (XML schema definition) as part of managing API definitions in our project. However, I keep getting blocked by this error. Here is an example base (I stripped down all columns that are not relevant to the XSD generation): Airtable Airtable: Organize anything you can imagine Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free. The formula for column XMLElementDef of table Attribute should be: IF(IsPrimitive, "<xs:element name=\\"" & Name & "\\" type=\\"xs:" & DataType & "\\"/>", DataTypeXMLElementDef) but the else part of the IF - DataTypeXMLElementDef - cuases the circular reference error. Recursive structures are necessary to many of my bases because many of the data I am managing is naturally hierarchical in nature (XML, JSON, folder/directory paths, org structures, etc). I understand that end

Hi, I have issues with a checkbox that doesn’t work proper with my IF AND formula. Or more correctly, when checkbox =“1” formula works fine, but when checkbox =“0” it doesn’t. As far as I can tell, an empty checkbox is supposed to be =“0”, right? Formula looks like this: IF(AND({Win}=‘Yes’, {EW}=“1”), (({Stake} / 2) * {Odds}) + (({Stake} / 2) * {EW Odds}), IF(AND({Win}=‘Yes’, {EW}=“0”), {Stake} * {Odds}, IF(AND({Win}=‘EP’, {EW}=“1”), {Stake} / 2 * {EW Odds}, {Stake} * 0))) Works like a charm as long as the checkbox is marked, but when it’s not the 2nd Row of the formula is not working and instead the ELSE thing at the end of Row 3 kicks in. I’m a total beginner at this so my apologies for novice language or stupid question. Many thanks! Best regards, Marcus Sjöberg

Is it possible to get the last 2 digits of the creation date (year) e.g. 20 for 2020 19 for 2019. This is to use in a unique reference field (first 2 letters of name + “-” + last two letters of creation year + “-” + Auto ref number) I currently have: UPPER( LEFT(Created By,2)) & “-” & ( YEAR(CREATED_TIME()) & “-” & {Project Ref} Which gives me: E.g. AA-2020-101 Where do I insert the RIGHT(XX,2)? I’ve tried the following but with a returned #error!: UPPER( LEFT(Created By,2)) & “-” & RIGHT( ( YEAR(CREATED_TIME()),2) & “-” & {Project Ref}

I have the following formula which returns the correct amounts, except when engineer report is selected. In this case it comes up blank. All the other selections produce the expected numbers. IF(Type=“Inspection”,“300”,IF(Type=“Estimate”,“30”,IF(Type=“Engineer Report”,50,IF(Type=“Photo Report”,“20”)))) Thanks

Hello and thank you in advance for taking a look at my question! I would like to have a formula that analyzes two columns of data to return a specific amount, or 0. Column 1: # Nights Column 2: Cabin Name If # Nights is greater than or equal to 7, AND Cabin Name is “299” then $1800, if not then $0 If # Nights is greater than or equal to 7, AND Cabin Name is “514” then $1850, if not then $0 I am struggling with the logic of whether the IF statement for cabin name comes first or the # of nights comes first. Also struggling with the IF(AND…) formula - or perhaps I should be using a different formula altogether? Please help! Heather

I’m trying to grab a variable amount of text from between two consistent characters, > and <, to format HTML. I’m using Zapier to convert rich text to HTML and then using an Airtable formula to modify the URLs with UTM codes. Example below. Starting Text Cooperative Currency Desired Output Cooperative Currency From this ">Cooperative Currency< To this Cooperative%20Currency">Cooperative Currency< Thank you! ~Brian

Hello, I am trying to do a simple DATEADD function. Add X number of days to Y date. I have a date formatted column and an integer number column. The formula column has this formula in it: DATEADD({Last PM},{Interval},’days’). I’ve tried every variation of the DATEADD function that I could think of, and nothing works. I’m getting this error: “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” I can use the WORKDAY() function, without an issue. But this isn’t what I need. Not sure what I’m doing wrong. Thoughts?

Hello airtablers, I have a field {RESPONSE} with 5 possible choices A,B,C,D,E I need to be able to give a score based on each response. So if response is A OR B score is 4 if response is C OR D score is 1 if response is E score is 3 This would be easy to solve with a switch function but the cell with responses returns all the responses concatenated in a single array (A;B;C;D;E). Is there a way to score the responses? Thanks a lot

Hi Airtable Friends, I want to combine four things in my primary field: (1) Story ID#, (2) Date, (3) Storyteller, and (4) Story Title – all separated by a " - ", of course. I was able to get the first two things with this formula: ({Story ID#} & " - " & DATETIME_FORMAT(Received, ‘DD/MM’)) & " - " But when I follow this up with {Storyteller} & " - " & {Story Title}, I get a message that says: “Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference.” To be clear, here’s the full formula that keeps getting rejected: ({Story ID#} & " - " & DATETIME_FORMAT(Received, ‘DD/MM’)) & " - " {Storyteller} & " - " & {Story Title} I’ve tried a number of variations using parenthesis and curly brackets, but always get the “sorry” message. Grateful to anyone who can help me get past this irksome obstacle!

I think the formula posted to the Airtable blog here, under the heading " Figure out which date comes first :soon: " is way too complicated. The formula on the blog is posted as: IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”) Mistake #1 is obviously that the second Expiration Date needs {}. Simple enough. But couldn’t it be written more simply as: IF(IS_BEFORE({Expiration Date},{Date}),“ :warning: Expired”,“Ok”) ? I get the same results. Even if we stick to the same original formula, I can delete all of the bolded bits and return the same results: IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”) Am I missing something? Just trying to understand. Thanks for

Hi all, pretty new here! I just want to set up a field to capture grams and kilograms (weight). Do I set cutomize the field to number? I’d like to to display KG or g like it would if I added a currency $/£/€ etc. Is this possible? Thanks for any help you can provide!

Hi, I’m writing an item order table and have been successful in pulling prices from a matrix based on qty input using the Tiered system, but I find I now need to include the ability to override the price. The Tier Formula is below and works - it populates the name of the tier based on the quantity input IF(Quantity > 1, IF(Quantity < 5, “Tier 2”, IF(Quantity >= 5, IF(Quantity < 10, “Tier 3”, IF(Quantity >= 10, IF(Quantity < 15, “Tier 4”, IF(Quantity >= 15, IF(Quantity < 25, “Tier 5”, IF(Quantity >= 25, “Bulk”)))))))), “single”) Once the tier is populated, I’m using a SWITCH formula to pull in the correct price based on the price tier SWITCH((Tier), “Single”,{Single Unit Cost}, “Tier 2”,{2-4 Units Cost}, “Tier 3”,{5-9 Units Cost}, “Tier 4”,{10-14 Units Cost}, “Tier 5”,{15-24 Units Cost}, “Bulk”, {Bulk 25+ Plus}) I find that I need to be able to override a price if their is a special one off price for a given line item. But since I’m using a formula, there doe