Formulas

I’m trying to use the SUBSTITUTE function to get rid of emojis so I can have a “clean” version of some fields my client want to have as downloadable in .CSV I need to have a formula with a bunch of substiutions, but I started from the very first and I get nothing but errors, formula atm looks like this:SUBSTITUTE({Respuesta Pregunta 1},'✅','')I realised the error has something to do with the type of field of {Respuesta Pregunta 1} as it is a lookup field, just for testing I created a single text field next to it, copy the data of the lookups to the new text field, applied the formula to the text field, and it works. So I’m suspecting SUBSTITUTE doesn’t work with lookups? Any ideas to work this out?

Hello All,In one of the columns in my Airtable, I would like add my Amazon affiliate link to the Amazon Product URLs so when the product is integrated to my website, selection of this product will have my Amazon tag in the URL. I am having trouble with a formula to create the new URL. Any suggestions? Thank you!

I’m attempting to set up air table to distribute parents possessions between 10 siblings Scenario:  If there are 100 items and 10 lots of 10 items each.  Each sibling wishes to receive a certain decoy, a certain lamp, and a certain painting. Sibling one rates, only three items, the decoy, the lamp and the painting. Sibling 2 enters a rating for virtually every item on the lists Sibling three enters a rating for 30 different items on various lists that she desires, leaving the rest blank.  Under this scenario, my understanding of the program is that sibling one is almost certain to receive at least one of the three items she covets.  Sibling 2 is highly unlikely to receive any of the three items. Sibling 3 has a middling chance of receiving one of those three items. Am I correct?   

Sometimes we have URLs that include "?" for example www.mytravelsite.com/deals?destination=florida. Its best practise when adding UTMs that if a url already includes a "?" then the UTM should start with "&" instead. The below is the expected outcome of this.www.mytravelsite.com/deals?destination=florida&utm_source=Offsite_P195&utm_medium=Patnerships_Video&utm_campaign=VisitFlorida_Sep-Oct2023_YouTubeThis is the formula i have attempted to use.IF(FIND("?", {Landing Page (from Campaign)}), CONCATENATE({Landing Page (from Campaign)},"&utm_source=",{Source (from Product)},"&utm_medium=",{Medium (from Product)},"&utm_campaign=",Name), CONCATENATE({Landing Page (from Campaign)},"?utm_source=",{Source (from Product)},"&utm_medium=",{Medium (from Product)},"&utm_campaign=",Name))This is what its returning, which is incorrect. www.mytravelsite.co

Hi guys, I’m trying to find a way to insert a formula that calculates the expiration date of leases based on the commencement date and the number of months that the contract is for. So far, I have this: DATEADD({Commencement}, {Term (mos)}, ‘months’) This calculates the expiration date almost perfectly, but I need to find a way to take one day off of the formula. A 12 month lease starting on 03/01/21 would return 03/01/22, but the expiration would actually be the last day of February, not the first day of March, if that makes sense. How can I get the formula to subtract one day from the result? Thank you!

Hi,   I’m used to work with spreadsheets where you can refer to a cell in a previous row.And I know spreadsheets (data in rows) and database (data in records) can’t be compared.But I would like to build a project where I can see the evolution of a certain value.Here you have a simple table where the first column (datum = Dutch for date) is chronologically ordered and each value is unique.In the third column, I entered the difference between the current status and the status of the “previous month”  manually to show what I want.  Is there any possibility to get this done in Airtable?  With formulas in another extra table?

I need a column to calculate the total days- formula being ENDDATE minus STARTDATE plus 1.  Any suggestions?  I can get the datetime_diff, but that is days between not total days.Thank you!! 

[URGENT HELP] Error 422 in Make.com + Airtable – “Invalid Formula” in Search Records even with correct fields📌 The problemI'm constantly getting this error: pgsqlCopiarEditarRuntimeError [422] The formula for filtering records is invalid: Invalid formula. Please check your formula text. Even though all the Airtable field names are correct, Make.com keeps rejecting the formula.🧩 Airtable Table Details: Table Name: Table 1 Fields: Nome do Cliente (Single line text) Produto Qtd Valor do produto Valor Total Data da Entrega ✅ Confirmed: Date with time format ❌ Formulas tested (all returned 422 errors in Make): plaintextCopiarEditarAND( {Nome do Cliente} != "", IS_AFTER({Data da Entrega}, DATEADD(TODAY(), -5, 'days')) ) Also tried with IS_BEFORE, simplified formulas, and also using "ok" in a formula field with static text — still getting the error.✅ Formula that worked in a previous database: plaintextCopiarEditarAND( {Cliente} != "", IS_AFTER({Entrega}, DATE

HelpI have a formula adding 17 days from a date in another column. It is adding the time. How do I eliminate the time?ALSO, is there possible logic to add to move the date forward if it falls on a weekend?Thank you for any help provided! 

J’aimerais créer un champ dans une table qui permettrait de calculer le nombre de rdv réalisés pour chaque personne à partir des rdv portés sur un calendrier

I have a multiple select field called “Weaknesses” in which a client can select different areas they’re struggling in with the language they’re learning. I’d love a formula that takes their responses and transform it to 1 word weaknesses and puts them together in a grammatically correct sentence. So... If they say “I can't stick to goals”, then I want the return to be “Goals” in the formula field. If they say "I can't stick to goals" and "I'm not great at speaking" it should return "Goals and Speaking." If they say "I can't stick to goals", "My habits and routines could be better", and "I'm not great at speaking" it should return "Goals, Habits and Speaking."What I can get it to return is a response of “Goals, Habits, Speaking,”I’m starting to think that’s not possible, and just want to see if maybe I’m wrong. Here’s what I have for a formula currently. REGEX_REPLACE( REGEX_REPLACE( TRIM( IF(FIND("I can't stick to goals", ARRAYJOIN({Weaknesses}, ",")), "Goals, ", "") &

Hi guys!I'm trying to convert a string into a slug, and I need to replace polish diacritics with standard Latin characters. Should I use the regex_replace() formula? If yes, which expression should I pick in this case?

Hi All,I’m relatively new to Airtable and I’m struggling to figure out how to structure the formula to do what I want. I know roughly how to do it in Excel, but really struggling to translate how it’d work in Excel to Airtable. There are multiple things I need the one formula to do, any help on how to structure one or more parts of the formula would be appreciated.Formula is in my “Modules” tab, but looking up data in my “Tasks” tab “Module name” field in both tabs is linked with a “Link records” type of field in the Tasks tab Each Module has multiple tasks (they’re sequential, with no more than 1 “In Progress” at a time) and I’m wanting the Modules tab to have a field that displays what Task is currently “In Progress” I also want it to state “Not started” if all the Tasks associated with that Module are “Not Started” or “Complete” if they’re all set to “Complete”The structures I’ve tried that are similar to Excel keep saying that the formula has issues and can’t be saved so I’m gettin

I have a date, some weeks, and some holidays. I need to add the weeks to my date and exclude all holidays and weekends. I’ve used the WORKDAY function. However, I’m a little uncertain how to treat my weeks. Should I multiply them by 5 (since there are 5 working days in a week) or by 7 (since there are 7 days in a week, and the function will filter out all the weekends)?    

Hi, I have a Table "Main" that is Linked to table "Agenda".Being in Main, on a record, lets say “33” I want a button to view agenda filtered by ID (Both table have the ID field, in this case “33”).Any idea to turn aroud this?Im new to Airtable. Thank you-----------------------------------------------Example:Table Main:-------------------ID Name 33 Marta34 Martin35 JohnTable Agenda:-------------------------ID  Done33 Mailed45 Called46 Mailed33 Zoom87 VisitedResult Expected------------------------------33 Mailed33 Zoom  

I want to sort 9 fields of numbers on a single row and order them into 9 new fields on the same row that show the numbers from lowest to highest. If the old fields are labeled #1-#9 and the new fields are labeled A,B,C,D,E,F,G,H,I, then I want to search #1-9, find the lowest, and put it in A, then search #1-9 for the next lowest and put it in B and so on.I’m not sure if I need to write an automation with code, a script, or what, but the base features of Airtable do not seem to be able to handle this.

I have two multiple select fields that I am joining into one field. The items are showing in reverse order. is there a way to show them in the order in which they are entered in the multiple select field?

Hi everyone,I'm encountering a data integrity issue when transferring a large set of records from Google Sheets into Airtable, and I’d really appreciate any advice. **Scenario:**  I maintain an a list in Google Sheets. The "Source URL" column contains rich-text hyperlinks — for example, display text like `Narrative Enneagram Resources` linking to `https://www.narrativeenneagram.org/resources/`.**Issue:**  When I click the link, it shows an error because the URL has become http://narrative%20enneagram%20resources/ instead of the correct URL When I try to bring this data into Airtable, the URLs get misconverted. No matter the method I use:- **Copy-pasting** a multi-row range directly into a URL field,  - **Importing** via _Add base ▸ Import data ▸ Google Sheets_, or  - **Exporting as CSV** and importing into Airtable,   **Question:**  Is there any way to preserve the original hyperlinks (i.e., the actual `href` URLs)? Thank you so much for any help! 

I am using Documint to generate PDF from data stored in Airtable (via link). It was working without problems for more that a year but I recently have trobles to build PDF on first try. Still some data are missing. I checked with documint and all is fine on their site. While the situation is getting worse, I am adding one more field to generated document to dump all data received from Airtable - so I see, that lot of data is missing so Documint has not enough inputs to generate PDF. It usually take several tries to get what I need, but tonight even 50 is not enought. I asked Airtable support on 15Jun but no reply  - still (OK 3 emails that they are busy, but no solution). Is there any way to make Airtable properly prepare all data and send it to Documint? What is the problem after one year?

In order to compute a change in a value between a prior record and a newly inserted record, is there a way to get the most recent prior value for a field in a table?Assume table is sorted (e.g., by DateTime).Ideally with a formula something like the pseudocode below where the “Prior_Value” and “Current_Value” are a number, such as: an amount, a quantity, a weight, a height, a length, et cetera.Delta_Value = [Prior_Value_from_Prior_Record - Current_Value_from_Current_Record]Thank you for any and all suggestions!Rick

HI guys, I’m trying to use the Concatenate function to help with my primary field names, but the date I’m pulling in from a ‘date’ field is a little messy. Here’s my formula... CONCATENATE(Client," ",'|'," ",{Month Date}," ",'|'," ",{Record Type}) And this is what comes back…Crystal Maze | 2025-09-01T00:00:00.000Z | Income - Forecast Is there a way of amending the date format within the concatenation formula?  Ideally it would look something like this…Crystal Maze | Jul-25 | Income - Forecast Many thanks in advance!

Hello everyone,Any clarity or information on this time and date issue would be awesome!I have 2 tables which are linked through a record. In each table, I have a formula field which produces a date. I need the dates to match. Unfortunately, the formulas are not the same. One is based on a date field in the first table and is a dateadd formula. In the second table, it is a createdtime formula. On the first day of operations, the date is submitted through a form to the date field in the first table. The formula field in the first table adds one day to this date so that process which happen on the second day are kept track of. In the second table which keeps track of the processes which happen on the second day of operations, I have the createdtime field. I need this field to match the dateadd field so that I can automatically link the records. However, something is happening with the formulas such that they are not the same day when I want them to be. Does anyone know how to fix this?I h

Hi there!I want to transform a ChatGPT output (30 words/names) into a multi-line column (30 lines) in Airtable. I'm using Make as my automation engine and its Iterator module. I'm having trouble finding the right array formulahere is the one I have: split(trim({{5.choices[].message.content}}); newline)It would be awesome if someone could help me!! 

Hello,I am working with a formula field that I have created to take a Select list value from Field 1 and change it into a number. Example:Switch ( {field 1}“Choice 1”, ‘1’,“Choice 2”,  ‘2’)The output of this is a text value of 1 or 2 not a numeric value. The tool will not allow me to use the following , the 1 and 2 turn red.Switch ( {field 1}“Choice 1”, 1,“Choice 2”,  2)Using the first switch function how do I now set the 1 and 2 to a numeric value?

Hello. I am working on a form to list specific medications, the vendor it originates from, cost, qty, etc. I’d like to be able to input the brand names of medications into the form and have the generic names of that specific medication auto-populate in a field below the brand name on the form and in the grid view. I’ve tried creating a separate table and inputting the medication brand and generic names in a list, but I cannot figure out how to use conditional formatting (or lookup) to populate the generic name in the form. Hopefully this makes sense. I’m struggling to describe what I’m looking for. Example: Single Line Text Field: Medication Brand Name - Adrenaline Conditional Field: Medication Generic Name (to auto-populate) - Epinephrine