Formulas

I've got a single select field for an address, and I want to autofill a secondary field based on that selectionIf 123 Street is selected, then it returns the corresponding email, etc.I'm using the below formula, but it keeps returning info@email.com regardless of the selection. IF(OR(Address="1234 Street",Address="1234 Road"),"Info@email.com","Info@email2.com") What am I doing wrong?

So, I have over 3,000 people in my database that people can opt in to help the charity in numerous ways like being: volunteers or subscribers or sponsors or trainers etc etc but some people has said they can help by doing a few of those options.My problem is that this data is pulled in from other platforms (Active Campaign, google forms etc etc) and I have zapier populating my CRM everytime a new form is submitted.  This means I currently have 3 records for a 'Mr Smith@email.com' as he offered to be a 'volunteer' and a 'sponsor' and he as subscribed to our newsletter.  So he now has 3 different record ID's as my CRM creates an ID for each new entry. I need a formula or an automation that creates a Unique ID that is linked to an email address so if he registers for a 4th option his Unique ID will automatically populate the ID field with the same ID as the other three if that makes any sense at all!! 

I have a column of time duration.  How do I add all of those durations together?Picture to show what I am trying to add up for a total time

HiI need a function that I will use to count the numbers of the same value in a column here is an example that explainsthinks

I'm using a date field {Date Processed} and a formula field {Report Periods} to interpret that date and produce a comma separated list to find the year, quarter, month, week, and day. I use this for reporting. Check out this screenshot showing how 12/31/2023 (USA time format) is messing up the formula.The final day of the year should be in the 53rd week of the year. Instead it's starting over at week 1. I believe this is a bug in Airtable. But please let me know if there is any known solution. The formula for the {Report Periods} field is:IF( {Date Processed}, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( DATETIME_FORMAT({Date Processed}, "YYYY-ⓨ, YYYY-ⓠ-Q, YYYY-ⓜ-MM, YYYY-ⓦ-ww, L"), "ⓨ", "year"), "ⓠ", "quarter"), "ⓜ", "month"), "ⓦ", "week" ) ) 

Is there an airtable formula that combines linked records from a column (say column 1) and another column (say column 2) and outputs it as linked records in column 3?  If not formula is there a way to do this without scripting?

Hello everyone,I've been racking my brain on this question for over a week and have tried countless attempts to solve this, but am still spinning my wheels. Basically I'm aggregating total work history of all our staff across all our events this year, so I need to calculate how many shifts someone has worked. The issue is that it's possible to have multiple records of the same worker per event because different supervisors may submit the same record with different ratings.In my example, here are all the reports (records) that we received on 1 worker for the year (Table 1):Here is the summary record (Table 2):In the summary record (Table 2), Total shifts should be "9" and successful shifts should be "6". The current formula in the ROLLUP SUM(ARRAYUNIQUE(values)) doesn't work because if any event has the same number of shifts as another, it'll get skipped. Because there's 2 records of DF23, I can't use SUM(values) on the "# SHIFTS" field either. I've been able to identify

Hi!So I'm trying to set up a table for our home school group to house everyone's birthdays. I've scoured the forums and can't work out why I'm getting an error on all dates on or after 13/05/2019. All dates prior to this work fine and I just can't figure it out! I also wanted to see if I can incorporate a set_timezone formula to ensure it's based on my Australian timezone. Here's what I have set up:DOB field (members will enter their DOB via a form) and a formula field "Birthday (this year)" with the following formula: DATETIME_PARSE(DATETIME_FORMAT({DOB},"DD/MM")&"/"&YEAR(NOW())) Questions:1. I'm getting an ERROR for any DOBs that are from 13/05/2019 onwards. 2. Do I need to add a SET_TIMEZONE formula to set the timezone to Brisbane, Australia to ensure the 'NOW' portion of the above formula will be correct throughout the year? 3. If I wanted to add another field titled "Birthday (next year)" how would I add 1 year to the above formula? Thank

I have the following formula to set the entry for "Current Month": DATETIME_FORMAT({🟢 Start Time}, 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM') It seems to be working just fine, however the last entry from last month is setting the formula to 1.If I change the start time (right now) to 6:30pm or anything older, the formula gets set to 0. My first thought was a timezone issue, but I confirmed on the Start Time field that it is set to my timezone (CDT).Am I missing something obvious?

I have a date & time field.For example, below is the data. I will have a different date and time and 100 rows with data.1 Jan 2024 1:00 PMAt 1:00 PM on 1 Jan 2024, I want to send a Slack message using airtable automation.How can we make this trigger?My ideas: using formulas and views

Hi there!I am trying to create a formula that will pull the Last Modified date and combine it with my initials and my Pending Items column. The formula I'm using right now works, but the Last Modified date is pulling tomorrow's date. I'm assuming this has something to do with time zones because it doesn't switch the date until 6pm CST, but I'm not sure how to fix it! Please help 😊Table:Formula:CONCATENATE(DATETIME_FORMAT({Last Modified},'M/D/YY')," ","RE: ",{Pending Items}) 

I'm working on my graduation project, using Airtable as an API. In the API documentation, specifically in the "create records" section, I'm having an issue. When I try to copy the 'YOUR_SECRET_API_TOKEN' for authentication, it stays as plain text and doesn't convert into the expected key format.Has anyone else run into this problem? Any ideas on how to fix it?Thanks for your help!

I am trying to put a formula I pulled together in the primary field, but get the following error "Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference".  Back story, I am using one tab to house all the event details. I am using the nesting feature to pull through things I need for all the sperate views (agenda, session intake, speaker details, ect.), where I would usually use separate tabs for each of these things. Here is the formula that I have pulled together. Depending on what Is selected in the Content Track field it will pull a particular reference field. I want to use this formula in the primary field, but even if I remove the "for column 1" field I still get the circular reference error.  Any one have any suggestions?     <--- THIS IS MY FORMULA    Here is a snapshot of some of the fields. [ Pink highlight I want to pull the formula I created and that works

Hi Airtable Community,I am having an issue with a formula that I was hoping to get some help on. Here is the issue:We do our invoicing on Airtable. We have a few different types / categories of invoices - they are as follows:Quotes: This is what we use when we have to submit a price 1st before any approvals to begin have been granted.Suppleental: This is like a change order - we use this when the job has changed and or scope was added.Estimate: This is used  when we have submitted a price but it has not been approved - what we call "settled"Invoice: This is what we use when we have submitted an estimate and it has been approved. We have the primary field be a concatenation field that takes the name  of the customer, the type of work it is, and then looks to the "(MIT) Settled Date" field and if it is empty, uses the word "ESTIMATE" as the last word of the primary name. The thinking here is that when there is no date in the "(MIT) Settled Date" field, it has not been appr

I got a table with DOB in date format dd/mm/yyyBest way to filter those records whose anniversaries (mm/dd) are within 5 days ahead?

Can someone help me figure out why the ‘else’ part of my IF statement isn’t working? I'm trying to create a friendly date/time/timezone field IF a certain date field is not blank. That part is working. But if the date field is blank, I want it to put “N/A” in the new field. That part is returning an error. Here is my formula.IF( {REQUESTED GO LIVE DATE} !=BLANK(),  DATETIME_FORMAT({REQUESTED GO LIVE DATE}, 'M/D/YY') & " at " & {REQUESTED GO LIVE TIME} & " " & {GO LIVE TIME ZONE}, "N/A")I also tried it this way:IF(  {REQUESTED GO LIVE DATE} !=BLANK(),  DATETIME_FORMAT({REQUESTED GO LIVE DATE}, 'M/D/YY') & " at " & {REQUESTED GO LIVE TIME} & " " & {GO LIVE TIME ZONE},IF({REQUESTED GO LIVE DATE}=BLANK(),"N/A"))But neither formula is recognizing what to do if the REQUESTED GO LIVE DATE field is blank. Oddly, it is working when I remove the first part of the formula to only look for blank date field. IF({REQUESTED GO LIVE DATE} =

Hi,I have a table which is sort of a Bill Of Materials containing the all the required parts to assemble a single product, including the required quantity of each part. It's basically looks like:|Product|Part|QuantityRequired|There is another Parts table containing the cost of each part.I'm trying to calculate the total cost for each part in a single field. So far I was able to do it through two fields, one is lookup for the cost of the part and another field with formula {qty}*{cost from lookup field}My question is can this be done directly in a single formula field, without needing the extra aux lookup field to fetch the price of a single part? Feels a bit redundant and was wondering if there's a way to fetch that lookup directly through the formula. (The final calculation of of the product's cost is done in the Products table through a sum rollup on said result)Thanks.

Hi all, I’m a social media manager. I have a base which links together editorial content with associated social posts. It makes amazing things like this possible: Now, I’m trying to create a fully automated UTM parameter system which collects data from various linked records — such as linked web content, associated campaign, etc — and spits out a single URL with appended UTM parameters for tracking in Google Analytics. I’ve got most of it figured out. One step remains: how to extract part of a string. In this case, everything found AFTER the final “/” in a given field. Here’s what I mean. I’d like a formula which scans this text, finds the final “/” in the string, then spits out everything AFTER that: Here’s my current, incomplete formula: Could I do this with RIGHT()? Any help is much appreciated! Thanks in advance. :blush: Best, Alex

Hello everyone, I have a data base with activities wher date and time are fulfilled but I would like to show only the hour in one column.I'm unable to display what i want. I have for example one column with ISO 14/12/2023 1:52 and i would like two different columns with 14/12/2023 and another one 13:52 Thank you for your help !

Hi, Seeking help here.. I need a formula in the Client name cell where in it will only show the client name if either of the Client or Brand columns has a name on it. Meaning if the Client cell has a name the Client name formula will only show "Path Social" and if the Brand name has a name on it "ThingTesting" it will only show in the Client name cell 

I have a script that has been working for over a year, but now will not update records.  I sync a table from another base to a second tab in main base.  I then use the following script I was given over a year ago (can't remember where I got it).  It has worked great to look at the synced tab to see a unique ID (emp #) and then pull that onto the main table and pull multiple records from the syned tab.  Now when I run it the script runs super fast and doesn't update anything even though I have matching Unique IDs.Here is the script I am using://Substitute "Orders" for table name which contains values//on which you want to run the vlookuplet mainTable = base.getTable("Covid LOA");let mainTableRecords = await mainTable.selectRecordsAsync({fields:["emp"]});//Substitute "Product" for table which contains range to searc

Hi, I'm pretty new to Airtable and currently just exploring if it will be viable for our company to use. The set up I have is two tables, one for learners and one for workshop sessions.  The sessions table has names etc and a field linked to the learners table that shows the attendees booked on the session (multiple select allowed). The learners table has a similar field linked to the sessions table showing the sessions that they are booked on to (again multiple selections allowed). What I would like to be able to do is have a new field on the sessions table that for example shows the attendees that did not show up (nothing fancy I would just use a linked field to the learners table and just input the learners manually, again multiple select allowed). But then I want a corresponding field on the learners table showing the names of sessions they have not attended, is there a way to populate this with the session name (stored on the sessions table) based on the d

Hello guys,I'm trying to create a formula for the main Name field on my table.I just can't do it, I'll be more than glad to write here the details and if someone can write the formula for this it will be great, and i will also learn that way how to create one in the future.My table is data from the computer inventory list in my office.I want the Name field to first start with the "User" field (the field type is "Link to another record" ) and after that it will have one space and then a Comma and then another space before the next field.Next I want the "Model" field (Single line text) and after that it will have one space and then a Comma and then another space before the next field.The last one is the field "Product ID" (field type is also Single line text) ,  before the field content i want it to have the letters/signs "ID:" and the one space and the the field content.For example i have the "User” John Jakson , he have a computer with the "Model" Apple MacBook Pro (16-inch, 2021)

I think I may have gone too complex with a formula as my base won't open now. It's just stuck on "Loading" on every table. How can I reverse what I did?Note: I created a duplicate of the table and it did the same thing

Hi…I have a field with ‘arrival date’, a field with ‘terms’ (which is days prior to arrival payment is due. This is a number and varies from record to record)(Both are lookup configurations) and I want the third field to calculate the date it would be if the terms number (such as -3) is subtracted from the ‘arrival date’. I tried DATEADD({arrival date},terms,‘days’) but it didn’t work. I experimented with DATEADD({arrival date},-3,‘days’) and with the ‘terms’ look up field just being an integer instead of the look up and those did work so I am not sure how to do this. Thank you!!