Formulas

I’m working to develop a directory in Airtable that will also export pages to PDF, and I need some help with a “conditional formula” (don’t know if that’s the correct terminology) that will pull in certain records in what I think is a self-joining relationship (but I’m not sure). I started by converting a Filemaker Pro solution to Airtable, but then for various reasons set forth in this article https://support.airtable.com/hc/en-us/articles/360007520454-Combining-multiple-tables-into-one-table-with-multiple-views, I decided to combine the several tables into one. There are 3 essential views: Individuals Households Directory display (grid and gallery views) Each household has a unique number and {householdname}, which identifies to which household an indivi

I want to assign a value to each record based on the 2nd level record it is related to in the org chart. This question is similar to the question posed here but instead of adding salaries, I want to have a column where everyone under “Tamara Muro” is assigned “Tamara Muro”, everyone under “Keith Shearin” is assigned “Keith Shearin” and so on. One of the commenters suggested using a script but I wouldn’t know where to get started with that. Is there not a way to use a lookup with a looping function to keep climbing the org chart until it lands on one of these names? I also tried this solution but with all the levels in my org chart, this isn’t sustainable. Thank you for your help!

Hey all, I’ve looking through a few others threads and haven’t found a solution so I’m hoping the hive mind can help: a bit of a tricky scenario, but I’m essentially looking for a way to compare comma-separated emails across two cells and identify the ones without a match. Scenario: I have an airtable of events, where each event has: a column called “invitees” (people invited to an event), where multiple email addresses are separated by commas within a cell – the values are a string a column/cell of emails called “RSVP’s” (people who have already replied ‘going’), where multiple emails are separated by commas as well – the values are a array that rolls up data from another table. So we’ve got two comma separated cells, one is a string and one is an array. I would like to send reminder emails only to the people on the invitee list who did not already RSVP (aka, remove RSVP emails from the invitees list, then email only the ones

Airtable

Dear Community, Need help in trying to calculate the difference in months and days between two dates. Q1: is it even possible to calculate difference and return result in month and days? Or is it only possible in either months OR days? Q2: I entered a formula to only display difference in months, but choosing Jan 1 to Dec 31 is showing 11 months, and not 12 months. I get it that is subtracts and does not include Dec 31 in it’s calculation, but selecting the employement period dates (in our case) to be Jan 1 to Jan 1 would technically create confudion to the employee, as the period ends Dec 31. Any work around this. Super thanks in advance.

Hello Airtable community! I would like to be able to choose the quantity of a product A or B ON THE SAME ROW and multiply this quantity with their indicated unit prices. Is my approach right? do you have another solution? How to multiply the quantities (by products entered) and unit price (by products entered) to obtain a TOTAL Is there a formula? Thank you

Hello Airtable Community, I am new to Airtable and I am struggling with a simple CONCATENATE formula: The DESIRED RESULT column is what I am trying to achieve. You can see that anytime I try to add “" as separators, but if one of the cells is blank then not to have the "” in it’s place. I tried this formula: CONCATENATE(CompName,"_", Length,"_", Dimensions,"_", Date,"_", Code) Not sure if CONCATENATE is the right solve. I have been able to do this in Google Sheets with this formula: =JOIN("_",FILTER(B2:F2,B2:F2<>"")) Been racking my brain for a whole week on this. I appreciate any help or guidance.

I’m hitting a wall, but got at least halfway to the solution! I have two scenarios that I’d want to return a given statement. Option A: Someone has a “Yes” in FCIHSEP and a “Fall 2021” in Cohort Option B: Someone has a “Yes” in FCIFEB and a “Spring 2022” in Cohort Either of those should meet the criteria to return the “ :white_check_mark: Paid” statement. I got it to work for Option A, but I’m a bit stuck on how to build in Option B as an alternate way to satisfy the statement. What I have so far: IF(AND(FCIHSEP = “Yes”, Cohort = “Fall 2021”),“ :white_check_mark: Paid”) Any help is greatly appreciated!

Heya, Can someone help me with writing if statemtent for my blank ‘deadline dates’, if there is a no date, I want my ‘Time Left Helper’ to say ‘On Going’ deadline rather than error. ‘Days’ is my countdown field, so i think I need to say if deadline date is blank then “on going” else, countdown days. Then I’m not sure how to transfer this part to the Time Left Helper? Maybe best to leave it to appear as blank and if blank then the time left helper writes ‘ongoing’ i am not sure… Days formula: (WORKDAY_DIFF(NOW(),{Deadline Date})-1) Time Helper formula: IF(Status = "Complete", "✅", IF(Days = 0, "Due today ⏰", (ABS(Days) - (2*(Days < 0))) & IF(Days > 0, " days left ⏳", " days overdue 💥"))) thanks!

Hi I’m quite new to formulas, but I’m trying to find a formula that would extract a username from a Social Media Profile URL and return the username with an “@” in front of it. E.g. Taking - https://www.instagram.com/originalsteve/ and returning @originalsteve I’ve been reading up about REGEX functions, but it’s a little but it seems quite complicated. Would anyone be able to help? Thanks

Hello all I have a single formula field called Threat Score I have multiple single select fields with answers of Yes,No, Na. I am trying to have a final threat score after all the single select fields are answered. For example Switch({Is there leaves, needles or other vegetation on roofs, gutters, decks, porches, stairways, etc.?}, ‘Yes’, 0, ‘No’, 4, 'Na’, 4 ) Switch({Is there any dead and dying trees, branches and shrubs or other plants adjacent to or overhanging buildings?}, ‘Yes’, 0, ‘No’, 4 'Na’, 4 ) If the user chooses no for both fields the formula field should have a sum of 8. How would I combine multiple fields into a single formula to come up with a final sum. Thank you Josh

Hello! I am looking to add a formula that would split this lookup field that can contain multiple items and separate or split into separate text columns for each “planning unit”. The planning unit column can have no items or up to 10ish items. Thanks in advance for the help!

Hey there, i am super new to Airtable but already love it. I am trying to write a little overview tool for our employees and gotten to a point where I could not find answers in tutorials. I have setup a example database to explain what I am trying to do. I guess it is pretty easy and I just don’t know where to look. i have one table with all the emplyoes in it. And I have another table with dates and 3 locations. I can now easily assign one employee to a location per day. What I am trying to achieve is a table, where I would have a overview “the other way around”. Where Names of the employees are in the rows (line in the “Personal” table) and dates are in the colums. With this would like to see, who is where on which day. Is that possible? I have setup a mockup in photoshop to explain what I am trying to do. Hope someone can give me a short guise on what to do. Thanks in advance, Thomas

Hey, I need to create a formula something like this: If field_1 = “text_A”, then; If field_2> field_3, “Text_B”, “Text_C” Otherwise “Text_D” I don´t know how to do it and I couldn´t find from community or google… Anybody? Thanks //Mikko

This post is in response to the 4/4/2022 email announcement titled “Changes to Airtable attachments” which announced that static URL’s will be going away. This has a HUGE and sadly dramatic affect on my application. I’m using Airtable to crowd-source content for a weekly email - each member submits a form with their content, and I use formulas to roll together all the content into an automated weekly email, including images. It seems with this change that using static urls (as obtained via a formula and embedded into HTML tags) will no longer be possible. I’m not sure how else I can embed photos into the emails I send out. I’m hoping someone can help think this through. A HUGE part of what I’m doing is about automating the compilation of multiple contributors into one email… it’s been working brilliantly for years… I’m so sad about this. I don’t know what to do…

Hello, we sell flowers online and use Shopify, we ask the customer for some extra information with each order, the date they’d like the flowers delivered and their card message. When we get the Shopify data (Line Items Properties Value) through to Airtable it comes through in the following format [Day][Comma][Date][Month][Comma][Year][Pipe Key][Comma][Card Message] (e.g. Friday, 22 April, 2022|,Happy Birthday, Love you xxx) splitting THIS up is relatively easy although it took some working out, (Note: commas can’t be used reliably to split up the data as customers often use commas in their card messages) so to do this I have the Line Items Properties Value data in a regular text field and use the following formula: Delivery Date: LEFT({Line Items Properties Value}, FIND("|", {Line Items Properties Value}) -1) Card Message: RIGHT({Line Items Properties Value}, LEN({Line Items Properties Value}) - FIND("|", {Line Items Properties Value})

I have a “Long Text” Field that contains a list of URLS. There is an associated keyword in the adjacent cell, which is what yielded those URLs listed in the LongText field keyword | LongTextUrlsField shoes | [50+ Shoe Related Urls] Is there a way to extract each of those urls and put them into a new table, along with the corresponding keyword (multiple times) like this? keyword | longtextUrls shoes | Url 1 of 50 shoes | Url 2 of 50 shoes | Url 3 of 50… I have looked at everything from python scripting, to regex formulas, and even AirScript Ideas. Although there are some similar topics out there, nothing did this. Any help would be greatly appreciated!

I have a few scenarios that would be so much easier if there was a way to have some more advanced options in the pivot table, is there other ways to customize this app or alternative pivot apps that might solve my needs? #1 - im trying to see the unique record names as a cell value instead of a calculation (ie sum, count etc). Google sheet allows me to enter a custom formula which means I can have a pivot table that maps the entire year of product releases by month per category, and the formula allows me to list each month as the unique item short-names. this way instead of seeing a count of 5 for example, I see a list of 5 item names. I know I can click on the pivot table cell and show record, but the ability to compare months and consider patterns or timings of things is easier with short-name visibility. Timeline view would have been nice but I have been unable to format it for viewing the name because it ends up truncating the text due to the

Hi Folks, Does anybody has an idea how we can reproduce the colored bar stats in the function field?

Hi there! I have a slight feeling I might need to use scripting for this but I am NOT a javascript trained human. I am trying to take the sum of the $ amounts in “January Birthday Notifications” and place it in the “January Balance” field. The trick here is… I need to do this for each month so efficiency is really important to me, haha. Any help would be greatly appreciated! Thanks so much!

Hi! I’m new to airtable. I’m running an automation to create google calendar events. I need to evaluate if the email addresses in a field are in our domain name, and if so add them to a new field. My challenge is that there can be many email addresses in the field, so the text-based formulas won’t work. Is there a way to loop through a multi-select and evaluate each element independently? Thank you!

Hi, I have two linked tables. (very basic stuff) How to get the sum of numbers in linked column and show the result into another column. Please check the image of my base: “hxxt://cdn1.imggmi.com/uploads/2019/11/26/f4ca6ea5ae2d7a7d6eb92a96c1bdc33b-full.jpg”

hi i am trying to put this formula IF(Level=1,“Yes”,IF(Level=0,OR(2,3,4,5),“A la carte”),IF(Level=0,OR(6,7),“No” )) but Airtable didn’t save it, when I check, only that part of the formula was saved IF(Level=1,“Yes”,IF(Level=0,OR(2,3,4,5),“A la carte”)). How can I do to solve this problem? How can i do to set this problem ?

Hi All - Hoping someone can help me figure out a formula to create the result outlined in the attached image. Involves stringing together text from fields, as well as replacing spaces with underscores, and shortening some Names to be Initials Only, etc. If possible to create a Loom or Video explaining how to do this, I would be so grateful! I can’t seem to figure out how to properly put all the proper formulas together to make this happen but know it can be done. Thanks!

Hello! I am trying to add a view onto my content tracker for localization efforts of some of our content assets. The content that meets a certain criteria feeds into this view and there are columns for all of our Tier 1 and Tier 2 languages. However, I want to automatically populate the “US” field with a link to the final asset, since all of our content originates in US English. The challenge is that there are 3 columns where the link might exist. The first column is the highest priority, if there’s nothing there then it goes to the second column, and ideally if there’s nothing there then it would default to the third column (the reason for this being that different content types will have different final content sources. I was able to create a nested IF for just the two options, but now I can’t figure out how to add in the third column to the mix if those fields don’t have any values. The hierarchy is {Marketo Link}, {Published Page Link} and then {Link to Final F