Formulas

Subrecords is the one aspect I don't like in Air Table and I am struggling with a case right now. Maybe somebody has a smart and simple solution. I feel my design is flawed but I did not find any other practical way to work this.Let's say I haveTABLE AField 1: Location name (text)Field 2: Sub-location name (text)Field 3: [Field 1] & [Field 2] (calculation field)Field 4:: A chekmark field to filter out sub-records, so I can a list of records without seing all the time I repeat Field 1.TABLE BField 1: Area code (text)Field 2: [TABLE A Field 3] (linked) What I would like is a list of unique records of Table A Field 1, with all the linked records in Table B Field 1.And I can't seem to find a simple solution. 

I am trying to figure our how to use rollups. Let's assume I have a list of applicants and associated interviews. The interview has "interviewer name" field.Soapplicant1 -> (interview1; interview2)Interivew1: JohnInterview2: RebeccaI am creating a roll-up and trying to figure out how can I access interviewer names (John, Rebecca).Rollup references gives some formulas that can be used in rollups, but is there a way for me to access the values?Let's say I want to send an automated email and sign it with the name of one of the interviewers. 1. SQL allows me to do something like ANY() to get a random name from the list of interviewers. Is there something similar in Airtable?2. What if I want to SORT BY interview date and access the name of the FIRST interviewer? (so not just any, but also do some manipulation with the array) is it possible?Thanks!

Hello communityAgain faced with this, hope to get a simple workaround for this where I can get a formula that extracts URL from a text field. Thanks

Hi everyone,I need assistance with getting the proper year in an ISO week start date so that I can get a valid week start date. I have the following formula: DATETIME_PARSE(DATETIME_FORMAT({Date Field}, "W"), "W") Month and day outputs from this method are accurate, but a year is not. It is stuck in 2023, the current year. Can somebody assist me? It's acceptable if I need to use more than one field to accomplish this, for example, one field to obtain the proper year and one field to obtain the correct day.Sincerely appreciated! 

We have multiple dates that we need to have automatically update for us in table.Example:Move-in date (4/1/23) plus policy day (60) = Lease end dateLease end date - NTV date (30) + 5 days= NTV date needArrival Instructions date = 2 days prior to Move-in date (4/1/32)How would you go about doing this in a table? 

Hi, Using the formula below I have an ERROR value issue when there is no date value available for {last deal stage change}. Can anyone advise how I would alter this to just leave a blank space or "no data" where there is no date value available in the date field column?I need to use the datetime format to tidy up and remove the timestamp TRIM({Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & DATETIME_FORMAT({last deal stage change},'DD/MM/YY') & " | " & {Deal Stage})Thanks!

Hi Community, I have 5 fields (checkbox) that can be selected or not, I need to assign a status triggered by these fields, if any field of the 5 is selected then a status will be set and if no one is selected It will assign a different status. Thanks for the help.

Hi all: I'm trying to simplify the process of converting a short ID code to a longer ID code inserting leading zeroes where needed. The ID codes are structured to track Week, Day, and Segment numbersFor example, if the input data is 1.2.3 (week 1, day 2, segment 3), the necessary output needs to be formatted W01.D01.S03The wrinkle is that while day and segment are always going to be a single digit, the week value could either be one digit or two digits. I've managed to get it working with a series of breakouts that takes the input field, breaks out the week, day, and segment into separate field, adds leading zeroes where necessary, then concatenates them back into the final output id. But it takes 8 separate steps/unique fields to accomplish.  I'm looking for a way to do it in fewer steps. Here's how I have it working currently:Input: "ShortID" (e.g. 1.2.3, 12.7.1) -- manually entered 1) "BreakoutWeek"   LEFT(ShortID, FIND(".", ShortID) -1) 

Hello, I'm trying to check if my "inputValue_1" field occurs in a string pulled from a lookup field (named 'test' in the screenshot below). I'm trying to use this to check if inputValue_1 has a valid input, as defined from this lookup field.The formula I'm using is `FIND({inputValue_1}, {test})`, which should ideally return the position where `inputValue_1` occurs inside of `test`.  I've tried SEARCH(), but just get errors. This leads me to believe that FIND() is right, but something is off, since I am just getting position zero no matter what. I would expect `new south wales` to return 1, and `victoria` to return 18. Any help is appreciated.

I have addresses in multiple lines and am using REGEX EXTRACT in Airtable to separate the components (street address, suburb, state, post code) before sending them on for shipping labels.I've used REGEX_EXTRACT({text input}, "^(?:.*\n){1}") to extract the first line and this works in most cases except when the street address is over two lines (see example B).Can someone please help me with a REGEX EXTRACT that extracts every line except the last line (whether the address is 2 or 3 lines)?Or is there a simpler solution I'm not aware of? Example A1 Test StreetRichmond, Perth 3021Example B1 Test StreetUnit 6Richmond, Perth 3021For reference on how I'm dealing with the rest of the components...I've used REGEX_EXTRACT({text input}, "^.\r?\n(.)") to extract the last line (Subrub, State, Postcode) and then used REGEX_EXTRACT({text input}, ".*(\d{4})") to extract the post code.Once I have the post code I'm matching it against a database to find the Suburb and State. I did this because I b

Hello,I have a multiselect column "Products" with empty, single, or multiple strings of product name inside.E.g. Xbox 360, PS4, etc...I want to create a separate column's, based on data from another table, that would take into account that column.1. Would calculate number of separate strings (Clear)2. Would replace string with number (Price of that product) (Data of price is stored in another table)3. Would calculate total sum of all those product prices. How would you recommend to achieve that, via Replace, Scripting or something else? And how? Would highly appreciate help!

Hi,We create forms to capture registrations for workshops. The dates and times of these workshops are set as Singapore time, but our participants register from all around the world, so we need these dates and times in the form to show up in their local timezone.This used to happen automatically, but now it doesn't.Can someone show me how we can have these dates and times show up in their local timezone? But in this case, I do not know which timezone the participant will be in when they are registering, so do not have the specific timezone.Thank you in advance

Hello, I want to calculate the total cost per tickets ONLY IF they are a specific type, and then I want to deduct that from a total amount that I set manually. My fields are currently set up: Total tickets (# field)Per ticket cost ($ currency)Total cost (simple formula: {Per ticket Cost}* {Total Student tickets}) Revenue source (single line text)Right now, I have "IF({Revenue Source}="X", {Total cost}-40,000)", but it's returning 0. Any tips? Thank you!! 

Hello everyone!So I've written a formula, it works, but I need a few tweaks to it so that it runs exactly as I want it to. The thing is: I am stuck. The formula looks like this:   IF( {Betalingsmetode} = 'PBS', IF( OR( WEEKDAY({Trækdato (FarPay)}) = 2, WEEKDAY({Trækdato (FarPay)}) = 5 ), {Trækdato (FarPay)}, IF( {Trækdato (FarPay)} < DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'), DATEADD(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'), IF(WEEKDAY(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY')) = 3, 0, 7 - WEEKDAY(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'))), 'days'), DATEADD({Trækdato (FarPay)}, MOD(10 - WEEKDAY({Trækdato (FarPay)}), 7), 'days') ) ), IF( OR( WEEKDAY({Tilføjet d.}) = 3, AND( {Betalingsmetode} =

Hi Airtable Community!I'm not sure what I'm doing wrong with this formula. We are changing our pay rates on April 1st and I want to program my payment field to reflect this change. My table has a date field called "Fecha !!!" and a lookup field for our new rates and our old rates.If the record has a date before April 1st I want it to use the old rates. If it has a date on or after April 1st I want it to use the new rates. Here's what I have so far! I'm a beginner! Thanks for your patience and  advice 🙂

I have four Airtable fields {Total Cost}, {Event Date & Time}, {Equipment Charge}, {Event Duration}, and {Number Of Techs}If the {Event Date & Time} happens during the Day time, the rate is $69.If {Event Date & Time} happens during the Evening the rate is $79.If the {Event Date & Time} happens during any holidays, the rate is $105.If the {Event Date & Time} happens during a weekend, add $60 to the {Total Cost}.I created a formula to calculate the {Total Cost} but it is not working. Your help will be very appreciated.See the Formula below:------------------------------------------------IF(DATETIME_FORMAT({Event Date & Time}, 'h A') >= '7AM'AND(DATETIME_FORMAT({Event Date & Time}, 'h A') < '7 PM'),{Event Duration} * {Number Of Techs} * 69,IF(DATETIME_DIFF({Event Date & Time}, DATETIME_PARSE('2023-01-01'), 'days') IN [0,1,2,361],{Event Duration} * {Number Of Techs} * 105,{Event Duration} * {Number Of Techs} * 79)) + IF(DATETIME_FORMAT({Event Date &amp

HiI've more than 10,000 questions and counting, and many questions added daily, the issue is finding and labeling then deleting the duplicate, duplicate are not exactly unique same question written with similar words but different context , and sometimes exactly the same but with extra word.i used the 2 duplicate extensions and it is very useful but it need too much manual work (see attachment )so my question, what Formula can i use to label any question in separate column and flag the question as duplicate and linking the duplicate form with lookup/rollup, so i could ? i wish the result to be like that . thank you , i will really appreciate your answers.

Hi,In table Courses, I have a "Related Lessons" field that is a link to table Lessons"Related Lessons" field contains 3 Lessons' records: L1, L2, L3In table Courses, I want to create a second field, named "Open Related Lessons" that contains 2 linked records only: L1, L2. The remaining L3 records should be excluded based on filters.--> How to achieve that?

Hi everybody, I'm trying to come up with a formula that would add after some text (taken from another field) the number of spaces needed to reach a total number of character.Let's say I have to get to 10 total character, starting from the text from a field name "Text".If text is "ab" I would need the formula to add 8 spaces after "ab" in order to reach 10 total charactersIf text is "abc" the formula should add 7 spaces after "abc" to reach 10 total characters, and so on.Is this possible?

Hello there,I have been using Airtable for a month and I absolutely love it.I am creating a basic HR system. I want to create a formula for the employees’ paid leaves.How do I automatically make it add up in Airtable, using the Hours off (8 hours/day)?Is there a formula for that?Thanks in advance.

Hey guys, I am new to AirTable. Here is the problem I am facingI have 2 tables, Apartments and Locations1. An apartment can only have 1 Location.2. A location can have multiple apartments. I want to do an API call to filter apartments with some contraints which will return me a max of 100 apartments.It is possible that all those 100 apartments belong to 1 location so my end result is 1 location. Is there any way I can add a filter that returns me unique values?Eg. If apartment 2 has the same Location as Apartment 1, return it just once and continue. I want my end result to be 100 locations in 1 API call.

Hi!I was wandering if it is possibile to use customers' emails as unique ID through with connect different tables (ex. Table Contact, with only info about the customers; table Customer journey, with info about first contact, conversions,...)Thank you!

I'm new with these and I think i'm having a big mistake but I cannot find it.I'm trying to extract the number of hours for an specific duration record.Obviously I use HOUR command but it doesn't work!As you can see in screenshots, the field Hours works in the following way:Hour({Time})But, it generates ERROR!What am I doing wrong?Thanks!  

Hi all, is it possible to have a formula that searches if a text field contains at least one item of an array?Specifically, I would like to achieve the following:I define topic names and related keywords for each one manually in table #1:TABLE 1: TOPICName (string) | Keywords (Multi-select) Animals | dog, cat, elephant,Fruits | apple, banana, orangeColors | red, blue, yellowI have some articles in another table and I want to update the Topic field by checking if the article title contains items of the array field from table 1.TABLE 2: ARTICLESTitle (string) | Topic (Lookup) (this should be the field updated by the formula)The dog is sleeping | AnimalsThe apple is red | Colors, FruitsI know I can use a formula and manually define keywords combining IF, AND, OR, FIND etc.However, topics and keywords frequently change, so I should update the formula every time a new topic enters the table.Instead, I want a formula that searches if any item of an array (Keywords field) is contained in

Hi Airtable ExpertsWe have a formula field field which calculates the conversion from euro to dollar, including the import duty. The outcome is shown in USD (field  "exwUsdStandard" = currency, 2 decimals to the right), this works fine.The next field ("exwUsdSpecial" = currency, 2 decimals to the right) shows a price in USD), is shown perfectly in the record. This value is not calculated with a formula.Now, I want to combine these two fields into a new field ("priceExwUsd"), however, I am not able to format it as currency $ with 2 decimals at the right side of the ",".What can I do to make it as currency, $, 2 decimals to the right?Thank you in advance 😄Gerben (from the Netherlands)