Formulas

Hi I am a complete Novice with Airtable. I was once OK with Microsoft access but a long time time ago. I have a website and I sell subscriptions which go up in the following increments 48hrs 1 month 3 month 6 month 12 months In my orders table I have the date ordered field and other field called Item’s Name. This contains the following text 12 months Subscription, UK TV, Box Sets, This has been imported via a CSV and whilst it is showing as a single line text field the contacts are part of a url. I am looking for a formula to create a renew date 1 month, 3 month hence etc please. I hope all this makes sense. Any help appreciated please. Andy

The funny part is that when I change the data type in field in question from a formula to a plain decimal number - it all works! Does Airtable not support this kind of calculation when the primary field is a formula deriving data from another formula, or I am doing something wrong?

Hi, I am trying to divide two fields using the following formula: {Variable1}/{Variable2} However I am returned a #ERROR!. I have checked and both fields are coded as Integer types and no value in “Variable2” is a zero. Can you help me find out why I’m being returned this error?

Hi squad, First of all I swear I’ve googled this, and I’ve seen some answers that indicate the thing I’m trying to do is possible but I haven’t actually been able to make those solutions make sense for my purposes. Basically, I have two tables. I’m planning a wedding. So one table has our guest list broadly, with columns for “table” and “household” and “category” and “plus one?” and such. The primary field is “Name” However, we’re using airtable to collect addresses for save-the-dates/invitations/etc. So there’s another table called “Addresses,” which receives form submissions (it’s actually a whole zapier/netlify situation because I wanted the form to be prettier, but basically, it’s a form). The addresses table also has “Name” as the primary field. All I want to do is automatically link one table to the other. Theoretically the names are the same, and if they aren’t, it usually means I need to update the “guests” formula with the person’s full name, so it’s useful for me to see where

I have a formula which looks for the string “geo” in my URL column {Cloaked Link} then display all text it finds after it. But sometimes I will have the text “visit” in the {Cloaked Link} URL column instead of “geo”, so if that’s the case I want to pull out all the text to the right of that string. How can I do that? Here’s what I have so far: RIGHT( {Cloaked Link}, LEN( {Cloaked Link} )-FIND( 'geo', {Cloaked Link} )-3 ) Basically I want to combine that with this: RIGHT( {Cloaked Link}, LEN( {Cloaked Link} )-FIND( 'visit', {Cloaked Link} )-3 )

I have two currency fields: Current Balance and Previous Balance. When Current Balance is updated, Previous Balance changes to reflect what Current Balance displayed previously. Is there a formula that can address this? (Have done this many times in SalesForce but that has engendered a workflow rule which I’m not sure is available in Airtable.) Thank you!

Hi all, I’ve been searching on the ressource center but I didn’t see anything. Does somebody have a template or formulas to calculate MRR ? best, Ben

I want to build a pdf catalog of my products grouped by product type. Not seeing an easy solution to this and I missing something? Terri

I have found that formulas entered on iPad don’t work correctly; especially if there’s a literal in them. I can go to my Mac and enter exactly the same thing and it works. Any solutions?

I am trying to write a simple formula: If field X contains ‘Apples’ or ‘Cats’, or ‘Chocolate’, or ‘Dogs’, then ‘Enter in the database’. I am not well-versed in formulas, as you will see, and am probably using a convoluted way, which is the following: IF( OR( FIND(‘Apples’, {Field X}), FIND('Cats, {Field X}), FIND(‘Chocolate’, {Field X}), FIND(‘Dogs’, {Field X})), ‘Actionable’). The formula works for the first two values, but breaks on the last two. (Airtable also inserts backslashes there.) Can someone kindly help me figure this out? Thanks.

Hello all, This is my first post to the community. I’ve seen other posts similar to this topic, but they don’t quite address what I’m trying to achieve. I’m having an issue with a nested IF statement and not sure whether a combination SWITCH/IF would be best for this formula. I have two columns - column 1 (Date) and column 2 (Location) that is single select. I want a third column (Status) that returns a value based on what is populated in the first two. Logical Argument 1: If column 1 (Date) is not blank OR column 2 (Location) is ‘Outside Area’, the Status column should return “Yes”. Logical Argument 2: If column 1 (Date) is blank the Status column should return “No”. Any help would be greatly appreciated. @Jeremy_Oglesby

Hello, I have a database that is updated live (through an integration), but some records can go directly to archive (via a filter). I am trying to build a formula that will identify as many archivable records as possible and mark them with :white_check_mark: . Such records would march either of the following criteria: marked (manually) as non-actionable in a multiple-select field type OR contain a certain word in a single line field (e.g., if a single-line cell contains ‘no-reply’ => archive) I already have a formula for the former, but I wonder if a formula that would combine both is possible to build.

Hi All, We are using airtable to create a delivery schedule for our team every week. We have many columns to keep track of each product. I would like to create a summary column which lists each product that was ordered. We then export this file to import into a route planner. We want the final output to follow this format: Name: 123 Address, City (Prod1qty, ProdNqty) $owing {comments} Our Columns: Name Address (with city) Products Ordered Balance Comments Product1 Product2 … Product50 The trouble I’m having is with the product list. We need it to show only “Product1” if there’s just 1 and “Product1*#” if there are multiple. And I need each product to be separated with a ", " without having a coma at the end of the list. I’m pretty sure I can use an extensive IF formula to create the list of products based on how many are ordered. I have no idea how to remove the coma at the end of the list tho. I was hoping there would be some kind of arrayjoin formula I could use to sum up the results

I have a unique situation that I couldn’t find an answer to, possibly to my lack of patience. But I am trying to create a pricing sheet of products. I have two manufacturers. I have installer and wholesale accounts that only get a discount on one of the manufacturers’ products. I was trying to come up with a formula that calculated the accounts discount automatically since I change prices every so often, but want it conditional to the one manufacturer it applies to. I need it to be that if the manufacturer cell says SCF, the installer sell is retail * .9, and wholesale is retail*.75 and if the manufacturer cell says Maven, installer and wholesale are both same as retail. Is this possible??? (I tried attaching a screenshot of my table, and it’s not letting me so I’m explaining it as best as I can)

Hello! I am trying to create an auto-generated field consisting of a single client name and a code assigned by me (e.g. “Client Name - Billing Code”). There can be multiple clients per billing code and currently, I supply the billing code in a separate field. My current formula is: IF({Client Last Name}="", Company,{Client Last Name}) & " - " & {Billing Code}. It is continually returning “SmithSmith - B01” rather than “Smith - B01.” My question is whether there is a function that allows me to limit to only a single client record despite the Client field being a multiple record field? If there is no function, is there a practical workaround? Thanks!

I have a table where users submit events that happen on a weekly and monthly basis. For example: a yoga class every Saturday at 9am or a group run the first Sunday of every month. I don’t want to add a ton of records of that same event but would like the original date field (Event Date) to be updated so that it populates the next coming date. So process would be: Event is submitted Event Date is populated with first class date ex: 5/20/20 After the first class date passes, it is updated to the next coming date: 5/27/20 Is there a formula that’ll auto-update that Event Date field? Or at least spit out that new date in a new formula field?

Hi all, New to Airtable and by no means a whizz when it comes to formulas. In short, I need to create a formula that will return a date of the 1st day of last month. In other words, today (May 21st), the field should show a date of April 1st 2020. As soon as we hit June, that date should change to May 1st 2020. Any idea how I could do this? Thanks all…

Hello all, I have question regarding a formula for the marking sheet for my university module: I want to compare my students’ final numerical grade (titled ‘total_grade_point’ field in image one) with a range in an adjacent conversion table. The second table contains a numerical range (image two), and I want to get the final string for my student’s grade. Basically, if you follow the images, I want to contrast my students’ grade point with the ranges in the conversion table, and then yield the grade string (zero, low fail, etc.). I hope this is somewhat clear. Thank you all for your help! :pray: Best, Manuel

Hi, is there a way to compare strings that include same word(or part of the word), but not the whole phrase?

Hi everyone, I’m building my CRM and I have two tables, one is the “Account” and the other “Register”. The client fills the form that it’s linked to the “Register” table, the account manager, based on the information provided by the client approves or not the client turning the Checkbox to 1. I want to create a new row for that client in the table “Account” when the account manager change the checkbox to 1. Can you guys help me? Or maybe I am overcomplicating everything. Thank you all! Best, Luis

Hello, I am building a form for the sales people in my organization. The idea is they will not have to actually login to Airtable put will just put all the information into this form and then the admins can keep track and do the work on the back end. Currently I have it worked out that when you select a contact (prospective client) it populates with their name, email and phone number thanks to a nifty formula! We also have an application form that needs to prepopulate as well. This just a pdf attachment we have added to their ‘contact’ file. Does anyone know a work around or way to prepopulate an attachment to a form by linking it to a table? Or a formula to add the attachement to the primary field? Thank you!

Hi! I’m trying to set up a CRM for some of our sales people and I’d like to auto-populate a ‘next step’ due date based on the selection they make for their ‘next step’. For example, if they select their next step as ‘Send follow-up email’, I want the due date to auto-populate for 2 weeks from when they entered the information. Or if they select their next step as ‘Place follow-up phone call’ I want the due date to instead auto-populate as 3 days from when they entered the information. Is this possible at all with a formula or would this require an external zap maybe in Zapier? Thanks in advance!

Hey there, I have created single drop down statuses for my projects I’m working on (In Progress, Not Started, Recently Completed, Roadblocked, and Archived). I’d like to create a formula that says "If the project status is anything other than Archived and Recently Completed, AND has a past Target Completion Date that’s before today, status is: PAST DUE. Otherwise, ON TRACK. Ideally the way I’d like this to show up is to have another column with PAST DUE and ON TRACK options in a single select drop down, and depending on what the formula says, the drop down option changes. Is that possible? Thank you!

Hi everyone, I’m trying to use formular HOUR（）to display my Time as a number. But I keep getting wrong result. Normally, if the time is 18:30, the number from HOUR() should be 18, but I get 9.0. Could someone help me to have a look if my formular is wrong? Cheers