Formulas

Can you make a single line comments within a formula?

I have an "Applications" table that tracks contacts through various stages: expressing interest (TYPE = EOI), applying (TYPE = Application), and other scenarios for a specific session.I want to determine how many contacts who expressed interest (TYPE = EOI) in a session ultimately applied (TYPE = Application) for the same session. This will help me calculate the conversion rate between expressing interest and applying.Is there a way to calculate this?For example, in the provided screenshot, contact records 14 and 15 both expressed interest and applied for the same session. Other contacts may either only apply or only express interest.In the "Sessions" table, I have created two lookup fields: one for contacts with TYPE = EOI and another for contacts with TYPE = Application. However, I’m having trouble figuring out how to identify which contacts appear in both lists. Is there a simpler method to achieve this?I have hidden any contact names. 

Hello,I have a problem performing calculations on numbers from rollup's columns. When I do additions and subtractions, I don't get any errors. However, when I try to do multiplications and divisions, I get an error. I tried adding value() to convert the text to a number (despite the original values being numbers). Although this no longer generates an error, the calculation doesn't work. I was trying to check if the divisor is greater than zero, but despite the tests, it doesn't work.I need some help 🙏Thank you,Angelo

We leverage a concatenate field to be able to indicate when milestones fall during certain weeks. We are wondering if there is a way to revise the formula so that we can have the grouping be in order chronologically by year. I have supplied the formula that we have and how the grouping is appearing today below. 

Hi, my first post here.  I've tried to find similar posts to what I'm trying to do with no luck.  I am working in a product base and am using formulas to create product titles based off of multiple formula fields.  We have products with stones and without, and then we have different types (ie. pendants, necklaces, etc.) that depending on their values make up the title.  I have created the following formulas fields (which are basically concat formulas) and now need to create another formula to come up with the final title:GG Title with StoneGG Title No StoneGG Title Pendants with StoneGG Title Pendants No StoneThe logic would be:If {Division}=GG and {Master Stone}="No Stone" and {Type}="Pendant", then use "GG Title Pendants No Stone"If {Division}=GG and {Master Stone}!="No Stone" and {Type}="Pendant", then use "GG Title Pendants with Stone"If {Division}=GG and {Master Stone}="No Stone" and {Type}!="Pendant", then use "GG Title No Stone"If {Division}=GG and {Master St

Hi all, I am creating a resource library and I want different info to be displayed in one field based on the selection in another field. Im sure I am over thinking this and there is an easy solution! Here is the example; My base is displayed in a gallery view. Based on the 'Provider' listed, I want the description to change relevant to the 'Name' (e.g. Online Learning platforms'). If more than 1 'Provider' is listed, I want info for both to be displayed.  Is there an easy solution for this? or a workaround? Many thanks! Nick    

Hi! I created a prefilled form link but somehow it’s not populating the field. I created other similar ones and those are working though. Help! Working: Form Not working: Form

Hello Airtable Community,I'm working on a project that involves calculating the Qibla direction using Airtable. I have latitude and longitude data for various locations stored in my Airtable base. I need to create a custom formula that can accurately determine the Qibla direction for each location.I have come across the term "boussole prière en ligne" (online prayer compass) and understand its importance in providing precise Qibla directions. Could someone guide me on how to implement the necessary trigonometric calculations within Airtable’s formula field to achieve this? Specifically, I’m looking for a way to:Input the coordinates of the Kaaba (latitude: 21.4225, longitude: 39.8262).Use the haversine formula or any other relevant trigonometric function to compute the initial bearing from any given point to the Kaaba.Ensure the result is displayed in degrees, representing the direction to the Qibla.I would appreciate any examples or step-by-step guidance on setting up this formula. If

I want to create a summary of my table row, which has linked records to them. Ideally, the entire summary in text, with the linked records expressed in text within the entire summary. An example below:Case TableCase |  Person |  Invoice  1234 |  Michael | XYZPerson TableName | DOB | AddressMichael | 1/1/1990 | 10 Oxley RoadInvoice TableID | ItemsXYZ | Hamburger; Fries; CokeDesired output:1234; Michael;1/1/1990;10 Oxley Road; XYZ; Hamburger; $2; Fries; $2; Coke; $2Ideally what I want to achieve is:Case: 1234Person: MichaelInvoice: XYZLine items1. Hamburger \t\t $22. Fries \t\t $23. Coke \t\t $2Total $6.Apologies for the lame example for hamburger and coke...I consider myself less than an expert, so if you do have better suggestions I am open to hear them.  

Because automations are so limited in formatting options for email messages, I'm stuck having to create a bunch of extra fields solely to copy specifically formatted other fields and change them to flat text so they'll appear correctly when emails are sent.Because of this, I'm currently hung up trying to get currency to format properly. I have a formula field that outputs values as a currency into a field that we'll call "Dollar Total", including thousands and decimal separators. I was able to use a concat formula to get the basic copying done, including adding the $ manually at the start, but it doesn't handle decimals properly which then further complicates trying to get a formula to manually insert the thousands separator commas.For instance, if a value is "$14,500.00" it'll output as "$14,500" but if it's $14,500.80" all I get is "14,500.8". It'll go out as far as I do with numbers after a decimal, but if it's only zeroes it drops them, and then that breaks the manual insertion of

When the lookup value is a field with multiple records, is there a way to filter the returned linked records and extract one field from it?For simplicity's sake, suppose that I have the following 4 tables1. Products - generic information about products2. Vendors - info about vendors3. vendor_products - products that vendors carry (i.e. vendor pricing), linked fields to products and vendors4. invoice line items - columns to select product, vendor, lookup field with vendor_products from vendorOn the invoice line item, I would like to be able to also filter the vendor_product by the selected product and then extract the price for it.  Is this possible?    

I am looking to take a field that has a total amount of seconds and have it formulate into a duration.E.g. My "seconds" field set as 210 would populate in my formula field as 00:03:30:000.Does anyone have a formula solution for this?

I have a date+time and a duration (days: hours: minutes). I want to add a day:hour:minute duration to the date+time and output a new date+time. Issues I've encountered:1. I can only set a duration field to be day OR hours/minutes/seconds. 2. When attempting to create a formula to add a "Day" duration field to a "Hour" duration field the output is incorrect. For example I have a Day Duration field with "1" and in the Hour duration field I have 00:05. I triedDATEADD({Next Output Days},{Next Output HH:SS},'seconds')   Results in #ERROR!SUM({Next Output Days}+{Next Output HH:SS})   Results in 301?I'd be fine with a result of the total seconds of the day + hours/minutes to use for my final formula but I can't seem to combine the day duration field and the hour/minute duration field into anything useable. I just want to recreate the function of this form under "Add or subtract time from a date" https://www.calculator.

Is there any way to display the Button field only if the URL field it references has data? So if {resource link} is null, don't display anything. We have some users who are confused when the button doesn't work (not all the records will have a resource link). These are views which are shared publicly and not Airtable users. Thanks!

When applying this formula "{Pago a realizar}-{Pago efectuado}"  to the selected fields, as you can see in the 4 file, the result is not 0 but 1. How could I fix this error?

Hi everybody.My issue is the following: I have a make.com workflow in which I search for airtable records with which to perform certain actions, but it's not returning the expected result. Here's the formula I added to the airtable module in make.com:AND(DATETIME_FORMAT({var1} , "DD.MM.YYYY") <= DATETIME_FORMAT(NOW(),"DD.MM.YYYY"), {var2}, DATETIME_FORMAT({var2} , "DD.MM.YYYY") <= DATETIME_FORMAT(NOW(),"DD.MM.YYYY"), {var3}, {var3} != "N/A", {var4} != "Text", {var5}, {var6} = 0, {var7} = 0, {var8} = 0, {var9} = 1)I narrowed it down to var2 not returning the result I expect. So if var2 is "31.07.2024" and today is "01.08.2024", I get a 0, meaning Airtable is not the same opinion that yesterday is "smaller" than today. The strangest behavior. Does anyone have some advice?

I am trying to group line items based on the below conditions, but for some reason it's not working for all the "Done" ones. It does work for the 2023 items that are marked Done in the status column, but the one 2024 item that is marked Done in the status column still returns the 2024Q3. I really hope this makes sense. IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",IF(AND({Labels},"(2023)",{Status} = "Done"),"2023 Complete",IF(AND({Labels},"(2024)",{Status} = "Done"),"2024 Complete","Unscheduled")))))))

I am building a table that creates a record for monthly billing. The billing happens on the same day each month. I have a Date Paid field and a Next Date Paid formula field, and an automation that triggers after the bill gets paid to create a new record with the Next Date Paid from the old record populating into the Date Paid field of the new record.The problem I'm having is with the DateAdd() function. My current formula is: DATEADD({Date Paid},1,'month')  I thought this would just add one to the month, but it actually just adds 30 days which means that my dates won't match up. I tried adding a field with a static number(MonthDay Paid) and doing something like: DATETIME_FORMAT(DATEADD({Date Paid},1,'month'),'M/{MonthDay Paid}/YY')  but the dynamic slug breaks the DateTime Format function and returns "8/{8thnt121amy Pami4}/24". I'm trying to automate this as much as possible so that the user doesn't need to really access the data layer much, so a

My workflow is based on priority levels, so I'm trying to create a "Due date" field based on the last time a record was updated. What am I doing wrong is there a better way to create this date? 

I've created on a base a table template with several formulas that I'd like to duplicated for the various projets that I managed but when I try to copy / sync  the table to another base, all my formulas are not transfered and the columns with the Formula Field Type are changed to a Number Field type.Is there an automatic way to copy the formulas or do I have to manually copy/paste them on each new base ? I've left some some images for reference, as you can see the field type is completely different and dont include my formulas.Thanks,

Hi! I'm new to Airtable, and my company just started using Airtable to build our merchandising assortments. We currently buy products from both US and AU with different duties and transit costs associated with each region. Previously when working in google docs we used IF formalas and vlookups to reference those costs depending on the type of buy. Is there a way to add that data set somewhere in Airtable to reference? I've added a screen shot of the data set of the duty and transit costs and our google doc formula for reference as well! Appreciate any help!

Hi. Thanks in advance for any help.I'd like to take a table with dated records in them, then group them by month-Year.How do I take a date format and make a field that will allow me to show the latest records at the top for a particular month? For example:2024 - 2 (all records follow)2024 - 1 (all records follow)2023 - 12 (all records follow)etceteraThen of course as we add newer records at the top, they'll continue.Thanks!  

I'm creating a script to compare two rollup fields and add the values both share into a third column. The fields roll up linked record fields and return them as just the text in a comma-separated list. Airtable documentation says rollup fields are an array; it looks like this is technically true, but it seems to be an array of a single string. When I log.console the cell value (note, I'm using getCellValue, not getCellValueAsString), I get ["Category 1, Category 2, Category 3"]. I want to get an array of multiple strings: ["Category 1", "Category 2", "Category 3"]. I'm very new to Airtable, so before I try to figure out ways within my script to separate the one string using the commas and all that, I want to make ask:1. Is this single string output the expected behavior, or is my script calling it incorrectly?2. If it's normal, is there way to configure the rollup fields to instead keep them as separate items?

I have a table with two date fields- "date created" and date "completed". I would like to create a formula that calculates the number of dates that passed from the date the record was created to the date it was marked as "completed". What's the right formula for this?

Hi, I have two tables in a Database.One table: called "People". It has list of peoples and their Emails.Second table: called "Mailing" Has email from table one.I would like to set an new field in "People" (table 1) that marks "1" if an email is present in both tables. I called this field "Did_I_found_Email" (1 = yes, 0 = No).How do I do this?