Formulas

I love Airtable! My goal is to use it for genealogy research which can be complicated, so I started with something fairly easy to learn different ways of doing things. My first base is a Pet Meds and Vet visits. Below is what I’ve tried, but I’m stuck on #3. If there’s a better way to do what I’m trying to accomplish, let me know! I’ve used SQL before, but it’s been a long time ago. 1 - I have a Prescription table which lists all the meds each of my pets has gotten (vaccinations and meds they take daily (Active meds)). I have a checkbox column for the Active meds in the Prescription table. #2 - Then I have a Pets table with 5 pets. I linked the prescription table to the Pets table. Now, the Pets table shows all the meds a specific pet has ever been given in the Prescrip

Hi! I am trying to create a directory of sorts in Airtable - we have providers and we capture if the users would recommend them. I then want to be able to easily be able to look at a provider (they are stored on their own table) and say X% of users recommend them. Any help is greatly appreciated!

I need some Airtable Formula help. I am trying to merge the below. I’ve been looking at it so long I can’t work out what is going on any more! This works: IF({Rejected}, IF({Rejection Reason}=“Unresponsive”,“Unresponsive”, IF({Rejected}, IF({Rejection Reason}!=“Unresponsive”,“Rejected”)))) And this does too: IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=2,“Done”, IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=1,“One of two”, IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=0,“Pending” )))))) However, if I put them together, then it only displays the results of the first 2 lines. If I switch it around so the 3 lines goes first, then it only displays the results of the 3 lines. Not sure why but any help gratefully received. :pray:

Hello! I have a Base with several complex formulas that I’m trying to pair down to be a bit more streamlined. If been trying to get the following formula to work for days with no luck. If anyone can help, it would be super appreciated… Here’s the setup: Title Category (Detail) / Single Select Field: Feature, Series (Comedy), Series (Documentary)… etc. Title Category (Main) / Nested IF Formula Field: This formula looks and the Genre Single select field and determines whether the record falls under four more macro-categories: Series, Feature, Sizzle, or Other. Title Release Date / Date Field: Date field that every record has formatted as YYYY-MM-DD Season Number / Single Select Field: Only records for series’ have this selected. If the record is for a movie it is left blank The formula field I need help with is titled Year/Season For Campaign Title. What I want this

Newbie question, I have a URL column but also want a single text column containing the url but in plain text tried =""&{URL} but that just copies the hyperlink to the new column. any ideas?

Hi All, Was hoping to get some input on the formula below: IF({D-Phase Status} = “D1 - Concept”, DATEADD(TODAY(), 30, ‘days’), TODAY()) I am trying to put together a formula which will add 30 days to today’s date if D1- Concept is chosen in the single select menu of the D-Phase Status column otherwise, just to display today’s date. I can’t quite work out why the formula isn’t working and would really appreciate your help. Thanks in advance

The formula below works for a single linked field look up but I would like to add a second linked record and calculate the total cost. The scenairo is Linked field =unit /start time/ stop time = cost I am looking for linked=unit +unit +unit etc start time/ stop time = cost on one line IF({Field 18}<24,{Look up}*Duration/3600)

This is odd. The display field for one table in the base I’m working on shows a concatenated string that represents each record’s basic uniqueness. At the end of the string, there is a reference to a charge. Should look like “$121” etc. For most of the 1000 or so recs in the table right now, that’s how it’s working. But I noticed a few records that were displaying something like “$121.000000001”. Maybe 5% of my records are showing that, maybe fewer. The display field’s calc includes a reference to a field ‘ChargeX’, a formula field. ChargeX = HowMany * Rate. The values in HowMany and Rate are not always integers but they are usually integers and they are integers in the cases where this display problem occurs. So 1.1 * $110 returns ‘$121.00’. The formatting of the ChargeX field is set to Currency with a precision of 2 (ex: ‘$5.00’). So I’ve got a nice clean $121.00 sitting in the ChargeX field. But when the display field for the

Hello, I am trying to concatenate 4 fields into one field with commas in between each field, only when the fields have a value in them (ie. no comma for empty fields). I’m using the below formula successfully to combine 2 fields, excluding the comma when a field is blank. But, I don’t know how to lengthen this formula to cover 4 fields. My 4 fields are, “Building,” “City,” “State,” & “Country.” I’ve tried several variations and can’t figure this out. =IF(OR(Building=BLANK(),City=BLANK()),CONCATENATE(Building,City),CONCATENATE(Building,", ",City)) Thank you for any help you can provide!

I would like to create a formula that could extract relative path from a full URL. Example: Column 1 domain1/landing-url-1 domain2/landing-url-2 domain3/landing-url-3 domain4/landing-url-4 Relative path: /landing-url-1 /landing-url-2 /landing-url-3 /landing-url-4 TRIM(MID({Column 1},SEARCH(“domain1”,{Column 1})+LEN(“domain1”),255)) I managed to create the formula that could extract from a particular domain, but I have 4 different types of domain in column 1. How can I combine them into one?

Hi I want to receive an Email or a Notification to let me know that a Due Date with Exactly time is coming up. More details about my case: I have a Bakery Store, my customer will pre-order our Birthday Cake. So I have to Deliver on the date & time they request. Example: Order date: 31/03/2020 Receive date: 02/04/2020 at 3PM I want to have a notification about 30-1hour to let me know for booking Delivery Suppliers to pickup and deliver the Birthday Cake. Thank you so much.

I have two tables, “Projects” and “Tasks”. I need to display the completion status of the tasks in the “Projects” table. In “Tasks”, I have a single select dropdown with “Not Started”, “In Progress”, and “Completed”. I would like to display the “Progress” in “Projects”. It should say “1/4” if there is one “Completed” and 4 tasks. The formula in Excel is “=CONCATENATE(COUNTIF(Sheet2!A2:A5, “Completed”), “/”, COUNTA(Sheet2!A2:A5))” Please help me find out how to do this in AirTable, thanks!

I’m trying to figure out a formula to give me the following info in our age of Corona… I want to put a column on our main table that returns either a ‘yes’ or a ‘no’ based on data from 2 other tables. I’ve got the tables set up to give me a ‘1’ for a count so all I want is for the column on the main table to read data from those other two tables and give me a yes or no… possible?

What’s wrong with this formula? It references a single field named ‘Date’. That field usually will have a valid date in it, but in some cases the field might be left empty. In a separate formula field I’m trying to use this formula but it’s not working. IF ( Date = BLANK(), “Date Missing”, DATETIME_FORMAT(Date,‘YYYYMMDD’) ) What’s wrong with that? How else do I test to see if a field is empty? William

I’m having people place orders for a sale online. I’d like to have a rollup form of their totals, since the only tidy way to submit orders is one item per submission, so there will be several lines. The buyer is submitting their e-mail with each order submitted: person1@email.com 1 shirt $10 person1@email.com 2 shoes $25 person1@email.com 10 chairs $7 person2@gmail.com 2 shirts $18 I’d like to be able to somehow summarize the total amount per e-mail address. I can figure out how to do it if I were inputting the e-mail addresses ahead of time, but not when they’re coming in from a form.

Hi everyone! Super new to Airtable (as of today actually) and I’m trying to have a “Status” column update based off a horrendous nested IF statement referencing multiple other columns, shown here: MD Signoff (Not Started, In Process, Pending adjustment, Complete) RN Signoff (Not Started, In Process, Pending adjustment, Complete) Rx Signoff (Not Started, In Process, Pending adjustment, Complete) I’m using the following formula (please don’t make fun of me :frowning: lol) but am only getting some of it to work as expected: IF( AND( {MD Signoff}=“Complete”, {RN Signoff} = “Complete”, {Rx Signoff} = “Complete” ), “Complete”, IF( AND( {MD Signoff} = “Complete”, {RN Signoff} = “Complete”, {Rx Signoff} != “Complete” ), IF( AND( {MD Signoff} = “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} =“Complete” ), IF( AND( {MD Signoff} = “Complete”, {RN Signoff} != “Complete”, {Rx Signoff}

How can I achieve the following from Excel in Airtable? In a time table of “Events”, the StartTime of an Event begins when the EndTime of the previous Event ends: A B C Record/Row: StartTime Duration EndTime 1 14:00:00 00:30 14:30:00 2 14:30:00 00:45 15:15:00 In Formulas in Excel 1 14:00:00 00:30 =A1+B1 2 C1 00:45 =A2+B2 I would be grateful to anyone who can help me figure out how to do this. Thank you! Peter

Why would this return blank and no errors? IF(AND({Status}=“AM: Follow Up on Proposal”,{24 Hours}=“2 Days”,“yes”,“no”)) Fields: Status=Single select 24 Hours=Formula

Hello all! I have an airtable issue where I’m trying to set up a notification for a field when Training Completion Date is prior to 1/3/18. For more clarification, the indicator is being used to know when to retrain my coworkers but I’m having trouble creating the equation if one exists. I’m still fresh to airtable, but I already see the appeal. If someone can help me create a formula for a column I would be very grateful. Thank you all for your time.

Creating a table for bidding purposes and after my first field I realized my formula table is going to be crazy! (Trying to avoid another field option) As seen in the screen shot I have (SF) Costs which is a formula for the cost per SF. However, not all cost are the same. Without adding a tremendously long formula for each product, possibly by SKU as the variable, is there a way to add the difference per SF cost? Thank you.

I am looking to create a formula that enters a date when the status of another field is changed scenerio: a field called “Job State” is changed to “sent to writer” and then the formula would fill in the date that action was made I am then going to build a Zapier Zap to kick out a status update email to the client

Hi all - I’m new to Airtable, we use it at the Transportation Authority I work for. What I am looking to do is create a database of bus schedules by their shipping quantities (amount). Most schedules are distributed to multiple locations, which I have a table for each. Quantities of schedules per route vary from location to location. I am trying to get the total quantity of each schedule - across all tables - to the Total page, where I can view the grand total quantity per route. I thought I was able to use the rollup feature, but I cannot get it to link multiple columns to provide me with the outcome I am looking for. Maybe I need a formula? Any assistance is greatly appreciated. Thank you in advance!

Good evening everyone. Hope to find some help calculating a tennis player rankings based on their best 6 results out of 15 tournaments played. What would be the formula if tournament values are: T1, T2, T3…T15? Thank you!!

HI Community I’m relatively new to Airtable. I’m working to develop a prototype for a client. I need some advice. The project is creating a system to automatically adjust the time requirements for staffing based on forecast occupancy of a hotel. Here is the flow for data: The “Setup Table” is the initial setup for the staff for the hotel: minimum hours, hours req per room occupied, etc. The “Forecast Table” gives the hotel management to outline the occupancy for the hotel given a 2 week look ahead. I have 5 different Calc tables, because the hours are calculated differently. Think of these as junction tables. Right now I’m working to create the “Time Req Table” that aggregates the junction table. Next I’m going to build the Time tracking Actual table and then the Dashboard to compare the actual to forecast metrics. I’m

I have a base with over 1200 number fields and a few text fields. Is there a better way to sum all number fields that start with “M”? At the moment i am using the below formula which i regularly need to update every time i add a new field. SUM(M001+M002+M003+M013+M018+M019+M031+M032+M036+M039+M040+M042+M043+M045+M047+M048+M049+M050+M051+M052+M053+M055+M056+M057+M058+M059+M060+M062+M063+M064+M065+M066+M067+M068+M071+M072+M073+M074+M075+M083+M084+M087+M095+M096+M097+M099+M100+M101+M103+M104+M105+M112+M113+M116+M117+M123+M125+M126+M127+M128+M129+M135+M136+M141+M163+M172+M186+M187+M202+M206+M207+M208+M209+M210+M211+M212+M213+M214+M215+M216+M217+M218+M219+M220+M221+M222+M223+M224+M258+M280+M290+M294+M317+M327+M330+M331+M332+M333+M336+M363+M364+M373+M579+M580+M581+M583+M584+M585+M587+M589+M590+M592+M593+M594+M595+M597+M598+M599+M600+M601+M602+M603+M604+M605+M606+M608+M611+M612+M613+M614+M615+M617+M618+M619+M620+M622+M623+M624+M625+M626+M627+M628+M629+M630+M631+M632+M633+M634+M635+M