Formulas

I’m currently working on a setup where I need to reference data from the previous record in my Airtable base. Specifically, I want to pull data from fields in the previous entry to use in calculations for the current record.Here’s what I’m trying to achieve:I have a field called Current Tank Level in each record.I want to automatically populate a field called Previous Tank Level in the current record with the value from Current Tank Level of the previous record.I’ve tried setting up linked records and using rollup fields, but I'm facing challenges with automating the process to ensure the Previous Tank Level field always reflects the correct value from the previous record. How am I able to grab the previous record entry "Tank Level value" and automatically add it to my "Previous Tank Level" record?

Hi all,Wondering if there's a filter or formula solution (not sorting) to filter all results down to only the "Soonest one" ranked according to date field. I have a form in one table linked to a view in another table, ideally so people can only sign up for one linked choice (it's a monthly event registration). Currently I have the filter set for Date within the next month which mostly works, but a day or two before the event, both the current and next month's start displaying (because it's only 28 days away, etc.) Perhaps my logic brain isn't on today, but I can't think of good filter terms to only include the soonest event. The other thought I had was a formula which would assign numbers or filters to the nearest date (-1 for past, 1 for soonest, 2 for next, etc.) and then I could make that the operative filter, but I don't see an obvious example of that in the Formula Reference Base or guidance. Any advice? 

Hi all, I was wondering if you could help me create a formula that would output an alphanumeric ID based on variables in other cells in the row. I'd like to create an ID that include:First, if Cell A = xYx then add "Z" to start. If Cell A = YxY then add "ZD" to start.Then, take first letter from name and surname, and then add these. So John Smith becomes JSThen, add first 4 digits from date field (DOB). So, 14/11/1980 becomes 1411So, as an example, ZDJS1411.Thanks so much community!

My Rollup field has (correctly) returned this array to me ARRAYJOIN(values) by looking at linked records (4 People) and their dietary requirements (dietary preferences are determined by a multiple select field):None,Allergy to Nuts (other than Peanuts),Gluten-free / No beef / Allergy to Nuts (other than Peanuts),NoneAnother formula field formats this array into an easier to read arrangement:1 x None1 x Allergy to Nuts (other than Peanuts)1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)1 x NoneWhat I would really like is to find a way to combine any duplicated items and show the correct count for them, such as:2 x None1 x Allergy to Nuts (other than Peanuts)1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)Any advice on how to do this please? I want any formula to work dynamically with the content of the array, as this will mak

Hello, community! I need to create a formula (or find some other way) to keep track of changes to individual cells in "column A" via a second column that auto-populates with first entries in column A but does not change if column A changes after that. So for example:  Column A = Project Start DateWhere a user manually enters the date they begin a project.Column B = Original Project Start DateWhere it auto-populates with all existing dates in Column A + new dates added to empty cellsBut does not update itself when a user manually changes the date in Column A after that. The goal is to be able to allow users to adjust or move out their start dates as needed but also keep a record of what the original date was without adding any other manual steps. I really hope this makes sense! TYIA for any help or ideas! 

I have a linked table called "Voucher Codes" and as you can see from the attached screenshot, I can click the + and select a voucher code from this linked table and assign it to a record in the Order Fulfillment table. This is great, it works, however it shows everything in the view "Available" in the "Voucher Codes" table.  Is there a way I can have it ONLY show voucher codes in the "Available" view if it matches the "Course" field in the "Order Fulfillment" table as the attached screenshot shows? For example, if the Course says ITIL 4 Foundation w/TAKE2, it should only show voucher codes that are titled ITIL 4 Foundation w/TAKE2.  And if it the Course says ITIL 4 Foundation, it should only show voucher codes that are titled ITIL 4 Foundation, and so on. Is this possible?

I have various contracts and various contractor invoices that I need to review, and I want two formula fields to confirm whether the billing periods covered by each invoice - and the invoiced amount - are "valid". My table is pulling in the contract start and end dates, as well as the budget balance and total invoiced, from another table. Right now, I have 3 manual entry fields: 1 for Billing Period Start date, 1 for Billing Period End date, and 1 for Invoiced Amount. I created a formula field to confirm whether the billing period falls between the contract start and end date, but it currently shows billing periods that start or end on one of those dates as invalid. IF({Billing Period End}<{PoP End},IF({Billing Period Start}>{PoP Start},"Valid","Outside PoP"),"Outside PoP")I thought I could adjust the < to <=  and so forth, but figured there must be a more elegant way to do this. I al

I'm building a base to help track my company's product lineup and I've built an Interface for each one of us 3 founders have our own To-Do interface, populated from a To-Do table. This To-Do table gathers information from other tables such as customer's name, product selection and other items. I want a column "Extended Task" to populate with a short checklist automatically, based on a selection of a "Task Group" field (which I've made a Single Select drop-down field in an effort to keep the names consistent). I thought of using an IF formula, where IF "Task Group" is "Quality Check", then the "Extended Task" field would get populated with "Verify Unit Powers On. Verify IR Remote Works. Visual Examination. Inputs Tested.  Doors Open Smoothly. Lights Operational." in checklist form. But not only can I not figure out how to do this complicated IFTTT formula, but I am also not sure if this i

Hi all, So I have created a base here (it is a gallery view)https://airtable.com/appPv62y3frc8BYJA/shrVTQGn7420FaGvUEverything in the base works as it should. The single select dropdown works great, but in the embed view, the single select dropdown doesnt exist. As in, there is no dropdown feature. Any ideas?   

How do I convert a duration into seconds?I would like a formula for the "Seconds" column that automatically calculates this. Understand this is probably so basic, but would appreciate any help. Thank you!

Hi all,wondering if you could help with the below, I need to give 1-5 rating to certain value ranges. Its daily revenue and you get rated based on your daily performance.This is the formula: =IF(AND(K2>=0,K2<=30),"1",IF(AND(K2>30,K2<=80),"2",IF(AND

Hello Airtable Community,My table includes an event title field as well as one for an event date.Event Title:The Chemical Regulations LandscapeEvent Date:June 6, 2004Formula:LEFT({Event Title}, 16)&" - "&{Date}Formula Result:The Chemical Reg - 6/6/2024All good so far…but there are also records that have an event title and no event date, so the formula above  returns #ERROR! for these records.So, for the records sans event date, how can I modify the formula so that i

Hello!I want to use the results of an extension into a formula to automate a field.For context, we are using a SendGrid extension to send out emails and once those emails have been sent I want to be able to automate a status field to change from 'email to be sent' to 'email sent' - any idea on how I could do so? I'm also hoping this means that it would keep the emails that have failed to send as the status 'email to be sent' so we can repush them.TIA!

Hi,I am using AT for the CRM purposes. I've got a list of proposals we're working on within my Proposals Table: https://airtable.com/appGL773TiIYzLwZD/shrzetl6rN5gnxAYzNow, what I'd love to calculate is: based on the stage selected (and % probability assigned to it) and proposal value, I'd like to calculate probability value.So if stage = "Proposal + SOW submitted (50%)" then, it's 50% of the value.Do I need to create another table for the probability? Have my stages outlined, then % and create a formula within my 'proposals' to calculate it?Thank you!

If I use this formula:IF({Whole-Body Conditions}!=BLANK(), {Whole-Body Conditions}, "") & IF({Body: Left Foot}!=BLANK(),CONCATENATE(", ", {Body: Left Foot}), "")I get this result when {Body: Left Foot} is NOT empty, WHICH IS WHAT I WANT: But when {Body: Left Foot} is EMPTY, I get this result that has an extra COMMA:When I take out the comma in the second IF statement, then t

Hello - I need to calculate a date, in certain circumstances, that is 75 days out from another date in my table. I’m using this formula:SWITCH({S/N}, 'no', DATEADD(DOB, 75, 'days'), 'NOW',DATEADD(DOB, 75, 'days'),'NA')which calculates correctly, but includes a time stamp in the results (2024-07-24T00:00:00.000Z). What do I need to do to my formula to get the date only, in mm-dd-yyyy format? My apologies if this is covered somewhere else. I’m not finding exactly what I need in the guides or

Hi,I need help creating a formula for scoring a quiz that I am making in Airtable. The questions are a combination of single select and multiple select. There are 100 questions total and each question is worth 1 point if answered correctly. The multi-select questions must be answered exactly right to get 1 point, otherwise they get 0. I am running into a problem with the multiple select questions in the formula. With the way I have it written, it will count 1 point if all three of the correct selections are selected in any order. BUT if one of the incorrect selections are selected in addition to the three correct selections, it is still counting 1 point. How can I tell it that if any one of the incorrect selections are selected that the score should be 0? In this particular example A, B, & C are correct. D and E are incorrect and should not be selected for them to get the point. The highlighted area in the attached formula is the part of the formula for the multi select q

I have a field that has a ton of linked records and I'm trying to format that into a nice list for use in the Pages extension.  It's generally coming through with commas and quotation marks separating it.  I found this formula in another thread, which seems to remove the opening quote marks, but not subsequent. IF(LEFT({Marketing Tactic - Detail}, 1) = '"',SUBSTITUTE(MID({Marketing Tactic - Detail}, 2, LEN({Marketing Tactic - Detail}) - 2),'""','"'),{Marketing Tactic - D

Hi!Two formula questions - I have (4) different date fields with timestamps and need to identify the latest date across the four fields. When doing a Max value, it does not bring in the timestamp or makes it 12:00a as a standard. I also have (2) date fields that I need to calculate the datediff in hours and leaving the field blank if the second date field is empty instead of the NaN.Please help! Thank you 🙂

Hello.I am trying to find records where a rollup results is less than or equal to a function. Both are formatted as numbers, and both work when I search for records in each column independently. Is there a solution for this?

I have a somewhat complicated formula I wish to create, and as a formula n00b, I don't know where to start. Here's the problem I'm trying to solve:1. I have a series of tags I enter into a single text field, where each tag is separated by a comma. I have two conditions to meet before I list a product with the tags, otherwise, the Make.com automation won't work, and I'll have to spend a fair amount of time deleting a partially created listing and re-editing the record in Airtable. These conditions are 1) I can't have more than 13 CSV tags in the field, and 2) I can't have any duplicate tags.2) I would like to create a formula that displays an ❌ in the formula field if the tags CSV field has more than 13 tags AND/OR duplicate tags. If both conditions are met, display a ✅.Thanks in advance to all the formula wizards out there willing to help! 

Hi all--We are a publishing company and we sometimes publish the same content multiple times throughout the year. We have a field set up "Publish Date" that shows all of the dates. The problem is when I try to have these feed into our general publishing calendar view, I can either only choose between the Minimum date (the first date) or the Maximum date (the last one), I can't figure out how to show both dates on the calendar (without having it span across the entire time in between the two dates). Is there possibly a way to separate the dates into separate entries and enter them into the calendar that way? Thanks in advance!

So Im pretty new to Airtable and not sure if I am going about this the right way and wonder if someone can point me in the right direction if not. Basically I am trying to use a multi select option to calculate the Extra costs involved in a job with solar installations which I have a field named Extras. So lets just say I have an option of installing on a tile roof which costs an extra 50 which I have added using the formulas below in a separate field called Extras Total.  IF(FIND('Tile Roof', Extras),50) +IF(FIND('3 phase', Extras),200) +IF(F

I am trying to add an AND statement to this formula:IF(     {Date Received} = "",     "Unsure of due date",     IF(        DATETIME_FORMAT({Date Received}, 'MM/DD') = DATETIME_FORMAT(DATEADD(NOW(), -14, 'day'), 'MM/DD'),        "TRUE",        "No match"   ))The AND statement I want to be if a cell does not equal a certain value from a dropdown. So I think on its own, the and statement should be something like:{Status} != "MoveOn"I just can't figure out where to put each piece and how to close the parentheses. The screenshot is my table without the AND part of the formula.Any help would be greatl

Hello AT Community, I am looking for a formula that capitalises the first letter of every word before the " |"  in a string. 3 examples belowEXAMPLE 1The output for "ARCHITECT | Tom Jerry” to be ArchitectEXAMPLE 2The output for "BUILDING AUTHORITY OFFICER | Road Runner" to be Building Authority OfficeEXAMPLE 3The output for "CIV