Formulas

Hi everyone, I did some searching and did not find an answer. I am searching a text field to see if it contains one of a set of words eg. Search for design, designer, product designer, experience designer, UX if found - set result to “Design” I have similar sets of words I want to search for and mark if a result is found How do I go about doing that? Cheers, Subir

I have to following situation : How to create a formula that will return “True” or “1” if at least one record appears in both fields ?

Hi! We rely extensively on AT for updating “downstream” tools. We previously had a complex workflow that involved checking off a box when the update was made and then using a series of formulas to say “if the field was modified after that checkbox was made, the downstream tool needs an update.” Because we have so much data in AT, this was dozens of formulas. I’d like to use the “date modified” field instead, but am not sure how to or if I can store the date modified information or if it will only show the most recent one. In other words, is it a use it or lose it? If the field is modified 9/19 and then again 9/20, is there a way to see both dates by creating a formula? Thanks!

I have 3 columns in my table that contain an amount range e.g. 3500;4800 What I want to do is split that string out into Budget From & Budget To fields, like this: I’ve managed to do the actual splitting how I want using this formula for the Budget From: IF( FIND(";", {Budget range PER PERSON $}), LEFT({Budget range PER PERSON $}, FIND(";", {Budget range PER PERSON $})-1 ), {Budget range PER PERSON $} ) & IF( FIND(";", {Budget range PER PERSON £}), LEFT({Budget range PER PERSON £}, FIND(";", {Budget range PER PERSON £})-1 ), {Budget range PER PERSON £} ) & IF( FIND(";", {Budget range PER PERSON €}), LEFT({Budget range PER PERSON €}, FIND(";", {Budget range PER PERSON €})-1 ), {Budget range PER PERSON €} ) But what I want to be able to do is append the correct currency symbol, which I can’t work out how to do, so something like this: IF( FIND(";", {Budget range PER PERSON $}), LEFT({Budget range PER PERSON $}, FIND(";", {Budget range

Hi there - I need help building a formula that tracks if tasks have been completed early, on time, or late. I have a column where I mark a task complete, so it will have to start with If status = complete, etc. I then have a column each for “Start Date” and “End Date” (projected end date). Then a separate column for “Completion Date” (actual end date). I cannot for the life of me get all the parentheses right, this is what I have, but it’s not functioning correctly: IF( {Status}=“Complete” IF( IS_BEFORE({completion date}, {end date}), “Done Early”, IF( IS_SAME({completion date}, {end date}), “Done On Time”, IF( IS_AFTER({COMPLETION DATE}, {END DATE}), “Done Late” ) ) ) ) Any help would be appreciated! (Simplified example attached)

Hi there! I’m looking to replace text a field with a figure from another field. I can get this to work if I reference the code via another field but I can’t figure out how to insert it via a formula (so that I don’t have to fill a field with this dummy data every time). Not too familiar with markdown/the symbology etc sorry!

I’m back with a puzzle it wouldn’t have occurred to me to even attempt if I hadn’t seen some miraculous-seeming formulas early on in my Airtable forays. I sell books that I inherited from my dad. In my Books table, there’s an Author field that sometimes lists names first then last (Mark Twain) and sometimes lists names last, first (Twain, Mark). There’s no consistency. The thing is that there are different circumstances that call for different formatting. So my modest dream for this situation is to have two additional fields, both formula fields, obviously: First Last Last, First This will allow me to rollup one way for online marketplace inventory submissions (first last) and another way for professional association catalogs (last, first). I’m guessing making note of the comma will be an integral part of these formulas. Am I wrong to think this is a puzzle that can be solved on Airtable? TIA for your puzzling skills!

Hello- Any idea on how to remove the - When the Campaign Brand is field is empty so it doesn’t show: -CAMPAIGN NAME:CAMPAIGN TYPE And only shows: CAMPAIGN NAME:CAMPAIGN TYPE When the Campaign Brand is blank IF( {CAMPAIGN NAME}, CONCATENATE( {CAMPAIGN BRAND}, IF( {CAMPAIGN NAME}, " - " & {CAMPAIGN NAME}), IF( {CAMPAIGN TYPE}, ": " & {CAMPAIGN TYPE}), IF( {CAMPAIGN THEME}, " - " & {CAMPAIGN THEME}), IF( {EVENT NAME}, " - " & {EVENT NAME}) ), ‘EVENT’ & ’ | ’ &{EVENT NAME} )

Hi! Im trying to create a formula from one column where all of our members manually inputted their location (NYC, LA, SF) and input it to another column where I have them as multi select. So ideally any record that contains New York City will fall under NYC, Los Angelas will fall under LA. Has anyone done something like this before? TIA!

Hey all, I’m attempting to create a formula that will automatically create SKUs from several different columns, but I’m running into a few issues. Below is the formula I have so far: SWITCH( {Brand Code}, "Kala","KA", "UBASS","UB", "Makala","MK", "Ukadelic","UK", "Kala Percussion","KP", "Kala Elite USA","ELT" )&"-"& SWITCH( {Product Type}, "Acoustic-Electric","AE-", "Guitar","GTR-" ) &SWITCH( {Type Code}, "Solid Top (2)","2", "All Solid (3)","3", "Solid Body (6)","6" ) &SWITCH( {Top Wood Code}, "Cedar","C", "Spruce","S", "Striped Ebony","SE", "Koa","K", "Acacia","A", "Mahogany","M" ) &SWITCH( {Back & Sides}, "Mahogany","M", "Rosewood","R" ) &SWITCH( {Size Code}, "Tenor","-T" )Preformatted text I want the {Back & Sides} portion of the formula to only show up if it doesn’t match with the {Top Wood Code}. For example in row 3 I have a ukulele that has its top, back, and side wood listed as Mahogany, but my current formu

Hi! I am working on a content management dashboard. I have a single choice field with multiple types of assets, and I have an expiration date field with expiration dates for each asset. I want to create a formula field named Needs Review, that would return different values. For example: If asset type is blog or newsletter, then Needs Review field should show Yes when expiration date is within 60 days. If expiration date is later than 60 days from today it should show No. If asset type is video or webinar, then Needs Review field should show Yes when expiration date is within 90 days. If expiration date is later than 90 days from today it should show No. And so on with a few other types of assets. I also want to add some emojis for the answers in the Needs Review field. I tried writing the formula using IF, AND and DATETIME_DIFF() but I can’t find a way to format it correctly. Do you have some advise on what’s the best way to write this formula? Thank you!

Hello: Question, The Formula link is not generating for Files (PDF, PNG, JPEG, MP4, Etc.) that are saved with an underscore _ in the file name. IF({DESIGN: REVISIONS},SUBSTITUTE( RIGHT(‘’&{DESIGN: REVISIONS},1+ LEN(‘’&{DESIGN: REVISIONS})-FIND(‘https:’,‘’&{DESIGN: REVISIONS},LEN(‘’&{DESIGN: REVISIONS})-133) ),‘)’,‘’ )) Is there anyway that we can fix this? Thank you!

Is there a formula anyone has used or encountered that can surface dates in between 2 dates? For example, if I have a “Start” date field and a “Due” date field, is it possible to extract the interstitial dates? I.e. Start: 1/1/22 Due: 1/5/22 Hypothetical Formula Field: 1/2/22, 1/3/22, 1/4/22

Hi, I have a (very) basic understanding of spreadsheets and databases, and usually rely on googling to find formulas to use in spreadsheets and the same for setting up AirTable. However, I am stuck on the best way to even start to set this up. Or what to search for to get in the right area. Any help would be greatly appreciated. I am looking to calculate an Honour Roll average for students, keeping in mind that not all students will take the same number/type of classes. Here is how their marks are to be calculated: Using their English and Social Studies marks Using their top Math mark (only grade 12s may have a second math mark if they took Calculus) Using their top Science course mark (this could range from a min of 1 course to 3 or 4) After the above 4 marks have been taken, the top mark from the remaining (from other elective classes, and any other science or math class they have taken) is used. Those 5 marks are used to calculate an average. My current thoughts would be to set

Hello- I have this one also: IF( {MARKETING CAMPAIGN}, CONCATENATE( {MARKETING CAMPAIGN}, IF( {ASSET TYPE}, " | " & {ASSET TYPE}), IF( {ASSET TASK}, " - " & {ASSET TASK}), IF( {CAMPAIGN TYPE}, ": " & {CAMPAIGN TYPE}), IF( {CORPORATE TASK DESCRIPTION}, " - " & {CORPORATE TASK DESCRIPTION}) ), ‘MARKETNG REQUEST’ & ’ | ’ &{ASSET TYPE} & ': ’ &{ASSET TASK} & ': ’ &{Item Description}) Need to have a comma separation when the item description is multiple so it doesn’t look like this: abcd Need - a,b,c,d Thanks!

Hello, I’m looking for help with a formula/script/automation. I have 5 fields that are checkboxes noting a user’s role. Some users have none checked, some have some, or even all. Also, these fields may be updated at any time by the user, so need something that updates automatically (both adding or removing a role). I need another field that uses the title of the checkbox field (or text I provide), and puts them into one field. I’m struggling with adding commas, because they only need a comma if another role needs to show up behind the prior one.

Hello- I need some help, need to remove the :, |, - characters for when the field type is not selected so it doesnt look lie this: MARKETNG REQUEST | | GRAPHIC DESIGN: STICKER - 5 X 7 ASTRA NATIONAL TOY STORE MONTH & TRYAZON MAILINGS or MARKETNG REQUEST | MON: YELLOWSTONE | DIGITAL MEDIA: SHOWCASE -

I am trying to figure out the best way to assign a unique, human readable code to each record having a unique entry in a particular field. In my primary table every record represents a Document and the information in each record is extracted from or associated with it, including its MD5 hash (i.e., a unique 32 character algorithmically generated alphanumeric code). The records in the database were created by identifying individual Documents contained within a File and creating a new record for every Document. So, e.g., a single PDF File might have 5 separate Documents contained within it, and thus 5 different Airtable records. Every File has a unique MDF Hash value. Every Document within that file has the same MD5 Hash (since the value is calculated based on the File, not the Document). So, I want to create the equivalent of a File ID # by giving each unique MD5 Hash a unique sequential value, and then associated that value with each corresponding record. Thoughts on the best approach

I keep track of articles that I publish online and their internal links to other articles I’ve written. So the table is structured like this: Field 1: Article Name Field 2: Related field with multiple select, choosing articles from Field 1 In real life, it’s something like this: Article 1 has internal links of Articles 2, 3, and 4 Article 7 has internal links of Articles 1, 3, and 2 I want to know which records from Field 1 do not exist in Field 2. In other words, which articles have I published that have not been internally linked in anything else I’ve written. In the above, Article 7 would be an example of the data returned - it has not been an internal link of another article (exists in Field 1 but not in Field 2). I’ve tried a few different things but can’t quite get the results I’m looking for. Can anyone help?

Hi Everyone, I know very little about formulas and only a little about designing a base. I am trying to create a table to track the hours of those attending study hall. Here is the screen shot I have a form set up so the student can check in then check out. I could not figure out how to make the form reappear so they could check in and later check out on the same form. So I created one form and they have to check in and when they leave check out. This formula is wrong but maybe you can get an idea of what I want to do: IF(IDs = IDs, {Check In Date-Time} = {Check Out Date-Time}DATETIME_DIFF({Check Out Date-Time} - {Check In Date-Time})

It starts with ‘rec’… but what else can we guarantee? I want a solid Regex or set of expectations to guarantee finding a Record_id string. So far I just have 'starts with ‘rec’ and Regex is made of 12 or more alpha-numeric characters.

Hi everyone, Hope you’re going well. I’m struggling with a formula. I have 3 colomns: #1 - All Time Slots Rollup A, B, C, D #2 - All Booked Time Slots Rollup A, C #3 - Formula Colomn that substiture Colomn #1 data with Colomn #2 strings. B, D I found the formula to make substitute one string. SUBSTITUTE(colomn1&"",colomn2&"","") Unfortunaly this formula doesn’t work when there is more than 1 string in colomn2. What formula should i use ? Thank you in anticipation :grinning:

I use VS Code for Airtable formulas as well as for my scripting. Airtable formulas are close enough to JavaScript in syntax that I just tell VS Code my formula is JavaScript. I noticed that Copilot often gives me some reasonable suggestions as I’m writing up a complex formula. I wouldn’t pay for Copilot just for formulas, but it’s a tiny added bonus. Anyone else use Copilot and seeing decent suggestions for formulas?

I’m trying to create a formula field that’ll output a sales tax amount based on another field in the base. So: IF {Tax Type} = HST, multiply {Subtotal} by 13%, IF {Tax Type} = GST, multiply {Subtotal} by 5%, IF {Tax Type} = “Zero-rated”, type “0%”, else leave BLANK I’ve tried several iterations of this nested IF statement and always have issues. The latest was: IF( {Tax Type} = “HST”, “13%”, IF( {Tax Type} = “GST”, “5%”, IF({Tax Type} = “Zero-rated”, “0%”, BLANK ) ) ) And I keep getting errors about the closing parenthesis, however, this was based on the Airtable Support article for nesting three or more IF statements in a row, so I’m confused. Can anyone help? Thx.

Hello, I need quite help with a formula. Currently, my formula is as follows which says If FIELD A = “Hubspot Invoice”, then use Field B to populate cell, otherwise use field C. currently, my formula is IF({FIELD A)}=“HubSpot Invoice”, {FIELD B}, {FIELD C)}) however, I’d like it to say if “FIELD A = HUBSPOT INVOICE” OR FIELD A = “ZOHO INVOICE”, Field B to populate cell, otherwise use field C. How do i capture the two conditions in the If field?