Formulas

I’m using AirTable to describe all products my company sells, or has ever sold, in our shop. Each record is a product. The tricky part is that some products are technically product bundles. To help describe this, I have a field called “Bundled Products” which can link to other records, for example: Product X (bundle) Bundled Products: Product Y, Product Z Here’s where it gets tricky. The way our store reports revenue, every product (including bundles) has sales recorded separately. But for analysis purposes, I want to be able to consolidate the revenue from Product Y with the relative percent of revenue from Product X, the bundle that includes it. I’m struggling to figure out how to create a formula or rollup for this. What I’ve done so far is define an additional field called “Part of Bundles”, so Product Y now has a reference to Product X, that contains it. If I were writing a program for this, here are the logical steps I would nee

I wanted the Weeknum of an end date field. The week number of those records that have 12/31/2021 is 1 instead of the last number of the year. How can I turn that into 52. I was trying to do a formula like this IF({End Date} = ‘12/31/2021’, “52” , WEEKNUM({End Date})) but it wasn’t working. I am new to formulas so if you can give me guidance I would appreciate it.

Hi all! I’m trying to create a %Complete column in a project table that includes several single-select fields used to track 1. whether or not a particular team has tasks associated with the project, and 2. what that team’s progress is on those tasks if so. I’m thinking what I need to do is create a formula column for each one of these, assigning a number to the values selected, a column that totals these up, and a column that calculates the % complete by dividing the total complete by the total that needs to be complete. Maybe use the SWITCH formula to assign numeric values? I just don’t know how to set up conditional formulas that complex at this point. Can anyone help me? Thanks very much!

Hai everyone, how to write formula on primary key column to capture Recorded time. I know there is column with type created time, but for primary key I’m not using so I think will be better if use for recorded time but with formula Thank you

I have a field called “Campaign Date” in my database where I start my experiment (November 1st). I would like to create a field where my first week of experiments is labeled Week 1, and so on. Can someone point me in the right direction?

Hey, I hope you’re well and might be able to help. I’ve created a table which records safety test results for some equipment. Each time a test is completed, it’s created as a new record and there’s a lookup field to link each test back to the piece of equipment that has been tested. It’s all working nicely. However, as we start to add more and more tests, there’s times where the same piece of equipment is tested more than once. This is causing some problems with some of the reports we generate, as the reports should only show the most recent test result. Does anyone know of formula I can create that can check if there are ANY other records in the table with identical values in the lookup field and IF SO, display a “TRUE” or “FALSE” result according to whether the particular record is the latest one? Thanks in advance, Sam

Hi Sorry if this seems dumb, but im a complete beginner. I have a task id field with a simple concatenate formula that puts together three fields data in the same table. And shows {Customer Name} - {Type of Task} - {Date of Task} and it adds the date time format D/M/YYYY. Its shows eg: John Smith - Photoshoot - 21/7/2022 This works brilliantly, but has a very minor annoyance of returning ERROR when adding a record because the three fields are blank. Once the fields are filled in it works a treat. Is there any way the formula can allow for the blank fields without showing error? Thank you in advance

I’m trying to use DATETIME_FORMAT to format a date calculated by a formula to the way I want it to be displayed. I have to use DATETIME_FORMAT rather than the settings button because I’m inserting it into an automated email, which defaults to YYYY-MM-DD format instead of the settings. It seems like this should be possible but I keep getting an error. Here’s my current formula: DATETIME_FORMAT((DATEADD(TODAY(),14, “days”), ‘DD-MM-YYYY’) The center formula DATEADD(TODAY(),14, “days”) works properly on its own, so I know it’s something about adding the formatting piece. Is it possible to do this in one formula?

Trying to figure out a formula to run a conditional average: Within the table, each record is a single asset. One field tells me what asset it is (unique ID), one field shows me where its physically located, another shows me the type of asset (may assets, although all unique are the same TYPE, etc.). I’ve got another field that contains a formula that shows me the average use each asset receive within a given time sample - that field/formula work perfectly. What I’m trying to do is average that use for the assets, but ONLY where the location and type are the same. Example - Location #3 has 4 assets. 3 are Type 1, the last is Type 2. The use formula is returning values of .25, .50 and .75 for the three Type 1 assets. I’d like this formula to average those 3 values together to return .5, BUT ONLY for Location #3, and only for Type 1 assets. The Type 2 asset should remain blank because its the only Typ

Hi everyone, Please advise how to create a formula for a field (let’s call it “CityCount”), which would automatically display the number of records in this table which have the same value in the field “City” as a current record? Here is an example: there are 3 records in the table with City=London, thus each of the record should have CityCount=3. If I add one more record with City=London, then each of such records would get updated with field CityCount=4. Any help or ideas appreciated!

Hi, I have some problem in calculating CO2 calculation. Calculation result values as below. Excel : round( 8.110000 * 0.000039 * 56.100000 * 1.000, 6) = 17.925857 Airtable : round( 8.110000 * 0.000039 * 56.100000 * 1.000, 6) = 17.743869 Each data in Airtable and excel is in the field (decimal 1.000000) It would be appreciated to help me. I think excel value would be right calculation result. Regards JP

From a look up field a have a row of values which are in my case dog breed subscriptions. I cropt this row by using the Array unique functions so it only shows unique vallues. Some rows have just one unique value of some have for example 3. I want to seperate always the last value in a row with an & or and instead of a comma. For example Bernese Mountain Dog, Rhodesian Ridgeback, Labradoodle → Bernese Mountain Dog, Rhodesian Ridgeback & Labradoodle Rhodesian Ridgeback, Labradoodle → Rhodesian Ridgeback & Labradoodle Does anyone now a solution for this problem?

Someone already asked above question, though. I couldn’t find the answer. I know how to use “prefill function” for text but I want to use it for attachments file. is it possible?

Hi, I have a rollup field for ‘Last Contact Date’ to fetch the most recent Communiques (which is my unique name for Contact Points) date and display it in my Contacts table. So far so good. Now - here is the challenge. For the sake of convenience I just thought it’d be cool to display the related text for that particular field in another field - like “Last Contact Notes” - this would give me a quick refresher on that contact if I was going to call them again. Just an idea. And wondering if it’s possible? I made a video to clarify: And… somet

Either I am crazy or I cannot find a drop-down to create a formula in any of my columns. This is my first time using the app I created a table from scratch.

Here’s one for you. We have a purchase order for a job, with a total amount in one table. We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table. I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget. Is this possible?

We are trying to add a formula that if one room is available even though others are not it should still says “Available”. If one room says Becoming Available but there’s no room that is available then it should say “Becoming Available”. If all rooms are rented then it should say “Rented”

Hi community, I am looking for a solution where I can compare two tables and update the main table with both status and also the new data entries from the second table. Appreciate if anyone can shed a light on how this could be achieved. Basically we have a data scraping code that writes the entire dataset to a table (let’s call this secondary table) on regular basis. And we have a main table used in production. What we want to achieve is to use the secondary table to update the main one. If the dataset does already exist in the main table and also exist during the latest script run on the secondary table (there is timestamp), then the “status” field in the main should be updated to “available”. If the dataset does not exist in the main table but show up during the latest script run on the secondary table, then the “status” field in the main table should be “new” If the dataset do exist in the main table but does not

Hi I am new to Airtable… big learning curve. I am using it for Shopify with the Shopify App Airpower for bulk uploading including images! (Amazing !). However I am changing themes and need to add to the text in my multiple select field. example. I use tags… so in a single field my current tage are formatted with a underscore… I have 20 tags…: color_blue, color_green, size_7", size_9", shape_tube, shape_slant, skirted_vinyl I need to keep the existing “tags” for now (during the transiton of themes) but for my new theme I need all the same tag but WITHOUT the first workd and underscore example: blue, green, 7", 9", tube, slant, vinyl… how can I creat an automation? I am not proficient in formulas other than excel. Please help! Thank you!

Hi I have two tables Transactions - with a date Periods of time - with a Title, Start date and End date. ie “Year 1” runs from 1st April 2019 to 31st May 2020, “Year 2” runs from 1st June 2020 to 31st May 2021. I’d like to have a field in the transactions table, which looks up from the periods of time table, which year a transaction took place. So if the transaction was on the 1st July 2020, it would be in the Year 2 fiscal period. If it took place on the 1st May 2019, it was in the Year 1 fiscal period. I know I could write that with a long IF statement, but that is cumbersome! If my fiscal periods were whole years, (ie 1st Jan to 31st Dec) it would be so easy! Any ideas or suggestions very welcome. Thanks, Andrew

I have a complex formula about 2 letter size pages long. The formula didn’t work and I spent 2 hours of my life trying to figure out why it didn’t. Looking at the formula, after procrastinating a bit I went on crunching through it but I was expecting to be rewarded by a nice catch worth my time going on through this fishing expedition. Instead I was taken by surprise of how sometimes complex problems require simple solutions. Here is the over-simplified formula. The issue was I wasn’t negating the formula on the LEFT correctly. To negate the formula on the LEFT, I need to negate also function “OR” and turn it into “AND”. I guess this is a 101 basic of Math but it has been so many years I don’t remmeber. I guess I am getting old now. The first column on the left is PRICE and the field can be empty, zero or non-zero. The formula is check if the field PRICE is empty or zero and show “YES”, otherwise show “NO”. The first picture shows the formula wasn’t conve

Hi everybody how are you? I am trying to create a formula that divides by 2 the amount of the field “Quantitat” only if in the “Tipus” field it says “sacas” and places it in the field “Tones”. Is it possible? I am using the following formula: “IF (Tipus =” sacas “,” Quantitat "/ 2) is it correct? Thank you very much for the responses, greetings.

Hello, I’m Phil And I’m working for a video production company. We create video for others companies, all the production follow up is made with airtable. I’m working on a formula that can increment a cell (0,1,2,3) if specific date cells are filled let’s illustrate my use case I need to log the delivery date of video production (SLA are calculated using those dates) if {v1} is empty = work in progress = 0 if {v1} contains a date = done = 1 if {v2} contains a date = correction done = 2 if {v3} contains a date = re-correction done = 3 then I’d like to use that “increment calculation” to calculate my production costs using another formula {unit_cost}*{increment_calculation} something like that. Tell me what’s your advices :slightly_smiling_face: thanks

I am using a simple formula: IF(TDay > TODAY(), 1, 0) It works fine in a table where TDay is a field containing Date values. However when I use the formula in another table where TDay is a field containing Date values that are pulled from another table via a lookup then the formula does not work (it either provides all 0s or all 1s and does not perform the evaluation function). Any thoughts on what I’m doing wrong? Thanks in advance.

Hi everyone, I can’t find anything about this in the documentation or in the Community posts: is it possible, in a formula, to refer to field option IDs instead of option values? Let’s take a basic single select field named “Choice” with 2 values: “1” and “0”. It’s likely that these values could be changed to “Yes” and “No” or “Acknowledge” and “Refuse” in the future. To avoid any issue with the formula when that happens, I’d like to refer to the option IDs matching the value “1” (something like “selQHcj58xhh44C1J”) instead of the values: instead of IF(Choice="1","blah","") I’d have liked to use IF(Choice="selQHcj58xhh44C1J","blah","") - but it’s not working. Any way this would be possible in a formula (not in a script)? Best, Ludo