Formulas

Hello all, This is my first time posting here! Apologies if the explanation is not the most succinct. I need help with creating a formula to work within an intricate email and list management system I've set up. I have two tables: Email --> Each record is an email being produced, tracking copy, design, and deploymentE.G. "Women's Board meeting 2 invite email" List management --> Each record corresponds to a named list of contacts from our CRM.E.G. "Women Board members" Both of these tables are linked to each otherEach record in the email table is linked to at least 1 list record, indicating the audience the email is going toEach record in the list table has 1 or more emails linked to it, indicating the past and future emails that have or will utilize this list's audience Fields

Hi all,I have created a table that houses outputs from ChatGPT; namely creating assessments. I am currently prompting the output to create an assessment section by section. Each section contains a number of questions, which as per the prompt, are referred to as 'Question 1', 'Question 2', 'Question 3'. I want to extract each question and populate them as new records so that I can create marking guides and model answers for them. What would be the formula I could use or script I could run to extract each question from the string? See an example of a generated section below:-----------------------------------------------------------------------------------------------------------------------------------------Total Marks: 15Instructions: Please read each question and select the best possible answer from the options provided.Question 1 (3 marks): Which of the following laws in South Afric

I need a formula for the column on the right that writes a number 1 to the first result on the left, a 2 to the second result, etc.I don't need an autonumber, this is because when the left column changes the numbers, the autonumber stays the same. Is there any way? 

I'm trying to set up an automated email to send clients after their first booking with my business. What formula can I use to identify if the record (booking) is the first one for that client (linked record from another table)?I don't want to manually check a box to trigger the automation, as that means first manually checking if it is their first booking, and that defeats the purpose.All I can think of is something like;IF Booking Count = 0 at time of Record Created, then = YESAny ideas on how this can be done? Let me know if you need more information.

This will be a long post, and a intricate task because Airtable somehow in 2023 doesn't have a "date is current month" type of filters.I want to in my interface dashboard to summarize the total sales value within the current month. And in a statistics summary show the total sales pr month.The rightmost box is is currently filtering the sales done within a month ago, which is not the correct setup.When it's april, that one should only display April sales, not anything from March.I have already a formula field that declares the date the sale was done, which is the field called "Salg".What i need the formula to do is to return "1" or "true" if it's this month.And then i wil

I am quite new to airtable, i am trying to create a formula that says if an end date exists THEN subtract today's date from the end date column ELSE calculate days remaining in the month.I have checked through various date formulas on the community and i do not find a fomrula that directly calculates days remaining between today and month end if there is no value in the field 'end date'.Can someone help please?

Hi guys need help with this formula if possible, basically the field 'total Pie' needs to return 3 types of values, but if I don;t add the blank condition blank fields default to 'high'How can I insert the blank statement in?IF(AND({Total PIE}=BLANK(),BLANK(), if({Total PIE}<=3, "low", if({Total PIE}>=6,"high", "medium"))

I'm putting the below formula but it keeps coming up with an error message - any ideas why when each part seems to work independently.  IF({Always On/Miniseries} = "Always On" , {Total Budget (USD)}*((13-{Number (from Start Date)})/12) ), IF({Always On/Miniseries} = "Mini Series", {Total Budget (USD)}*{Year 1 (from Mini Series Amort) (from Start Date)})

New here!Is it possible to set a different formula in each row of a column? For example, I am looking to set a due date in one of our rows (which is one of our tasks) to automatically calculate a date 2 months before a start date of a program. The second row, is another task and has another due date (7 days before the start date). The start date is the same for both. Is this possible? Thanks!

Hello all,I'm struggling to edit the text in a field referencing an attachment field. I want it to extract a label code from the attachment name. The text looks like…GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/c3a3a85e047021c86d494d90be7dcb64/08f0ad8f/GAR0027SouthernVinesCabSauv15F.pdf), GAR0028 Southern Vines Cab Sauv 15 B.pdf (https://dl.airtable.com/.attachments/034388155febbaac0b80a222437f97f5/3a723729/GAR0028SouthernVinesCabSauv15B.pdf), GAR0027 Southern Vines Cab Sauv 15 F.pdf (

I'm thinking I can use a 'formula' field to do this task. Can anyone help with the 'Formula' field... or is there a better suggestion. Thx.IFFirst 4 characters of a record in {Unit & Week} = First 4 characters of a record in {Unit Name} AND {2023 Fee Paid} is Checked, insert the amount in {2023 Maint. Fees} field.Association Table{Unit & Week} is not the primary field{2023 Fee Paid} is a CheckboxMaint Fees Table{Unit Name}{2023 Maint. Fees}

My custom personal CRM is composed of 3 tables: People, Organizations, Interactions.  People belong to one or more organization.  Interactions are individual events that could include multiple people.  Organizations has a linked field to People.What I want to do is show when the last time I interacted with each organization on the Organization table.  I figured out how to show the last time I interacted with a person using a Rollup with the Aggregation formula of MAX(values).  Now how do I go through and aggregate each persons last interaction to show the latest for the organization?Thanks, Jon

Hoping someone can assist me in creating a formula. Basically I want to take a field that has a numeric value in it and convert it to CM or if there is no number in that field output the text that is in it. e.g.Field A contains value: 60 or contains value "not applicable".Using a formula field I have {Field A} & " in, " & ROUND(VALUE({Field A}) * 2.54) & ' cm'. The output would be "60 in, 152, cm" if it just contained numbers. If that field instead contained text and no numbers I want to just output the text. It won't always be "not applicable" that's just an example. So I figure I might have to use the search() function with regex in the formula with

Hello everyone,I'm fairly new to Airtable and I've been impressed with the level of questions the community has solved.  This is the first time I'm posting a question with the hope someone can help me.  Here is my situation:In Table A, I have 3 single-select fields called Trade Choice 1, Trade Choice 2 and Trade Choice 3. Each record (applicant) has a Province of Residence.  I have a lookup field to Table B that lists each trade with the specific provinces where the trade is a Red Seal trade in a multiple-select field called Red Seal Province.I want to add a verification step to confirm when the choices of jobs are entered that they are a Red Seal Trade in the province of residence of the applicant. I would ultimately use condition colour to highlight any choices where the trades don't exist as a Red Seal in the province of residence.

I have a campaign work back schedule. I need to calculate four date fields based on the Launch Date:Assign Due DateDeveloper Due DateQA Due DateApproval Due DateLaunch DateThe number of days for each due date varies based on the combination of two single-select fields:{Channel}: Email, Push, Banner{Send Size}: Large, SmallThe logic is:If Email (Channel) and Large (Send size) with Launch Date 6/1/23{Assign Due Date} = 6/1, -12 days {Developer Due Date} = 6/1, -7 days{QA Due Date} = 6/1, -6 days{Approval Due Date} = 6/1, -7 days {Launch Date} = 6/1If Push (Channel) and Small (Send size) with Launch Date 6/1/23{Assign Due Date} = 6/1, -5 days {Developer Due Date} = 6/1, -2 days{QA Due Date} = 6/1, -1 days{Approval Due Date} = DELETE (or show NA){Launch Date} = 6/1

Hello! Can someone help me create a formula that finds the first wednesday after the 10th of the month? I have 1 date field that I manually give a date value (eg. 1/4/23), and then I have a formula field that should find the first wednesday after the 10th of the month. Is that possible?

Looking for some help. I have a table were I utilize "last modified time" to be inputed based on the Specific field ("Checkbox").This sequence works well for what I am doing, which is; creating time logs for when we "Start" and "Stop" work on a specific task. I then have a formula set up "Datetime_Diff" where it grabs the difference between the two in minutes (set up as integer) so that I can log billable hrs towards a project (Time on Task).   Here is my problem... I have another table "billing" that I want to utilize a "rollup" field for, to grab the SUM of that data in my "Time on Task" field and have it contribute to a specific job number. I am utilizing this exact same method for and "expense" field but the field the rollup is connected too on that method is a "currency" field (which I cannot use on my time tacking). I am sure there is a solution but I am not an expert.. yet! Any help would be

Hello everyone! I have this formula:  IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Dato for udløb}, "D")<10), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),3-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))>3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),10-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"))))) Currently when the field 'Trækdato (F

Inside my client management system, I have three columns:Client status - Active or completed [this changes automatically when client reaches a certain amount of weeks]Weeks - How many weeks they have been inside the program [this has a formula]Start date - when they began the programMy program was 4 months and then I extended it to 6 months.Here is the formula inside the column 'Weeks' that works for 4 months: IF({Days Since Joined}<7,"Week 1", IF({Days Since Joined}<14,"Week 2", IF({Days Since Joined}<21,"Week 3", IF({Days Since Joined}<

Hey, Is there a formula that pics the name of the month by date. We have built a system where we manage our project invoicing. We send our customers an invoice on a monthly basis based on the work completed, but we try to forecast the billing months by recording the assumed billing month for the project part. So, Airtable should understand that the (billing) month is a date field (billing predictability), but the option value should be the name of the month Thanks Mikko

Hi! I am looking for to have 2 different conditions in one formula. I believe I should be using IF OR but I must be missing something. Here are the formulas:IF(DATETIME_DIFF(TODAY(), {Finish WK 17-24 (Progression)}, 'days')<0, BLANK(), IF( {Start WK 17-24 (Progression)}=BLANK(), BLANK(), IF(DATETIME_DIFF({Finish WK 17-24 (Progression)}, {Re-Entry: Start WK 17-24},

Hi everyone! I need your help with a problem I still haven't managed to solve on Airtable I'm creating an online store where the cart is generated by answering a diagnostic/questionnaire.Each answer to the questions assigns tags (words from the "single select" menu) to the people who answer.Some tags may appear several times.I want to create a program that allows me to count the number of times a tag appears in one cell and rank them from the one that appears the most to the one that appears the least.Then I would like to be able to search, in the "product" table, those that correspond the most to the first 5 tags of the ranking.Thus, I will give the best product to the people answering the questionnaire.(If you also know how to use the TF-IDF (Term Frequency - Inverse Document Frequency) in my case, it would help me a lot)I thank in

Hi, I have 2 tables: Table 1 has a list of contractors and a field showing the value of the projects they've gotten. On Table 2 I have a field showing the contractor with the max value that auto updates as other contractors acquire projects with higher values but I can't figure out how to match the contractor to the value in Table 2. I tried concatenating the 2 fields in Table 1 but then I couldn't figure out how to get that data to populate in Table 2. Thanks!

Hello, I am using airtable to keep track of inventory for my Short Term Rental.I have an "Inventory" table + an "Update Inventory" table.When my cleaner is counting inventory in my STR she'll click on the "Update Inventory" button on my inventory table and a form pops up for her to enter the quantity of an item.After that's done, It'll show the data entry in the "Inventory History" column, as well as all past data entries.How can I show the quantity from the LATEST data entry show in the "On Hand Items" column?

Good evening! I'm creating a scorecard for golfers, everything is going well except, I can't figure out how to calculate their +/- score. I have the total score but I don't know how to add the static numbers of par to each hole so I can formulate the calculation to get the +/-.