Formulas

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

Hello, Thanks in advance for reading my post. I have a table which includes a list of equipment, and then I have another table that contains dates of bookings (a booking is per day), location of booking and other fields. I would like to be able to see on the first table the last location of a booking and it’s date. Is that possible to do?

As you can expect, I am using a function field to manipulate a time field; two issues appeared gmt zone went crazy inside the new form field language for dates now are shown in english. Two solutions came to rescue: For issue # 1 I can use: DATETIME_FORMAT(SET_TIMEZONE({date field}, 'America/Bogota'), 'dddd, MMM D, YYYY h:mm a') for issue #2 I can use: DATETIME_FORMAT(SET_LOCALE({date field}, 'es'), 'dddd, MMM D, YYYY h:mm a') They both work perfectly INDIVIDUALLY, How can I combine these 2 formats inside one single formula.

I’m looking for a formula that can help me add the numbers 1-6 to records, looping. So: Row 1 - 1 Row 2 - 2 Row 3 - 3 Row 4 - 4 Row 5 - 5 Row 6 - 6 Row 7 - 1 Row 8 - 2 I’ve thought about using the autonumber field and then trying to extract the number 1-6 from that, but I’m genuinely at a loss.

Hi, I would like to achieve something that seems basic but i can’t find a solution. I have a table of contacts, another table of companies and of third one of projets. I link a contact to a company and a company to a specific project. I have several contacts for each company and i would like to link a project to a specific contact. I obviously can link the project to a contacts but i would like to only be able to select someone from the company in charge of the project. At this point i can pic any contact. I know that we can make a condition on a specific view but the view as to be “dynamic”. I don’t want to manually create a view for each company. Thanks for your healp!

I have a table that contains my users, and their current balance of chips in a casino. I’m building a leaderboard that will tell them who the top five chip holders are. (This is the easy part.) I also want to tell them what place THEY are in, if they’re not in the top five. This requires one of two solutions: Grab all of the data, and iterate through it until I find the user. This is a heavy operation, because I’d have to pull all of the user data to my application before iterating through all of it. Have a ranked column that automatically updates every time that a user’s balance changes, much like Lookup, Rollup, or Formula fields. This is my preferred approach, but I can’t figure out how to do this. Do any of you amazing people have some guidance or advice? Thanks!

When calculating the difference between two dates it gives me NAN, since one of the cells is empty. ¿How can i fix it? Help me :blush: IF( AND( {start_isolete}, NOT(DATETIME_DIFF({date_out}, {start_isolete}, ‘days’) < 1) ), DATETIME_DIFF({date_out}, {start_isolete}, ‘days’) )

Hi, I need some help creating a formula. I currently have the following set up: Table1 Primary column = text Start column = date End column = date Table2 Primary column = date Notes column = text In Table1, I’d like to display what Table2 records exist that are in between the Start and End dates for each Table1 record. Any advice would be appreciated!

Hello all! I have a question. I would like to create a formula with 2 conditions. IF “Item” in row 1 = “item” in row 2 AND “P” in row 1 is equal to “P” in row 2 then “it’s ok” otherwise “it’s not” Do you think it’s possible? Thanks for your help!

Hello all! I wouldlike to know if it’s possible to have in the column “test” all the items which are in the same period. For example in the first row of the colum “test” the result would be “FRT54522” because “FRT54522” has a period in common with “FRT5678C”. Many thanks for your help :slightly_smiling_face:

I’m having a strange issue with my formula, it’s been working for the past year but then all of a sudden it didn’t seem to be updating. I ended up fixing it by duplicating the formula into a completely new field and it seems to work on the new field, but I don’t want to break any old references, etc. Any ideas what’s going on?