Formulas

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}) - 1) This works well and without any problems, how

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 way the view is when you zoom out a lit

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 File} Here’s what I’ve

Hello, I want to calculate the number of days of a mission, either from a period of time with beginning and end dates - which I can do with the [ DATETIME_DIFF() function] or (and this is where I have an issue), from a list of specific dates. I want to make the sum of theses days. What is the formula for that please?

I have a Time Entries table that is linked to a Tasks table via a linked record field. I want to use a formula field to calculate Total Payment dependent on the Task selected, but I can’t seem to format the formula currently. My currently formula: IF({Task}=‘MCCE – Facilitating Workshop, Grading & Prep’,{Standard Facilitation Rate (from Project)}*Quantity,{Planning Rate (from Staff)}*Quantity) I’m unable to figure out a way for it to trigger the TRUE equation when the MCCE – Facilitating Workshop, Grading & Prep task record is selected.

Hi, I am trying to use the concatenate formula to combine the name of the class and date while adjusting the date format. However, I want the values to be combined only if the class field is not empty. i have tied variations of blank and if statems without any luck. Here is the formula I am using now but when the class is blank, the fomula still returns the formated date only. Appreciate any help. Thank you! CONCATENATE({Name of Elective Session}, " “, “on”,” ", DATETIME_FORMAT({Date}, ‘MMM Do’))

Hi there, Im a beginner of Airtable living in Japan. Im about creating DB in airtable for generating invoices automatically but there are some problems. My english is not good so if I didnt make any sense, just tell me. I want to calculate datetime data with a fomura in other field linked to another record like below, however it doesnt work. DATETIME_FORMAT(DATEADD(DATEADD({FieldName},2,‘month’),-DAY(DATEADD({FieldName},1,‘month’)),‘days’),‘YYYY/MM/DD’) I dont have any ideas about this. Please tell me if there are any knowledge. Best Regards,

Hello, I’m trying to figure out how to express the below in Airtable. I took inspiration from this thread This is what I have so far… IF ( {CALCULATED SCORE} < 1, “0”, IF( {CALCULATED SCORE} < 2.2, “⭐️”, IF( {CALCULATED SCORE} < 4.5, “⭐️⭐️” IF( {CALCULATED SCORE} < 6.9, “⭐️⭐️⭐️” IF( {CALCULATED SCORE} < 8.9, “⭐️⭐️⭐️⭐️” IF( {CALCULATED SCORE} < 10, “⭐️⭐️⭐️⭐️⭐️” ) ) ) ) ) )

I’ve tried everything and I’m so close, but I can only seem to nest 4 IF statements and can’t find how to get up to 5. Background: I’m trying to assign dollar signs to different price points. If a price is less than or equal to 50 = $ less than or equal to 100 = $$ less than or equal to 500 = $$$ less than or equal to 1000 = $$$$ more than 1000 = $$$$$ Here’s what I have that almost works: IF( {Price} <= 50, "$", IF( {Price} <= 100, "$$", IF({Price} <= 500, "$$$", IF({Price} <= 1000, "$$$$" ) ) ) ) I’d like to just add one more IF in there, but I guess when I add a “>=” it messes up the code and I get an error. How do I get that just one extra step? Thanks in advance!

Why isn’t this 0? What is it. Look at the picture for details

Hi all, I am sure this has all been asked before. I have a field that is linked to another table but I have ‘Allow linking to multiple records’. When I create a formula that multiples this field to another, it will work when there is only one record selected in the linked field but returns a ‘1’ when it tries to multiply to both. Ideally, the numbers in the linked field would be added together to give a total, then the formula could multiply that number with the other field to give me a total. Is that workable?

Hi there! It appears as if I have stumped even Reddit… here is my question: I am attempting to customize a typeform link to include hidden fields associated with one table so that I can sync it to the next table and keep the data consistent. I have tried both of these in a formula, and neither are working: “Brand Onboarding - New & {First Name for Typeform URL} & “&email=“ & {Email Address} & “&brand=“ & {Brand Name} & “&last_name=“ & {Last Name for Typeform URL} CONCATENATE("Brand Onboarding - New, {First Name for Typeform URL}, “&email=“, {Email Address}, “&brand=“, {Brand Name}, “&last_name=“, {Last Name for Typeform URL}) I created the first name and last name for typeform to replace a space between two words with %20 so I already took care of any space issues. Any ideas on what else I’m missing? Thanks! P.S. I would LOVE to ditch Typeform, but until the UX and customization options on the airtable forms vastly improve, it doesn’t

If I have a formula in one table that concatenates date and text. Now I want to bring that to my other table, and it would show like so: Is there a way to start the date on the next line with added additional space? Essentially, I would like to show the data like so:

So i have a formula field where i would like to show a % of other field. So my problem is that im horrible at math and i have no clue how to set it up.

I’m trying to establish a schedule of events for a multi-day event and it would be optimal to have the primary field with a concatenate formula read: CONCATENATE({Description (from Performer)}, " by ",{Performer}, " from ", {Start Time}, " to ", {End Time}) However, for Start and End times, I would love if ONLY the time (sans date) would show, because currently I have this mess showing: Classical String Duet by XYZ Strings Band from 2022-04-14T02:00:00.000Z to 2022-04-14T03:30:00.000Z TIA, Community!

Hi all, I’m trying to create a field that will use the data in the field shown to categorize each record into “private only,” “public only,” and “both private and public.” It seems like there should be a way to do this, but I’m stumped! If it’s helpful, the field shown is a double lookup. It is in a “programs” table, each program is linked to funders in the “funders” table, and each funder is designated private or public. I want to see which programs receive exclusively private support, exclusively public support, or both types of support. Thanks!

I’m trying to create an order form that calculates price (FORMULA column) by multiplying the SQ. FT. size of an item by the SQ. FT. PRICE of the material. The SQ. FT. column is a simple calculation done based on sizes input into the order form. The SQ. FT. PRICE column is a linked field value pulled in from a different table (based on the material part number). I keep getting #ERROR! on the calculation. The Error appears to be stemming from using a linked field value. Look at the second image below…when I change the formula to use a different value from the same table it calculates without an issue. Is this an odd occurrence or are you not able to use linked fields in formulas?

dear airtable community, i would like to create a formula that sums up the size of the identical item. in the screenshot you will see, that every record has only one size, even though its sometimes the identical sneaker. i want it to be: -“dunk low disrupt 2 pale ivory black” : 39 EU - 8W US [case 1: only one record with the title - nothing should be changed, just display the available size] -“dunk low light violet (venice)” : 38 EU - 7W US [case 2: multiple records with the same title, but with the identical size - only one record should include the size they all share and the others should be empty] -“dunk low two tone grey” : 38 EU - 5.5Y US / 44 EU - 10 US [case 3: multiple records with the same title, but with different sizes - only one record should include all the sizes which are seperated with a " / " and the others should be empty. if anyone of you could help me with this challenge i would highly appreciate it, i hope i described the requirements understandable, if not feel fr