Formulas

Hi There, Apologies as I am very new to airtable. Essentially, I have a column which contains a series of numbers which indicates how many businesses make it through a particular process. This number automatically updates based on my input in other areas of the table. What I would like to be able to do is have the success rate automatically update in the next row. I can do it on excel, but with airtable being a database I am not sure how to go about doing this. In excel it looks like this : How can I do this in airtable? The point of this is because I would like to then create a chart which keeps updating with this information, showing how many as a percentage make it through each stage. Thanks L

Hello! I am trying to find a formula that will extract a customers name and address from a body of text and create new single line text fields with this data. The data is coming into airtable via an email automation, and the body of the text data coming in is always consistent in format, with four pieces of data that change, the name, address, market, and URL for the customer. There are other keywords within the body that could be used to identify the before and after for the desired text - but the left, right, and mid formula’s will not work because each body will have varying lengths based on the customers info.

Greetings All. I am new to Airtable, but getting around pretty well. I have a Base with multiple tables. I am trying to automate Prospect tracking as much as possible. I have a situation where when I enter a Zip Code I want it to populate a District, Regions, POC Name, POC Email, and POC Phone. I was able to do that with Zips I coded with a linked table and associated lookups. However, I had to restructure the table and index by District rather than zip. I just cant figure out how to do a sort of VLOOKUP function based on Zips in Airtable because the zips are no longer the index on the table. So, I figured I could use formulas instead. This is the full text of my formula: “If the value from the “Zip Code” column in the “Prospects” table appears in the “Zips” column on the “List of Districts” table, then enter the value in the “District” column of the same “List of Districts” table.” My issue is I cannot write the formula to save my life!! Is this possible? I hav

Hello community, I’m trying to average geographic coordinates. First I split the coordinate into lat & lon fields, for example: 41.3310413 and 19.7828039 In Google Sheets I’m using average() with correct result In Airtable the rollup with average() fails with NaN Does anyone know another way of averaging coordinates? PS: Also tried to remove the dot with SUBSTITUTE(Lat,".","",1) but am getting an error.

I came up with the following formula. IF({Registration date}=BLANK(), ‘’, SWITCH({PLAN}, ‘A’, SWITCH({TYPE}, ‘1’,DATEADD({Registration date},30, “days”), ‘2’,DATEADD({Registration date},30, “days”), ‘3’,DATEADD({Registration date},30, “days”) ), ‘B’, SWITCH({TYPE}, ‘2’,DATEADD({Registration date},30, “days”)), ‘3’,DATEADD({Registration date},30, “days”), ‘5’,DATEADD({Registration date},30, “days”) ) ) ) Depending on the PLAN and TYPE, we want to return the value of {Registration date} plus the number of days. And if {Registration date} is blank, I want the value of the formula to be blank as well (to avoid #ERROR). However, the returned value is not recognized as a date and is displayed as follows 2021-12-17t00:00:0.000z If I remove the IF statement at the beginning, it is recognized as a date format. Is there any way to resolve this?

Hello, I am still very new to Airtable so maybe this is easier than I think but would still like help. In general, I am looking to write a formula so that when a field (that is a single select option) is selected to “deleted” it will then clear many fields (that are multiple select) of all of their data. So in the picture included below, when the field Deleted is selected to “Deleted” I want all of the data in the other rows to be cleared and back to like nothing had been selected. How would I go about this? Thanks

Hi Currently, I have 3-columns Due date (date field) Overdue (formula) IF(IS_BEFORE({Due Date},TODAY()),“ :rotating_light: OVERDUE :rotating_light: ”,"") Due Now (formula) IF(IS_SAME({Due Date},TODAY()),“ :gift: NOW :gift: ”,"") I would love to see both results/formulas in the OVERDUE column. Then it is not necessary for both columns. That said, I am not finding the right way of merging these two formulas in one field/column. Any help will be much appreciated. Thank you

I am trying to combine two fields in a base. Both of these fields are Linked Records. Is there a way to combine the two fields and retain the records. Currently I am getting text if I use CONCENTATE or ‘&’ Here is a demo base with the example https://airtable.com/shr1OM2ygBXu8ygke Thanks for the help!

Hi, I’m making a database of coffee beans. Roasters often sell in different units of measurement like grams, pounds, kilograms and ounces. I have a column for weight (number) and another for unit of measurement (single select). I also have price. Is there a way I can use a formula to calculate price per pound without requiring extra work from contributors?

Hi all, I have a table where I want to have a “Cleared?” formula box that looks up if the current record also exists in a view, “Outstanding items” In plain language, if an employee in “Pre-service” view also exists in the “Outstanding Items” view, place an :x: . Otherwise, if they don’t exist in the Outstanding Items view, place a :white_check_mark: I am thinking the RECORD_ID() formula might work (catching an exception) but I can’t quite wrap my head around how to use it with a specific view.

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” )