Formulas

Is there a way to count the number of times a duplicate name appears in one table and display that number in another table? For example, Minnie Mouse’s name appears 3 times in the “Billing” table and I want to output the number 3 under a column for Minnie Mouse in the “Child” table. The way my data is set up is I have 2 tables, one for “Child” and another for “Billing”. The “Billing” table keeps track of all the client with the months we billed them for, their name will appear multiple times corresponding to the month he/she was billed. This duplication is how our biller kept track of the data, by having a Jan, Feb, Mar, etc tabs in excel and the client’s name being inputted in the corresponding month tab. The “Child” table has a different set of data that can’t be combined with “Billing”

Hi everyone. I’m trying to extract a string that occurs between the same left string, but the right string has 2 possibilities The bolded string is what I’d like to extract VAR. 1 - between > and > Industry > Banking & Finance > Trading (1.0) VAR. 2 - between > and ( Industry > Banking & Finance (1.0) Thanks a ton for your help. Subir

I’ve a linked field that I want to compare to a lookup field. If any of the linked records in the linked field appears in the Lookup field I want to generate an error of some kind. Linked field: Wishlist (Internal) (link to toy table) Lookup field: Toys (lookup toy table) Formula that I used: IF(FIND({Wishlist (Internal)},{Toys})<2, “ :white_check_mark: Wishlist updated​:white_check_mark:”, “ :stop_sign: Wishlist has given toys​:stop_sign:”) It works if there’s only 1 linked record in the “Wishlist (Internal)” But it doesn’t work for multiple records. Is there any other way to do this?

Hi there, I wanted to create a formula for a field with 15 variables. See table below. Basically, is % complete + status is what’s on the corresponding column, it updates the “new stat” field. Tried using this, but wonder if there’s a more efficient way than using ‘IF’ statement 15 times. IF(AND({% Complete}=0, Stat=‘red’), ‘red’, IF(AND({% Complete}=0,Stat=‘white’), ‘white’))

Hi! A while ago I asked for help to concatenate several values together (kids’ ages) and the following was suggested by Justin_Barrett, which I am using now, thank you Justin. SUBSTITUTE( TRIM({K1 age} & IF({K2 age}, " " & {K2 age}) & IF({K3 age}, " " & {K3 age}) & IF({K4 age}, " " & {K4 age}) & IF({K5 age}, " " & {K5 age})) , " ", ", " ) However, it doesn’t seem to allow for a “0” value. Can someone advise why and/or how to modify this formula to allow for the “0” value?

We are using a form to accept and manage participant registrations for various groups (we call them Circles) we are offering (and managing through Airtable). The participant registration feeds a Participant table to collect all registrants. Each registrant can select multiple groups. The groups are provided on and linked to another table on the base. Each group has a different maximum number of people that they can accept in their group. I’d like to create a formula to use that when the maximum number of registrants for that group has been met, it will automatically move the participant record from the accepted participant record type (column) in the Circles table (our group table) to a waitlist record type (column) for that group. Here’s the form --> https://airtable.com/shrVhQh9RC40kuqhj

I have an attachement link that has a bunch of attachments but every now and then one of them looks like the below, but works when clicked. It seems to usually be .pdf’s. Any way to avoid this?

I need some help editing my original formula which is: IF(IS_BEFORE({Actual Site Walk}, TODAY()), “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Forecasted Site Walk}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: In-progress​:running_man:”)) I’d like to add/change the formula to show “DUE SOON” for dates in the Forecasted Site Walk field that are within the next 14 days, but keep the rest as well. How do I do that? Thanks!

I am using the AND() formula to apply multiple filters through filterByFormula query in the API via Adalo. Each formula within the AND function works independently, but when combined, neither works. There is a discussion open on the Adalo forum with details: Adalo – 22 Jul 20 Filter (airtable populated) list by search term? @bgschust Try to change from double quotes “ to s

Hello! I am creating a workflow with 12 tasks for a client tour. 4 tasks are due 30 days before the tour, 4 tasks are due 7 days before the tour and 4 tasks are due 7 days after the tour. Each task is a checkbox field. I’d like to create a conditional format that shows the record is “red” if any of tasks go past their due date without being checked “complete”. Here is how I can do it: Create a new field called “All On Time?” Use a long IF statement with OR (check all 12 tasks) and AND (box is unchecked and deadline has passed) to check all criteria against their due date If any of the 12 tasks are not done on time, the field will display “Late”, otherwise it will display nothing Then I will use the Color formatting to make the record red if the new field says “Late”. If anyone has a more elegant solution, I’d love to hear it! Thanks, Ch

Hi everyone! I have been working on a general Airtable for a huge team that needs the same field to show a value called “what”. But every team calculates that value in a different way so I did a Nested IF formula that almost worked for every team but didn’t work for Content Demand Team :cry: Here is the formula I did: IF(Department="Illustration/Logos",{What}, IF(Department="Content Publishing", {What}, IF(Department="Mockups",{What}, IF(Department="Stock Photo", {What}, IF(Department="Content Operations", {What}, IF(Department="Mockups External Content",{What}, IF(Department="Logos External Content",{What}, IF(Department="Motion Graphics", IF({Motion To Publish}=1,({%Project}+{%Q&A}),(70+{%Q&A})), IF(Department="Graphic Design", IF(AND({Did SC}=1,{Did Assets}=1), AVERAGE({Assets Performance %}, {SC Performance %}), IF(AND({Did SC}=1,{Did Assets}=0),{SC Performance %}, IF(AND({Did SC}=0,{Did Assets}=1),{Assets Performance %}, IF(Depa

I’m trying to send multi-line text through an airtable button via webhook to zapier. Zapier is picking up the text as a single line with no line breaks. How would I send multi line text? The text that is being sent across is of inconsistent length, so some records will longer and have more lines than others. Any help greatly appreciated!

Hi, I’m working on a button field that will allow one of our users to send emails to our customers. Within this table, I have a field containing customer specific URLs that we want to use within the email. My question is whether there is a way to map the hyperlink to text within the email I am formatting using the button field. I’d rather not send our customers the entire URL, it would be nice to show them something like a hyperlinked “Click Here” or something along those lines. Hopefully this is enough to go on. Thank you for all the help.

I.m new to Airtable. I’m trying to concatenate the nickname if the field isn’t empty or the first name if no nick name then a space then the last name. It puts NaN in the field. Here is my code in a formula field: CONCATENATE(IF(nickname="",First,nickname)+" "+Last)

Hello! I am hoping someone can help me create a formula that will calculate the date on which a child turns 7 years old. I have listed their birthday and have figured out how to calculate the child’s age today, but am unsure of how to figure this other data point I need. What follows is a screenshot of the columns. Thank you so very much for your help! ScreenshotforAirtableQuestion|448x142

Hello! I am a programming coordinator at a library, managing programming for 3 different branches with multiple different rooms. I’m trying to create a formula to pull from a “room” field into another that will formulate the “address” for the building the room is in. Example: Room X, Room Y, and Z should put Address 1 in the formula column, but Room A and B are at Address 2, Room C is at Address 3. I am able to use an IF formula to get it to work for 1 room, but anytime I try and add others I get errors. Here’s what I am working with so far: IF(Room = “HPL Program Room”, “Address 1”, “Address 2”) This will give me Address 1 for the “HPL Program Room” records, and address 2 for anything that is not “HPL Program Room.” If I try adding other conditions, I get errors. How do I add the other circumstances to the same string/formula field?

Hello, I have a due date column and I would like to use a formula that adds “not due yet” in an adjacent column if the date is more than 6 weeks in the future. Any advice? #support:formulas

I’m trying to create an automation with Zapier by using a search formula upon a field in Airtable. What I can’t figure out is how to create a conditional formula that will search for strings containing certain information. For example, Based on input data from an Outlook Event input into Zapier, I want to search the subject of that event where the string value contains something that matches what is in Airtable. So, if the Subject is “Meetings | 100.1” how do I search for items matching “100.1” in Airtable? Thanks.

I have a base that contains records of artwork with record#s that contain letters. D for drawings, P for Paintings and S for sculptures. The prices for all the artwork is based on a formula derived from the dimensions of the art. Drawings and paintings have their specific formula to calculate value. At the moment I have two columns that show the price: one for the paintings with its specific formula and another one for the drawings with a formula of its own. I would love to create a single column with a formula with an IF statement that would apply one price formula if it finds a P in the record number and another one if it finds a D. Is it possible?. I don’t understand if it is possible to write a conditional to IF if it finds something. Any help or suggestions would be greatly appreciated.

I have two columns, one for USD price and EUR price. I want these prices to automatically be converted to DKK (Danish crowns), so: USD price*6.51 = DKK price or EUR price*7.44 = DKK price So, if there is a value in the USD price column, this value gets multiplied by 6.51 and the result is displayed in the DKK price column. If there is no value under USD, then it takes the EUR price instead. So far I have added this formula: IF(OR({Realized price (USD)}>0,{Realized price (EUR)}>0),{Realized price (USD)}*6.52,{Realized price (EUR)}*7.45) But this only takes into account USD price. If USD is empty it will return a value of 0 and disregard EUR price.

I’m trying to compare two fields. One contains certification requirements for a role and the other the certifications achieved by an individual. The certification requirements are contained in a lookup field. The certification achievements are contained in a linked records field. I tried an if statement which works if the items are in the exact same order in each field, but it fails if the order is not the same. This makes sense now that I think about it, but I’m not sure what my alternative is to determine whether an individual has all of the required certifications. Thanks in advance for a nudge in the right direction.

Airtable newbie here, if this question has a simple answer that I should be aware of please accept my apology and direct me to the right resource I’m trying a simple operation that I feel should be possible. How do I count number of records in multiple linked tables based on input values? I have a table called Projects. Projects can have a status based on a value from another linked table called Status (currently three different values are available in Status: planned, started, finished). Project are also assigned a region from another linked table called Region (currently: West, Central and East) I want, in a fourth table called Results, count the number of projects with a certain “status” AND “region” combination. Is this possible? I’m foreseing that I have Region and Status as columns in Result and then countif in Projects based on the value in those two coulms. Thanks a million

Hi! I’ve been using the below formula for awhile, but it returns an #ERROR! for the fields where {FOC (Current)} is blank. For cleaner reporting I’d like to have it return as “PENDING” but can’t seem to get an IF formula to work successfully. Any suggestions? DATETIME_FORMAT({FOC (Current)},‘YYYY, MM (MMMM)’)

I am building a CRM database. There is a table in my database called Deals that contains a multiple select field called Deal Stage. One of the options in that field is called Closed Won. Those are deals for which we have a signed term sheet and are going to provide a loan. I want to create a new table called Loans where I list details for each of the loans we have made to that subset of Deals for which we Closed Won. For the primary field in the Loan table, I would like to create a formula that looks up the name of the deals in the Deals table and only finds those ones that have the status Closed Won in the Deal Stage field. I would be so extremely grateful for help me with this. Thank you!!!

Hello wonderful Airtable community! I was wondering if anyone knew a way to take values in a lookup field and then provide the percentage of duplicate values? (e.g. for numerical values: “5,6,6,6,7” the output would be 20% for 5, 20% for 7, and 60% for 6) (and e.g. for string values: “Dentist, Doctor, Surgeon, Dentist, Doctor” the output would be 40% for dentist, 40% for doctor, and 20% for Surgeon) OR a way to see this in a chart if that’s easier Edit: figured it out. In the shared view for the base (so that the filter doesnt stay locked), just have to filter in the View I needed, and the chart will update accordingly.