Formulas

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 Type 2 asset at that location - there is nothing to average it with, etc.

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… something that will make me absolutely jump for the moon is if it were possible to have a field that would pull the notes from my Communiques table and arrange them by date as so: 14/7/21 - CALL Spoke to Bob - he said he’ll get Amy to call me back. 18/6/21 - CALL Spoke to Bob - he said Michael will have to approve. 1/6/21 - CALL Spoke to Bob - he said Cartright will have to review. Does that make sense? Because right no

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 exist anymore during the latest script run on the secondary tab

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 converted correclty on the RIGHT. Th

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

Hi, having trouble getting rid of the “NaN” when trying to figure out the average difference in dates per sales manager. In one table (named “Tracking Log)” have a date field for “Transport Order Date” and a date field for “Commitment Date”. I used the following formula to look determine the difference in dates by number of days for each sale they made in a field I named “Time to Order Transport (days)”. IF( AND( {Transport Order Date}, {Commitment Date} ),DATETIME_DIFF({Transport Order Date},{Commitment Date},‘days’),BLANK()) Each manager is doing dozens of these transactions each month so I now want to see the Average days per manager for this date difference. I have another table (named “Sales Manager”) where I did a look-up of the field from Tracking log named “Time to Order Transport (days)”. I then created a new a rollup field, as well as a formula field using “Average”, however I have some new managers who have just started and have blank fields and I can’t figure out how

Thanks in advance to anyone who even scans this query! :blush: I have a rollup field that is flattening an array of dates - that’s working great! What I want to do is produce this same information showing only the month. For example, I have this list (a flattened list of dates from 3 different records showing only unique dates): 13/09/2021, 20/09/2021, 27/09/2021, 04/10/2021, 06/10/2021, 13/10/2021, 20/10/2021, 13/12/2021, 29/11/2021, 22/11/2021, 15/11/2021, 08/11/2021, 25/10/2021, 20/12/2021, 09/12/2021, 01/10/2021, 08/10/2021, 18/10/2021, 22/10/2021, 29/10/2021, 05/11/2021, 12/11/2021, 19/11/2021, 26/11/2021, 10/12/2021, 17/12/2021, 03/12/2021 I want to create a formula that converts this list of dates into (just the month of each date as an array): 09,09,09,10,10,10,10,12,11,11,11,11,10,12,12,10 etc… If I extract the month before producing the full array of dates, of course, it only ever gives me 1 instance of each month which is not what I want. I’ve been trying to work out the

Hi, I am trying to figure out why I keep getting this error message: The Impact Value is the average of the Confidentiality, Integrity and Availability column. (Confidentiality+Integrity+Availability)/3 The Risk Value should be multiplying the Impact Value with the Likelihood Value. But I am getting an error message with this basic formula {Impact Value}*{Likelihood Value} Please help.

I have 4 sets of formulas. SUBSTITUTE({Status}, “Floater Membership”, “Available”) SUBSTITUTE({Status}, “Pending Lease”, “Available”) SUBSTITUTE({Status},“Pending Payment”,“Available”) SUBSTITUTE({Status}, “Floater Membership”, “Available”) We wanted to merge them into one. I tried to do it with this formula SUBSTITUTE(SUBSTITUTE({Status}, “Pending Transfer”, “Available”), “Pending Lease”, “Available”), “Pending Payment”, “Available”), “Floater Membership”, “Available”) but it is not working. What is your workaround with this?

Hello ! I’m new to airtable and struggling to write a formula that allows me to extract dates from a field. I have tried many different formulas found online but nothing matches my case. I have a SPRINT column that hosts in each field a set of dates: 2022-01-03, 2022-01-10, 2022-01-17… (can go up to 53 dates). I would like to extract from any of the fields in the SPRINT column the FIRST date (2022-01-03) and send it to another field in a new column named START. Then, extract from any of the fields in the SPRINT column the LAST date (2022-01-17) and send it to another field in a new column named END. Do you think that is possible and if so could you help me find out the correct formula ? Thanks !