Formulas

Hello to the AT community, I need to list some related records with arrayjoin(linked field,"\n") in a formula field. Unfortunately, this is not working on linked records as it is on lookup records. (See picture below). I would d like to avoid creating 28+ additional lookup fields in the table (this is a person table linked with 28 project tasks table) to get the proper list with carriage return. I will use this in automatic SendGrid mail blocks. Does anyone knows how to arrajoin linked records instead of lookup records. Thanks a lot. Thierry

I tried the already existing topic about duplicates but was unable to find what I am looking for. So basically, I have a list of applicants listed chronologically according to the day they applied, and each of the applicants has their own unique ID number. What I need is to be able to see automatically if someone with the same ID applies…I don’t need to remove duplicates from the list, nor to regroup them or anything. I just need them to be marked somehow in case the same ID occurs, so that I can now immediately if someone is applying again or for the first time. I google sheets I use the =countif(A:A,A1)>1 …is there an equivalent in airtable? Thanks and sorry if this was already asked.

Hi! I had the following situation when using the following formula to compare dates and determine the current row as the current date. the first part state if the count I less or equal to 1, that’s the current row, then if the count is above 1 and now is greater than the start date and now is previous to next date or next date is empty, that’s the current row IF({Count}<=1, “current”, IF(AND({Count}>1, Now() > {Start date}, OR(Now() < {Next date}, {Next date}=“”)),“current”)) This would give the correct output. But when I tried using instead of NOW() > TODAY() IF({Count}<=1, “current”, IF(AND({Count}>1, {TODAY()} > {Start date}, OR(TODAY() < {Next date}, {Next date}=“”)),“current”)) It would throw a completely different result, completely shift on dates. WHY?

Hi, I’m trying to figure out why I’m experiencing issues with my nested IF(AND()) function on Airtable. When I limit the fields in AND() to just two conditions, it works, but anything else and Airtable won’t let me use the formula at all. Please advise! Thank you. What works IF(AND({Person}= “1”, {A}= “Yes”), “Approved”, IF(AND({Person}= “2”, {B}= “Yes”), “Approved”, IF(AND({Person}= “3”, {C}= “Yes”), “Approved”, IF(AND({Person}= “4”, {D}= “Yes”), “Approved”, IF({Quote} = “0.00” , “Temporary approval”, “Unapproved”))))) What doesn’t IF(AND({Person}= “1”, {A}= “Yes”), “Approved”, IF(AND({Person}= “2”, {B}= “Yes”), “Approved”, IF(AND({Person}= “3”, {C}= “Yes”), “Approved”, IF(AND({Person}= “4”, {D}= “Yes”), “Approved”, IF(AND({Person}= “5”, {E}= “Yes”, {F}= “Yes”), “Approved”, IF(AND({Person}= “6”, {E}= “Yes”, {F}= “Yes”, {G}= “Yes”), “Approved”, IF({Quote} = “0.00” , “Temporary approval”, “Unapproved” )))))))

Hello! Is these a way to reverse this? To show 2023/Q1 instead of Q1/2023. IF({Your Date Field}, “Q” & DATETIME_FORMAT({Your Date Field},‘Q/YYYY’) )

Hi everyone, Hitting a bit of a wall with some formulas and wondering if anyone has a more elegant solution to this… I’m working on a new base that records start and end dates of some work that we do. I then have a datetimediff that calculates the number of days that the record was running (see mocked up example below). We compile reports annually, and I regularly need to show days of work that only fall within the previous calendar year. At the moment I calculate this by pulling off a CSV, adjusting the start and end dates to only cover the range and do a DATEDIF in Excel. Can anyone think of an automated/conditional way of only counting days that fall within a certain calendar year? As you can see above, reporting for 2021 would involve projects 2 and 3, and reporting for 2022 would involve projects 1 and 3. Any thoughts/ideas?

Hello, I have formatted the date but the hour displayed shows 1 hour less. How can I display the exact time in hours and minutes in the field {date formatted} ? The formula I use is : DATETIME_FORMAT({Time slots},‘DD-MM-YYYY à HH:mm’) How can we solve this ? :thinking:

Looking for help creating a formula to output one of these answers based on a person’s meeting start date and the completed date of the program. Meeting Status field (formula): • Done • ToDO • NA • Upcoming My fields are- • Meeting Start Date – • Program Date • Current Date Field If a person has a meeting start date of 11/1/2022 and the program date is 12/1/2022 then the field should output “NA” If a program is scheduled for a future date, then the output should be “upcoming”- If a person hasn’t completed the program, the output should be “ToDo” If a person completed the program, then the output should be “done” I appreciate any help.

Is there a date formula to calculate the 1st of the month based on a date? For example, I want to calculate 11/1/2022 for 10/15/22, 10/5,22, etc. = Wendy

Hello- Trying add a field that can add days to my formula, any thoughts? IF({DC Arrival Date}=BLANK(), BLANK(), IF(OR({Production Type} = ‘Domestic’,{Production Type} = ‘Puzzles’), DATETIME_FORMAT(DATEADD({DC Arrival Date},-56,‘Days’),‘MM/DD/YYYY’), DATETIME_FORMAT(DATEADD({DC Arrival Date},-98,‘Days’),‘MM/DD/YYYY’)))

Apologies, very new to Airtable. I have a view based on a table that has players weekly scores. The view is group by Player Name and Sorted By Score (Desc). In each group I would like to add a rank column so then I add an additional filter to filter by Top x scores per player. I tried autonumber but would need autonumber to reset for each player grouping. Any ideas? Thank you Mark

Hello, im trying to introduce a new formula field but for some reason it is just highlighted in gray and won’t let me type anything on the formula field.

Hey All, I’m sure this is somewhere and I’ve searched but I’ve not seen an explanation to certain “things” within the REGEX_EXTRACT field: REGEX_REPLACE(Email, “(\w+)\.(\w+)@.+”, “$1 $2”) for example, I get what it’s doing, but not sure of those internal things, like \w+) etc. What I need is really simple and I was thinking I’d use the regex to extract just the @email.com part. IF({Attendee Email - for Invites}, REGEX_EXTRACT({Attendee Email - for Invites},UNKNOWN PART OF FORMULA),“BLANK FIELD”)

Hi, I have the folowwing formula: IF({Type Relation} = "Mr","ok","pas ok"), it’s just a test because in my make automation i’ve a problem, and now I understand why, because in my airtable base the formula doesn’t work correctly in a first try. When my automation write “Mr” in my “Type Relation” field, the formula don’t see “Mr” in the first try. If you see the next screen, you can see that when I remove the final “r” of “Mr”, the formula is always to “pas ok”, it make sens and no problem here. When I rewrite the “r” to have “Mr” (What I had in the beginning), the formula is updated correctly to “ok”. Have you ever seen that ?

Hi! I need to create a formula that concatenates the First Name and Last Name columns. I could do that part easily enough. But I also need it to look to see if there’s a value in the Preferred Name field and if that field has a value in it, to substitute that for the First Name. Is this possible? How would I do this? Here’s a screenshot, for reference:

I have an Inventory base that was not created by me, but I’m the owner now. I have a Materials Checked In table, that I need to sum up the qty received by PO and description. I tried using the Roll up, but it pulls all of the items by description, which is not helpful when I need to see items received by PO. This way I can see the Ordered Qty, Received Qty, then have an Open balance remaining per PO.

I use Notion as a notebook to keep track of the formulae I’ve written in Airtable, it has a handy “code” block to highlight language syntax. Does anyone know which language I should select that’s most similar to Airtable formula syntax? It doesn’t have “Excel” as an option.

hi, I really have no idea what Im doing here, I have a field that is a series of number and letters followed by a ( then more letters and numbers followed by ) example field 1 bob( Joe ). and I want Bob the amount of chariters inside and out of () is variable, the only thing I can go off of is the ()

When creating a formula field with an IF statement where I want to check the value being returned before deciding to use that value or display something else I run into an issue where it seems terribly inefficient to have to basically have to call all the functions twice. IF({INSERT CONDITION HERE WITH A LOT OF FUNTIONS} > 0, {INSERT CONDITION HERE WITH A LOT OF FUNTIONS}, “NO”) Is there any way to avoid having to basically calculate a value for something twice when using it in an If statement? I’d hate to have to make a field just to hold calculated values that are only useful so that the IF statement doesn’t need to calculate the same thing twice. Our fields are already a bit bloated as it stands. Any creative solutions?

I am getting in inexplicable result from a RIGHT(SEARCH()) formula. My .csv import includes the field [Payroll Name} formatted as LastName, FirstName. I found a name reversal formula on this thread: RIGHT({Payroll Name}, SEARCH(‘,’, {Payroll Name})-2)&’ ‘&LEFT({Payroll Name}, SEARCH(’,', {Payroll Name})-1) The formula returns the last name correctly, but the first name returns a maddening variety of results. Some first names are correct, others include the comma and space from {Payroll Name}, some include the last letter of the last name, or remove the first, second, or third letter of the first name as shown below (The FirstName and LastName fields are the respective portions of the {Name} formula, shown for clarity). I have tried a variety of values for “-1” and “-2” (among other things), with unsatisfactory results. Of course I can correct the first name problem in Excel before importing, but I am super curious about how to fix it in Airtable. What is going on here? How d

Hi everyone! After checking several topics I still couldn’t find something to solv the problem I’m facing, so here I am. We have a table called Tasks and the following fields are important for this: Due Date (Date field) Completed (Checkbox) Completed within due date (formula) We added a Last modified time field to look to Completed field, so it returns the date that collaborator finished the task. To compare the date that Completed was checked with the due date, I tried the following: IF({Due Date} = {Last modified time},“1”, IF({Due Date} > {Last modified time},“1”, IF({Due Date} < {Last modified time},“0”))) The problem is even if Due Date and Last time modified has the same date, the formula field is returning 0 and my guess is beacause of the time. Due date was not created with time - because it doesn’t metter for us - but if we allow time, it shows “11/16/2022 00:00”. So Last time modified will always be after due date since we will always finish our task after 00:00. And

Hi community! :ukraine: I need some help with some basic formula creation. I have a 2 table, first - my clients, second - affiliates. I want to count how many clients each affiliate bring to my services. Each affiliate has thair unique ID, witch are in bouth tables. I clearly understand what formula should do, but I’m new to any programing skills so do not know how to wrote it correctly using formulas syntax. My formula should find affiliat ID in Clients table, count them, and show result in Affiliat table alongside to each affiliate. Will be thankfull for any advise with this.

This is a newbie question. I have a sequence of numbers in my inventory and these are not auto generated, so I am either working with Number or Formula here. I am trying to add zeros to a sequence so that all numbers have 6 digits. Currently, my numbers start at 0 and go up to 4 digits, I think I am looking for an If / Then LEN formula, but I can’t figure out the syntax to make it work. For example, I need 5 to read as 000005, and I need 1401 to read 001401. etc. Thanks in advance.

I would like to omit the semi-colon when a field is blank, and I know it can be done using IF, however my brain cannot wrap around how to pull it together! Can anyone help? Here’s my example: The current Formula I am using: Much appreciated :slightly_smiling_face:

I need a formula column to show weeks until a Due Date. I have a Due Date column, but I can’t figure out how to set up the formula column to calculate the number of weeks from today until the Due Date. And I need it to update continually. Is this a possibility? Any advice on making this work? Thanks!