Formulas

Hi,Which Language setting should be used to paste formulas, i mean here:  Several times I wanted to ask it on old forum engine, but then just selected ‘Javascript’ and postponed it until next time. Here it looks not so good, so maybe I’m just missing a better way to do it?Well, in my opinion, the Javascript was better in old forum as well, here it’s too much red, while red supposed to be warning/error color. But maybe it’s just a matter of taste and mine is far from perfect.

I’m working on a voting app, where there are tons of enhancements. There’s a ‘Votes’ field which is of type ‘User.’ Users can add their names to that field in order to vote for that enhancement. This is based on a simple, single table.I want to have an interface page that lists enhancements that have gotten votes, sorted by the number of votes each has gotten. So if I add my name to that Users-type field, the number of votes for that enhancement goes up by one, and the sorted list of enhancements by number of votes gets appropriately updated.I thought I could just do a calculated field with this formula: COUNTALL({Votes})However, that’s giving me a value of one (1) for each record, even though right now most fields have no entries and a few have 2 or three.I’ve only done pretty bas

I need a formula to populate a field based on three other fields - where only one will have data. So in fields 2 - 4 below, only one will have data and I want that to show up in Field 1. Field 1 - formulaField 2 - Lookup from table 1Field 3 - Lookup from table 2Field 4 - Lookup from table 3

I use the software to keep track of all my craft projects as well as all my supplies. I am trying to calculate the price of the supplies used for my projects ("cost per item * # of items" kind of thing), but one of my fields has multiple values. When I try the below formula, it always returns a 1.0.{Price/sq in (from Vinyl)}*{Area of Vinyl Used (sq in)} Is there a way to have each value in the "price/sq in" field be multiplied by the "Area Used" field (which only has 1 value)?

Hello, I am new to Airtable and formulas. My current formula is thisIF({Full/Part Time}="Part-time", 0.5, IF({Full/Part Time}="Full-time", 1.0, 0))I want to add a condition that if another field has two selected clients, the part time value of 0.5 will be doubled.The Full/Part Time column is a single select field. The formula isn't coming up with any results. Any idea what I did wrong, or what's missing in my formula? Thank you! 

I have a table for payment processing. When new records get created with a certain status, I would like to find the biggest check number in the table and add 1. Not sure if this would be using automation or formulas but thought I would start here. Thank you!

Hello Airtable community!I inherited a pretty robust Airtable system from a predecessor and have been working to revamp it, but my formula skills are not as good as the person who came before me, and I've been struggling to get IF AND statements to work. The logic seems sound, but they keep returning empty cells.One of the ones I'm struggling with concerns a formula that should return 1 of 2 options if a cell has a specific entry. I can get a single IF statement to return the correct result, but as soon as I try to include an AND, it fails. Here's what I have so far:In my table, the formula field is 'Ship From' with the two options being 'Direct from Printer' or 'X Distributor'. I want the formula to return 'Direct from Printer' if another field - 'Role' - is one of 3 options: Marketing/Sales, Licensor, or Editor in Chief. All other Roles should return 'X Distributor.'So I put in: IF(AND

Hi there! I am trying to remove NaN values from a formula field where both positive and negative values exist. This is in an effort to clean up two extensions: One chart indicating the number of each value's occurrence and one number where I am trying to get the average of the values, but with NaNs included I receive an infinity. Here is my current formula:DATETIME_DIFF({Due Date},{Date Completed},'days')+IF({Date Completed},{Due Date} = "NaN", BLANK())Thank you in advance!

Hi,I'm trying to create a table to track income and the records feed from different tables using automations which are assigned a deal type as they're created. If they're type A the commission is 15% of the payment amount, if they're type B the commission is 10% of the payment amount and if they're type C then the commission field needs to return 0. I don't know if it makes a difference, but the payment amount field is a formula. I've tried SWITCH({Type of deal},"A", {Payment amount} * 0.15,"B", {Payment amount} * 0.10,"C", 0) But I just keep getting ERROR in the results. 

Hi there,I'm looking for a formula to automatically calculate the Venue and Producer split of the Budget and Budget Committed as per the "Deal." However, at present, the "Deal" field is a text entry, and I wondered what the best way to achieve this is? Many thanks,Tom

Hi There, I know I should already know this but I always get nervous when dates are involved. I want to be able to highlight either a field or a row when a task in the row passes the deadline. I don't need to know any other information like how many days over it is, I just want it to be highlighted to bring attention to it. I have a [Date Due] column and a [Status] column. The idea is that the status column would be set to "On Time" or "Overdue" to allow filteringCan anyone tell me what formula I can use to trigger the change in the status column?Thank you for your time. S

I’ve been using Airtable for a while now, and while it’s a fantastic tool for organizing data and streamlining workflows, I’ve been contemplating how I can enhance my note-taking system using Airtable formulas. My idea revolves around integrating a simple online notepad feature within Airtable to streamline my workflow without overcomplicating things.Airtable’s versatility and customizability make it perfect for managing complex data sets, but I feel like an integrated online notepad could improve the way I interact with my notes directly inside Airtable. I’m thinking of using a set of formulas to create a simple, text-based interface where I can store short snippets of information, thoughts, or even checklists. This would eliminate the need to flip between different apps or browsers to jot down quick thoughts, making the entire process smoother.An online notepad typically refers to a digital tool or platform that allows users to write, edit, and save

Hey there,Thank you for your time helping me !So i'm a mushroom farmer. I cultivate different batchs simultaneously. Each time I harvest one, I create a record for it, with the date of harvest, the batch harvested and the weight harvested. There is a tab dedicated to it.I also have another tab which contains more infos about each batch, and also a sum rollup of the harvests of each batch.My main metric to know if a batch is good is the yield, I compare the total weight of the batch to the amount harvested. For example, I harvested 10 times a batch, the total is 300kg of fresh mushrooms on a batch of 1000kg of mushroom substrate, hence 30% yield.But I'd like more complex infos, for instance I'd like to know on which specific date of harvest I crossed the 10% yield mark, the 20% ... So i could know for each batch after how many days I crossed the 10%/20% markI've tried some combos of rollup and formulas but nothing works so much, any idea

I'm new to Airtable formulas, so I would appreciate your input.I created a formula that I absolutely KNOW was working. But suddenly the field where the calculation was says #ERROR! now although it said 100% before. I can't figure out what has changed because I didn't change the fields used in the formula.I would appreciate your help.The field name is Sold v Booked %The formula is:IF({# of Winners Booked},IF({Number of Winners/GCs Sold},{# of Winners Booked}/{Number of Winners/GCs Sold})) The # of Winners Booked field is a Lookup field from another table. The Number of Winners/GCs Sold field is a Number field. 

Hey everyone, I’ve been spinning my wheels on a simple issue for the last few days and hoping someone can put me out of my misery. I have the above dataset and hoping to split it up automatically using a formula. The data comes in via Zapier and the delimiter is always a ,. I need a solution that can run automatically. Few keynotes on the data; Could be anything from 1-3 records. They won’t always be the same The length of them will also vary FWIW I have only two records now and have formulas in place based on searching around. They are; Product ID A IF( FIND(",", {Product ID purchased}), LEFT({Product ID purchased}, FIND(",", {Product ID purchased})-1 ), {Product ID purchased} ) ``` Product ID B

I am trying to create a formula that will take the latest date from a linked field (Appointments) and enter the latest date in a new field called "Last Appointment." I tried to generate a formula using the AI which gave me this formula:DATESTR(ARRAYSLICE({Appointments}, -1, 1))but it keeps coming back with an ERROR. Any help would be appreciated.

Hi, we receive form emails in our airtable base. what's the best regex to use to extract information?Email looks like this:*Your Name* Jane Doe*Organization* Best Company*Your Job Title* Sales Manager Thank you!

I'm having a really hard time with making a formula field only calculate a value if the input field is not empty, including if it's 0. For some reason Airtable seems to treat 0 and empty value that same.Here is my current formula: IF({Monthly Actual} = BLANK(),BLANK(),{Monthly Actual} - {Monthly Goal})In the attached screenshot, I want the fields with monthly actual = 0 to have a difference calculated, and I want the empty monthly actual to not have a difference calculated. The latter works, but the former doesn't. I've tried ChatGPT, Perplexity...etc. all types of AI and can't figure it out!

Hello Airtable Community!I'm facing a challenge with constructing a filterByFormula and came across a similar issue discussed here: https://community.airtable.com/t5/formulas/formula-with-multiple-values/td-p/174768.I have a column named Klasse, which can contain multiple values. I want to check if my search terms ('A', 'A1', or 'A2') match any of the entries in the Klasse column. Using the method suggested in the linked discussion, I crafted the following formula:  OR(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) > 0, FIND('A2', {

I have a table of books with the following columnsBook Titleyear publishedAmazon LinkBookshop.org LinkI want to create a field that returns Book Title (Year Published) Amazon Bookshop, where the "Amazon" is linked abd "Bookshop.org" is linked.Is it possible to use a formula to return text with links? I tried creating columns for buttons, but when I include the button in the formula, the output is just the URL. For each author, I want to roll up a list of their books with the corresponding links. 

Hi there. I'm trying to create a formula that returns a numerical value (price) based on a dropdown field. So, if "Option Zebra" is selected in the dropdown, the formula returns a value of 250. If "Option Lion", then the formula returns a value of 300, etc. Would some amazing wizkid be able to help me with that?

Hi all! I am trying to find a method to tell me the Field Name of the last modified field, using a Formula. Is this possible without external scripting? I'd also settle for Field ID, although not most ideal 🙃Was hoping for a function like LAST_MODIFIED_TIME( FIELDNAME() ) or something.Basically, I have logs coming into a Logs table with 100(ish) columns, and I want to know at a glance what type of logs that particular one is 🙂Thanks in advance!Adam

I need a formula to verify a single-line text field only contains productId's separated by commas with <=30 comma separated productId's.OK: <=30 comma separated 8-character productIdsNot OK: >30 productId's; <8 or >8 character productId's, not comma separatedexample (with 5 comma separated productId's)correct: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9incorrect:7 productId's: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9,H4VPMWY7,MRKM1Y6F5 productId's with missing commas: B0D5FHXP B0D86NWH BCVPRWY7 DRKM1T5G GK3DHGH97 productId's with semi-colons: B0D5FHXP;B0D86NWH;BCVPRWY7;DRKM1T5G;GK3DHGH9 

Hi there,I have a team of account executives and I'm trying to rollup the amount of revenue each person manages during a 12-month period. Revenue is generated on a per project basis. Each project has a start and end date and a monthly retainer fee, which are recorded in the project's record in my project table. I have a formula field ("Project Value") that calculates the total project value.I maintain a second team member table and I link account executives to the projects they manage using a linked record field. Some projects are managed by two account executives (and are linked to two account executive records as a result).The problem is that when I rollup the amount of revenue each person manages during a 12-month period, for projects that have two account executives attached, the rollup attributes 100% of the project revenue to both of the e

Hi,I have a formula that almost works. I'm trying to make my primary field a formula where it looks at "Column A" and looks at "Column B". If a there's a value in "Column A", enter that value as the primary field. If there's a value in "Column B", enter that value in the primary filed. If there's a value in both "Column A" and "Column B", combine those two values and place a "/" between the two fields. The formula I have looks like this: IF(AND({Column A} != BLANK(), {Column B} != BLANK()), CONCATENATE({Column A}, "/", {Column B}),IF(Column A != BLANK(), {Column A},&nbs