Formulas

Hi community! I have a very strange issue here… I have a lot of duplicates within the same field, a lookup field (I’m not so sure why, but that’s for a different question) In order to try to create a field with no duplicates, I created another field with the formula: (ARRAYJOIN({Most recent First Name :zap: },", ")) It works fine for most of the records, but in some it creates a a comma in front of the name, which I couldn’t get rid of. It happens only in some of the records, and only those that are not duplicated to begin with For the full picture, the next step is a field that takes the string that I created and find the name to the left of the comma which gives me just the name. Any idea what is it that I’m doing wrong? Thanks!

In order to track the workload we’ve had in the past, we want to create a formula that displays which specific weeks we worked on different projects. That way, we’ll be able to see how many projects we had active during specific weeks. We know which week we started a project, and which week we finished it, but I can’t figure out how to display the weeks that passed in between those dates. The number of weeks in between isn’t relevant, we want to see “We worked on 45 different projects during week 14”. Any ideas?

I’m running a datediff formula but it’s not displaying the result I’m expecting. I would like the output to include the day itself. In other words if I’ve got a date range from 28 April to 30 April, for our purposes this is a 3 day event. DateDiff gives 2 days though. DATETIME_DIFF ({Date ended},{Date started}, ‘days’)&" days" Any thoughts?

I understand this is a known issue, but I’ve read several threads on the topic and tried a few solutions but still can’t figure out how to fix this. I have two fields of scores, one from a 2020 publication and one from an upcoming 2022 publication. I am trying to be able to show if the score is an improvement, worsening, or the same. The issue being that the score “0” is an acceptable input and the formula is recognizing 0 as blank instead. The formula is currently as follows: IF( {KI-1 2022} = BLANK(), "", IF( {KI-1 2020} = BLANK(0), "⬛️", IF({KI-1 2020} - {KI-1 2022} = 0, "⬜️", IF({KI-1 2020} - {KI-1 2022} < 0, "🟥", IF({KI-1 2020} - {KI-1 2022} > 0, "🟩", "") ) ) ) ) I have tried COUNTA as well, but nothing changed: *I tried both COUNTA()=COUNTA() and COUNTA()-COUNTA()=0 but neither scenario worked…

Dear community, first of all I really appreciate this forum, even if just as a silent participant so far. I’m pulling my hair out trying to replace strings in variables to make them ready for webmerge/formstack documents. Input string: my webmerge variable Wanted result: {$my_webmerge_variable} Formula I am using: REGEX_REPLACE(REGEX_REPLACE({regex test},"\A","{$"), “\s”, “_”) Result I am getting: {$my_webmerge_variable How do I get the closing brace = } at the end of the variable? I tried $ and \z but they don’t seem to be working. Thank you all in advance, any help is much appreciated :slightly_smiling_face:

Hello, I am trying to add a duration field in my base and I want the end date to be calculated based on the duration I am inputing. How can I do that? Also, I would like to be able to change the duration whenever I need to and it so reflects the end date accordingly. Thank you for your help! Celine

I have an Airtable form that has a number of yes/no questions on it. I want to convert the Yes’s to 1s and no to 0s. Is there an easy way to declare Yes=1 without having to write as many IF statements as I have questions? Appreciate your help.

I’m trying to write a simple IF statement in the CM Maturity Date column comparing the Policy Type field and outputting either a DATEADD function, if “Claims Made”, or a text string (“N/A”) if “Occurrence”: This is the formula that I currently have in CM Maturity Date that results in the above screen cap: IF({Policy Type} = ‘Claims Made’, DATEADD({Retro Date}, 5, ‘year’), “N/A”) For the Claims Made policy type, it should have added 5 years to the retro date. I can’t figure out what I’m doing wrong here, so I’d appreciate any help. Thanks!

Hello everyone, I was just wondering if there’s a way I can make my airtable cells clickable. I know there’s a button field but with that, I can’t have different labels for each row. Basically, I want the cells in each row to be redirected to the respected detail page for that cell. Any help would be appreciated, thanks!

Does anyone know if it’s possible to run an if() formula for a keyword in a lookup field. (see screenshots below) Im looking to return a ‘true’ or ‘false’ if it contains the word “Floorplan” however i believe that the lookup fields are not considered searchable by the formula as it is returning ‘false’ even when the parameters are meet? Can anyone confirm if my suspicions are true and if so how can i go about making the lookup field compatible so it returns the correct answer in my if() formula Thanks in advance

please help me. I have 2 tables, one for sales and the other for stock. The inventory table receives data (customer id and product) that is informed via a bot every time the customer goes through a certain flow. however, I need this product to be counted only if the sale is made. In the sales table you will find the customer’s id. and this table is only fed if the customer makes the purchase. So basically I need a column in the inventory table that asks the following question: if the sales table has an ID equal to the inventory table then 1 if not 0.

Hello! What do I have to do if I want to add an image (logo company updated as an attachment) to my main record with a formula? I tried {Company name} & " - " & {Logo}, but it gives me the name of the file (logo.png), not the image as it self. Is that possible? Sorry, I am a begginer, as you can see! Thanks!

Already seen: /t/if-contains-formula/10126 How can I count the number of linked records in a linked record field (column)? – Airtable Support /t/counting-linked-records-formulaically/20537 /t/formula-to-count-records-in-linked-tables-based-on-values/32018 Hello Airtable Community :slightly_smiling_face: I’m currently consuming Airtable via the API in Javascript and I want to fetch some records accordingly to some linked field based criteria. I have a linked field (called “trainings”) and I need to return records containing only a specific linked value. The way is obviously with filterByFormula options in my base.select() function BUT I cannot use SEARCH() or equivalent in my f

Hi! I am building some QA into my Base, and have x4 date columns that I want to check all equal each other. To ensure the first x3 dates equal each other I have used: IF(AND({Date 1}={Date 2},{Date 2}={Date 3}),“Match”,“Not match”) I think that is right and seems to work well. This all works fine, but the issue I have is that the {Date 4} column doesn’t always have a value (intentionally), and hence I want to ignore it in the date comparison if not populated. i.e. if {date 1}={date 2}={date 3} but {date 4} is empty, the formula should still return “Match” to signify that all dates match. I’m sure this is very simple, but I’ve tried multiple different IF statements and quite simply can’t get it to work! Any help would be greatly appreciated :slightly_smiling_face: Thanks.

Hello, I have a text field and a date field. I am trying to write a code that finds a certain value inside the text field and adds a number of days based on that value. So far I have managed to write this: IF( FIND(‘3 luni’,{Produs}),DATEADD({Data activării},90,‘days’),FIND(‘6’,{Produs}),DATEADD({Data activării},180,‘days’),FIND(‘12’,{Produs}), DATEADD({Data activării},365,‘days’)) It works for the first value (“3 luni”), but it returns “1” for anything else. Can you help me?

Does anyone have a solution for calculating IRR within airtable?

Hi, I have a base that contains three tables (Inventory, Budget, and Invoice) in my workflow I select items from the inventory manually and add them to the project budget. After adding them, I have a checkbox field in the budget table that is titled “billable” I am wondering if there is a formula that I can implement on the Invoice table that will automatically pull in a record from the Budget table IF the checkbox on Budget is marked as billable. At the moment, all I see is the potential of doing linked records, but this would mean clicking each one by one with very little ability to verify that I have moved over every single applicable item. Any help you can offer is greatly appreciated! J

Hi, I would like to count activities’ numbers daily in my CRM. I have a table with all my clients and their activities. In this table, I have a “Last Modified” field. I would count (in another table) how many clients have received some kind of interaction in their records. So I should have 2 fields in the counter table: · Today date (manually or automatically inserted) · A counter of the record modifications in the considered date Can someone help me out? Thanks in advance. (I hope I’ve been clear enough)

Hey guys: I’m currently working on a lead list and I’m working on having two new columns in order to know when to follow-up with a specific person. The formula I have so far is this, IF({Date Last Contacted} != BLANK(),"")& IF({Date Last Contacted}, DATEADD({Date Last Contacted}, 10, ‘days’)) For some reason here’s what I’m seeing when imputing this formula, 2021-02-18T00:00:00.000Z How can I modify this formula to have the date only and not ‘T00:00:00.000Z’ at the end? Thanks!

Hello, I’m kinda new to this and hopefully someone can help me figure out a formula to help me with this. My data in one column consists of a number, a name, and some extra information in parentheses. I would like to extract just the name into a new column. Here are some examples: 1234 This Is What I Want To Keep (not this) 555 Keep This Text (get rid of this and the number) Is this possible? Thanks so much

Hi, all. I have a table that has a “Planting Date” for a vegetable field. This record is linked to another table that contains the number of growing days (ex: 90 days). I’d like to use a formula that would take the planting date plus the number of growing days (90) and give me an anticipated harvest date. What’s the right formula? By the way, each week has a different “grow days” associated with it so hard coding 90 days into the formula won’t work. Thanks, Paul

Hello AT Community, I am in need to create a view that shows records only if the actual time is earlier than a specific time (7.00 pm) everyday. E.g.:if it is April 19th, 4.30 PM the records should be showed but as soon as It is 7.01 PM, April 19th’s data shouldn’t be showed anymore. As soon as the day changes to the day after (April 20th in our example) the new records should be showed until 7.00 pm and so on. Does it make sense? I have used the formula IS_BEFORE(NOW(),TODAY(‘D MMM YYYY 19:00’)) so I could add a filter to the view based on the formula outcome but I am not getting the correct result even if I am not getting an error. Does anyone have any suggestion, please?

Hey guys, I wanted to export data into csv file but image url was exporting with the name of the file and the URL between brackets… So I did some digging to figure out a way to extract th URL and found this small formula and thought to save you guys some time researching: REGEX_EXTRACT({Image Field Name}, ‘https?://.+[^)]’) Cheers!

Hello All, I have something that is absolutely stopping me in my tracks. Here is the situation: I have a workflow process where we are going to use an Airtable form to add a new row to a sheet - this is a new job for us. That in turn, using Zapier, makes an email that sends to my scheduling software called Service M8. Service M8 (SM8) converts this email into a job, and automatically converts the name of the customer from first name last name to last name, first name. We have to use a set template for the email to be able to be ready by SM8 and therefore be able to convert it to a job with one click. And the tables for name are as the below example: First Name: John Last Name: Smith When a job is automatically made from the email: Smith, John Once the record is in Airtable, and the job is in SM8 we run workflow that constantly updates fields in Airtable. They look for the column we have called “Job Name” as that’s how we track eve