Formulas

Hey all!I've got a long formula, that currently has just one IF statement, however, I need to update to add another alternate IF.Here is the existing formula, that works:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}Here is what I was attempting to add:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}Any ideas how to fix?Thanks!

I am using Softr and Airtable.Create a formula. If the field meets a criterion I require it to display a text "x", otherwise it must display a text "y".But the text has a separation (enter / space) between two paragraphs. When copying the text it has the separation (enter / space). But when displaying the information in Softr I am getting the next character "R" to create the hyphenation (enter / space).How can I have the separation (enter / space) in aritable, but do not show the character "\R" at softr?I appreciate your help.Translated with DeepL

I'm sharing a solution to a problem I had; hopefully this will help others. Note: you will need to be on a plan that includes Automations.When you try to reference information from a linked field in a primary field, Airtable will warn you of a circular reference, even if no dependencies will be affected by the contents of the primary field.For example: I work with cars, and I have two tables (among others) in my base: Vehicle Generations and Vehicle Years.In Vehicle Generations, I'll have a record with the primary field "Jeep Wrangler, 2007–2018"; in Vehicle Years, I'll have a record with a primary field "2007 Jeep Wrangler", or "2008 Jeep Wrangler", and so on. And each of these Vehicle Years records is linked to a Vehicle Generations record.Now, if I want to dynamically generate the primary field for Vehicle Generations (e.g. update the end year as new model years are added), I could use a formula field like{Make & Model} & ", " & {Start Year} & "–" & {End Year}, w

Have a base that looks at deal flow, including anticipated close date and value of each deal.  While we can easily view our "pipeline" at any time, struggling to figure out a way to store that snapshot of data on a routine basis.  We don't store updates to pipeline as a record itself, but update the record to the anticipated value.  At the end of a specific month in the past, what did our pipeline look like in terms of deal value, grouped by when we expect it to close?  Used to look at accuracy (anticipated vs actual) of our project pipeline projections.   

Hi, I have this table in which each tab has a column with a checkmark ('Interested Lead'). I would like to sum up the total number of checkmarks in the 'Interested Lead" column across all the tabs in this table. 1). What should I use? a function? a roll-up? or a look up table? 2). When I try a look-up table, it only gives me the option of selecting one tab {PMf Sales} but not the other tabs.  Thanks in advance!  

Good afternoon!I'd like to create a field that would simply show if a date within the record is within the past year. We already have a field "Date Received" for each record. I'd like to have a field that can quickly show if that date is within the past 365 days. I'm guessing the best way would be a formula field that would display either a "Yes/No" (or a check or something) if the "Date Received" value is on or before one year from the current day.Anyone able to help craft that formula? Or is there a better way to do this?

Hi Airtable Community,Looking for some advice. I have columns where I have cut length of webbings in a cell.  I have noted the placement of the webbing, cut length and quantity in the cell.  I wanted to have a column that can take the numerical data and divide by 36" (i.e. [(9.75 x 1)+(17x2)]/36)), to give me usage of the material.  Is that possible without having to manually split each cut length into multiple columns?  I do need the text description of the location (i.e. Haul loop) or else I cannot identify what the cut lengths are for, if I need a column for each description of cut length then I will have a lot of columns which I'd like to avoid.  Any suggestions would be appreciated!Thanks,Annie 

Hey all!Currently, I have a client database with start date {start} and finish date {finish} fields.Once I have added a finish date, I then manually update the {status} field from 'In Progress' to 'Done' - it is a single-select field).However, I'd like to automate the above process, meaning I'd like to automate the following:If {finish} is not empty (meaning a date has been selected and the project has been completed), change the {Status} field from 'In Progress' to 'Done'I've attempted to do this but the automation comes back as an error.I look forward to hearing any ideas!

I have a rollup formula that returns a bullet pointed listed, which works beautifully (supplied by this incredible community - thanks!). I need to concatenate that with additional information though and am at a total loss.  This is my current output.  I have an additional column with the tentative open date that I'd like to add to the list in Pending Branches.  My current formula:   IF(values,IF(COUNTALL(values) = 1,"• " & TRIM("" & values),IF(COUNTALL(values) > 1,"• " & TRIM(ARRAYJOIN(values, "\n• "))))) Please help!!! And thank you 🙂 

I'm trying to find a way to display time zone for a 'formula' field that ends up being a datetime, in the same way that time zone works for actual datetime fields.I've reviewed the new SET_TIMEZONE function and some posts here, but not finding an answer.A simple scenario to help clarify:- A have a datetime field, my settings are to use the user's time zone when presenting datetimes. So that everyone from around the world understands what time is referred relative to their time. E.g. a meeting start time- I also have a formula field that calculates a different datetime using the datetime field mentioned above, for example, add x hours to that date time to produce a new date time.- Now, this is where it all falls apart. The formula field doesn't behave like a regular datetime field, even though, for all intents and purposes outside of its own calculation, I want it to work as though it was a regular datetime field.- It would appear, that I have no way of this formula datetime appears wit

Hi, I have many rows with multiple attachments. Some attachment urls are having https://v5.airtableusercontent.com/v1/ format. Some of them have https://dl.airtable format.How do i tweak the script if possible to get https://dl.airtable format? I need the direct url link with .png in the link for another purpose

Hi everyone!I am currently setting up a database to calculate how long a project has been active.This means I'd like a formula that calculates the difference between (a) the start and finish dates or (b) the start and current date (if a finish date has not been specified).I know of formulas that can do either of the needed functions, but not one that is conditional and selects which calculation is appropriate for each project. Hopefully someone might be able to help me out? Thanks again!

I have tried a ton of suggestions but I am not figuring this out.  And I thought it would be a simple auto-parsing formula.I have an interface that allows our team to enter a new task/call record into our task tracking base (see pic below)I have a field that is Date-Time field that when an entry is created, the person adding the entry (through an interface) selects the current day/time when they start the task. (There is a lot of behind the scenes processing that happens with this information)I want to be able to auto-populate a Date only field based on what was entered in the Date-Time field. Right now the Date and Month field are manually updated by the person filling out the entry.  (I want to remove this manual set of steps)I also want the Month field to auto-populate the Month field - so again we can present the what happened in the month (Closed, Open, Pending tasks)Reasons: We can have dashboards and the DB Views that organize/present what happened on each da

I have a column that has a list of counties and they all have the state abbreviations next to them. Is there a way to remove the abbreviations with a formula inside of airtable? 

See screenshot below. I'm using this to generate invoices. It does everything I want it to do, except for one thing. For the final invoice that the customer sees, I want all products, both custom and standard, to be listed in a single field (success!). Then I also want all product prices, both custom and standard, to be listed in a single field (no success! Circled in red).Issues I've run into with using "&" and CONCATENATE:1. I want everything separated by commas, but I don't want there to be a comma at the end, for example if there are only standard products and no custom products.2. I want the column to be formatted as currency, the same way the "Itemized Prices" and "Custom product price" are already. When I have tried using various formulas, it tells me that my field is no longer a number or a currency and cannot be formatted. Please help!  

Hi all, Im struggling to figure out why a formula in my table produces a blank response vs the intended result... I can provide more context if needed but suspect the error is more obvious to some of you and context is irrelevant. LMK if context is needed.  The formula is: IF({Junction}=1,LEFT({Status},1) & [Product}," ")'Junction' field can have only two possible results 1 or zero. I would like that if a record contains a 1 in this field, that this new field formula produces a text string that combines the status and product, (eg "Ongoing - Product A") It's not doing that though... it's producing just " ") What have I got wrong? 😞

Hi, i would like to ask for help if there's a way to get the count in group view maybe via automation or scripting. (please see screenshot). Though we have a count My goal is to check one of my field when the program is under the same metro schools - cna & other licenses and same provider full name. Most likely, if the highlighted number here is 1 it should automatically be checked. On the other hand, there are instances when the highlighted number is more than 1.  (please see screenshot below). In this case, I will just randomly check anything or base it in the program with Upcoming class.For now, I manually check all the programs which is very time consuming. I hope you can help me. Thank you!

I’m trying to find the correct syntax for a formula that will compare two lookup fields, each with multiple values, to find the common values. I see similar questions posted but don’t seem to find anything that fits what I’m trying to accomplish, so any help would be appreciated. Here’s the scenario: I have three tables: Vendor Database, Product List and States. The Vendor Database has a Lookup Field for the Product List and a Lookup Field for States, and typically has multiple values in each field, since many of my vendors have several items available in a few states in the U.S. I have another table that includes all my incoming requests, and my goal is to see which vendors have service in the specific state requested. Here’s what I have so far: In my Workflow table where I have views for requests, I have a Lookup Field for Vendors with Services and another for Vendors in State and the results are perfect in those two fields. I want to compare the two to find common values that will s

Hi there,I'm experiencing a strange situation with a simple formula. Here's what i'm using:WORKDAY_DIFF({Date-test-1}, {Date-test-2})-1 I set date-test-1 to Today's date (Friday). If I set "Date-test-2" to next Monday, the result is correct: 1(Note that my dates are in european format) If I set it to next Friday, the result is correct: 5 It seems that it does it perfectly whenever Date test is a future date. I also want to calculate the working days between today and a past date, and here's where i'm getting strange results:Setting Date-test-2 to Yesterday, Thursday, the result is: -3, should be -1 Setting it to last Friday, the result is: -7, should be -5 Any explanation on why this is is happening? I could do an easy trick, and add +2 whenever the date is past, but I can't understand why this is happening, so i don't know if this fix would stay consistent. Thanks in advance! Javier  

I have 10 fields with star ratings, is it possible for me to list the top 3 star rated fields in  different field? 

Hi! I just moved over here from Quickbase. On QB, when I did a search, all the matching results appeared, and no other records were showing.In my new Airtable, my search results in a single highlighted instance of the matching search result, and I must keep hitting the down arrow to see the remaining matches.Is there something I can set up, such as a view or whatever, that will behave similarly? My customers are asking for this behavior because they're used to it, and I'd like to help them.Thanks, Eric

I've got an events calendar and an automation set up so that event registrants will get an email 6 hours prior to an event, using a formula field and the TONOW formula:TONOW({Start})The automation sends an email when the formula field reads "6 hours".The trouble is, the field renders "6 hours" twice - once 6 hours before the event and again 6 hours after the event, so it triggers the automation to run again 6 hrs after the event.Can anyone recommend a better formula to get what I'm looking for here?Thanks!!!

I think what I am wanting to do is also possible with a rollup field. However, I can't get it to work.I have a column that I want to add all the values on a different table. Table 1 has all the prices in it.Item 1 > Shoe > $39.95Item 2 > Shoe > $39.95Item 3 > Shirt > $19.95 I want to add all the "Shoes" together in Table 2 and display that total. So I made a rollup field and said if the values in table 1 match "shoes" then "insert formula here"I tried using SUM....but I am not doing something right. Am I overthinking this? XD

I am trying to create a button to make calls and mail using URL "mailto:xyz@abc.com" but I am not able to use that. Can you suggest me something?  The email address and phone number are stored in different columns.

I am trying to design a record keeping system for a market garden. One of the key pieces is recording events that happen to a garden bed - e.g. transplanting, seeding, harvesting, bed preparation events. The record is the date of the event.I am having trouble making a simple system for when there are multiple "parts" to an event. E.g. the "preparation" event has 4 inputs (which come from an "input" record table, which I would like to be able to break down & summarise the costings of. The user would select the name of the input and it would look up the APPLICATION RATE(KG/m3) the BED SIZE receiving the amendment, the COST/KG. This is then summarized in a formula which multiplies APPLICATION RATE (KG/m3)*COST/KG*BED SIZE. This is simplest if I can make the input field link to multiple records - however, my formula for costings won't work if there are multiple records in each of those look up tables.Is there any way to deal with this? I am new to this kind of stuff, so