Formulas

So I am trying to create a formula that would help me out w/ some grammar issues in my Page Designer. Basically, if there is a contact in the secondary contact field named Gina Malone, I want the function to populate as “and Gina.” If the secondary contact field is blank I want the function field to remain blank. The below function does a great job of producing what I need when there is a contact in the secondary contact field, but always populates as “and” when secondary contact is blank. IF({Secondary Contact}!=BLANK(), "and “) & LEFT({Secondary Contact}, SEARCH(” ", {Secondary Contact})-1)

I am trying to do a date diff formula and i need help. Lets say it has to do with aging. Products a for a 1 week, product b for 2, etc. If I have product type in column “Product” and start date in “last active”. I need “aged” column to look at “product” to get the type know how many weeks it needs to age and do a date diff from “last active” to tell me either “within min time” or “time met”. Any ideas?

Hello, I would like to make a formula that does this: Display 1 if the item “lightyellow” was selected from the Color multiselect I see that I can use the SEARCH or FILTER functions. However, these will also evaluate to true if a non-exact-matching option was selected. For example, let’s say I have the options “yellow” and “lightyellow”. The following formula will undesirably match items that have “lightyellow” selected. SEARCH(“yellow”, {Color}) Is there nothing like this? CONTAINS(“yellow”, {Color})

Hello! What I need: For a field to update automatically depending on the number of days remaining in a clients account. For example, if the account ends at the end of the month 4/30 - I would want a “Renewals” field to say NEEDS RENEWAL in Red if the date is less than 14 days away. What I have done: I cannot figure out how to write this formula within one field. So what I have done is set up another field that puts the function - DATETIME_DIFF({End},TODAY(),‘days’) which displays the number of days until the account needs to be renewed, and then in the Renewal field, I put the statement IF({Renewal Date Parse}>14, “”, “NEEDS RENEWAL”). I would like to know if I have over complicated this and if it could be put within one field to decrease redundancy. Here is an image for concept. Thanks so much!

Hello, I am new to Airtable (AT) and I am evaluating it so as to become (or not) my mainstream application. One of the features I desperately need (and I can’t find it) is the vertical summation of records. It is a pity that the AT provides a summation indicator at the bottom ribbon of each field but this cannot be accessed or used in any way. Am I missing something? Can this be done? The second question refers to dynamic linking of fields. I want to link an entire field (with how many records it may have, i.e. linked dynamically) because there is no way I can add them manually one by one with the plus sign (they are just too many records and the number of records in the field can change any time). Is there a way around this?

I tried to post this yesterday in much more detail… but got an alert saying the post had to be approved? Not sure why, that’s never happened to me before, and it hasn’t been approved over 24 hours later… so let’s try this again! Background: I’m building a base for employee clothing orders. Based an employee’s title, they get a certain allotment of clothing. If they want to get more than their allotted amount, they are responsible for paying for the overrun. Let’s say a manager is allotted 5 sweatshirts. If the manager wants to get 7 sweatshirts, they are responsible for paying for the extra 2, and we will pay for the 5 most expensive items. What makes this tricky, is that larger sizes have an associated upcharge. Say $2.50 for an XXL, $3.50 for a 3XL, and so on. Table set up: [Clothing Standards] holds each title and their associated allotments for each type of clothing. [Items] holds all clothing items, sizes, and prices. [Clothing Orders] links to [Clothing Standards] and [Items]. He

Hi there! I’m trying to create a base to track employee clothing orders. Each employee, based on their title, gets a certain allotment of several types of clothing. For this example, say a manager is allotted 5 sweatshirts. If that manager wants to get 7 sweatshirts, they certainly can, but they would be responsible for paying for the extra two. Here’s where it gets tricky… If all sized sweatshirts were the same price, this would be pretty straight forward, but they aren’t. All sweatshirts over an XL have an upcharge (XXL adds $2.50, 3XL adds $3.50, and so on). I want to determine how much the manager would owe if we pay for the 5 most expensive items. Say the 7 values of the sweatshirts the manager chose are in an array… can I look at that array and only sum the 2 lowest value? Table set up: [Clothing Standards] determines what title gets what allotment [Items] holds all Clothing items, sizes, and prices [Clothing Orders] links to both [Clothing Standards] and [Items]. It counts how m

I have 2 date fields. A ‘Book in’ and a ‘Book out’ that logs the date and time someone shows up to work and leaves. Then I use DATETIME_DIFF({Book out}, {Book in}) to get the total hours worked. Is there a way to also calculate over hours. If working past 6pm then anything after 6 would calculate as over hours?

Hello Everyone, I am trying to apply some logic to some of my fields that get automatically filled by a web hook API by Zapier. For instance, when one of our staff submits a form, the data will be sent to Airtable and then a sequence of actions will happen. My question is this: how can I get a value of one field to change the parameters of another? I.e. if field 1 labeled “Service Type” contains Google Ads, I want field 2 labeled “Team Group” to automatically assign a tag called “Search Team”. Basically, if “Google Ads” then “Search Team”. I just don’t know how to correlate this formula because there are multiple selections for field 1 that would all need different ‘answers’ from field 2… if that makes sense. Please let me know if you can help my situation! Best M

I’m having problems suppressing extraneous comma delimiters when trying to combine multiple fields into a single column of values separated by commas, but showing no comma if any of the combined fields are blank. I tried various combinations of this formula to no avail. IF({Racer 1 Full Name},{Racer 1 Full Name})&IF({Racer 2 Full Name},", "&{Racer 2 Full Name})&IF({Racer 3 Full Name},", "&{Racer 3 Full Name})&IF({Racer 4 Full Name},", "&{Racer 4 Full Name})&IF({Racer 5 Full Name},", "&{Racer 5 Full Name}) The above formula yields the following output: The above example is trying to place 1 to 5 names in a single field separated by commas, if the name field being combined is blank, there should be no comma separator. Note, there is always a Racer 1 name so no need to suppress on that field, just on Racers 2 through 5). Thanks for any workarounds offered. Not sure if I’m missing something simple here or not and confused why the basic IF statement is not

I got a field that summarises some numbers with some text. One of the numbers needs to be rounded to a with 3 decimals so I wrap the number in ROUND(), but it only gives me a precission of 1. 'Total weight: ’ & ROUND({Total Weight},3) & ‘kg’ Gives me: ‘Total weight: 0.8kg’ But looking to get: ‘Total weight: 0.800kg’ What an I not getting?

Hi everyone! I’m creating an integrated content + social media calendar for my team. There are two tables: Editorial, for website content Social, for social content On the Social table, each record has four “Copy” text fields for each social platform. What I’d like to do is create a field which does the following: a) If any “Copy” field contains text, display the relevant social platform name. b) If the above returns multiple values, concatenate the string with ", ". I’ve tried this formula, which works for a single entry / satisfies condition a): IF({Twitter Copy} != "", "Twitter") How would I go about creating a formula which satisfies both a) and b)? Thank you very, very much in advance. :orange_heart:

I have a database of exercises and am trying to classify types of exercises. For example, Barbell Squat and Bench Press = Weights, while Running and Stair Master = Cardio. The problem that I’m having is with OR statements. I want to say something like this: IF {Exercise Name} = OR(Bench Press, Squat), “Weights”, "Cardio) I’ve tried about 100 different ways to do this and am always getting incorrect results. I’m basically trying to group exercises by muscle group so that I can graph different sets of data. Any and all help is appreciated. Thanks!

Building a budget and I’ve searched for 2 days on this formula and now realize, its time for help. I have my paycheck weeks listed but I want to group by month - I’ve added a new table labeled Month to convert (using a formula) the dates to the corresponding month. For instance… 1/4/19 would be January 2/15/19 would be February 4/5/19 April and so forth. Thank you in advance for your help.

Hi Folks, I’m creating a research repository and want the primary field to be a concatenation of the value of a particular field in a linked table. Right now, it seems, that you can only access the primary field when making these links. Is there a way the value in another one of the linked records fields to show up and be accessible? Thanks!

I am new to AirTable and am not sure how to create a formula for a specific function I need. I am using a base to track involvement of triathlon team members in our club. Each event they participate in (team workouts, races, volunteer events, etc) awards them a particular number of points… referred to as DOTS. At the end of the year, their total dot count determines the monetary reward they get to use toward new team racing gear. I created a table that includes each MEMBERS of the team. I created a table that lists each EVENT. I created a table that lists each category of event and how many DOTS are earned for that event. To use the base… first I entered all the team members’ info. Each week I enter the events that earn dots. One column allows me to chose the type of event from the EVENTS table. I also have a column where I manually enter the number of dots earned for that event. Then is the column where it connects to the MEMBERS and I choose each person that was there for that event.

Hello! Could you help me with a formula? We’d like to archive everything with a date in the past. Right now, we’re checking a box that then causes the entry to filter out. Is there a way to automate this? I was thinking an If statement (If the publication column’s date is prior to the current date, check the archive column), but I can’t figure out the exact language. Thanks!

Hello! I have a table with 4 sheets. 2-4 sheets collecting data 1-shets columns with lookup from 2-3 sheets, BUT i need latest entered data from 2-3 sheets and then calculate from columns Here is tables Airtable Customer credit limits - Airtable Explore the "Customer credit limits" base on Airtable.

Hello, for context I am creating a form that will be shared publicly, that will be shared widely, where the names of people will also be able to be seen in a public view (it is for a petition). I am afraid of people writing inappropriate/joke names and would like to catch as many as I can with an if statement that would turn them blank in this public view. I am not sure how to go about this exactly especially since I know the case matters. For instance IF(OR(Name=“Jesus christ”,Name=“Jesus”),Blank(),Name) is there a function i can use that disregards case and/or just searches the string for certain inappropriate word? Thanks

I’m trying to work out some hire prices using a formula, where the weekly cost of item is used to calculate the daily cost of it. I have an item that cost me £1000, and I have decided that the weekly hire price is this figure divided by 10, so £100 a week. The daily price is calculated by dividing that by 3, so £33.33. However, I want the spreadsheet to round this up to £33.50. How do I use the ROUND function to do this? At the moment I have: Weekly cost = {Purchase Cost}/10 Then, daily cost = {Weekly Cost}/3 For both, I want it to round to the next 50p. Thanks!

I’m doing a survey asking users to rate features on a scale of 1 - 5 using the single select data type. However, the responses are strings like Very happy, Happy, Neutral, Unhappy, and Very Unhappy. I need those values to correspond to numerical values like “Very happy = 5”.

What causes this error to happen? See first comment for the table. Formula here: IF({Premiere - HKT}="",“N/A”,DATETIME_FORMAT({Premiere - HKT}, ‘dddd’))

I recently discovered that when I multiply 6.1 * 3, and convert the product to a string using product & “” the result is a string with repeating decimals, even though the numeric result is simply 18.3. This is actually causing a serious problem in my table when formatting prices for invoices. Any insight is appreciated, thanks!

I made this formula that tells me when it has been 5 days since my last contact with a client IF(DATETIME_DIFF(TODAY(),{Last Contact},‘days’)>5,“Overdue”,“Ok for now”) and it works perfectly. There are some clients that I want to follow up with weekly, monthly, quarterly etc ! Now what I want it to do is look at my contact frequency column and if someone is tagged as weekly then say overdue after 8 days, if they are bi-biweekly say overdue in 15 days, if they monthly say overdue in 35 days etc etc. Any ideas on how I would do that.

Hi There I was hoping I could get help on linking 2-tables that are within the same base. I have a table called “List” and this table has fields which consist of PO #, Accounts & Shop Order #. On another table “Invoicing” which is populated with a variety of fields including PO #, Accounts & Shop Order #. Is there a way to link the PO #, Accounts & Shop Order # fields from the “List” table to the PO #, Accounts & Shop Order # fields on the “Invoicing” table? Basically if I populate the PO #, Accounts & Shop Order # fields in the “List” table then the PO #, Accounts & Shop Order # fields from in the “Invoicing” table will automatically populate with the same information. Is this possible? If so can someone give me directions on how to do this? Thanks so much.