Formulas

Hi Am fine with IF(FIND) formula when I want different outcomes but not sure how to set up when I want all outcomes to be the same. I know the outcome from AND/OR will be 1 or 0 which I can then work from but need help with how to write the actual formula! Below is my template formula I use to remember the IF(FIND) formula and adapt to whatever I need - if it helps to show me what I need to add where please use IF(FIND(“search#word”, {search#field}), “the#result”) Any help really appreciated have been searching but not finding yet. Thank you

Hello all. I want to do a simple sum on a group by. In my example, I am building a recipe calculator and want to find out the proportion of cost for each ingredient out of the total cost. My formula would be : cost/sum(cost group by recipe) In sql this is call a window function but I am not sure if there is the equivalent in Airtable. I have a screenshot, but seems like I cannot upload it. Must be because I’m a new in the community. Anyway let’s say i have recipe_a ingredient_a $3 recipe_a ingredient_b $6 recipe_a ingredient_c $4 recipe_a ingredient_b $2 I want a new column that would output that ingredient_b stands for 1/3 of the cost of recipe_a A workaround is to do a pivot table but it seems like a lot of trouble for something simple (plus not a very scalable solution) If you have any input, I’ll gladly take some. Thank you Stay safe !

Hi everyone, I have a table where user X can endorse user Y on a particular skill Z. The final table will be something like this: John | Carol | Painting Carol | John | Drawing Fred | John | Drawing Bill | John | Writing Then I will have a separate table with user profiles, and I would like John’s profile to display “Drawing (+2), Writing (+1)”. The way I would achieve this using a pure SQL mentality would be to group by name, skill, do a count, and then JOIN it with my user profiles table. The way I’m trying to achieve it with Airtable is to create a new column on every row with the value {Name} & “(+” & something & “)”. This would essentially be doing a row-level PARTITION and GROUP BY COUNT, and would allow me then to link to these records from the Profiles table and select the unique. In this case, we would have repeated Drawing (+2), Drawing (+2) and Writing (+1) for John, and the desired resu

My goal is to have a DOB field concatenate with a name and calculated age field. Currently the concatenate works fine with CONCATENATE(Child_First,", “,Gender,”, ",Age) = Rohnan, Boy, 10M When I add DOB field I get Rohnan, Boy, 10M, 2020-12-15T00:00:00.000Z How do I get rid of the T00:00:00.000Z at the end? When I use the Date_time format I get gibberish. CONCATENATE(Child_First,", “,Gender,”, “,Age,”, ",DATETIME_FORMAT(DOB,‘dd-mmm-yyyy’)) = Rohnan Boy 10M Tu-000-yyyy What I want to see is: Rohnan Boy 10M 15-Dec-2020

Hello, I’m writing with a question on how to set up a REGEX Extract. I assume it should be relatively straightforward but as a beginner I’m finding myself out of my depth. Any help would be appreciated! Goal: Extract a SKU number from a Webflow Ecommerce string. String Example: count: 1 height: 0 length: 0 productId: 618573ee195d39c0e1ba4199 productName: Test Board productSlug: test-board rowTotal: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantId: 618573ee82825fd59dc4b5bc variantImage: {‘fileId’: ‘6181a0f79b29435921241715’, ‘url’: ‘https://uploads-ssl.webflow.com/618058264546c2bae56f2188/6181a0f79b29435921241715_Screen%20Shot%202021-10-14%20at%208.46.20%20PM.png’, ‘alt’: None} variantName: Test Board variantPrice: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantS

Hi! I’m looking for help writing a formula. I need something that will automatically check off boxes in separate fields for 2/9 through 2/21 if individuals will be present based on the entered arrival/ departure dates. “if x date is between the range of arrival date and departure date, insert checkbox, otherwise blank” For example if the arrival date is 2/12/21 and departure is 2/20/21, I’d want it to check off all of the dates in between, but not 2/9-11 or anything after 2/20

I’m trying to put together a form for screening, however I cannot figure out a way to create a formula that would enable me to give an automatic output based on the form response. In short, if someone writes a symptom I want the last column to output a false, or a fail message. I have tried doing this but haven’t been able to. Here you can find a look into the form: And a formula I put together to try to get an automatic output:

I would like to create a formula that will: Sum the {Amt} fields only from records that have the identical data in another field {ticket/voucher #}. For example: Records 1, 7 and 14 have the identical data in {Ticket/Voucher #}. The formula would sum the amounts only from those records 1717.40 980.00 250.00 and put the total 487.40 in {Formula field}. The same would go for records 10, 12 and 15 and 11, 16 and 13. See link to image of my table. https://hctny-my.sharepoint.com/:i:/g/personal/office_highclasstravel_com/EesBZqFYxQpIvxnKzbAVLu8Bs6pO4BEkdNQWgzHui4Gj_g?e=4zSeTF

I’m using Eazyform app to get time slots separate from the date/time field. I am having trouble then figuring out what formula to use to calculate total hours for employee scheduling. Additionally I would like to be able to view in a Calendar view.

This ought to be easy. I want to display records on a calendar by a single date field, but the date field for display is calculated: It’s the DueDate if there is one entered, otherwise it’s CreatedDate. IF( DueDate=BLANK(), CreatedDate, DueDate ) Dates in, dates out, right? Nope. A formula field using the above formula cannot be used in the calendar, apparently because the result of the formula is not recognized as a date. Now the following DOES work: DATETIME_PARSE( DATETIME_FORMAT( IF(DueDate=BLANK(),CreatedDate,DueDate),"MM/DD/YYYY" ),"MM/DD/YYYY" ) Seriously? Is there not an easier way to do this?

I’m using a button with “Gmail” & ENCODE_URL_COMPONENT({Email}) & “&su=” & ENCODE_URL_COMPONENT({Subject}) & “&body=” & ENCODE_URL_COMPONENT({Body}) to send an email through Gmail it it works great, but how do I attach a file from an attachment field on the record? I really like the control of a button. I’d rather not use an automation. Thanks in advance!

Hi, I’m using Airtable to manage a conference. I have a table which has conference proposals with the authors’ contact info. Then I have another table with people that have subscribed to the conference. I’m trying to send emails to those that sent proposals in but haven’t subscribed. So I’m trying to accomplish that by comparing the emails from the Subscribed table with the ones in the Proposals table. These are the steps I followed so far: I linked the 2 tables and added a lookup field to the Proposals table to look for the emails of the Registered participants. In the Proposals table, the contact info is spreaded out in different columns, so I used CONCATENATE() to join all the emails with a separator. I realize that the lookup field returns an array while the CONCATENATE() function returns a string. Here is the tricky part. I’ve been trying to compare both groups of emails and it hasn’t worked. I need something that loo

I want to use a rollup to get an average of the existing numbers, but not all the fields in the column I want an average from have an existing number and return NaN. I want the rollup to ignore NaN.

Hi all, We have a table with records per customer, with multiple variables and their scores coming from typeform (auto-sync). The goal is to get out the 3 highest scoring variables and send the label of those variables to the customer. I can’t seem to find a way to get inside 3 fields the 3 column-names with the highest value of that record. Another solution I was thinking of, was when the auto-sync worked, to store the variables in different rows in another table but also with this I see no reason than to get the max 3 variables out of this table linked to the original one. Help would be much appreciated, thanks!

Here is what my formula looks like. I can not get Airtable to accept it. Can you say why? Current formula IF( ({Sales Rep}!="" ), AND( NOW()>={Followup Date}), “Delay Notification”) Proposed formula IF( OR( AND( {Sales Rep}!=BLANK(), {Followup Date}, NOW()>={Followup Date} ), AND( {Sales Rep}!=BLANK(), {Followup Date}=BLANK() ) ), “Delay Notification”, “Send Notification” )

Hey fellow AT users, I’m trying to set up a formula that calculates the renewal date of subscriptions based on if they are Monthly or Annual. I currently have 3 columns that I’m working with: Frequency, Billing Start Date, and Billing Renewal Date (which is where my formula is). I was testing the following formula: IF( {Billing Start Date}, IF( IS_BEFORE({Billing Start Date}, TODAY()), DATEADD({Billing Start Date}, 1, SWITCH({Frequency}, "/mo.", 'month', "/yr.", 'year')) ) ) The issue is that while it DOES show the next date based on the Frequency (i.e /mo. or /yr.), it’s not current. For example, a Billing Start Date of August 29, 2020, that’s set to renew monthly, is showing as September 28, 2020 instead of October 28, 2021 (since it’s currently 2021 and September is past). Does anyone know how I can get this to work? Do I need

The formula is simple in that I want field A multiplied by field B to = field C What happens is because field B is rounded due to the limit I put in the field because I just want two places after the decimal, it is calculating based upon the original math and not what the cell shows. For example. 51 x 68.33 = 3,484.83 but the cell shows 3,848.70 bc of the original amount being 68.3275 How do I get the field B to use the actual data in the cell instead of the original so that if someone not in the know can see that 51 * 68.33 = 3,484.83 Thank you in advance!

Hi Airtable Community! I’m developing a base to manage a bike polo championship. So far so good, I could implement all the function that are required, but I’m stumbling on a formula that I can’t get to work. On this table I want to get the final score for both Teams. As for shown with the example on the last column which it was achieved with a Count field and a filter with the name of the Team ‘Test1’. That is not a solution for me, because what I want is to filter the count based on the name of the column ‘Team 1’ and not on a particular team name. I’ve tried to combine IF () with FIND(), IF(FIND({Team 1},{Which team scored?}),1,0), but then I get only a 1 or a 0. Does anyone have any idea on how I can then count the frequency of the string in the column ‘Team 1’ and than count it or Sum it? I’ve tried to add SUM() or COUNTA() in the formula

Hello! My use-case may be unique, as I am project managing a movie theater. I have a Calendar view of all confirmed films by Date Range defined by a “Start Date” and “End Date.” Some films are on runs (typically Friday-Thursday ie Start Date 10/29/21, End Date 11/4/21), and others are single events (ie Start Date 10/29/21, End Date 10/29/21). I have written a formula to categorize films by status (“Coming Soon”, “On Screen”, “Closed”) as follows: IF( IS_AFTER( {Start Date}, TODAY() ), "Coming Soon", IF( IS_AFTER( {End Date}, TODAY() ), "On Screen", "Closed" ) ) My issue is that single events will appear as “Closed” on the day they open (duh). This status is easily fixed by setting the End Date to the day after it opens, however this yields two-day-long entries for single events on the graphical calendar, which is obviously misleading for our staff. Any recommendations for rethinking my formula (an IS_SAME function?)

Hello team, am facing an issue in getting multiple columns values from a table and store in different table with multiple columns For eg: In a legal system , we have an one case number and case was failed by the party. so again the party appeal in high court for the same issue. so different case number issued at high-count so if new appeal is happening on this case, then if user select the old case number then it will auto fill to the columns fields with the previous values, means if old case number is 200/2020 and other field like case subject, claim amount, etc once new case comes with new number (201/20221) when goes to appeal, if user select the old case number as 200/2020 it will select or pull all the fields such as case subject, claim amount etc

I am wondering if anybody knows a way to change the way the field for date created is displayed in concatenate? This formula reveals the date as follows: 2021-10-29T20:28:27.000Z - Task Detail - Task Assigned to - Projects I am looking for something a little cleaner like: 2021-10-29 - Task Detail - Task Assigned to - Projects IF(Projects = BLANK(),“No Project”,CONCATENATE("",{Date created}," - “,{Task Detail},” - “,{Task Assigned to},” - ", Projects)) cheers!

So I have a base that has a lot of images as a part of it. I’m trying to have it so that in Table-3 if Option-A is selected from Table-2 then attachments from Column-A on Table-1 are displayed but if Option-B is selected then attachments from Column-B on Table-1 are displayed instead. However, I have tried everything I can think of to make this work and I can’t. I’ve tried multiple look-ups, formulas, everything I know how to do. I can not get a single column that displays the attachments and switches according to the selected option. Help, please.

Hello, What would be the easiest way to configure a formula that combines all of these? IF({Request Type}=‘New’, DATETIME_DIFF({DCML Issue Date Lookup},RequestDate,‘days’) IF({Request Type}=‘Major Update (Procedure Change)’, DATETIME_DIFF({DCML Rev Date Lookup},RequestDate,‘days’) IF({Request Type}=‘Minor Update (Formatting, Spelling, Grammar, etc.)’,DATETIME_DIFF({DCML NCC Date},RequestDate,‘days’) IF({Status}=‘Incomplete / Closed’, DATETIME_DIFF({Status Last Modified Date},RequestDate,‘days’) I tried a formula that combined the first two, and the system accepts it, but it doesn’t return anything. Thanks :slightly_smiling_face:

I have a field that includes a number range for each record: “5-10”, “15-20”, etc. On another table, I would like to do a rollup that adds together all the first and last numbers in those ranges, displaying “20-30” essentially. Is there a rollup formula that can find the first and last numbers in a range? The only thing I’ve thought of so far is separating that range into 2 fields and then combining them into the other table with a concatenate, but I’m hoping there’s a solution that keeps the range in a single field

I am managing ad contracts and needing to include the year of their creation date plus the contract number in the first column. I use a formula that pulls data allowing me to have at-a-glance info for tracking our ads. Example: the column is called CAMPAIGNS with the following formula: CONCATENATE({Contract #}, ": “,Account,” - ",{Ad Size}) This results in a field that might read: “21 004-BQEA: Bruce’s Quick Eats - 1/8 page” When blazing through production, I can see the year the contract started, that it was the fourth contract of the year, the account code and full name of the client, and finally the ad size they ordered. We also use the 21 004 to populate part the invoice numbers for billing in each ad cycle. (Contracts generally cover multiple ad cycles). This also informs our file-naming for ad image files, so we can find accompanying assets quickly and easily. I intially built contracts for each year in their own tables, because I needed