Formulas

Hello,I'm moving into the List View Interface, and I am loving the "Prefix" field which allows me to use visual indicators to quickly see the status of records. My current roadblock is this, I haven't been able to figure out:How can I have a Single Select Field automatically assigned based on a formula?As shown in the screenshots below I have:1) Created a Single Select field that has a "Stoplight Anaylsis" of a record's status as Red, Yellow, or Green.2) I added this Single Select field as the "Prefix" in my List View Interface3) Now I want to know how to automatically assign the status based on other fields. Specifically:GREEN if {Leader} and {Client} are filledYELLOW if {Leader} AND/OR {Client} are emptyRED if {Leader} AND/OR {Client} are empty AND the date is in the Past.It's not a difficult concept, just trying to see how to implement it in Airtable.Quick Update 10/7 4:35pm EST:* I was able to generate the emojis using a formula, BUT formula fields are not available for me to

I have master blocks of code in my code table.in another table - main table -I wish to get data from a field in the code table by specifying it’s rec ID. I will then combine this code with other data from the main table. I can do this with a linked table but it requires that the user must remember to select a record every time a new record is added in the main table and since there is no default for linked record, I cannot find a way of using linked records.i was hoping to create a formula that did this get(field.codeTable(recID))I could put the actual field data from the record in code table directly into a formula but then every time my code is updated rather than update the code table code I have to remember to do this elsewhere.any help much appreciated, perhaps a script?

I am trying to create a project tracker with a task list and start dates and end dates for each task that are calculated automatically based on a dependent (linked) task and its associated dates. My problem is how to create the first row because no matter what formulas I build for the various date columns, I end up with a circular reference error. I'm expect this is possible in Airtable but I haven't figured out how to do it.I have tried adding a Manual Date column and a Calculated Date Column and then using this function for the start date of each taskIF({Previous Task},{Calculated Start Date},{Manual Start Date})But it still gives me a circular error reference.

Hi,I'm in the unfortunate situation that I seem to have an automation bug that creates unwanted changes to some records, but not all.Since I cannot on my own find out which automation causes this (mailed support regarding this), I wanted to at least identify all instances. The symptom is I end up having two different contact information for the record. Unfortunately there are no identical fields to make an easy IF (!=) between, only parts of the fields are the same (basically a name, with different stuff before and after).In Sheets/Excel this would be a VLOOKUP case, and in the automations there is 'CONTAINS' that could be used. But how could I build a formula that takes one column (in full, I was able to SUBSTITUTE it down to the exact string) and find records where the other column contains the search string?TIA,Björn

New to all of this. No coding background; small business owner. I am trying to create a rudimentary inventory system with Airtable. I can't figure out how to get a formula to "count" the number of array entries in a linked record field, and return back a simple integer value. For example, in the photo attached, the "Reserved" field should return the value of "2," since there are two entries in the linked record field. Any ideas? Thanks. 

I have been reading for hours through the help forum and documentation, but ARRAYSLICE doesn't want to work for me.I want to achieve the following: Show me the first value of an array. My formula is: ARRAYSLICE(verticals_from_contact, 1, 1) but it doesn't show me the first value of the array. Instead, the field shows me the whole array. If I change it to: ARRAYSLICE(verticals_from_contact, 2, 2) nothing is shown anymore.  I tried the formula on lookup fields, linked records, and multi-select fields, and nothing worked (see image). Does anyone have an idea what's wrong?

I have four (4) columns that are number data fields titled {Trip 1 Miles}, {Trip 2 Miles}, {Trip 3 Miles}, and {Trip 4 Miles}. The settings to the number data fields include decimal places and a screenshot of the settings are attached.Column five (5) is titled {Total Miles Traveled} which is a formula data field that SUMS the total of the number data fields listed above. THE PROBLEM TO SOLVE: I am unable to get the formula in {Total Miles Traveled} to include decimal points in the SUM total. A screenshot is attached. The formula is totaling [26.70 + 26.70 + 9.10 + 9.10] and is rounding the answer to [72] instead of the desired answer with decimal points as [71.60].

I working on a formula that will provide me with 4 different outcomes depending on a products Variance from our target cost of product/FOB. Any products that have a variance less than -5% need to output "Red -5% or More Variance", products with a variance between -1% and -4.99% need to output "Orange -1% to -4.99% Variance", products with a variance greater than -0.99% need to output "Green - Meeting or Exceeding Target", and any products that have an empty value need to return an empty value. I have figured out how to return most of these responses, but I'm hung up on how to return the empty value. Any help will be greatly appreciated! IF({P1 FOB % Variance From Target}<= -.05, "Red -5% or More Variance",IF(AND({P1 FOB % Variance From Target}>= -.0499, {P1 FOB % Variance From Target}<= -.01), "Orange -1% to -4.99% Variance",IF({P1 FOB % Variance From Target}>= -0.0099 , "Green - Meeting or Exceeding Target"))) 

I've searched on the community for solutions to combining one field that contains a date and another field with a duration to create a datetime field, but haven't come across any solutions to solve it. I'm using the DATEADD() formula but Airtable is not returning a correct results. What am I missing?

Hi everyone!I created a base for employees in our company with the table where are all information about every employee. I also created a form to update those information if necessary. I prefill all the fields and create a button field so that i can quickly open the form. But somehow that button doesn't work with some of the employee. they were added after I created this button. I attached some photos for you so that you can see what I'm talking about.Does anyone know what is the problem here?

Assume you have a total of 100 items and a multi-select field that allows you to select from a set of values. You want to find out what percentage of the total is represented by the selected values.

Hello, I am trying to calculate the difference between two dates, excluding weekends. Datediff. However, weekend is Friday and Saturday at my company. Workday doesn’t help much since it assumes weekends are Saturdays and Sundays. Your help is highly appreciated.

Hi, I'm simply trying to subtract hours in one field from hours in another. I have a column with 'hours recorded' and one with 'hours billed'. They are both formatted as time units (hours). I just want to subtract one from the other in a new field. As an additional question is there a way to set the hour units to just hours, with no minutes, at the moment it's h:mm and I need to enter a 0 for minutes each time Thanks   

Hi all - I'm having a very strange issue with a Formula field. I've got a Status field (single select) that informs a Formula field. i.e. if the Status field says ABC, then make the Formula field say XYZ. It's working perfectly except for two outputs. Now here's where it gets weird: after extensive testing, the issue seems to be with the output string, not the input. If the Status is Booking Received, the Formula field should return "Booking received, waiting for pickup confirmation" but it returns a blank field. I also can't get it to return any results when I change the formula to return my name, the word Test, or gibberish lettering. But when I type one of the other results that is working for another status, it works! Meaning if I type "Decline" in to the formula in place of "Booking received, waiting for pickup" it returns Declined. So weird, right?? Why will it only return certain other existing outputs?The same thing is happening for the "On hold" result. It will not r

Hello! I want to generate some HTML code using a Lookup function and I've been running into a weird issue. Each HTML button is defined in a table, then is combined like such in a Lookup function:<a href="/water-coaster/192/" style="text-decoration: none;" title="Go to this water coaster"> <span style="background-color: beige; color: black; padding: 10px 20px; border-radius: 50px; font-size: 16px; font-weight: bold;">Montanha Russa Aquática - Thermas dos Laranjais</span></a> , <a href="/water-coaster/281/" style="text-decoration: none;" title="Go to this water coaster"> <span style="background-color: beige; color: black; padding: 10px 20px; border-radius: 50px; font-size: 16px; font-weight: bold;">Acqua Coaster - Thermas Acqualinda</span></a>Unfortunately, there is a pesky comma that Airtable adds in between the two blocks. I tried using a second column with the formula  SUBSTITUTE(WaterCoastersManufacturerHTML,",","") This

Hi,I am trying to add cells together in order to find the line by line tracking.For example, in the image below, I am looking to add up the different profits in order to obtain the amount present in the bank account.Can you help me ?Thanks !

HiI want to search a number with a date, like this : Can you help me ?Thanks !

Hi, I'm trying (and struggling) to create a formula that allows us to filter by 'people who are currently contracted by us'. We have a start date and end date column within the table, so ideally I'd like to add a formula that allows us to filter based on whether today is in between their start and end date. Does anyone know if this is possible please? Or if there's an easier method of filtering that doesn't involve us manually ticking a box for people that are currently contracted. Thanks! 

I have two tables in the same base. Each has a field that contains a member number. However in table 1 the member number is a formula. How can I write a formula that will return a different field (member name) from the second table into a new field in the first table if the member number matches? In Excel I would use vLookup

Hello --I am interested in developing a formula to assign ID's to a series of payments based on type of payment, date, and information on payee's name. This is for tracking payments in a program that is pairing experienced mentors with mentees.There are various payment categories (Mentor Initial Payment, Mentor Final Payment, Mentee Stipend Payment), the year in which the mentorship in question started, the name of the mentor, and the name of the mentee(s) that I would like to integrate. I'm hoping to develop something where if this was a Mentor Initial Payment to John Smith for mentoring Joe Boggs and Jane Doe in 2024, based on values in the Payment Table (payment type and date), linked records Mentor table (First and Last Name of Mentor), and linked records in the mentee table (First and Last Name of Mentee or Mentees), a unique payment would auto calculate producing something like MIP_2024_JS_JBJD.The first part is obvious to me (IF function), I've looked up how to extract year

 Hey there. I came soooo close to getting a working formula for what I was trying to achieve but fell at the last hurdle.I have a date field and a number field from a lookup. I am trying to create a formula that gives me a new date based on the the date field and the plus or minus number in the lookup number field. I am using this formula currently which is working and showing the new dates correctly.DATEADD(Date, VALUE(Deadline&""),'days')  I am trying to update this formula so that it only takes into account workdays but anytime I also try to add this, I get an error in my formula field:WORKDAY(DATEADD(Date, VALUE(Deadline&""),'days'))doesnt seem to work. Wondering if someone can see where I am going wrong. Thanks in advance.

{Rate (from Contract LU)}/{Frequency (from Contract LU)}Rate is a currency field and Frequency is a number field. Both reside in the Contract LU table.I have been using the above simple formula in invoicing successfully for quite a while. I recently had to create a pro rated  invoice for the first time, so I could not use the formula as is.My approach at the moment is to create a checkbox field for pro rated invoices if it is pro rated. To use it I need to be able to write a formula that allows me to enter a pro rated amount if the pro rated checkbox is checked or to allow the original formula to continue if it is not checked.First, is this the best way to do this? Second, how do I write the new beginning of the formula to allow an input.Many thanks in advance.Scott

Hello,I've created a database with a form and applications that allow each employee to make holiday requests.They can apply for leave over two months (example: from 10/08/24 to 16/09/24).My requirement: I need to know, for each employee, how many days of holiday they have taken each month.Additional information:I have the date of the first day of leave + the date of the last day of leave + the total number of days of leave (excluding public holidays and weekends). If they only take half a day, the number of days off is 0.5.What can I do?Thank you for your help.

Please help I need a formula for reoccurring seasonal milestones.  We have seasonal launch dates and milestones in each season that occur at intervals;  -130 wks, -80 wks, -56 wks.  I want to calculate the due date of the milestone based on season launch date  & milestone -wks (-50wks) to generate the due date for that milestone.   Please help! 

So I have a single Attachments field in a table that allows for multiple individual files to be attached to a record. I want to create another field that counts the number of files that have been uploaded/attached to that record. I then want to them pull this number into the primary field of the table to provide a quick way to see how many files have been attached to the record (e.g., 5 videos, 11 screenshots). However, the built-in ‘count’ formulas seem to be only counting the occurrence of an attachment for the record, not the number of individual files within the record; so as a result, it is only showing things as having 1 or 0 attachments, even though there are multiple individual files that have been attached to that record within the Attachments field. I came across an old thread about counting attachments (for some reason it’s preventing me from including the link here) but it seems outdated given the formulas now available, plus I could not get the formula to work at all. Any