Formulas

My text fields imported from excel have a question mark where an apostrophe should be.  Is there a simple formula to correct this?

Hi everyone - I have a Airtable based that hosts events across states. And another base that hosts emails and location of users. I'm trying to create an automation that send emails weekly event reminders to users based on the event location that matches the users chosen locationsIn the email list base - a user can input multiple location based on their preferences. In the event based - events are listed based on state. I think I should be doing the automation in the email list and link that to the event base. I hope to send a list or grid of event to X user name and email. I was able to setup the first half of the automation using find records but not sure how I can automatically send the emails with limited manual work. could someone please help me out? Happy to share my base to assist 

I have a URL field that I need to grab specific text from (in bold). Does anyone know if there is a formula I could use to easily grab just the string in bold without pulling in additional data from the 3 possible formats?Here are the multiple formats that the URL could be in:Sample format 1:https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9ASample format 2: includes ?ABC=1 at the end https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9A?ABC=1 Sample format 3: includes 2 URLs only separated by a spacehttps://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9A ABC: https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9A?ABC=1Sincere thank you in advance for anyone who takes the time to look into this and provide any suggestions!

Hello,I am working on a base that includes artists' names and a lot of them have diacritics in their first and last name. Is there a formula that I can use that could identify if there are diacritics used? It can be a simple True or False formula that I can then use a filter to isolate everyone with diacritics in their name. For First and Last name I am used simple Single line columns.A list of names is important because it is used for a remapping feature on our website, so the end result for airtable needs to be a filtered view of names. I appreciate any help you can provide with this. 

Is there a script/formula I can use to extract zip files as separate files and have them saved in another field?

Hello everyone,Me and my team are working on a database for my business, to be frank we're new to Airtable, and there's something we can't quite figure out. There's this one table that is for recapping all of our clients' consultation sessions. After each client consultation sessions, that session will get added to this table as a separate entry, and be given a specific Consultation ID. The goal is to make something like this:Consultation ID: EVA/COU/2024.06/000004-001The things that matter here are the field "Unique ID" which is 000004, and the three digit after the dash, which in the example is 001, which should signify the amount of sessions they have with us, 001 here meaning it was the client's first ever session.The field "Unique ID" is tied to the field "Participants" which is linked to a different table filled with details of our clients. Each Participant is assigned a Unique ID.If they have following sessions with us, then we would like the three digits after the dash be auto-

Hello!I am trying to create a formula that categorises ships by type, then size category, and if they meet both of those criteria they are assigned a value that is annual fuel consumption appropriate for the vessel's size category. If not, the IF test continues through all size categories and finally ends on "NA" if nothing it true. I would have thought I would be able to nest like the below example, but for some reason it only generates the correct answer for the first two size categories, then everything higher in size gets the same second highest fuel value:IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}<=1000),"XX Fuel consumption value",IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}>=1000<=1999),"XX Fuel consumption value",IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}>=2000<=2999),"XX Fuel consumption value","NA")))Below is the actual formula if that helps anyone understand. I also hoped to be able to have all vessel types an

I am setting up a new system for our company to track our projects and record how many days each designer has worked on a project and, equally, how many days a project has been worked on. The calendar view doesn't work for us, as date ranges don't work here. We also don't want to have to fill in time sheets anymore, we just want the table to calculate the number of times a record (project) appears in the design schedule. The rollup function isn't working here to add up each instance of a record.Can you help me figure out how to set this up so that the "Projects" table, and the "Design Schedule" table link to count up how many times a project appears across the 5 days. Ideally it would be great to figure out how to show how long each person has spent on each project but ultimately the goal is to work out how much time has been spent on each project total. I don't know much about functions and their language so I assume this is where I will need the most help! Th

Hi Folks,I am essentially trying to automate quota calculations for the sales team in Airtable. As I explore setting up the tables, I'm running into constant brainfogs.1. Im dividing the "record" (quarterly target) by "3" (to break this into a monthly target) but i'm receiving a #ERROR. Can anyone help me fix this?2. The way I set up these records is to have the employee ID + Month when the plan start date is; Therefore, each employee has 12 records. How can I build a reference table so I can link the records and specifically link the values into the current table? I currently calculate the quotas in Gsheet to then copy and paste in the current process but I'd like to automate this. Additionally, if I need to link the record to each of the 12, how can I automate that? Seems like automation setup could address this issue.I thought about setting up the reference table to still have the following (see below) but if the records are currently by column, how do I link it in a way where

I'm experiencing something weird - I have a formula field ("Cheque 2 amount") set up to subtract different currency fields. All fields, including Cheque 2 amount are set to currency format with 2 decimal places.  Everything looks normal in Airtable:However, I have a "send email" automation set up that uses the Cheque 2 Amount as a token, and some of those emails are displaying this token with 14 or so extra decimal places (here is the email generated from that same record above): Seems to be happening to maybe a quarter of the 160 or so records I was working with this morning. I can't see anything different about the ones that are experiencing this glitch. My best guess is it has something to do with the Cheque 1 Amount field, which is a formula that calculates a percentage (SUM(.75*{Approved budget})) - however it is also set to currency format with 2 decimal places, along with all the other fields. Anyone have any ideas???  

Hello everyoneI'm NawafI have a long nested if statement that i used to use in excel but I can't figure out how to use it in airtable the idea is i have 3 columns first ( permit status ) second is ( remark ) when the conditions meet, I want the third column ( action ) to show as "action" or "no action" or "no action closed"(permit states) has only two terms closed or valid(remark) is different i want to find the word "renewed" or "to be" or "re-issue" or "on hold" "redesign"this is what i used to use in excel =IF(AND([@[Permit Status]]="Valid",[@Remark]="No need for renewal"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="No need for renewal"),"no action closed",IF(AND([@[Permit Status]]="closed",[@Remark]=""),"need action",IF(AND([@[Permit Status]]="Valid",[@Remark]=""),"no action",IF(AND([@[Permit Status]]="Valid",[@Remark]="Renwd"),"no action",IF(AND([@[Permit Status]]="Valid",[@Remark]="Re-issued"),"no action",IF(AND(ISNUMBER(SEARCH("to be",[@Remark])),[@[Permi

I found this formula in the community. However I want to include a timestamp aswell. Since {date 1}, {date 2}, {date 3} may occur on the same day. I tried adding a format specifier but it wont work... The formula sets the time to a default 12:00am instead of the actual time that is showing on the datefields.IF( OR({Date 1}, {Date 2}, {Date 3}), DATETIME_PARSE( MAX( IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD'))) ), 'YYYYMMDD' ) ) 

Probably no way of doing this without an Automation right?I'd just like to have an image for my records based on a single select field. Essentially a Single Select field for images! I want to be able to assign a Logo for each record. Then on an interface, we can see Red Team or Blue Team logos distictively.My formula solution for this would be to host the image URLs somewhere and use a SWITCH() formula to find the URL. I don't know how to get it from the URL to the Attachment field.I can do this through automations but that seems over-engineered. Since I already have the teams in a single select field, all the data is there and I don't need to overcomplicate my base. The want for a logo is purely cosmetic haha.

I receive a due date and have to calculate backward to identify different milestones in our projects. What formula(s) do I need to use to make this work? I've attached a picture of what I'm trying to do.  Thanks in advance for the help!

I have a base with information about students in a music program, including their birthdates (MM/DD/YYYY). I have a formula that calculates their age as of today:DATETIME_DIFF(TODAY(), {Birthdate}, 'years') and a formula that calculates the number of days to their next birthday:IF({Days from today} < 0, {Days from today} + 365, {Days from today}) I want to create a field that will tell me how old they will be on their next birthday. Is it as simple as adding 1 to the formula that calculates their age as of today? Thanks-Jen  

I'm working with a relatively simple table with columns for room number, name, and dietary restrictions. Dietary restrictions will be a dropdown multi-select column.RoomNameRestrictions1JohnHalal, Diabetic2SallyHalal3JaneHalal, Diabetic4Mike 5KimHalal, Diabetic, No PorkWhat I'm hoping to achieve is a count of the unique values, so in my example above:Halal, Diabetic 2Halal 1Halal, Diabetic, No Pork 1None 1Can I do this?

I have a simple base that shows transactions each month and allows me to track date of the transaction.I want to add a formula field that will compare Entered date (year & month) to the current year and month: IF(  AND(    IS_SAME({Entered},TODAY(),'year'),    IS_SAME({Entered},TODAY(),'month'),1,0      )  )   This isn't returning any values in the formula field though. If I just use one of the IF statements for either year or month, it works fine. It's when I try using AND that returns nothing. Any suggestions?

Currently, formula fields support numeric, date and text output only. Are there any plans to extend formula field to work with other types of output (such as the single select field)? A bit of context: I have 3 single select fields (let’s call them A, B, and C) which represent the status of a task. Statuses may have one of the following options: Todo, Doing, Paused, and Done. For example, given some task, it could have the A field as Doing, B as Paused, and C as Todo. With that in mind, I’d like to have a field D that automatically selects its value based on fields A, B, and C. Let’s say D is a formula field that does some least common denominator logic to figure its value out. The problem I’m facing is that the output type of D is text, and I’d like it to be a single select field’s value type. Also, is there a dedicated place to ask Airtable for feature implementations?

Hey, there! 🤗I have a column named "Time" that has the time a test was taken based on military time.  I'm trying to get the the column "Time of Day" to be able to populate automatically either "morning," "afternoon," or "evening."I believe I need an IF statement.If score time is:0-11:59: morning12-16:59: afternoon16:59-24:00: eveningMy formatting is probably way off 🤣 I am very new at formulas.  This was based off my research in other forums.Thanks! Here's what I tried: IF( {Risk Score} < 0, "error, value too low", IF( {Time} < 12:00, "morning", IF( {Time} < 17:00, "afternoon", IF( {Time} <= 24:00, "evening", IF( {Time} > 24:00, "error, value too high" ) ) ) ) ) 

const Airtable = require('airtable'); const airtable = new Airtable({apiKey: 'patMH--changed--FEQXBB4',}); // Replace with your Airtable base IDconst baseId = 'appM0aa--changed--DFmhzwJv6w'; // Replace with the table name you want to accessconst tableName = 'home_first';airtable.base(baseId)(tableName).select({filterByFormula: '{RECORD_ID() <= 5}', // Filter to get the first 5 recordsmaxRecords: 5, // Limit to 5 records}).firstPage((err, records) => {if (err) { console.error(err); return; }records.forEach((record) => {console.log(record.fields); // Access record data using record.fields object});});  I keep getting the following error:  ○ node one.jsAirtableError {error: 'AUTHENTICATION_REQUIRED',message: 'You should provide valid api key to perform this operation',statusCode: 401}Yes, I have given full permission in the scope to access the base and the read and write permissions. 

Hello:New to Airtable and new to the forum here...Looking for a solution to combine records in my field {Component], for every change in records in my fields {Location ID} and {Room}.  For example, in the attached image I would want to combine WF218, W361824, RF9624 3/4, W3330, and so on to B18, in a new field.  This is to correspond with {Location ID} 1-3-302 and with {Room} A Acc Kitchen.  For the next combined field, the result would be ADA SB30, for location 1-3-302 and room A Acc Bathroom.  I would like to accomplish with a formula, but I understand I may need to create a new linked table.  I am attaching 2 views; one is grouped and one is a straight view, for clarity.  Thanks so much!!  

Hello! I found the "Splitting text to alternate columns separated by ;" thread and was hopeful that it was exactly what I needed, but after trying to implement this formula I am not getting the expected outcome. I'm not sure if it has to do with the format of my data including a mix of hyphens and underscores or if there is something else I'm missing.I have anywhere between 1 to 5 lines of text that I need to break out into their own columns instead of being combined. Ideally I would have five new formula columns that allow me to break these out based on them being separated by "\n" or if that it not possible, I can also add a ";" at the end of each line to simplify the formula if it helps!Example Data:abc_def-gh_ij-x-kl_mno-pqu-first-msg-x-240101abc_def-gh_ij-x-kl_mno-pqu-second-msg-x-240102abc_def-gh_ij-x-kl_mno-pqu-third-msg-x-240103abc_def-gh_ij-x-kl_mno-pqu-fourth-msg-x-240104abc_def-gh_ij-x-kl_mno-pqu-fifth-msg-x-240105A sincere thank you in advance to anyone who takes the t

Hi, I created a form where people can choose if they want 1 or 2 tickets. I have 40 tickets only available. I have no idea how to setup the form and the table so the form close when the 40 are reached.... Any ideas ?  Thank you !

Hello,I am trying to make the process of time tracking more user-friendly. Instead of using the duration field and writing manually each time record, I would like to use two single select fields as StartTime and EndTime pickers, each containing values 00:00, 00:15, 00:30, 15:30, 15:45, 16:00, etc.But I am stuck with a formula that makes a magic difference between these two times.Is it even possible with a formula, or does it need to be done with some sort of script?Thank you 

Bonsoir,Je souhaite lier la colonne case à cocher : Classée, avec la colonne Prix, pour que dans la colonne : Prix collection, apparaître le prix uniquement si j'ai coché.J'ai mis la formule : [SI ( {Classée} = 1 , CONCATENER ( Prix ))]Sauf que je ne peux pas faire la somme de la colonne : Prix collection.Dans la mise en forme, j'ai ce message : Votre type de résultat n'est pas un nombre ou une date. Pour le moment, les options de mise en forme sont disponibles uniquement si le type de résultat est un nombre ou une date. Je vois bien que mes chiffres sont à gauche et non à droite pour avoir cette somme, comment je peux modifier pour obtenir ce que je veux ? Merci d'avance de votre aide.