Formulas

Hello! I am trying to create an auto-generated field consisting of a single client name and a code assigned by me (e.g. “Client Name - Billing Code”). There can be multiple clients per billing code and currently, I supply the billing code in a separate field. My current formula is: IF({Client Last Name}="", Company,{Client Last Name}) & " - " & {Billing Code}. It is continually returning “SmithSmith - B01” rather than “Smith - B01.” My question is whether there is a function that allows me to limit to only a single client record despite the Client field being a multiple record field? If there is no function, is there a practical workaround? Thanks!

I have a table where users submit events that happen on a weekly and monthly basis. For example: a yoga class every Saturday at 9am or a group run the first Sunday of every month. I don’t want to add a ton of records of that same event but would like the original date field (Event Date) to be updated so that it populates the next coming date. So process would be: Event is submitted Event Date is populated with first class date ex: 5/20/20 After the first class date passes, it is updated to the next coming date: 5/27/20 Is there a formula that’ll auto-update that Event Date field? Or at least spit out that new date in a new formula field?

Hi all, New to Airtable and by no means a whizz when it comes to formulas. In short, I need to create a formula that will return a date of the 1st day of last month. In other words, today (May 21st), the field should show a date of April 1st 2020. As soon as we hit June, that date should change to May 1st 2020. Any idea how I could do this? Thanks all…

Hello all, I have question regarding a formula for the marking sheet for my university module: I want to compare my students’ final numerical grade (titled ‘total_grade_point’ field in image one) with a range in an adjacent conversion table. The second table contains a numerical range (image two), and I want to get the final string for my student’s grade. Basically, if you follow the images, I want to contrast my students’ grade point with the ranges in the conversion table, and then yield the grade string (zero, low fail, etc.). I hope this is somewhat clear. Thank you all for your help! :pray: Best, Manuel

Hi, is there a way to compare strings that include same word(or part of the word), but not the whole phrase?

Hi everyone, I’m building my CRM and I have two tables, one is the “Account” and the other “Register”. The client fills the form that it’s linked to the “Register” table, the account manager, based on the information provided by the client approves or not the client turning the Checkbox to 1. I want to create a new row for that client in the table “Account” when the account manager change the checkbox to 1. Can you guys help me? Or maybe I am overcomplicating everything. Thank you all! Best, Luis

Hello, I am building a form for the sales people in my organization. The idea is they will not have to actually login to Airtable put will just put all the information into this form and then the admins can keep track and do the work on the back end. Currently I have it worked out that when you select a contact (prospective client) it populates with their name, email and phone number thanks to a nifty formula! We also have an application form that needs to prepopulate as well. This just a pdf attachment we have added to their ‘contact’ file. Does anyone know a work around or way to prepopulate an attachment to a form by linking it to a table? Or a formula to add the attachement to the primary field? Thank you!

Hi! I’m trying to set up a CRM for some of our sales people and I’d like to auto-populate a ‘next step’ due date based on the selection they make for their ‘next step’. For example, if they select their next step as ‘Send follow-up email’, I want the due date to auto-populate for 2 weeks from when they entered the information. Or if they select their next step as ‘Place follow-up phone call’ I want the due date to instead auto-populate as 3 days from when they entered the information. Is this possible at all with a formula or would this require an external zap maybe in Zapier? Thanks in advance!

Hey there, I have created single drop down statuses for my projects I’m working on (In Progress, Not Started, Recently Completed, Roadblocked, and Archived). I’d like to create a formula that says "If the project status is anything other than Archived and Recently Completed, AND has a past Target Completion Date that’s before today, status is: PAST DUE. Otherwise, ON TRACK. Ideally the way I’d like this to show up is to have another column with PAST DUE and ON TRACK options in a single select drop down, and depending on what the formula says, the drop down option changes. Is that possible? Thank you!

Hi everyone, I’m trying to use formular HOUR（）to display my Time as a number. But I keep getting wrong result. Normally, if the time is 18:30, the number from HOUR() should be 18, but I get 9.0. Could someone help me to have a look if my formular is wrong? Cheers

Hey, I have a form and I wish to auto-fill a field with content in another table. It’s already linked to another table and I want to have a specific info auto-filled. How it’s done right? Thnaks

Hi there, If I have a formula that uses values from a lookup field (which has more than one value from another table), the formula doesn’t recognize or include the commas from the lookup fields. Does anyhow know the solution? I’m trying to get to a Unique array formula which won’t be possible if my values aren’t separated by a comma. Thanks!

Hi, I want to how can i get remaining salary when employee I absent few days and from total salary the days he is absent should be excluded and formula gives me answer. Below are 3 tabs from which I need formula to be completed. Please help me.! @Gareth_Pronovost , W_Vann_Hall anyone please

Hello, I’m trying to subtract a specific time (3:30 hours) from a duration field using formula that looks like this: Difference field = IF({Ride duration}=3:30,0,({Ride duration}-3:30)) Basically, I want to subtract 3:30 hours from the duration field to know the difference between the times What am I missing here? Thanks in advance, Liat :slightly_smiling_face:

I need to write a formula that tracks our uniform distribution and cost. Here’s what it needs to say: We have 6 types of shirts: Newbie Shirt ($18) Gorilla Gang Shirt $26 Macaw Shirt $26 Rooster Shirt $26 Champion Shirt $26 Executive Shirt $26 Hat: $17 I need a formula that tells me based on the type of shirt they have * the qty based on the cost of the shirt what the total comes to. Let me know if you are able to write this and if you need any additonal information.

I know that Airtable has recently changed a few things and as someone who struggles to remember the technicalities of all of this, I just want to make sure that I am not doing any more work than I need to. Or maybe the update has made it simpler as it has for other things! What I want to do is populate fields from another table into a single cell, one on each line. This has multiple purposes, though right now I am trying to display to-do ID’s one at a time so my brain can actually grasp it, and then make a makeshift-dashboard displaying multiple categories in one place. Another purpose this has is when I do documentation I want to list goals we addressed and have it listed on a different line to, again, make it easy to read. I currently have a link to the table, an “array join” rollup, and then a formula to substitute the commas for new lines. This has the negative effect of creating a list, but having the first be one space off of the rest of the lines, which looks BA

Hi In Excel I can put formula in one field which will calculate Sum(all fields in the column)/Sum(all fields in another column)*100= percentage How can I do it in Airtable for current and future records? I need just one value to gauge progress.

Hi :slightly_smiling_face: I want to create formula that will return true if today is after date or s within two dates. IF(AND(NOW()>{Check-in},NOW()<{Check-out}),“NOW”,"") Above is the formula that does not work for me, it always returns false. I would appreciate some help. Thanks

I consider myself a fairly adept user of Airtable, but I think this question is going to say otherwise. My objective is to see this (the red dollar sign represents my Job Cost field): So on a calendar view, I want to see a concatenation of the Company Name, Contact Name and the Job Cost. The Company and Contact are two separate linked tables, and work fine in the primary field using the following code: {Link to Companies}&" | "&{Link to Contacts} But my Job Cost field is a Lookup from another tab called Proposals. My modified code of: works fine in any field/column other than the primary field/column: When I add the

I’m trying to put together a quick gardening database app that allows me to pull together information about a particular plant, including what companion plants it grows well with and what should be avoided. I have a list of “companions” and “antagonists” for each record, but I can’t see a way to make those relationships reciprocal. For instance, if carrots have a companion of tomatoes, then it follows that tomatoes should automatically inherit the companion of carrots. It seems like this is going to require a script, but I don’t know enough about scripting in Airtable to start from scratch, and I can’t find examples of people doing exactly this. If anyone knows of any other ways to handle this or a script I could use as a base, I’d be much obliged. Thanks, Ryan

Hi all, This may be a quick fix that I’m missing but figured I’d ask the community for guidance here: I have a base with 8 tables. On 1 master table, I have a primary field (auto-numbered) and 7 additional fields pulling in information from linked records across the other tables. Each record on this master table is only linked to 2-3 other tables, so the majority of the Linked column fields are blank - making it a pain to see all of the linked information quickly when expanding the record (I have to scroll passed a bunch of empty fields). I’m wondering if there is an easy way (using either a formula or rollup field) to create an additional column on this master table that searches the other 7 fields and if there is a linked record, pulls it in to this field. In a perfect world, the information being pulled in wouldn’t just be the text of the linked record but rather the actual link itself - essentially just combining all linked items across the diffe

I have rate fields low, mid, high. The rate fields are lookups - formatted as decimals. Then I have a formula field IF(rate = “low”, {low rate}, IF(rate = “mid”, {mid rate}, IF(rate=“high”, {high rate},""))) which results in the correct number, but the formula will not allow me to format as a number. What am I overlooking? Thanks!

With the new conditions formula I am excited! However, now that I have split up my columns to have only the selections the previous question asked, I need to get them all combined into one column again to upload. For example I do kid’s clothing so they choose infant, toddler, little kids, and big kids category then they only have the options for that size in that selection. However I now need to combine the selections in one column to upload to a shopping cart system. They are NOT all numbers. Some might have 2T… Is there a formula for this? Thanks in advance for posting your question! When someone provides an answer, remember to mark their reply as the :white_check_mark: solution. Please delete this text before writing your topic.

I am trying to understand a why a simple MOD function isn’t returning the correct answer. Airtable

Hi All, I have question regarding creating a 3rd column by comparing following two columns; I want to have a 3rd column which shows what is the common item for the both first two columns. So if we take above image as the example, in the new column it should only show “Knitting” on 3rd row and leaving other rows empty. In this scenario there is only one value (comma separated) on first column, but in can have multiple values in some cases. However it will only have one common phrase for a given row. Your help will be much appreciated :grinning_face_with_big_eyes: Thank you in advance :pray: -Eranga Perera