Formulas

Hello. I have two tables. Hardware and Ratings. Hardware is linked to Ratings and rolls up different ratings for hardware records. I have a roll up field in the Hardware table called ‘Average Rating Back’ which rolls up all the ratings (from the Ratings table) per hardware record to create an average. The formula I’m using here is: IF(ISERROR(AVERAGE(values))=0, AVERAGE(values), BLANK() ) I have a formula field as below in the Hardware table which converts the roll up fields integer and turns it into stars (essentially an average rating). IF({Average Rating Back}="",“This hardware hasn’t been reviewed yet”,REPT(“ :star: ”, {Average Rating Back})) If I don’t have an integer in the roll up field then the text will appear as per the IF statement. If I do have an integer then I use REPT to show a star per integer i.e. 2 = :star: :star: . This was all working fine until I just received an #ERROR! after the roll up field rolled up a ‘5’ and a 4’ rating for a hardware record. Do you know wh

Hi there, I have have a roll-up field that gives a load of values as CSV. Great. Turns out they are naturally in chronological order - Great again. They are all variable values with sometimes variable decimal points too. So I want to extract the last value. e.g. From this 19795.59,3909.09,6753.98,12435.720000000001 I am only looking for “12435.72” I’ve been thinking that I can use RIGHT and FIND the last “,” and select what comes after. But I always need a number of characters to look for. Is there a way to include the range to be from the last “,” to the last “.” +2? Thanks in advance!

So im having an awful hard time trying to figure this out. I only need the price in “Monto Texto”, im trying very hard to get only the number but for some reason “Monto2 Cut” is not formatting into number. My other guess was that its the comma that makes it unable to treat it as a currency or number, so I created a third column where I deleted the comma and still no luck. Any help is appreciated. I just need to be a number to make some calculations.

I want to track when a person takes stamps, and display the total stamps we have left. For example, We have 200 1st class stamps. Courtney takes 5 1st class stamps. The new stock is 195. Bridget takes 5 stamps. the new stock is 190 1st class stamps. Also tracking global stamps, and ‘over 1oz’ stamps. I need to track who takes what, when, and how much we have left. Easy to do in excel, not so much here.

I have print shop. And I have a need to calculate the useful usage of printing roll. For example, I know that the roll is 100meters, so if I add all printed jobs between replacements (ex.89meters), I know that my useful usage is 89%. So I have this table with fields (order#, meters, date roll changed, #number5) where #number5 right now filled manually. How can I replace it with formula field doing same comulative function between roll changes.

Hello everyone! I have an email address column. I want to create a formula column that will search each email domain for a specific text string; if that specific string exists, the formula column will print a pre-determined ID (also as a text/string). To illustrate: If an email address comes from abcd@paypal.com, input “PAYPAL” into the formula column. If an email address comes from zyxw@vrbo.com, input “VRBO.” I’ve tried combinations of Nested IF statements, SEARCH and FIND formulas, etc. but I’m stuck and would appreciate help. Here’s a screenshot showing the end result I’m looking for.

Hello Airtable Community! We have a situation where I am trying to create a “pass” of “fail” grade on wether or not an estimate was submitted by a certain date and time. Our table is structured as follows: There is a field called “24 Hour Deadline for Estimate” that is the deadline we are holding our estimator to as far as his deadline to submit to our customers. There are a few other fields that show the two different estimates and their correspond totals, as well as the roll up of the two types for a grand total. We also have two lookup fields that are filled out when we actually submit each type - “(MIT) Date Submitted (LU” and ““CON” Date Submitted (LU)” are these two fields. When our estimator submits one or the other on another table, he puts in the date. That is what these two fields are looking up. The ultimate goal of the formula that I am stuck on is to grade as “Pass” or “Fail” if we submit both of the estimates before the deadline. This field is named “Grade on 24 Hour Sub

The primary field here is the issue key. The issue key represents an “Epic” when the {parent} field is blank or when the issue type is “Epic” not “task”. The parent field links issues, with their epics - this confuses my existing formula for concatenating parent and summary or issue key and summary fields.

I am trying to have fillable fields in a encoded url that uses filterbyformula. here is my example: unencoded formula that gets a subset of records from a table. Here the search criteria is explicit. OR(AND({authenticatedUserId},{authenticatedUserId}=‘recB7VzcJLuL6UgIK’),AND({mobile},{mobile}=’+1 2345678987’),AND({email},{email}=‘johndoe@example.com’) here is the encoded form that will be used in the url as query. filterbyformula=fields%5B%5D=zwsMemberID&filterByFormula=OR(AND(%7BauthenticatedUserId%7D%2C%7BauthenticatedUserId%7D%3D’recB7VzcJLuL6UgIK’)%2CAND(%7Bmobile%7D%2C%7Bmobile%7D%3D’%2B1+2345678987’)%2CAND(%7Bemail%7D%2C%7Bemail%7D%3D’johndoe%40example.com’)) I would like to be able to have the explicit data be fillable fields. filterbyformula=fields%5B%5D=zwsMemberID&filterByFormula=OR(AND(%7BauthenticatedUserId%7D%2C%7BauthenticatedUserId%7D%3D**{{input_auth_user_id}})%2CAND(%7Bmobile%7D%2C%7Bmobile%7D%3D{{input_phonenumber}}’)%2CAND(%7Bemail%7D%2C%7Bemail%7D%3D{{input_

Here you can see I have 2 records, one has the value "abc\ndef" and the other has "abc\rdef". I can query the first one with {Value}="abc\ndef", but I cannot figure out how to query the second one. The obvious thing of "{Value}="abc\rdef" does not work, nor does any other thing I’ve tried.

Edited: I solved the first issue I mentioned. However I cannot figure out the issue regarding the formula {750mL}*13

Hello everyone! Hope you can help. I’m looking for a formula that will return the number of a quarter (e.g. Q1, Q2, etc.) based on the current date and the date when the customer started his subscription. For example, if the customer subscribed on October, 1st, 2021, and today is 13 January, 2022, it should return Q2 so that we knew his subscription phase, or quarter. Thank you!

Happy New Year, Airtabletons. I hope 2022 is good to you all. Here is my scenario. I have a table which is populated with all our employees, freelancers and contacts. Using a combination of manual entry and linked records I then populate a 2nd table with only our employees and freelancers which is then used to construct an org chart. My question is, is there a way to automatically copy the contents of a cell from the master table to the 2nd table IF the record contains a certain company name within it’s record? So for example: If a record within TableA contains “CompanyA” or “CompanyB” I want to have the first cell of that record entered into a new line on TableB. I hope that makes sense guys. Thanks in advance for your time.

Hi there, I’m using Airtable to log some vitals for my doctor, and, for some reason, DATETIME_FORMAT is returning the wrong times. So, 1:17 becomes 7:17, for example. Here’s my formula: DATETIME_FORMAT({Auto Date},"M/D/YY - h:m ") & {AM/PM} Any suggestions would be appreciated! :slightly_smiling_face:

Hi, I am agonizing over a function that will allow me to subtract a value of -1 every one day from the number in the “Days” column. The function is supposed to start counting when TODAY()>=“Date Start”. I made such a function in the “Days count” column but it shows the range always smaller by 1(when there are no weekends) and when there are weekends by (-1, -2, -3 etc) depending on how many weekends it has along the way. Here is my function from the Days count column: IF(TODAY()>={Date Start}, IF(Weekends="with weekends", DATETIME_DIFF({Date end},TODAY(),'d'), IF(Weekends="without weekends", DATETIME_DIFF({Date end},{Date Start},'d')- (INT(DATETIME_DIFF({Date end},{Date Start},'d')/7)+ IF(DATETIME_FORMAT({Date Start},'e')>DATETIME_FORMAT({Date end},'e'),1,0))*2, "") ), “”) Anyone have any idea how to write such a function that would do this correctly? The function has to subtract from the value in the Days column the value -1 every day and ideally it

Hi there - Looking for a way to have a formula show the first of the month for the current month and year. I.e. the formula fields says 2/1/2022 when it is Feb, then when it is March 3/1/2022 and so on…it would update on the first of each month Any help out there?

Hello there! I was just reaching out to see if there was a chance that someone, literally anyone can provide me with a BMI formula, please! I am trying to calculate BMI using height in inches and weight in pounds, and I’ve been searching for weeks for one that works, but it never accepts it. I’ve seen many articles and tips on conditional logic, but I don’t need it to tell me whether or not they’re overweight, I just need the actual BMI number. I’ve exhausted my personal and searchable resources, and since I’m also pretty new to this, when I DO find a formula that I think may work, I have trouble converting it from kg and meters or centimeters… I would be VERY grateful for a formula that I can copy and paste into the formula spot. My height and weight are both formatted to integers in inches and pounds and the result I would need for BMI would be for example Height: 64 Weight: 160 BMI: 25.7 Despite my many attempts I have become very frustrated with this and for some reason I cannot fi

Hello to the community, And happy 2022 for everyone who reads this topic :winking_face: Maybe I’m the only one to face this situation but I’ve created a pretty simple formula field which should provide a simple 1/0 boolean answer based on 3 conditions. One of those conditions is if one linked field to another table (Order_ID) is still blank/empty. IF(AND({Payment Status}!=“paid”,Order_ID=BLANK(),REGEX_MATCH(Email, “(\W|^)[\w.\-]{0,25}@[A-Za-z0-9.-]+\.[A-Za-z]{2,}(\W|$)”)=1),1,0) Guess what ? It doesn’t work. I’ve also tested the NOT(Order_ID) based on what I’ve read and it doesn’t work either BUT it works on a separate formula cell (see below the second line) : Other 2 conditions work perfectly. I assume this is due to the structure of a linked record by itself but I’m not sure. Thank you for your help. Best, Y

Hi, I want to remove the text “project to archive” in my primary field. Can I do this with a formula? This is a primary field, I don’t know if that’s an issue?

Hi, I have two date fields - one for a birthdate I actually know, and the other for an estimated birthdate (horse rescue here and sometime we know, sometimes we don’t…). I want to populate a third field to show “current age”. One or the other of the birthdate fields will be empty. I have the following but it doesn’t work. Can someone tell me how to fix it? Thanks for your help! IF({Foal Date}, DATETIME_DIFF({Today},{Foal Date},‘years’), ELSE(DATETIME_DIFF( {Today},{Estimated Foal Date}, ‘years’))

Hi there, I have written and tested a formula (it works) - AND(IS_AFTER({Invoice Date}, “12/31/2020”), IS_BEFORE({Invoice Date}, “12/31/2021”)) Where invoice date is a valid date field. This works perfectly well on the table interface, returning 0s and 1s as needed. When I use the exact same formula on filterByFormula parameter in the API, it returns (NodeJS) - AirtableError { error: ‘INVALID_FILTER_BY_FORMULA’, message: ‘The formula for filtering records is invalid: Invalid formula. Please check your formula text.’, statusCode: 422 } I couldn’t find any documentation saying that AND could not be used. *note - I already succeeded using only one date formula - IS AFTER

Hello, I have 1 single select field called “Status”. This Status field has the following options: Awaiting Information (Outreach 1) Awaiting Information (Outreach 2) Awaiting Information (Outreach 3) On Hold Each of these options has its own date field connected to an automation that date-stamps when the option is selected in Status. Outreach 1 Date Outreach 2 Date Outreach 3 Date On Hold Date I need 1 formula that returns a date with the following considered: If Status = Awaiting Information (Outreach 1), add “2” business days to Outreach 1 Date. If Status = Awaiting Information (Outreach 2), add “2” business days to Outreach 2 Date. If Status = Awaiting Information (Outreach 3), add “3” business days to Outreach 3 Date. If Status = On Hold, add “30” business days to On Hold Date. This is what I’ve tried so far with the first 3 dates conditions: IF(Status=“Awaiting Response (Outreach 1)”,DATEADD({Outreach 1 Date},2,‘days’)) & IF(Status=“Awaiting Response (Outreach 2)”,DATEAD

I’m using the following formula to show date and time for “Facebook event_time field”… DATETIME_FORMAT({Date},‘YYYY-MM-DDTH:mm:ss’) I want to add a letter “Z” to the end of the field… so at the moment its returing the event_time as: 2022-01-10T13:02:00 I want it to look like: 2022-01-10T13:02:00Z

Hello Everyone, I’m looking to get some fresh thoughts on this project I’m working on. I have a working base, but I feel like it will be too bulking as the tables grow. The goal is to create a notification message or message string in a record when a piece of equipment reaches a certain mileage or hours. Think of a car manual. In the maintenance section is a schedule of suggested repairs to be made once the car gets to a certain mileage. ie…at 30,000 - replace cabin air filter, replaced engine air filter. at 60,000mi replace the fuel filter, check belts,… I have a base with 3 tables - Equipment, PM Record, Repair History I’ve linked all 3 together using Equipment as the main working table. PM Records is used to capture mileage and hours at the time of maintenance. I then use two rollup fields on the Equipment table to display the Max() Miles and Hours record. I also have columns on the Equipment table per repair that display a repair message based on the rollup field. Then use anoth

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 need to calculate this “Master Revenue” figure for Prod