Formulas

Hi there - I am looking to create a column that will essentially lookup the field values of columns Q1 - Q4 and output the latest number.We input quarterly projections and want to be able to identify the most recent number by looking at 1 field ("latest projections"). *Note - not all fields will have data from Q1 - Q4.I tried using the IF function but that only seems to work with 2 different fields max. ( IF(Q2> " ", Q2, Q1) )If I have data entered in Q1 field and then in Q3 enter in new data in the respective Q3 field, I want the "Latest projections" column to display that Q3 field since it its the most up to date #.I hope this makes sense. I will attach a screenshot for reference where I manually updated the "Latest Projection" field. 

Is there any substitute for INDIRECT in AirTable?

I have a table that generates specific tasks for clients on a frequency that required an interesting set up, but got me the result I was looking for (leveraging multiple formulas, jump tables, and linked records). Currently, the tasks are generated into a table called 'Client Work' that my team works the tasks out of. I have other team members that need to submit tasks to that same table, but are one-off tasks, not auto generated tasks. Unfortunately - the primary field (Task Title) is a formula generated field that combines multiple look up fields to generate the title, so I am not able to have Task Titles. I am looking to add an "OR" function, IF the fields that the formula is referencing is blank (aka: a work around to have another field that acts as the task title that will only be used whenever a task is generated via a form). For the task name issue – I feel like I have a potential solution, but need some guidance on the formula for this… The current formula is IF(

How to make a line break when I do strings concatenation? For example, I have 4 fields and I do a concatenation: "field_1: " & value_1 & ", field_2: " & value_2 & field_with_lots_of_text I’m trying to do smth like that: If I do like thins, it works ok: But I want to add more data and add a line break:

Can I add a table to “long text” field?I want to add table that include columns " code - description - price" example:code - description - price“1167 - bike tire - 75”

Hello,After the customer submits a request form to the client for order fulfilment, if the client has a follow-up question for the customer - Is there a feasibility/workaround to submit a comment/question on the request so that it can be re-directed to the customer for further clarifications/information? Can somebody suggest a workaround? Thanks!

I have a field where people can select multiple options. The question is “what are some emotions you experienced” and they can select from 9 options. I want to know how many people choose Joy and Anticipation, whether they’re the only options selected or if they’re part of a bunch of responses. I’ve found several ways to set up a formula field that tells me if someone ONLY chose Joy or Anticipation, but it doesn’t pick up if someone chooses Anticipation and Sadness or Joy and Anticipation together. There are too many possible combinations to realistically set up a nested IF formula. Is there a way to write a formula that will tell me if someone chooses either option, regardless of whether they chose other options too? I have tried OR, FIND, SEARCH, and REGEX, though it’s likely I’m just setting it up incorrectly! If it helps, the data is coming in through an external site via Zapier, so if needed I can change the field type from multi-select to text.

Hi Everyone, I am trying to prefill a form with a range of record information and just can't seem to get it to work. I would like to produce a unique prefilled form link for each record and fill the invoice number in for each.   My current formula:CONCATENATE({Base Form Link},'?prefill_Invoice=',{Record ID}) But when I click the concatenated form link it is putting the record number in the invoice field (which is supposed to be just a 5 digit number) I tried moving the invoice number from the primary field (a number) to a single line text field but still wont work Any help would be much appreciated. Thanks

It appears that "tel:" hyperlinks does not work to create a button that opens a URL to call a phone. The button is not clickable.However, the "mailto:" URL shortcut works, but not "tel:"Are there any workarounds to this that will put a clickable link in any view type? (grid or interface?) 

Hithis should be simple but i am stuckAAABBWhats the formula for a rollup in a linked table to have "3A, 2B" as a result? thx!:M:    

Hola a todos! Estoy trabajando en un proyecto en donde los usuarios podrán escribir artículos. La idea principal, es que las keywords se generen automáticamente a partir del texto del artículo. He buscado por todas partes pero aun no eh logrado esa funcionalidad, como tampoco logré hacer que una vez que esten detalladas las keywords pueda hacer un conteo de las mismas para ver si alguna se repite. Aguardo su ayuda si son tan amables. Gracias

BonjourTout à fait débutante dans airtable, après avoir parcouru de nombreux forums, je me permets de venir vers vous pour trouver une solution à ma formule ou une autre idée. j'espère répondre aux critères et codes du forum.Dans une table « Badges » il y a 2 champs pertinents l'heure de début et de fin de journée de travail, un 3 ème champ pour le temps de pause à soustaire du temps de travail et le 4 ème champ , appelé « Total_heures » qui calcule la différence entre les relevés d'heures de début et de fin de jour moins la pause.Sur une seconde table ou onglet « Bilan_badges » il y a un champ rollup « Total_heures_payées » qui fait la somme par mois et par employé.Question :Peut-on faire un arrondi du champ rollup « Total_heures_payées »J'ai essayé plusieurs formules, exemple :1°):ROND({Total_heures_payées}/15,2)*15En appliquant un format Durée h:mm, l'arrondi ne se fait pas.J'ai tenté de contourner le problème du rollup et de faire sur l’onglet « Badges » et un nouveau cha

I've been reading through several posts about similar topics, but I'm just missing something here. I imagine some of this is due to some problematic base design, as I've been building it as we go but here's where I'm at - I have a table of club members. We are in the yearly renewal time, so, since I didn't have a way to allow submissions from our membership form to update existing records, I have redirected submissions to a new table. As it is possible folks have different emails, phones, etc, and they aren't aware of the formulated member ID I've assigned them in our main table, this seemed the easiest way to collect new and updated form submissions. So, my plan was to do a comparison between are All Members table and the 2024 Membership table - 1. In All Members table, If email exists in 2024 Membership table, update a field to indicate member has renewed2. In All Members table, if a record in 2024 Membership has the same email, compare other fields for any updates3. I

I currently have a 'Date' field that shows year, month and date. I am trying to create a new 'Year' field that is just for the year. Is it possible to create a formula or automation that allows for my new 'Year' field to automatically pull the year from my date field?

Hey there,I've been trying to achieve the following for a few hours now, but without any luck.STEP 1: I track the time I spend on a certain activity (# session in minutes column in screenshot)STEP2:  I also sum up the time I spent across all sessions (# sum minutes column in screenshot)Question: Right now I have to do step 2 manually. As you can see in the screenshot, I manually add the last session's number of minutes to the existing sum of minutes. I oftentimes forget, and / or make errors.How can I do STEP2 automatically? I just want a column in which the last sessions' number of minutes gets automatically added to the existing total sum of minutes. Do you have ANY idea on how to do this? Seems kind of simple and I'd know how to to this in excel but I can't seem to get this to work in Airtable.Thanks! 

Hello!I'm quite new to airtable and have been unsuccessfully trying to solve this for a while now. I'd like to assign a tags to a record based on keywords in a long text field. For simplification. Let's assum guests at a wedding text me their food options as a text field. Then I want a script or formular to find keywords in the text field. If it matches a keyword it should then assign a tag. In the image below: I want to assign the tag "omnivore" if either keyword (meat, fish, chicken) appears in the column "Dinner requests". The other keywords respectively.In my case, I only need to assign one tag, not many. We can disregard the possibility that a guest wants vegan and meat option.This is my keyword mapping table. In total, I have a fixed list of around 20 tags with 3-4 keywords each.Thanks for your help!

Hello everyone,I'm trying to extract the first Date&Time from an array from a lookup field that looks like this:2023-06-02 11:00, 2023-06-07 11:30, 2023-06-12 11:45, 2023-06-23 11:00, (etc)But using this formula: ARRAYSLICE({Lookup field}, 1, 1) Gives this result: 2023-06-02T09:00:00.000Z which doesn't get recognized as a Date&Time, so I can't format it.  Is there maybe something I'm doing wrong? Thanks in advance for any advice

Hello,I have a formula in Airtable which should output "SMS" if certain conditions are met.This formula has worked for a long time and has not been changed. Now I have noticed that something has been changed in the formula function and Airtable indicates that the formula is incorrect. I can save, but I can't find the error that Airtable is referring to. What am I doing wrong in the formula?  IF( AND( OR( {Terminart} = "Hauptuntersuchung (HU)", {Terminart} = "Sicherheitsprüfung (SP)" ), OR( AND( MONTH(TODAY()) = MONTH({Datum nächste Prüfung fällig}), YEAR(TODAY()) = YEAR({Datum nächste Prüfung fällig}) ), {Datum nächste Prüfung fällig} < TODAY() ), WEEKDAY(TODAY()) = 1 ), "SMS" )   Please correct the following errors in the formula: Missing opening parenthesis "(". at line 3 column 9 Missing opening parenthesis

I am using a form with some prefilled fields. It works great as long as the item name doesn't include certain special characters. For instance, if it includes an "&" anything after the "&" won't be prefilled. So "Mop & Broom Holder" prefills as just "Mop". Some of the product names we have are a bit long, so it can be a pain to have to type everything out. I know why this is happening, but I'm wondering if there is a way to include the "&" in a url and have it be part of the prefilled information. This is the formula I am using in the button that leads to the form.Form URL'&'?prefill_Name='&Item&'&prefill_Quantity='&{Received Qty}&'&prefill_Project+Tag='&{Project Tag}&'&prefill_Date+of+Update='&TODAY()This is the URL that I get from that formula using the "Mop & Broom Holder" and an example...prefill_Name=Mop+&+Broom+Holder=&prefill_Quantity=4&prefill_Project+Tag=ATN&prefill_Date+of+Update=2024-01-25T0

Is there a formula to have a checkbox automatically checked if the current date is later than a specific date field in that record?  We want to be able to sort by "eligible" records and have it automatically updated as each goes past its "eligible date."  Thanks!

Can anyone please help me solve this issue? Before 1/1/2024 I have to calculate a 2.99% fee and after is different 2.29%. I am trying a formula but only giving the result after 31/12/2023, before the result is blank: IF(    {Date Booked},    IF(        IS_AFTER(                {Date Booked},                DATETIME_PARSE(                    '31/12/2023',                    'DD/MM/YYYY'                    )      

Hello - I'm trying to figure out how to set a price based on 2 other columns. One column is Product Type (Socks) and the other would be Variant (single or 3-pack). So for example if Product Type is Socks and Variant is Single, the Price should be $3; if the variant is 3-pack, the price would be $8.Both Product Type and Variant are single select values, and I could potentially make the Price a single field as well. I also thought of combining Product Type and Variant into one column (Socs - Single, Socks - 3-pack, etc) and do Link Records instead, but I would like to eventually sort by Product Type alone (all socks, for example). Does anyone have any experience in this for inventory tracking? Thanks!

Hi all,Airtable Table Newbie, but feel in love.I want to group rows by partial text in the field.(CDPA) Campaign 1(CDPA) Campaign 2(CDPA) Campaign 3I want to group all three above using the (CDPA).I assume it would create a formula row to do this, but I do not know how to create the formulaCan someone help?Thanks,Judd 

Hello,I have been using the article "Writing nested IF statements in Airtable" as my template for assigning performance ratings based on a percentage score. This is basically exactly like the example in the article, however, I have not been successful. I cannot get "MSE", "FME", "EE", etc. to populate at all, only "DME". I tried using SWITCH as an alternative and was not having success there either. Can anybody tell what I'm doing incorrectly, and how I can remedy this problem?

Hi Airtable Community! I am looking for a way to pull values from multiple columns into a single cell. The setup is as follows: I have a list of countries, and each country has a team comprised of people from multiple departments. The departments each have their own table in the base, so when I create a table of Country Teams, each line item (country) has multiple departments that it's pulling from. In the example above, the bolded columns represent linked records. I need help creating the "Team List" column and eventually the "Team Email List" column (which will likely use a Lookup or Rollup function to pull emails from the different Department tables). But I have been having trouble figuring out how to create a column that pulls values from multiple columns across an entry. Can anyone help with this? Thank you!