Formulas

How can I use linked records to create scalable reports? I am trying to build a maintainance databases with “assets”, “maint types” and “log”. For example… Assets: red truck, white truck Maint types: engine oil, etc Log: date + asset (link) + Maint type (multi-link) I would like to create a report for each asset & maint type combo when the last date of maintenance was. I am aware that I can use logic formulas on the “log” and use rollup on the asset to find the latest date in a specific category, but that requires unstable and manual formula creation for each maintenance type. Goal: Item, Asset (link), Maint Type (link), Latest Service Date (for this combo) Here is a read only version of the base… @W_Vann_Hall I thought this might interest you. I looked at several of your examples and posts, but could not find my solutions… perhaps you have already solved it and I

Thanks in advance for your help here! I have created a formula field that has nested IF statements that multiplies a Currency field by either a 12, or a 4, depending if the payment is monthly or quarterly. The result displayed is numerically correct, but when I try to format the number shown, the Formatting tab in the Customize Field Type dialog displays, “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” The greater issue for me here, beyond the formatting, is not being able to sum or do other calculations on the field. IF({Payment frequency}=“Monthly”,{Price AUD}*12,IF({Payment frequency}=“Quarterly”,{Price AUD}*4,IF({Payment frequency}=“Annually”,{Price AUD},""))) It doesn’t make sense to me that the calculation works fine, but the returned result is, it seems, considered to be text, not a number? Thanks in advance for your insights everyone! Cheers MC

Hi, When I try to save my images from Airtable into a folder and change the name, it converts the image file into a file that I can’t open. Any suggestions.

Does anyone know how to delete a Category. I accidently created one. My other question is how to rearrange them. I don’t see any functions on how to. Thanks again, and I apologize if I am using the wrong terms.

I need to make a formula. In it, I will have a report due date, then the date the report was submitted. I want to make a report progress column that states if the report is ‘overdue’, ‘in progress’, or ‘up and coming’. However, here’s my problem: Report Due Date will always have a date Date the report is submitted may be blank How can I make a formula based off Report Due Dates saying the status if the date in the “Report Submitted” column is blank?

How do I get information from one table with a certain status (on schedule) to automatically update to another table? All of my tables are linked, but I cannot get the IF formula to move the information to another table automatically.

I use Airtable to keep track of my service tasks at work. We are watchmakers and we repair watches. So, I would like to record the date when a job is finished by us, and another when the customer picks up the watch. The intervals between the start, finish and pick up are very useful to us! I have used Zapier to export a field to a new Airtable form, via a Google calendar event, to make a time stamp. This works well, but adding another time stamp to the same original listing has proven very difficult to accomplish. Any help in much appreciated!

Hey Airtable Kinds and Queens! Need a bit of assistance for a mere airtable peasant. I have 2 tables. One table has: rep ID and other sales data. This data is updated monthly with the amount they sold that month. ID#, Name, Retail Sales, Date 343, John Doe, $5,000, 1/1/2018 343, John Doe, $2,000, 2/1/2018 Second table has the contact details including email, phone number, etc. Here is where I am struggling. I want to create a column that will show the Sales Persons last months sales, previous 3 months average sales and average sales for all time. I have the tables linked by the ID# so the data is linked. I can rollup the average for all time, but I’m not sure how to put a date function into the rollup or other field to only give me the average for a specific date rang. Any ideas? Thank you so much!

Hey folks! I’m moving all my finance to airtable, so far so good. Couldn’t figure out how to generate a cashflow view. In each record, I want to see the sum of the previous records. The Records are sorted by date and i want to see every day the cash status. I didn’t see any option of doing it… Thank you!

Aloha! Looking for help. Need help with a formula. I tried this: Here’s an example: On 6/21/19: Clock in at 6:47 AM One 6/21/19: Clock out at 11:00 AM On 6/21/19: Clock in at 11:30 AM On 6/21/19: Clock out at 1:00 PM On 6/21/19: Clock in at 1:15 PM DATETIME_DIFF({Time Out (1)},{Time In (1)},{Time Out (2)},{Time In (2)},{Time Out (3)},{Time In (3)}, ‘minutes’) This is what I keep getting back in return: Time duration: -63142200000 Help Please! I am at a loss!!!

Is anyone using Airtable in a Healthcare Organization? If so, do you consider Airtable HIPAA Compliant and secure for the loading Protected Health Information, PHI, into an Airtable database.

Hi folks, I’m trying to create a formula that will return a date and time that is a pre-designated time the next day. For example: Jan 12, 2019 @ 2:19 PM AND JAN 12, 2019 @ 4:00 PM Would both return a date of JAN 13th, 2019 @ 5:00 PM I can add 24 hours with the DATE ADD formula, but is there a nested formula I can use to modify all times to a predetermined time? The purpose of this formula is to create a due date field, that is a set time one day after all form submissions that populate my table. Thanks!

How do I delete multiple duplicate data from the sheet?

Hi, I’m trying to calculate if {Table 1 Field 2} (date field) is before {Table 2 Field 2} (rollup field to a date field). The structure is: Table 1. Field 1: Student name Field 2: Date student enrolled into college Field 3: Subject name(s) (linked record to Table 2) Field 4: Date(s) (rollup field through Field 3) Table 2. Field 1: Subject name Field 2: date of class Field 3: Student name Basically, any formula cannot work around the rollup field because 1) it’s an array and (usually) has more than one date as there is more than one linked record… so I get something that looks like this: date, date, date, date. Does anyone know how I might calculate if the Date student enrolled into college comes before Date of class, given that there might be more than one class? Alternatively, creating a bridging table to capture both of these so that there is no use of a rollup that is showing more than one date. T

I’m working on a P/L table to find the revenue per job. I’m running into a roadblock trying to figure out how to get the percentage increase or loss per job to show a more accurate %? No matter how I change the percentage set up (1, 1.0, 1.00 etc). See below of my current table - suggestions are appreciated. Here are the formulas I am using: Overhead: Net Income: Gross Profit Percentage :

Alrighty — hopefully I can explain this correctly – So I have a table with all my Load information – the Master Table. I have a side table that has my Sales Reps info —contact, ppw, etc. What I would like to do is pull info from the Master Table - Load Information to my Sales Rep table. if the Sales Rep is Adam in the Master Table, then I want to take the corresponding Revenue Margin in the same row to pulled to the Sales Reps table and then ultimately added together so we can get a grand total of revenue this sales rep brought in. Thanks guys!

I am using airtable to make a rota I have a table of doctors and a separate table for each month of the year A field in the month table links to the doctors table so i can put a doctor’s name in that day i would like to count how many on calls each doctor does more specifically, i would like to count weekdays, weekends and public holidays separately to that end, i created another table that links to the dates on the month table and classes each day as a weekend, weekday or public holiday i know the information is there - if i select a doctor from the doctors table, the linked field to the month table contains a list of dates that doctor is on call but how can i count those dates and even count them based on whether they’re a weekend or not etc? i’ve been trying for a few hours now, messing about with rollups, new tables and copious google/forum

Thanks in advance for your help! I need to create a formula to determine a project’s classification based on Date Added. There are three classifications: Rocks – 90 days or less, Boulders – 180 days or less, and Mountains – 180 days or more.

I have a column named “Weights” with values like “1.0” or “2.2” that I use for basic item ordering. x.0 = default item x.1 = first optional item x.2 = second optional item eg: 1.0 - Include 2.0 - Include 3.0 - Include 3.1 - Exclude 3.2 - Exclude 4.0 - Include 5.0 - Include I’m using a Rollup field to add up prices, but it’s including all the optional items in the sum, where I only want the default items included. My thought was "Create a formula field that outputs the Total Price field, but only if the far-right character of the value of the Weights column was a zero, if it’s anything other than zero, then set the price to “0” instead… Then create a rollup field on that “Total Prices Without Optional Values” column… Can’t figure out how to get RIGHT() to show me the right-most character of the Weights field so far…

Hi all, Just wondering if anyone has an excel template (or Airtable?) that has some embedded formulas to prefill a form and would be so kind to share it with me? I’m trying to create one myself but I’m only intermediate at excel, and the form I’m using has about 20 prefilled fields (plus the record ID at the top hooked in with a Zap to update the record). Issues I’m struggling with is using the TEXTJOIN and CONCAT formulas, especially given that the fields are sometimes long text with multiple spaces. Happy to collaborate with my current work too. Regards, Tom.

I want to execute my own third-party code when I set the value for a record. For example, when I set the value for my applicant to be “Pending”, I want to trigger a texting API to send that applicant an SMS. Can you guide me on how to do so

Hi All! I’m new and I have a basic question. I’m an investor organizing our deals. I need to do the following: In my 1st table, I have a field called “Priority”. One of the options within the Field is: “No Interest”. When I select “No Interest”, I want that record to automatically populate another table called “Deals Declined”. I’ve looked at the following Help article but unfortunately, I can’t get it to work. Any help would be MUCH appreciated.

I am trying to make a simple formula that would show a total amount available between two fields if a third field had a particular category in it. IF {Type of Credit}=“Credit Card” then({Total Credit}-{Total Amount Due:}) I have not been successful getting the syntax right, but I am a new user to airtable and am probably still thinking like a spreadsheet… Thank you for your help! Colleen

I am having trouble getting this formula to work. I need it to calculate 28 days from column one and insert the date in column two, and if no date exists in column one the second column should be blank, any help would be greatly appreciated guys, thanks.

What service do you use for background checks? Checkr.com does not support airtable