Formulas

Hi, I’m having trouble with the following formula: IF(LEN({Copy})>150,“ :rotating_light: ”,“ :ok_hand: ”). It returns “ :rotating_light: ” if the Copy cell quantity is longer than 150 characters and “ :ok_hand: ” if less than 150. However, I’d like it to return a blank value if the Copy cell is empty. I’ve tried adding “BLANK ()” to the formula a few different ways, but it either returns an error message or doesn’t take. Any help would be appreciated. Thank you!

Hi folks, just a quick announcement about a new formula article that walks through how to check for missing field values, and display them conditionally in a formula field. Here’s an example from the article of what the end result looks like. You can use and modify this approach for any number of workflows, so we hope it’s helpful! If you have any requests for support articles around formulas (or anything else!), just let me know :thumbs_up: Airtable - Support

Hello! I have an Airtable set up with an entire database of product for a client. Each product has a unique SKU and subsequently a record for that SKU. Every product falls within a collection of product and is tagged with that collection as well. For example 5 SKUs may fall under one Collection of product. From there each SKU (record) is tagged with features of that product. I created a table with a linked record for collections and am attempting to find a way to roll-up common features within a collection. In other words, if all SKUs within a collection have the feature ‘Waterproof’ then show ‘waterproof’ on this table. I am hoping this question makes sense.

I have 2 fields that contain email addresses. The first column contains 1 email address and the the 2nd column contains multiple email addresses. I want to create a 3rd column that combines both fields with the appropriate commas separating each email address. What formula can I use?

I have two rollup columns that each contain (potentially a lot) of text values. Rollup column 1 will most likely contain some, but not all, of the same text values of Rollup column 2. I need a way to write a formula for a third column that pulls out any unique text that exists in column 2 that does not exist in column 1 and then shows what those unique text values are in column 3. (Essentially I’m trying to find values that don’t match between the two columns, but I don’t want to calculate just a true or false statement as to whether the two columns match. I want to know what the text values are.) Anyone that can help? -Thanks

I feel like this is possible, but I’m not 100% sure. I know I can run a formula that says do “X” if the value is “Y” and otherwise do “Z”, but can that be done on whether the number is negative or positive? This is what I’m trying to do - I have a number field that captures the number of days before (negative) or after as listed in a contract. I need to calculate a date based on those numbers, but the date I calculate from depends on whether it is listed as before or after in a contract. So for example, if in the contract it says the survey is due 30 days before the end of feasibility, I need to use the feasibility date field based on that -30. If it says it is due 60 days after the effective date, I need to use the effective date field based on that 60. Hopefully this makes sense. Thank you!

Hey team, I think this is an easy one, just need some help. I have two tables - Objectives and Steps. Each step within the objective has a different “weight” / percentage tied to it, based on its importance. I need to add up the % of completion within each objective based on the weight of each step, since each step is not equal in value. Objectives table: Steps Table: So ultimately I would see on the Objectives tab the % of completion based on the steps “checked” in the Steps table

In Table B there are 3 columns. C-A (%Contributions) has a percentage value (1 to 10), C-B (Over) has decimal numbers and C-C (Under) has decimal numbers. when a user selects a “%Contributions” value AND either “Over” OR “Under” option the result should be given. ie… if the user enters %10 and Over the result should be 0.31. Thanks in advance Regards David

Im looking to do simple warehouse management with Airtable Sheet 1: scan incoming barcode + date Sheet 2: scan outgoing barcode + date Sheet 3: how to display barcodes that are in the warehouse (show only rows with barcode that are not in sheet 2) And is there a way to make barcode field unique? So that same barcode cannot create a new row?

I have a table containing adverts. This is linked to a table containing advertisers, where I have created a formula that counts the number of adverts linked to an advertiser. I have a third table containing the percentage discount that will be applied on the advert’s price for the number of adverts that the advertiser has bought. For example: 1 advert = 0% discount, 2-5 adverts = 5% discount, 6-9 adverts = etc, etc. I cannot figure out how to use the column containing the count of adverts on the advertiser table and fetch the correct discount from the third table. Any ideas how to do this? I am trying to avoid hardcoding the discounting rules in lots of IF statements, but rather want these configurable on a separate table.

Hi AirTable Geniuses! I’m fairly new at this, but I’m hoping I could create a formula field that auto-generates an Amazon-friendly cell for each book record my old Homebase book inventory. Homebase allows for an infinite number of book binding descriptors, but the only ones on Amazon that work for my books are Hardcover, Paperback, and Miscellaneous. So my AirTable has two binding fields: HOMEBASE BINDING and AMAZON BINDING. The first will just be copied from my old database. I think the second, though, needs a formula that starts like this: IF({HOMEBASE BINDING}=“Hardcover”,“Hardcover”,IF({HOMEBASE BINDING}=“Paperback”,“Paperback”,IF({HB BINDING}!= This is where I get stuck. How can I tell my AirTable table that if HOMEBASE BINDING does not equal either “Hardcover” or “Paperback”, my AMAZON BINDING field should say “Miscellaneous”? I’d be mighty grateful for some assistance with this one! It would mean not having to comb through 25,000 records. :slightl

Hi, I have a formulat with prices, like that : € 440,00 I need to convert this value to : 440.00 I’m using the formula SUBSTITUTE({Prix}, "€", "") to remove the €. But I would like to Also convert the “,” in “.”. Do you know how can I add a new part to my formula please ? Thanks ! :slightly_smiling_face:

IF(FIND(“EN 60065: 2014”,LVD),“YES”,"") This can work in Excel, but does not wo rk in Airtable, why?

Hi. I am working on moving our workflow from google sheets to Airtable. We have clients who have contracts for 12, 18, 24 or 36 months. We are trying to have a column where we see their anniversary. I figured out the 12 month, that was easy because everyone is at least 12 months. It get’s tricky with formatting after 12 months. What I want to tell it to do is check if the term is greater than 18. If so, then calculate what the 2 year anniversary is (because not everyone has a 2 year anniversary, or a 3 year anniversary) I tried this and it didn’t work: IF([Term]>18, DATEADD({First Month}, 24, ‘months’),"-"). Help!

I am building a UTM builder in airtable. Everything is working except there is one field where we do not always need a value. when I leave it blank the end of my URL has “_content=”. I’d like to have a formula that removes that last part of the URL when that value is not present. Does anyone know how to program that?

Is it possible to use rollup to pull the sum of values from two different fields (both in the same view) into a single field in a second view? I’m designing a teambuilding exercise app. It tracks exercise efforts from various teams, based on A) non-walking activities and B) Walking. In the main view, one field uses various formulae to convert different types of exercise into their “step count” equivalent. For example, 30 minutes of playing basketball is worth 5000 steps (not really). The second field only requires a number input by the participant, based on a pedometer reading. In the end, both fields need to be summed together to show total steps taken by each participant. The expectation is that participants will record steps (from walking) and step equivalents (from non-walking activities). Thanks in advance.

I am trying to make an Airtable to easily export my product data to the proper format to then upload the csv to our website. The product subcategories need to be listed as such: “category::subcategory” I have a formula splitting the multiple select columns into stringified columns, then separated by comma, and so on, until I get four columns that then fit the above formatting. However, in despite pulling from the same information and having matching formulas, I am getting the second, third, and forth column results with a floating space before the subcategory. See image below: Code for the first column: IF({First Option}=BLANK(),BLANK(),{Cat} & ‘::’& {First Option}) Code for the second column: IF({Second Option}=BLANK(),BLANK(),TRIM({Cat} & ‘::’ &{Second Option})) It was happening with TRIM() and with

Hello! I would like to know if there is a formula to identify duplicates? I have a database with codes for each product. When a new product is entered into the database, I would like to be informed if the code assigned to it already exists. Thank you for your help.

I have a table a that has a column with a paid field. I want another table column to bring over that paid field column only if it is filled. Not empty.

Hi Everybody, I am trying to create a “concatenate formula”, that creates a line break and a fixed size table cells, all text should be aligned left. because I am applying a bulk query search and I want one result per line. I know the “\n” creates the line break My formula is CONCATENATE("\n",{Google Slides Format Title}," | “,{Google Slides Format Release},” | “,{Google Slides Format Origin},” | ",{Google Slides Format Title Rating}) so my output is like this “Bilal: A New Breed of Hero | | 1x105 | 01/09/2020 | UAE | :star: ” I want all of that to be in table cells fixed sizes and always aligned to left I think it needs some code for app surface to land right on google slides Thank you so much guys for your help in advance

Hello there, Is there a way to get an email reminder with tasks by daily? I’m trying to find the way to setup recurring email reminder. For example, I want to get an update from a view “Tasks due in 7 days” everyday at 7am. with the tasks and details. The closest way that I found is from the post below Daily recurring reminder emails formatting

I’m trying to figure out how to write a formula that matches a field that contains a link to records. In my setup, I have a field that is a link to another record. I’ve tried {Field_Name} = rec########, {Field_Name} = “rec########”, {Field_Name = “Record Identifier”}. What am I missing??

I’m looking for a CURL formula that will filter out dates in the past. As in a schedule I don’t want it to show me the scheduled times in the past. More Specific I need the Query Parameter Value

Hello all, so I have a table of Cars Status, which show the status of each car for each each day - Up, Down for engine prob, down for brake prob, down for tire/wheel prob etc. (My fleet is much larger than in this example, but let’s just consider a fleet of three cars.) So each car has a record in the Car Status table for each day. In my Daily Reports table, there is one record for each day, and (among many other things) I would like to have a field that counts how many cars are “Up” on each day, as recorded in the Car Status table, and another field showing how many cars are down for “Engine” and so on. So for each record in the Daily Reports table I need to have a formula (?) field, that looks at the Date field for that record, and then counts how many cars there are in the Car Status table which are “Up” on that same date. Here is a read only link to a base I set up which has these tables set up as I describe, for clarification.

I am new to airtable. I can’t find a way to write following query in airtable using filter by formula. Select * from tablename where bertha={varibale1} And berthw= {variable2} I have the 2 variable in my hand and i have the fieldname too But in search table contain ids not direct values Means BerthA table [ id] Berth B table [id] So direct matching is not possible. Anyone please help me to search a value inside the array. I did OR( ${berthA} = ’ xxx’) Doesn’t return anything because berthA is array