Formulas

Hi all, I'm looking at creating a Button on a base which creates a draft email to send to a client. Within the body of the drafted email, I want to include a unique Calendly Link hidden beneath a "click here" button on the drafted email. I've got so far with it and the formula is correctly drafting the email, and body, but the link stops at the 'clickable' link part. Here's the part of the formula that works;"mailto:" &{Email} &"?subject="&ENCODE_URL_COMPONENT("Book a Meeting")&"&body=" & ENCODE_URL_COMPONENT("Hi "& {First Name}&",\n\n I hope you're keeping well...")&  The next part is the bit that stops working;ENCODE_URL_COMPONENT("<a href="&{Calendly URL}&">click here </a> to book a meeting\n") Any help would be appreciated, thanks in advance!

I received some contact info (Excel format) from another source that I'd love to incorporate into my Airtable. It's over 1000 records. There are two columns. The first column is fine as is.The 2nd column lists a person's name and email in this format: First name Last name (email address)It's complicated by the fact that many of the fields in that column have 2 or sometimes 3 people, so it's listed like this within the field:  First name Last Name (Email), First name Last name (Email)And sometimes even 3 people, like this: First name Last Name (Email), First name Last name (Email), First name Last name (Email)Help!!! Thank you!!!!-Amy  

I am trying to figure out how to create a field that will update every time the "CLIENT STATUS" field is changed and maybe another field that says how many days that record has been in that status. Any ideas? Thanks!

Hello all, I'm working with formulas, but got stuck on something. What I'm trying to accomplish here is simple, but not sure how to formulate it. I have a linking record for an invoice for NET Terms: NET15, 30 etc. I have the formula set up, but I want that converted into a date format rather than what I have.  IF({TERM}="NET15", "15 Days from 'Due Date'", IF( {TERM}="NET30", "30 days from 'Due Date'" ) )  Simple, but this is the output:Pretty simple, however I was testing the formula, and for invoice structure - I wanted a date format approach. So If NET15, then have the "Due Date" 15 days BASED off of the "Date" field. I don't know how I'd structure that, but that's the primary goal. Instead of showcasing "15 days from 'Due Date'", I'd rather it show a date format. EX: Date: 7/9/2023 Due Date: 7/24/2023 TERM: NET15Any advice would be helpful! Thanks. 

Good afternoon! There are several product sections, each product has its own identification number. How to make the id is filled in automatically in a row (example P00005) and you need to save the number of characters (example, P00009. P00010)

Hi everyone! Excel offers this ability so I'm wondering if there's a similar formula I can use to achieve this here? I want the "Link" column to be populated with a URL but the ID at the end of the URL needs to be populated with the "Number" column.Bonus points if you can show me how to hide the URL string behind the same name of the number column as well.

I'm ok at writing formulas, but I always need to look up stuff, and it takes me a while to do basic things as it doesn't come naturally. Which means when I go back to an old formula, it takes me quite a while to understand how it works. I know in other forms of coding, you can add notes that don't affect the code - i.e. to help another programmer understand what something is doing. Is there a way to do that in the formula fields?

Hi I'm trying to use a nested formula to calculate a risk matrix if an entry has different conditions: F({ Platform product data sources (from Submissions)} =Platform Product/Data, OR ({Subproccessor (from Submissions)}=Subprocessor), ‘CRITICAL’,IF ({ Platform product data sources (from Submissions)} =HRIS (People data), "MEDIUM",IF({Platform product data sources (from Submissions)} =Commercial and Financial platforms/data, 'HIGH','low’)))But I'm getting a formula error, I'd appreciate any help on where I'm going wrong! Thanks! 

I use the primary field to display the content of another field (single select)In the primary field, I have a formula that is the other field name (ML ID)When I add content to ML ID  it saves and displays ok. However, it doesn't show up in the primary field. The only way I can get it to display in the primary field is to delete it in ML ID and then select it again, whereupon the primary field displays it.I attach screenshots14.51.48 shows the field ML ID in which I've added abc14.52.59 I've removed the abc (by selecting the blank or empty) shows the primary field which displays (for no apparent reason) --14.53.20 shows the primary field and ML ID field as it should beAny suggestions, please? 

I have a multi-select field of colors.  I have some records that have the same color listed mulitple times (i.e., yellow).  I would like to remove the duplicates.  I have tried the work-around in "Rollup Array Unique?" (make the field a lookup field and then use arrayunique(lookup field)), but I still get duplicates.  Any other suggestions of how to get rid of the dupes?

Hi all!I was wondering why ARRAYJOIN(values, "\n") is not working in my base.The only way I have been able to achieve line breaks is by creating a formula field.I added some screenshots!Thanks!

Hello!So i was wondering if it was possible to create a formula (or combine formulas) in order to use the data from one date field and add 7 days to it in another. and then filter it if the project status matches certain conditions.I got the first part to work using: DATEADD({Project Date}, 7, "days") which worked perfectly,  but im not sure if its possible to then have it say "not needed" or even have it be blank if the project type matches certain conditions and still show the date if it matches other conditions! I also tried this:IF(OR({Project Type} = "Wedding",{Project Type} = "Elopement"),DATEADD({Project Date}, 7, "days"),IF(AND(OR({Project Type} = "Couples",{Project Type} = "Family"),"Not Needed","Not Needed"))) which kinda worked but it only filled in the wedding date status and not of the other options. Let me know if that makes sense and if you can help please 🙂

Hello,I have a formula column where I dynamically build a string of characters. Everything is fine to build the new string.However, when I copy and paste (Command-C) the cell, I end up with a character " added at the beginning and end of the new string but also, by consequence, in the middle of my content if it detects a " character.It automatically doubles it! GrrrHow can I eliminate this problem? Thanks for your help. I am getting crazy.

I've HTML Code to use inside formula to insert virables inside the HTML code then export it as final HTML Code.. the problem is when i tried to copy the field it add some extra character (")...Here's the formula code:"<div style='position: fixed; top: 0; left: 0; width: 50%; height: 100%; opacity: 0.6; z-index: -1; background-image: url(\\"https://image-cdn-p.azureedge.net/offer-image/ARABGG/202304022245344534.png\\"); background-repeat: no-repeat; background-position:left;background-size:100% 100%;'></div>" & "<div style='position: fixed; top: 0; right: 0; width: 50%; height: 100%; opacity: 0.6; z-index:-1;background-image:url(\\"https://image-cdn-p.azureedge.net/offer-image/ARABGG/20230402224407447.png\\");background-repeat:no-repeat;background-position:right;background-size:100%100%;'></div>" & "<div style='padding :5px;background :rgba(255,255,255,0.1);border-radius :5px;\\r\nbackdrop-filter :blur(5px);box-shadow :0 2px3px rgba(0,0,0,0.1);'>" &

What could one do to include data validation in the conditional logic; e.g.: I am creating an internship application form and I want the applicant to select out of the list of employers. Once done, they can select the relevant internship position, nothing else. And also, how could we include the employer's email in it so that each entry is also sent to the respective employer?

HiIs it possible to use a single select field to populate the options of a multi-select field that is in a separate table?Thank you for your help,Collin 

I’ve got a bit of a puzzle which needs cracking here. I have a table in which each record is used to track jobs in my business. I have a Single Select field which tracks the job status of each record. Job status examples: Job Accepted > Worker > Reviewer > Manager > Complete I need to record the amount of time that a record spends in each status. I’m looking for something like this: Formula field - Time spent with Worker: 100 minutes Formula field - Time spent with Reviewer: 25 minutes Formula field - Time spent with Manager: 15 minutes The catch: records will sometimes move backwards and forwards through the statuses. i.e., record status will go from Worker > Reviewer > Worker > Reviewer > Manager. I can’t figure out how to make this happen (or if it is even possible). Can anyone think of a way to do this? I’d love to hear an alternate approach if there is one!

I have a "Person" table that has birth date. On another table, I lookup that birthdate to find the person's current age when "Event" happened. Next year, a new "Event" will have different age for the person (as they are a year older). But I need to know what age they were at each "Event"So how do I create a column of ages that are dynamic on entry, but don't change once they are entered?

IF(AND({State (from Government Body)} = 'Iowa',{Public Records Received (Yes/No)}= 'No',{Proof of Service (Differential - Proof of Service+(x)Days)}),IF({Proof of Service (Differential - Proof of Service+(x)Days)} <= 20,"Pending",IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)} > 20,{Proof of Service (Differential - Proof of Service+(x)Days)} <= 50),"Delinquent",IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)} > 50,{Proof of Service (Differential - Proof of Service+(x)Days)} < 60),"Complaint Submitted to Iowa Public Information Board",IF({Proof of Service (Differential - Proof of Service+(x)Days)} > 60,"Government Body Failure")))),IF(OR({Public Records Received (Yes/No)},{Page Count} = 0;"Completed"))) I am teaching myself and/or conditions for a status formula and I am requesting assistance with adding an or condition to the "Completed" section of the code.  The rest functions as desired.  

I have a table that tracks subscriptions for a specific product add-on. It tracks subscription movement for this product based on the unique account business id. Every time a user subscribes, upgrades, downgrades, or churns it adds a record with the new quota, business id, date of upgrade, month of upgrade, and the expansion or contraction MRR. A churn is recorded very similarly to a downgrade or contraction record so I'm trying to come up with a way to tell the difference for reporting purposes.  Basically if the business id doesn't occur in the list again, has a 0 quota, and a negative MRR then it's a churn. If it does occur again but has a 0 quota and negative MRR then it is contraction and not a churn.I'd like to add a formula field in Airtable that based on the instance of the business id it labels the record with either New subscription (if it's the first instance of the business id in the table), expansion (if it's after the first instance of the business id AND h

Hi there, thanks for taking the time to read my question! New to Airtable and it's formulas - what I'm trying to do is create a simple Hours of Operation setup where a user can select the days of the week a business is open (stored in Open Days), add the open hours for each day, then combine the values to show the complete hours of operation (combined in Hours of Operation field). I'm starting with just Monday and Tuesday and I don't seem to be able to put two IF formulas within the same record. Only the Monday IF/FIND formula works and not the Tuesday. If I delete the Monday portion and leave the Tuesday, then the Tuesday portion works. That's what makes me think that you can only have a single IF formula (as well as nested IFs) and not more than one. I included screenshots and my formulas. If there's a better way of doing this I'm definitely open to that.Formula in "Hours of Operation" field:IF( FIND("Monday",{Open Days},0)=1,"Monday "&{M-Start}&" - "&{M-End}, "Mond

Hi, I need to have three fields that have parts of a phone number in each field. For example, if the phone number is (123)456-7890, I need three fields that return:field1: 123field2: 456field3: 7890Can anyone help me with a formula that would accomplish this? Thank you!

Hello! I'm new to Airtable and trying to create a formula field based on a date field. The formula should add 1 year to the date (the easy part), then round up to the half year beginning January 1 or July 1. For example,  5/15/23 would calculate 7/1/24; and 10/2/23 would calculate 1/1/25. Any and all help is appreciated!

Hey Everyone, Trying to create a formula that counts how many records in a date field are todays date, let me know if you know how to do this.I am totally fine if let's say there are 10 records and 4 of them are todays date and the formula counting the ones with todays date just says "4" next to all of the ones that are today - I don't need it to be like chronological as in the first one saying "1" the second one saying "2", etc. Thank you for any help, greatly appreciate it!

Hi! I am looking for a formula to calculate the school year. I have a date field that I want to use. The formula I thought would work is not working. Please see below: IF(AND(Date>“08/15/2021”, Date<“06/30/2022”), “2021-22 School Year”, IF(AND(Date>“8/15/2022”, Date<“6/30/2023”), “2022-23 School Year”, IF(AND(Date>“08/15/2023”, Date<“06/30/2024”), “2023-24 School Year”, IF(AND(Date>“08/15/2024”, Date<“06/30/2025”), “2024-25 School Year”, IF(AND(Date>“08/15/2025”, Date<“06/30/2026”), “2025-26 School Year”)))))