Formulas

Column #1 {Need Hotel?} Yes or No Column #2 {Hotel Cost (Estimate)} {Estimated # Staff} * 250 * {Duration (Days) (Do Not Edit)} What I need is to change the column in #2 so that If {Need Hotel?} = Yes, then Do {Estimated # Staff} * 250 * {Duration (Days) (Do Not Edit)}

Ok so I got one that really threw me. I have a lookup field on Tab 1, referencing Tab 3, I need that info in Tab 2, but rolled up, so I created a rollup field in Tab 2 that rolls up the lookup field in Tab1 (Tabs 2 & 3 aren’t linked). The contents look like ABC, DEF in Tab 3, and the lookup field in Tab 1, but the rollup field in Tab 2 produces “ABC, DEF”. Where the heck did the quotes come from? Using ARRAYUNIQUE. Get this, I go back to the original record, ABC, DEF and change it to ABC-DEF and bam, no quotes any more!! I’m really confused, if anyone could make a suggestion would be much appreciated.

Hi! I’m new to Airtable and getting our bases set up. I’d like to have a field/column to notify us at quick glance if something has passed the “Anticipated Due Date” AND not been marked as “Completed” or “Not Applicable” for the Status. I’m struggling to find the correct formula for this. Is it also possible to also have this formula set to change color, say to red, when “Overdue” as you could do in excel? I’m terrible at properly formatting these formulas… sigh.

Greetings! I am trying to track event registrations. My table has these fields: Primary First Name Primary Last Name Event #1 Status (Single Select: Yes, No, Cancel, Didn’t Attend, Hasn’t Replied–applies to attendee coming alone or to attendee bringing a guest) Event #1 Meal for Primary First (Single Select: Meat, Veg) Guest First Name Guest Last Name Event #1 Meal for Primary Guest (Single Select: Meat, Veg) How best to see a total sum of all the meat meals and all the veg meals? Thank you–any assistance is greatly appreciated!

I’m trying to ROUNDUP() the value of a “Wall Width” / “Usable Roll Width” but I’m getting an error in the formula. See screenshot here: UGH why can’t I upload a screen shot!! t’s a very straight forward formula and I can’t figure out why it’s not rounding up to 4, in this case.

Hello, I am a student in art history and I find that Airtable is a good way to make my own database. But, I would like to know If I can use it for the whole year ? I feel afraid to lost all my datas… Thanks for your answer ! Alithéia

Hi everyone, I’m not sure if I’m missing something or what but when I clear out all the data in a column and then paste new data into the column, it still has the previous data saved? I know this because when I switch from single line text to multiple select, the old data shows up as a bunch of choices. Any help? The solution I found is to delete the column and make a new one but I have a ton of data and this seems like it may lead to issues half way through my project. Thanks.

I need help with an IF Formula. I have the following “Category” Field - Single Select “Est Rate” Field - number currency “Rate to Quote” Field - number currency (where I want the formula) Looking for a formula that does the following: If the the “Category” is NOT "Wedding, then “Rate to Quote” = “Est Rate” + 25%, If “Category” is “Wedding”, “Rate to Quote” = “Est Rate”

I’m struggling with the following problem. I’ve made a small system for a clock in a clock out, which tells how much money we’ll be invoices at the end of the month. However sometimes staff manage to clock in and out twice, which makes my end of month salary calculations in accurate. I’ve been trying to think up a way to check if the same staff has logged the same date twice. I’ve found a way that involves a sperate linked table where each row contain it’s own date and each staff has its own column with a bunch of look ups. However its very messy an inflexible and way more time-consuming than meticulously combing through all the clock ins at the end of the month. Sofar I’ve come up short, I have the feeling it’s not possible, but is there anyone who has any suggestion?

I’ve got a table of rates with “effectivity dates”. Such that when an order comes in, I need to find the “current” rate associated with that order, on the order’s date. My structure so far is as follows: Table Order - Line Items (Link to Order Line Items) - Order Date Table Order Line Item - Order (Link to Orders) - Order Date (Lookup to Orders.Date) - Line Item Total - Line Item Vendor (link to Vendor) - Effective Vendor Rate (?????HOW????) Table Vendor - Name - Rates (Link to Vendor Rates) Vendor Rate - Start Date - End Date - Rate (Percentage)

Hi wonder if there is anyone who can help. I am trying to make a deadline field. I need a formula that adds a certain amount of time to the created date if another field has a certain word in it. hope this makes sense

I’ve got a table that already has a link that is working. In this case it is Order Line Items to Products. I’d like to have a column in my Products table that has the total of Order Line Items (line item total) within the past 60 days. I could change the link to be filtered by a View, but then I’d not be able to see the Total Lifetime Orders. How can I filter the linked records during Rollup?

I’m looking for a formula for the following. I have a Checkbox Field “Msg’d” & Date Field “Date Msg’d” & another Checkbox Field “Responded” I’m wanting to find a formula where, If “Msg’d” is checked & “Responded is NOT checked” & it’s been more than 3 days after “Date Msg’d” it is marked “FOLLOW UP”

I’m trying to get a formula that allows we the take the total of numbers in the linked filed to be summed together For example Total Amount, Amounts used, Total Amount used, Currently in Stock 10 1,4,5 10 0 Idk if this makes any sense at all i just have a list of items in one table with all their information in another table any time i use the item i track the date and the amount used I would like my stock table Currently in Stock number to be able to subtract the total amount used since i may use a product multiple times in different areas or times My Total Amount is to help me track the amount i started to help know if we should get less or more incrase our amount

I have one table of people, and another with records of their visits. In the people table, I am trying to add a field that will tell me the date of the earliest visit… after 9/30/2016. I tried adding a formula field in the visits table to return the date of the visit only if it is after 9/30/16, but I have no idea how to build a specific date into the formula. I really don’t want to add another field to the table that will force me to add “9/30/16” to every single visit record just so I can do an IS_AFTER formula with two date fields. (Which reminds me, why don’t default values you set for certain fields show up when you add new records in the mobile app? Real bummer. But I digress.) IF(IS_AFTER(DATE,9/30/16),DATETIME_FORMAT(DATE,‘MM/DD/YYYY’),"") returns #ERROR everywhere. I am really hoping my format “9/30/16” is the problem, but I have tried other variations with no success. Meanwhile, if it is possible to get that formula to work, will a roll-up field in the people table let me pul

Hey everyone, I have an update issue I can’t figure out. In my database, I have filtered my projects by brand type which will show up on my Android phone. However, on my boss’s iPhone the filter isn’t showing up. The other issue is that I can search for a specific part number based on the item description such as 1/4" ratchet and find it in my Android app but my boss can not. Once he types in the ", no record will show up. Any tips?

I have two tables - one for income transactions and one for expenses. I’m trying to create a summary table that aggregates values from the two other tables to show monthly balance. Is there a way to: Aggregate rows with dates into months? Pull in amounts from income and expense tables that map to those months?

Without going to Google sheets, how can I format an imported date/time from 2019-09-06 7:52 PM in the formula field to 9/06/2019 7:52 PM? Everything I’ve found just sets everything past 12pm to 12:00 AM. Also, is there something I’m missing using the regular date/time field? This doesn’t seem like a big stretch, but when I change the field type it just clears everything. Thanks!

Hi, I have a base that covers a script breakdown. One table within the base includes all information in regards to a Scene within an Episode, including all characters that need to be recorded within that scene (Script Breakdown). Another table is a more granular version of this that breaks out each character of that scene to it’s own row. Both tables are linked via the Episode and Scene number. In this table I have a check box that I tick if a particular character voice has been recorded (Script Recorded Breakdown). What I would like to do is create a formula within the Script Breakdown table that says something along the lines of ‘if all of the checkboxes within the “Script Recorded Breakdown Table” that link to this particular scene are checked’ than give the answer ‘recorded’ otherwise leave blank or something along those lines. I hope this makes sense? In excel I would use a combination of if/vlookup but I’m not too sure what to do in airtable. Any ideas??

Greetings to all. I’m trying to build a filter that only shows me records from specific items that are linked from another table. I have two tables linked together. One shows the names of users on my network along with other general information. My second table is a list of licenses and prices per licenses. The license table is linked to the user table so that if a user have 2 licenses I will be able to see which licenses the users has from the user table. There are some other roll up columns that calculate total cost of users licenses. Every time I create a regular filter on the user table to see a list of specific licenses that I can group by the user department, it treats every combination of licenses as a different criteria. I want to be able to filter specific licenses only. Not sure I’m explaining it the best way. I have images but I guess I can’t upload them on a post Any help will be greatly appreciated.

So I’ve got a ton of columns, I’m rolling up using ARRAYUNIQUE, I get ", " between each item getting rolled up. I tried adding ARRAYUNIQUE(values, “”) to the formula which doesn’t work, it does in a rollupfield for ARRAYJOIN, but not ARRAYUNIQUE. So I got the idea in the rollup to add the formula ARRAYUNIQUE(ARRAYJOIN(values, “”), which amazingly worked, got rid fo the commas, just one catch it duplicates the first value (it does the same if I create a formula in the column next to it so my main rollup column is a ARRAYJOIN and then formula column next to it is ARRAYUNIQUE but it duplicates the first value), any advice on a better way to do this? FYI for the record what I have now is the rollup table using ARRAYUNIQUE, with a field next to it using a Substitute formula to get rid of the ", " the Arrayunique is adding in, thankfully my data doesn’t have any commas but what if it did? What would I do? Cheers, Skyler

Hi all, I’ve got a request about a specific formula. I’m creating a rental database and have ‘Start Date’ and ‘End Date’ columns that link to calendar events (jobs). I’m looking for a formula that I can put in a ‘Status’ column. Is there a way to have an IF statement that returns “Booked” if the start date is before today, “In Use” if today is between the start and end dates, and “Available” if today is after the end date. I just can’t quite wrap my head around it since most date formulas return either a 1, or 0 and I’m working with 3 return values. Right now I’ve got the following: IF(IS_AFTER({Start Date}, TODAY()), “Booked”, IF(IS_AFTER(TODAY()), {End Date}), “Available”, “In Use”)) but it is not working. any help is appreciated, thanks!

Hi, I have the following formula that works by displaying a ! when col1 is empty: IF({Col1} = BLANK(), “!”, “”) However I would like to add OR statement so that the ! is displayed if either Col1, Col2 or Col3 have no entries. I have tried varying different ways with no luck, such as: IF({Col1} or {Col2} or {Col3} = BLANK(), “!”, “”) Thanks in advanced.

I generate 50,000 records per year. Is possible has in Airtable 500,000 records (10 years) without problems?

I have 3 tables; one is the main table that has two linked fields to the two other tables. The other two tables have an attachments field with one attachment. I want to add a new column on the main table that shows the attachments of both the the attachments in the associated tables. Is there anyway to do that?