Formulas

Hello Airtable friends.I have a somewhat simple formulaic excel sheet that becomes very complicated when breaking it up to be used in Airtable.The goal:Based on the type of sale, each of 6 static 'sale stages' should have defaul percentage (out of 100) assigned to them. Then for each of those 6 stage percentages, I need to allocate all of them across a variable amount of Sales Team members. Here's an example:Sales Type (variable) = New LeadSales stages:1 = 10%2 = 30%3 = 5%4 = 25%5 = 15%6 = 15%Now for this sale, Person1, Person2 and Person3 all contributed. So for each stage I need to distribute their percentage (this is manual input)1 = 10% - Person1 = 3%, Person2 = 3%, Person3 =4%And so on through all of the stages.There's like 8 different sales types, of which the % distribution across 'sales stages' varies and the potential number of people involved in a sale can be up to 6. So to sum up variables:Sales Type % Allocation per Stage (based off of Sales Type)# Of Pe

New to Airtable... Trying to create formula that will calculate 6.25% tax but ONLY if the "State" column is "MA" (for Massachusetts)... I figured out the tax part (Price*0.0625), but not getting the If State=MA, only then calculate tax for the tax column (otherwise, if not MA, just want to see 0 in the tax column).Thanks!     

Hello,let us say that I have a simple table containg a list of products and the sale amount of each product. Now i want to add the share of sales of each product in this table.The only way I found to do that is to create a new table called "Total". This table contains only 1 line and is linked to the product table in order to rollup the value Sales to compute and store the Total of sales accross all products. This total is then brought back by lookup into the table Product, meaning the a new formula field (Sales / Total) can be set up.Problem is that this formula is not dynamic, meaning that if we filter the table, the share is not correct anymore since the total is not recomputed.is there a solution do set up a computation with a dynamic total responsive to the filters applied on the table?Thank youPascal

Hi, I have a field of clothing sizes which varies for each garment record. Ideally I'd like to be able to set up a single select field with options that vary depending clothing sizes held in the multiselect or linked field for that particular record.I can display the options so the user manually picks one of those out of all the sizing options, but it would be nice if the options presented only pertained to the specific garment.Is there a way to do this? Any thoughts would be much appreciated!

I am struggling to get the DATETIME_FORMAT to return the correct time for me. For example, my Start Date is 10 January 2023 00:00 and in my formula column, I just want it to extract the time of the "Start Date". So I put in this formula: DATETIME_FORMAT({Start Date},'HH:mm')However, it keeps returning the time as 16:00.Can anyone help? 

Hello Community! I need some guidance... I have a few columns with fields that are Single Select and one of the options is "COMPLETED". I am looking for a way to create a formula for it to add "how many COMPLETED fields" I have for that record... For example if it was Excel I would try an "IF" formula or "COUNT" to add how many columns have the COMPLETED field, but here I just can't figure it out. Anyone know how to go about? 

I have two fieldsa start day for a task (date field)an estimated number of workdays the work will take to complete (a number).I want to add those workdays to the start date and count ahead to the date the work will be completed. I do not want to count weekends.I've tried the formula below to estimate the date the task will be completed by combining my two fields. DATEADD({Imaging Start Date},{Forecast Workdays for this Batch},'days') However, that counts weekend days as workdays, thus returning an earlier forecasted completion date.For example, if the work starts 6/1/23 and should take 10 workdays, the returned date should be 6/14/23, not 6/10/23.Is there a way to account for this in my formula or the data? 

Hello!I have a table of clients, each of whom has been assigned a coach.I have a second table of appointments which is coming from Acuity. In that table, I have the clients linked so I can see all of the client's meetings with anyone in our company.What I want to do is find the FIRST time that a client has met with their coach. This would not be their first meeting ever; that would be too easy.Within the "clients" table, I have the "client" name, the "coach" name (and a ton of other data)Within the "appointments" table, I have the  "client" name, the "coach" name, the "calendar" the appointment was booked on (which would match the "coach"), and obviously the dates and times of appointments. I can use "min" formula to find the first appointment, but I'm struggling with the first appointment with the specific team member that is the "coach". Thank you! 

I am trying to create a tracker for trip expenses. I have a list of Attendees in one table, then Receipts in another with a "Charge To" field (since sometimes expenses wont be shared evenly across the group) that is a link to the Attendees table. What I would like to do is auto-calculate from the "Charge to" field on the Attendees table in a "Amount Owed" field.So basically I am looking for a way to look at "Charge To" see if the Attendee line is there, if so, get the the length of the number of attendees and divide by it and add it to the amount. I can add some columns on the Receipts table if I need to for the amount per person but the thing I cant figure out is how to check the multi select if the Attendee line is in it. Any suggestions?---- Tables Set up ----Attendees:Name (Single Line Text) | Total Owed (?) Receipts:Date (date) | Total (currency) | Charge To (multi select [Attendees::Name])

I hope this is the right place for this question, but we'll see!Context: We have a 'Tasks' table where each team member may have a list of hundreds of tasks (thanks to the joys of generating task templates). I would like to have a View for users that allows filtering out all child tasks until the parent task's 'Status' field reads "Complete". This would create a nice flow of the next steps for a project filtering into the view once a parent task has been resolved.The Tasks table has a linked field called "Blocked by:", which links to the same table. Here, users link a record (parent task) that must be completed before they can complete this task.Is there a way to 'hide' any records that have a linked record whose Status is NOT "complete"?  Then, once the linked record's Status is "Complete", by virtue of not meeting those conditions, the child task appears? 🤩Ideal situation - there are a few team members who use this table– it would be optimal if a single user

I am trying to generate sales goals in a formula, I have a column with the previous years result (manually entered), I have a % column with my growth target but I cannot figure out how to create a column with a formula that takes the previous result ($) and adds to growth target (%) to create a goal ($) 

Hello!I am creating a list of tasks that need due dates assigned using the following conditions:1. Each task is due one day after the previous task is due (note: dependencies would be nice to have but not necessary)2. The tasks are never due on Friday, Saturdays or Sundays 

Hi Everyone!I'm looking for some help editing this script from the marketplace.Right now, I can select 2 fields for the duplicate comparison but I actually need 3. How can I change this to have it compare 3 fields instead of 2?Also, this script has the comparison field where it will choose the record with based on this field. I actually need to have 2 different options for this field. Can this be done and how do I do that?Thanks for the help! let settings = input.config({    title: 'Delete duplicates',    description: `This script will delete duplicate records in a given table according to the value oftwo input fields. Duplicate records are detected when they contain the same cell value for each identifyingfield. For any two records that&nb

I have a table named Licenses with a Current? field [Yes|No].  The table is linked to the Employees table by the Employee # field. For each Employee, there could be a License record with:zero or many rows where Current? is Nozero or one row where Current? is YesI need a formula, which will I will use as a filter, that returns a Boolean if the Employee doesn't have a Current? = Yes row. Essentially, I need a list of Employees that don't have a current license.How should I approach this?

Hi all !To explain my project : I want to make a task manager where tasks can be dependant to each other (for example a task will start when the previous one end, and so if I change the end date of this previous task, the start date of the task will be udpated too)So I created my table like that :The previous task of a task is linked from the "Previous task" field. The "Start Date (Not first step)" is a lookup of the "End date" field from the link to "Previous task".My goal is that the "End date" field have a formula that verify if "Previous Task" is empty or not, if its empty that mean the task dont have previous task and so it calculate the end date from the "start date (first step)" that is entered manually. If "Previous Task" is not empty that mean the task have a previous task and so its start date is the end date of the previous task (and so, the end date need to be calculated from the "Start Date (Not first step)" field. Here is the formula I would like to add, and Airtable

My table is for students in a tutoring program.  One field is their current grade.  Another is the calculated formula of today's date.  Every August 15th (each year) I want each student's current grade to increase by 1.  Is there a way to do this? 

Hello,I am working on a property management system and there are contracts in two currencies. For all the data to make sense I need to convert the dllr contracts information to pesos with the current exchange rate. In the table where I have inflation data I have a column with the monthly rate when the contract was signed but I need to multiply for current exchange rate. I figured that manually making a column with current rate and coping the latest data to all rows gets the work done. I just want to know if there is a better way to do it?It would be much simpler to have a last() command where a formula finds last record in a table!!!!!Thanks,   

Hi there! This is a very newbie question, but is there a formula for cutting numbers out of a string? For example, if I have the value 01 Lorem ipsum and I want it to read Lorem ipsum what formula would I use?

I will preface with saying I have a hard time with functions. Not my brain thing. So, as much as I have searched for solutions I suspect my search terms weren't correct enough to find the answer. So I am hoping someone here can help.I need my email column in "table 1" to pull the email from "table 2" IF client is "who ever".Ex., in the client column of '"table 1" I enter Sally. I want the email column of 'table 1" to pull Sally's email from "table 2" and add it to the email column.  If it's Dave then pull Dave's email, etc... My client list is growing so more clients will be added. Can this be done without using "multiple select"?I hope I am clear in my explaining. Thanks in advance. 

hello, I'm trying to make a table where employees upload two dates through a form, when they go on vacation and when they return.So far so good, I was able to make a formula that counts the weekdays and these are subtracted from the number of available vacation days (vacations days left) . What I do not know is how to accumulate the days taken in the event if this person fills out the form againMaybe there is a simpler solution to this problem.Thank you

Hi,I'm trying to validate customer's mails, to send them automated mails.I created a column validating another Email column in my relevant table, but the formula shows me that the mail doesn't match the regex, even though I know it's a working mail.the formula is: IF((REGEX_MATCH({Email}, "[A-z0-9.+-_]+@[A-z0-9-]+\\.[A-z]+/g")),"Yes","No") I've basically took it from here:https://support.airtable.com/docs/guide-to-regex-functions  the example is this:besor23@gmail.com Can someone please assist?

Hi all, I figured out an easy workaround, but trying to understand why this is happening.I have three table that matter to this scenario - Employee, Certificate, and EmployeeCertificate (junction table)The Certificate table has a number field {Period of Validity} that records the period of validity of each certificate in months.The EmployeeCertificate table has the following fields:What is happening is that I have a lookup field in my EmployeeCertificate that looks up a number field in my Certificate table. The field it looks up is calledEmployeeCertificate is a junction table. It has one linked record field to my Employee table and another to the Certificate Employee (Linked Record)Certificate (Linked Record)Date of Certification (Date)Period of Validity (Lookup from Certificate table)Expiration Date (formula)The Expiration Date formula is DATEADD({Date of Certification}, {Period of Validity}, 'M')No matter the number in {Period of Validity}, that formula returns the same date as

Hello,I am experimenting with Airtable and Make, but I have a table with a Primary key which is a Formula field.I am trying to create a record in Airtable with this primary key as a formula, but the primary key is populated with NaN value.Isn't supposed Airtable to create this primary key even when I create a new record via Make?edit: I added some screenshot to show that inserting a new record via Airtable API is NOT updating the formula primary key------My second problem is that Upsert from Make in Airtable is not working, it constantly replies with "NOT FOUND", but Upsert is supposed to create the record if not found.Thank youCamillo

Hello team! I am trying to calculate the average weekly attendance in airtable. I downloaded the data from airtable and was able to calculate it in excel using an "averageif" function but I am not sure how I can implement it in airtable. The last column is the average weekly attendance I want to populate in airtable and I was able to do it in excel with this function: =AVERAGEIF(D:D,D2,E:E)The number of days a week we are open varies which is why we need a dynamic denominator to count how many days in a week we are open. Any and all help is greatly appreciated! Thanks so much 🙂

My brain is broken (or my formula is)… We have 4 departments (Production, Creative, Marketing, BD). When a prospective client is in our radar we run their product through a review process to get a sense from a variety of perspectives, the quality of their work. We submit the materials into an interface and the head of each department assigns a member from their team to the review. Once all 4 heads of assigned and marked their department ‘ready’ an automation kicks in and sends out an email… Sometimes there is 1 person assigned from each department… sometimes there are 2 or more… So my sheet has (for each department) 1 field for the user assignment 1 field that looks up the emails (and formats it in the manner expected “graham@email.com, joe@email.com” finally though, I have a formula feild that attempts to combine all those emails into one string of comma separated emails to that it can be inserted into the to: field of my outbound, automated email. Problem is… that doesnt work… When