Formulas

This is an interesting little discovery regarding the VALUE() function. On the formula field reference page, it makes it seem like VALUE() will simply extract all the numbers from a text string. But this isn’t true under all circumstances. The VALUE() function will sometimes perform mathematical functions as well, if certain symbols are included within the text string! For example, the formula VALUE("**6hxyzb0**00987**") will extract all the numbers and return 6000987. This is expected. But the formula VALUE("**6hxyzb0%%00987**") will return 60009.87. The very presence of a percentage symbol in the text string makes VALUE() multiply the extracted numbers by 1%. Similarly, if the VALUE() function sees a dash in the text string, VALUE() interprets the dash as a minus sign and it multiplies the extracted numbers by -1. So the formula VALUE("123-XYZABC") will return -123. Interestingly enough, if it sees 2 dashes, it multiples the 2 negative ones together to get a positive 1. So the formu

I am using the Value formula to extract the year from a text field column. In most cases this is working just fine, however, a handful of entries are returning a random string of numbers that do not appear related to the text field that the formula references. Screen capture for reference attached. What could be causing this to happen?

Hi there! I want to send reminders on collaborator emails. I build a table where they create reminders for themselves. The email has to be sent at the selected time. I try to use automation trigger “When record matches condition” or “When record enters view”. In the “Reminder” table I have a “Date&Time” field and a field with formula: IF({Date&Time} <= NOW(), 1, 0). I noticed what the formula field is not updated instantly when the condition is true. QUESTION: How often is it updated? Is it a best practice to use such solutions?

I’d like to know if there’s a way to check/validate/verify the number being entered by the user when entering in the form. The tab(table) has preassigned numbers(should they be text or numbers for using elsewhere?), so I want to be sure it just automates a new number. Is there a way to do this? Also it does have to be two digits only.

Hey everyone, Hoping somebody can help here. I’m having trouble returning some info from a formula I have created. I have a table to show the duration of certain tickets that are received for problems. I have a Date Raised field, Date Resolved field, and a Days Outstanding field. I want the Days Outstanding to return the difference between Date Raised and Date Resolved when there is a value in the Date Resolved field, and I want it return the difference between Date Raised and TODAY() when the Date Resolved field is empty. I’m currently using this formula: IF({Date Resolved}!=BLANK(), DATETIME_DIFF({Date Resolved},{Date Raised}, ‘days’), DATETIME_DIFF(TODAY(),{Date Raised}, ‘days’)) It’s working fine when Date Resolved is populated, but returns NaN when it’s empty. I have created another field just to check that the today-date resolved formula works and it’s returning exactly what I want, it just doesn’t work when it’s inside the IF formula can anybody explain this and show me a solu

Hi, i have two bookmars “Objednavky privatni” and “Kaumy”. In “Kaumy” a need to add new column with numbers from “Objednavky privatni”. For example. “Objednavky privatni” is ORDERS in english, and Kaumy is company name. If they made a order, i will make a new record in ORDERS. There is 6 product, they can order in different quatity. I need to get these quantity number into new column “Kaumy”. In excel is very easy. In airtable is work for hours! Thanks for help. ImgBB V-st-i-ek Image V-st-i-ek hosted in ImgBB ImgBB V-st-i-ek1 Image V-st-i-ek1 hosted in ImgBB

Hello, I am sure this is a pretty basic question but because I lack of knowledge I want to ask you what I am looking for in technical terms. How do I describe technically correct what I am looking for? This is what I am looking for: I have a “list of zip codes” where each zip code is assigned to a certain value. This value is a tax rate. I also have a “database of clients” where there is always a zip code. I want to find out which tax rate applies to them according on the “list of zip codes”. 3.I have a already a field where I link from my “database of clients” to the “list of zip codes”. If I type in manually in each line the zip code it works! BUT: I created a table where I put the list of zip codes and tax values, I linked it from my table where I store my user data and now I can look up manually by typing in the zip code in my user table the tax value from the new table. But I want that “automatically” so that the zip code from the user table automatically is requested in the oth

I have list of jobs records in Jobs table. jobs table has a field called Industry (Multi-Select dropdown) dropdown and it has list of items… I want access those items from the dropdown to populate a new dropdown. I am using API call to get data from Jobs table

Hello, I’m having trouble creating an IF CHECKBOX formula. I’m trying to create a numeric score based on multiple checkboxes, assigning a value to each. I’ve successfully done the first using this formula. IF({FIELDA}=1, “80”,“0”) This puts the number 80 in my formula column if FIELDA is checked and 0 if not. What I need to accomplish is having other checkboxes with other values. Example IF({FIELDA}=1, “80”,“0”) IF({FIELDB}=1, “10”,“0”) IF({FIELDC}=1, “-25”,“0”) I’m looking for help creating the formula so if each box is checked, it adds another value that is calculated. In the above example, if all three were checked, the SCORE in my formula field would be 65 since 80 + 10 - 25 = 65. But, if the first two were checked the score would be 90. Any guidance would be much appreciated.

Hi would someone be able to help me create a formula to find the % difference of 2 numbers? Thanks!

Hello, I have a field called “Quantity” that needs to count how many parts are being replaced for each machine. I have a field marked “PartNumber” where I can link the parts from a PartsList (table of a partnumber for each record along with a description). Is there any way to calculate that I have 2 of “M10506” and only 1 of “M34563”? We are coming from paper copies of Service Reports and I have no idea how to do this. I would gladly compensate anyone who would spend more time with me to help iron out this database. Thank you for the help. -Nathan

Hello, I am trying to get repeating tasks to work based on their regularity (Daily, Weekly, Monthly) with the IF and DATEADD formula used together. Here is what I have tried: Can anyone suggest where I am going wrong?

Hi friends, I’m trying to score ideas and calculate a weighted score, but I’m not sure how to execute this in Airtable (it would take me seconds in excel). I have a list of ideas, and a score for each across several variables (see 1st image below). I have a 2nd table with the weights (see 2nd image below). I would like to write a formula in the Total Score column in the 1st table (3rd image) that multiplies each of the score variables with their corresponding values so that The total score reflects the weighted result. So conceptually, I want to calculate like this – how the heck to I do this in Airtable? Total Score = Sum { (Var1 * Var1_weight) + (Var2 * Var2_weight) + (Var3 * Var3_weight) } … etc. I really appreciate any guidance you can provide.

Hi guys, I’m very new to Airtable. It is a marvelous tool but it’s a bit different than excel which i use my whole life. It left me confused with some formulas that seem easy in Excel. Background I’m trying to make an automated contracts form. I constructed a few databases A freelancer’s database with all data like address, fields of expertise etc. A project database. Each row contains a project name, member of a team , budget per member, few columns from Freelancer database - field of expertise etc. A contract database with project start and finish date, project scope. And the thing i have the most problem with is the contract number. Problem: Contract number is formatted like this: number of contract/ project name / yearI need to count the order of occurrence of duplicates to make multiple contracts in the same project numbered in ascending order. In excel it would be like this *Names are from airtable template After that i will try to export all of this data as a PDF file with c

Hello! I am using the airtable API with a formula to basically filter a set of records from a table. I am filtering on a multiple-select-field, combined with a logical OR, so something like this: OR((FIND(LOWER("First"),LOWER({Select})) >= 1),(FIND(LOWER("Second"),LOWER({Select})) >= 1)) Now I will have records that have both “First” and “Second” selected in the “Select” column. I would like to sort these more accurate matches to the top of my results. Is there any way to do this? Thanks in advance! Tom

Have a field InvoicedDate. I want to get the last two digits of the year of the InvoiceDate value. So for example, if InvoiceDate contains “2/15/2021” I want to get the result “21”. This is what I’ve tried: RIGHT(YEAR(InvoicedDate),2) Looks okay to me, but it returns #ERROR. I suspect it’s a typing problem, that is, YEAR() returns a number, but RIGHT() can only be used with a string. But I can’t see how to coerce the number to be a string. I’ve reread the functions reference but can’t find a NUMSTR() function or anything like that. What am I missing? William

While creating a record at work, e.g. Q#1640, I kept making new records (#1641-1645) clumsily and deleting them. Unfortunately now the business is missing those rows. There’s no field to leave a comment. Is it possible to add, paste, or restore rows #1641-1645, and have the table working normally?

Hi there - Whenever I try to do a basic division formula between the values in two columns I get an #ERROR! message. I can’t figure out why. Below is what I’m inputting. Formula for column: {Measured Coil Temp.}/{Max Rated Temp.} Would really appreciate some help. Thanks in advance!

Hi All, I am really new to formulas and I am trying to do along string IF statement. Can anyone help me identify where this is going wrong? I looked and read through other topics and just can’t get it to work. I essentially want this to go all the way from 1 to 100 with greater than or equal to all the way up. IF({Security Price (from Search Criteria)}>85,“1”) ({Security Price (from Search Criteria)}>=70,<=84,”2”) ))

I have a column that was a name of a product, which is customer-facing, and I have another column that I use for myself. I would like to create a formula where if (Column B contains the word Blue) output BLUE to column B If (Column B contains the word Whale) output WHALE in column B If neither is true output ERROR I’ve tried so many options, nested ifs, and substitutes … I only have 5 products, so I also tried using a switch statement … but it’s not working … does it only work with integers?

Hi, I am working on a project where invntory can be rented for a certain period. I need a formula to count how many records that have requested a rent for the same day. When I know this, I can set the status as ‘unavailable’ or ‘available’ if too have requested the product for the same day. Thank you!

In the documentation for the NOW() function it states that: If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies. I have a Zapier process that polls a view, does this count as a sync dependency? The formula i’m using calculates whether the value in a date field is greater than the current datetime, and if it is it will appear in the View that Zapier is watching. It’s important that the formula is calculated 24/7 and not just when the base is open.

Hi All, I would like to know if it is possible for me to calculate how many due dates were missed in a month. I have a column for Due Date (The last day to complete), the status of the task (To Do, Created) Count the number of times the status was changed from “to do” to “created” post due date of the task. Thanks in Advance.

Hi all. I am kinda new to formulas and was hoping there was a way to make a formula that can take into account data from other tables? What I am trying to do here is assign certain data a simplified number. Example: If the number in column A from table 1 is between 1,000 and 1,999 then it is assigned ‘1’ in table 2. If the number in column A from table 1 is between 2,000 and 2,999 then it is assigned ‘2’ in table 2. So on and so forth.

Hello, I’m new to writing formulas and am having trouble figuring out how to nail this one down. I want two things to happen, the date and the status fields both to be updated if the task has not been completed on time. Any pointers? Thanks! IF the DEADLINE (February 15, 2021 format) has past AND the STATUS (single drop down select) is not COMPLETE, THEN change DEADLINE to today AND change STATUS to OVERDUE I’m thinking this could be solved in one nested formula but where would I even enter it if that’s correct?