Formulas

I don't know if this is possible .... if anyone tells me to restructure my data, I'm quitting lol I have Orders, Products, and Warehouses table. Using linked records, I assigned various warehouses to products. And then when orders are placed, it's looking up the warehouse based on the product.  I want to show open open orders per warehouse in a rollup, but it's rolling up the products .... not the count of linked orders. I cannot some come up with how to write this formula .... it feels like it should be simple!  The linked records field is set to only lookup orders that are not yet shipped. This is what I currently have set up as the formula - it's counting the number of products assigned to the warehouse though .... not the number of orders.  Which makes sense. I just don't know how to make it do what I want.  Also - I have duplicate order numbers (expected) because it's creating a line for every item in the order, not the order itsel

I am new to Airtable and Formulas I saw a post by Florian_Kleinicke Who said he had managed something similar, could someone please explain how I use the formula below. DATETIME_DIFF(DATETIME_FORMAT({Birthday},‘MM-DD’),DATETIME_FORMAT(TODAY(),‘MM-DD’),‘days’) My problem (which is likely is also a problem here) is that TODAY() won’t update on its own. It stays the same for a while. Is there a solution to that problem? I’m using automations to send an email, once the computed difference hits 0. Ah I just came um with a solution, that might circumvent that problem. I can compute the birthday DATETIME_PARSE(DATETIME_FORMAT({Birthday},‘MM-DD’),‘MM-DD’)

Hi guys, When I’m using Blank() in a formula (to flag empty fields) it also counts the number ‘0’ as a blank, and the other way around. So when I have a formula that says {MyNumberField} = 0 it also registers the empty fields as zeroes. Has this always been the case? At the very least I’d expect the more specific {MyNumberField} = Blank() to neglect ‘0’ fields. Arthur.

Hi,I'm working with the NPM package to get data from tables which have lookup fields and I'm wondering if there's a way to ignore these lookup fields in the initial API call either by using some parameter or `filterByFormula`. I've checked the API documentation for my tables, checked the documentation for the Formula field and nothing seems to be able to filter by field type.EDIT: I guess the question can also become, is there a way to ignore fields when using the `base().select()` method? I have a list of the fields I want to ignore from the Base schema but can't find a way to ignore those fields. I can just drop them from the returned data afterwards but that is going to be fairly expensive as we're processing potentially tens of thousands and eventually millions of records. Thanks in advance,Thomas

I'm not sure if what I'm trying to do is possible, but here goes:I'm working off of an existing base template, and am trying to tweak one of the formulas that's in there. The existing formula is as follows:I would like to add one additional condition: if Equipment Status is "Unknown" the result is"❌ Not Available". Since this is pulling from a different field than the existing SWITCH formula, I can't add it in there; I've tried nesting another "If" within this formula, but that also doesn't seem to work. It may be that I simply can't do this, but if anyone has any ideas or suggestions, I'd appreciate it--I'm pretty new to Airtable formulas, and trying to figure out how best to leverage these formulas. Thanks in advance!

I'm trying to create a date formula with the next/upcoming birthdate from a staff profile with Date of Birth. I need this to be formatted as a date so I can use it in a calendar, which has proven to be more difficult than I anticipated. Here are 2 ways I have gone about it. Any suggestions?Attempt #1:DOB is Staff member's Date of Birth:IF(DOB = BLANK(),BLANK(), DATETIME_PARSE((((MONTH(DOB) & "/") & DAY(DOB)) & "/") & YEAR(TODAY())))-the result is shy by 1 day. Birthdate of 4/12/__ is showing up as 4/11/23. It is recognized in Airtable as a date though. I just can't figure out why it fails. I believe it could be linked to timezone, but I don't have time on DOB or the formatting for the birthday field. Also, this really only creates birthdays for 2023. Some January birthdays are in the past and don't show up in 2024. Not a major issue but still problematic. So I tried the option below... Option 2: Calculates corr

Hello Formula Gurus- I am trying to convert some excel formulas to airtable and struggling a bit.  This is the formula in excel:=IF(J2>=8.5,"A",IF(J2>=7.5,"A-",IF(J2>=6.5,"B+",IF(J2>=5.5,"B",IF(J2>=4.5,"B-",IF(J2>=3.5,"C+",IF(J2>=2.5,"C",IF(J2>=1.5,"D",IF(J2>=1,"F")))))))))Any help is appreciated!  thank you 

I'm hoping someone can help me with a date formula and how to implement it.I've got a project plan (with dependencies) and I've got three fields, "start date" "end date" and duration.The project has a kick-off date and all start and end dates cascade from there based either on the duration of the task or a a dependency.All dates to be calculated on a M-F work week.When a task is marked complete, the end date needs to be updated to the current date (thus re-calculating the tasks dependent on that end date).Bonus points for the ability to integrate holidays into the calculation.I'm completely lost on how to achieve this. Any help you can offer is much appreciated.Thanks.

Hi TeamI need to know what formula I can use to subtitute the culumn at the left, this is a multi select culumn to the culumn a specific name. It seems possible to have severall subtitute fonction in a row but my formula doesnt work.Any suggestions?Here what I tried to test SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Annonce}"Lovely Clichy Home - Mairie de Clichy / Levallois","Xavier Clichy"), "Studio au pied du Sacré-Coeur","Feutrier"),"Magnificent duplex in Montrouge near Metro Barbara","Barbara")

Let's say I have 3 date columns namely Attempt #1, Attempt #2, and Attempt #3. I want to send Attempt #1 on May 1, 2023 --- then I want Airtable to automatically compute the next follow-up date (which is after 3 days). But if it falls on a weekend, I want it to automatically add the nearest weekday date available. The same goes for attempt #3, I want Airtable to compute the next follow-up date based on the date indicated in attempt #2. Is this possible? 

Good day all.I have a formula that currently works:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}However, I now need to add an additional IF statement to check for Archived:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&am

I extracted unique names of participants in my table by using the ARRAYUNIQUE formula in a field called {Progress Made BY}. This produced a list of unique names separated by commas in the {Progress Made BY} field, which is what I was looking for. I used this one instead of CONCATENATE because I had a couple of duplicate text strings. I then created an IF LEN SUBSTITUTE formula field called {# Participants Progress Made BY} to count the names separated by commas produced in the {Progress Made BY} formula field. IF({Progress Made BY},LEN({Progress Made BY})-LEN(SUBSTITUTE({Progress Made BY},",",""))+1) *Note that I had used this formula successfully in other tables to do the same thing, however the reference field in the other formula fields was a CONCATENATE formula field instead of an ARRAYUNIQUE formula field. I get an #ERROR message in the {# Participants Progress Made BY} field where there is data (separated by commas) in the {Progress Made BY} field in the record, and nothing retu

Hello!My table is the first step to generate documents with personalized names. Sometime there is 1 name on the document, sometime 2.List of my columns :Name 1Name 2Name 2 + and (formula)I'm trying to find the good way to write the formula of my name 2 + and column : Option 1 : there is a second name and I need « and name 2 »Option 2 : there is no second name and I need a blank field At the moment, if I have no « name 2 » I still see « and » in all my fields 🙂 There is my work/try :CONCATENATE({And}, " ", {Name 2}) (it works fine)IF({Name 2} = BLANK() => don't show "and" in this column. Can you help me fo write this formula please ?Thanks for your reading!Have a nice day.

I'm trying to get this formula to calculate the difference between the date 'today' and the date in the field 'End date', then stay blank if it it's more than 10 days but return 'Yes' if it's 10 days or less. I can't get it to return the right values though. Any suggestions for how I can fix this to get the right result?  IF(DATETIME_DIFF(TODAY(),{End date},'days') >10, " ", "Yes")

Hello!I'm making a table to generate contracts. Sometime there is 1 name on the contract, sometime 2.List of my columns :Name 1Name 2And (just a formula for write "and" in the column).I'm trying to find the good way to write the formula of my and column :CONCATENATE({And}, " ", {Name 2}) (it works fine)IF({Name 2} = BLANK() => don't show "and" in this column.Thanks for your reading!Have a nice day.

I am trying to write an IF function such that the condition in the statement is to look for the presence of a particular word in a multi-select column. If the word is present in the multi-select, the value-if-true should be displayed. In a multi-select column, there is a list of amenities: Airconditioning, Lounge, Studio, etc. I want to create a new formula column that looks for "Studio" in the multi-select column. If it is present then show Yes, if it's not show "No". Something like this: IF(Amenities="Studio", "Yes", "No"). This gives me the correct output if the only value selected is Studio. In the case where the column has "Lounge" and "Studio", I get No as the output. 

Hey dear Airtabler!I have a question about tracking the duration between modifications. A user will type a number in the same field several times a day and I would like to check how long each interval took to use this duration in a formula. How to build such a time tracker? Thanks for all the help in advance! Best, Emmi

I’m setting up a custom ordering form for a family business, where the options that a customer picks will determine some end results like total price, total items, and/or fulfillment time estimates. I’m wondering if there is a way to show a formula field within a form to accomplish this. As a very basic example I might have: 3 number fields 1 formula field that sums the 3 number fields A form that allows users to fill in the 3 number fields I would like for the customer to be able to see the total (formula sum-ing the three number fields) in real-time as they fill out the form, just as I would if I was creating a new record and filling it in inside of the grid view in Airtable. Is this possible or is there another way to accomplish this?

Hey guys,This is driving my brains a bit nuts. I have a base for sales & stock management. In my sales base I have a Date field called INVOICE DATE and a LOOK UP FIELD for the Product's Latest Stocktake.The formula I am trying to achieve here is:IF Invoice Date is BEFORE last stock take (including time) then Result is 'BEFORE'IF Invoice Date is AFTER Last stock take then 'AFTER'IF Invoice Date is AFTER last stock take AND equals or higher than Today+7 days then 'FUTURE'If Last Stocktake is Blank then 'NO STOCK' So far this has worked the closest, although it doesn't have the NO STOCK option and it gets all messed up with I set times and timezones to the date fields.IF({Invoice Date}<{Last stocktake},"BEFORE",IF(AND({Invoice Date}>{Last stocktake},{Invoice Date}>=DATEADD(TODAY(),7,'days')),'FUTURE',IF({Invoice Date}>{Last stocktake},'AFTER'))) Can anyone help, please? 

I'm thinking that there would be a formula to calculate all the days in this pattern described below. But, I just can't seem to crack it.I have a scheduling base that I would like to group by month. But the beginning of this month grouping would need to start on the Monday that is on or before the 1st of the month. This way the grouping includes all 7 days of the 1st week. The monthly grouping would end on the last Sunday of the Month to include all days of the last week. Example: For June 2023... Jun 1st is on a Thursday. So the Grouping of June 2023 would start on the Monday before, May 29th. The grouping would include all rows from May 29th thru the last Sunday of the Month June 25th.Then the next grouping would start. The July 2023 grouping would include all dates from Monday Jun 26th until Sunday July 30th, and so on...The formula would label each row "Year Month#: Month Name" or "2023 06: June". This keeps the groups in chronological order.Thanks in advance if anyone has sug

IF(AND(PHASE="Cancelled",NOT(Notes)),  "Input Re-Exam cancellation reason into the Notes field.",IF(PHASE="No Request Sent",  "Input Re-Exam Packet sent date into the 1st Request field. Take the Due date from the Due field and put on letter."&"\n"&"\n"&"OR update Finalization status to "&"\\""&"Cancelled"&"\\""&" if the Re-Exam will no longer be occuring due to deceased HOH or concurring relocation. Explain cancellation reason in Notes field.",IF(PHASE="1st Request",  IF(AND(IS_After({Due.},TODAY()) NOT({In-Office Date}),{In-Office?}=0), "1st Request is due on "&{Due Expressed}&". Which 

Hello,I want to compare the values of a Rollup field with a Linked Record field.Field A is a rollup that contains the following text:"Value 1, Value 2"Field B is a Linked Record that contains the following records:Value 1 Value 2I want a formula that will check if the two fields are the same. At the moment Airtable is failing whenever a comma is included in Field A. e.g.,Any help would be much appreciated!Cheers

I have a lookup field that I use to pick what Client the new record in my Activities Base is for called ContactsIt auto populates the field record properly when I select the new clientHowever because you can't have the first field contain a Lookup (DUMB BTW especially for rollups and such) I have to put a formula to use the data from the lookup field record to do the rest of my workflow for the clientIt is putting the information in "" (see graphic) (Client field)It did not do that when I just used the old fields of First Name /Last name when I concatenated them (Concatenate field) which used to be the way until we moved into a separate table for Contact informationIs there something I missed in setting up the Account name field or the Contact Field to prevent the " " (quotes) from being shown?

Hi,I am getting this error when I try to upload a string valued date to a "Date" type column in airtable using python script. [Error: {\'type\': \'INVALID_VALUE_FOR_COLUMN\', \'message\': \'Field "Uploaded Date" cannot accept the provided value\'}]Which format will it accept?   

Is it possible to calculate future dates based on the nth occurrence of a day of the week in a given month? For example, I would like to return the dates the 1st Monday in April falls in 2023, 2024, etc.