Formulas

I’m working on setting up a daybook for my public radio station, but I’d also like the daybook to pump out FCC logs with certain columns to streamline the process. Is there a way to generate a duration based on the media length posted in another column? For example, if I post a spot that is 1:32 long, could a separate duration field be populated with 1:32? Thank you in advance for any help on this.

I’ve got a lot of data (~75k records) that won’t fit into a single table, so I need to separate it out into multiple tables. It’s basic contact info (name, address, city, state, zip, phone, etc). We’re doing a zip-code search on the front-end (via the API), so with a limited data set (smaller than the final batch of data), it’s easy enough to query the zip field for the passed value. I was figuring I could break it out into states as that’s an easy & logical breakdown. However, now that I need to break this out into multiple tables, I don’t want to search ALL of the tables for that single value. Is there a way to create some kind of lookup table that would accomplish this? I imagine something where each record is a pointer to the other tables, along with say, min & max values from that table (eg. min zip & max zip)? This way I can search the lookup table for the row whose min is less than the desired zip AND whose max is greater than the desired zip. Then, I can t

I’m trying to make a meal-planning table that will produce a sheet like this: Right now I’ve got this: What is the best way to efficiently get columns for each ingredient, which will populate with quantities (I have them broken down in another sheet) if that ingredient is listed for that recipe? Heads up, I’m no good at coding. I’ve copied and pasted the exact airtable instructions into the scripting block with no success. Thanks!

Hi everyone, quite new to Airtable and being very impressed so far, I’m currently underway managing more complex formulas. Still, what I’d like to accomplish for my task management table is too overwhelming so I ask for a little help :). I have “Do” dates as well as “Deadline” dates; every task has a “Do” date but not necessarily a “Deadline” date. “Deadline” Dates are only relevant to me if they’re overdue. . Here’s what I’d like to get in the “Status” column to be able to group accordingly: If Deadline is before yesterday, show “Overdue X days” If Do is before yesterday AND Deadline is blank, show “X days ago” If Do OR Deadline is yesterday, show “Yesterday” If Do OR Deadline is today, show “Today” If Do is tomorrow, show “Tomorrow” If Do is further in the future, show “in X days” Seem

Hi! I need help figuring out a formula that will do a rollup of a horizontal range in an Asset database. SUMIF would be a simple solution if I were using excel/gsheets. Creating a separate table to do a vertical rollup is not an ideal solution, but I’d settle if it is the only way. If anyone can help point me in the right direction, I’d appreciate it! Here’s a sample set of my table fields: [Record 1], {Employee 1 Name}, {Employee 1 Affiliation}, {Employee 1 Allocation%}, {Employee 2 Name}, {Employee 2 Affiliation}, {Employee 2 Allocation %}… {Employee 6 Name}, {Employee 6 Affiliation}, {Employee 6 Allocation %}, {Affiliation Sum}; [Record 2], {Employee 1 Name}, {Employee 1 Affiliation}, {Employee 1 Allocation%}, {Employee 2 Name}, {Employee 2 Affiliation}, {Employee 2 Allocation %}… {Employee 6 Name}, {Employee 6 Affiliation}, {Employee 6 Allocation %}, {Affiliation Sum}; [Record 3], {Employee 1 Name}, {Employee 1 Affiliation}, {Employee 1 Allocation%}, {Employee 2 N

Is it possible to create a formula that sums values in other rows based on a filter with the current row’s other column value? For example, let’s say I have 3 columns: Topic (Primary Field) Owner: Person object (other table) or email address Score: Integer field I want to calculate the sum of all Score fields where the Owner is the same. From a table perspective, I was thinking about adding another column with a function like the following: SELECT SUM(Score) WHERE Owner = this.Owner This will result in the same value for all rows where Owner is the same, but I’m okay with that. What’s the best way to accomplish something like this?

Hello, I automatically create invoices based on the data in my database. Ideally, each line comes with an invoice number that I have to add manually based on the previous line. Is there a function that allows you to increment this data and add it automatically at the creation of each new line? I would like to reduce the number of manipulations and the risk of possible errors. Ex: Line 1: Invoice number: 207. Line 2 : Invoice number: 208 and so on and so forth. Also, some data is recurrent, such as the Logo. And I wanted to know if instead of manually copying and pasting each time, there was a function to add it each time? Thanks !

In my base, I have a column called Authors. I would like to make a formula so if the Authors field has “Hoch” in it, it will say Author, and if it doesn’t, it will say Not Author. How do I do this?

Hi! I have 2 tables - one with a long list of individual clients, and another listing “teams”, which the clients belong to. The client table has 2 fields: “Team” & “meal preference”. I want to summarise the meal preference data in the Team table table, organised to show the total number of each meal preference for each team. I have set up a pivot table block, which does summarise this, but I don’t know how I can pul this info into the “Team” table. Any assistance greatly appreciated!

Hey all, Just getting to play around with the new button field (it’s awesome) and had a quick question - any idea if it’s possible pull multiple emails into the “To” section of the email? To explain further, I have two 2 tables within a base: 1 is locations and 1 is contacts. On the locations table, each location has multiple contacts linked it. I’m looking to build a button that when clicked, would include all of the contacts associated to that location into the “To” section of an email. Appreciate any and all help! Noah

HI all, I am trying to use a base to “build” and format HTML that can be copy/pasted into an E-bay description page. This would then save me tons of time and give me unity in my listings. I can get output as simple text, but line breaks, bold font and even color formatting eludes me. Here is a the current “formula” , I would like (ex) TITLE to be 14pt and BOLD when deposited in the listing (see bottom) TRIM(TITLE) &"-" &GUAGE &"-" &COLORQLTY &" " &COLOR &"-" &TRT &“min " &DESCRIP &” Unfortunately if the film does not have a synopsis in the can. Please search the web. " &VS &" V.S. " &STORAGE &" " &CONDITIONOLD &" Overall condition-" &CONDNOTE & Wear & " wear " & IF({Teach Guide}=1,“Comes with Teacher’s guide”," " ) &" I

I have a field with a value "feather29sum"cxa. I need to extract the feather29sum only and ignore everything else. Can someone please help with this formula?

Hi there! I’m trying to write a formula that will pull JUST the image url into my column. Not all of this add’l info:

Hi everyone, we are building a little CRM inside Airtable and I am trying to do something that seems to be almost impossible. Perhaps I am just not seeing it. So, would love some help to get this going: Setup: One table called Customers Another table called “Health Score Reviews” The first table is self-explanatory. The second one, has a series os “log entries” for every time someone from our Customer Success team interacts with a customer. In that interaction, they can add a SCORE to the interaction from 1 to 3. Objective: We want to show the LATEST Health Score for that customer in the Customers table. The first part I was able to do, which was to retrieve the max(id) for the Customer Health entries and pul that number to the Customers table. The problem is that I want to Lookup in another column what was the Score that was

Is it possible to only download a group of records and not the entire record table?

Is there a way to count the number of times a duplicate name appears in one table and display that number in another table? For example, Minnie Mouse’s name appears 3 times in the “Billing” table and I want to output the number 3 under a column for Minnie Mouse in the “Child” table. The way my data is set up is I have 2 tables, one for “Child” and another for “Billing”. The “Billing” table keeps track of all the client with the months we billed them for, their name will appear multiple times corresponding to the month he/she was billed. This duplication is how our biller kept track of the data, by having a Jan, Feb, Mar, etc tabs in excel and the client’s name being inputted in the corresponding month tab. The “Child” table has a different set of data that can’t be combined with “Billing”

Hi everyone. I’m trying to extract a string that occurs between the same left string, but the right string has 2 possibilities The bolded string is what I’d like to extract VAR. 1 - between > and > Industry > Banking & Finance > Trading (1.0) VAR. 2 - between > and ( Industry > Banking & Finance (1.0) Thanks a ton for your help. Subir

I’ve a linked field that I want to compare to a lookup field. If any of the linked records in the linked field appears in the Lookup field I want to generate an error of some kind. Linked field: Wishlist (Internal) (link to toy table) Lookup field: Toys (lookup toy table) Formula that I used: IF(FIND({Wishlist (Internal)},{Toys})<2, “ :white_check_mark: Wishlist updated​:white_check_mark:”, “ :stop_sign: Wishlist has given toys​:stop_sign:”) It works if there’s only 1 linked record in the “Wishlist (Internal)” But it doesn’t work for multiple records. Is there any other way to do this?

Hi there, I wanted to create a formula for a field with 15 variables. See table below. Basically, is % complete + status is what’s on the corresponding column, it updates the “new stat” field. Tried using this, but wonder if there’s a more efficient way than using ‘IF’ statement 15 times. IF(AND({% Complete}=0, Stat=‘red’), ‘red’, IF(AND({% Complete}=0,Stat=‘white’), ‘white’))

Hi! A while ago I asked for help to concatenate several values together (kids’ ages) and the following was suggested by Justin_Barrett, which I am using now, thank you Justin. SUBSTITUTE( TRIM({K1 age} & IF({K2 age}, " " & {K2 age}) & IF({K3 age}, " " & {K3 age}) & IF({K4 age}, " " & {K4 age}) & IF({K5 age}, " " & {K5 age})) , " ", ", " ) However, it doesn’t seem to allow for a “0” value. Can someone advise why and/or how to modify this formula to allow for the “0” value?

We are using a form to accept and manage participant registrations for various groups (we call them Circles) we are offering (and managing through Airtable). The participant registration feeds a Participant table to collect all registrants. Each registrant can select multiple groups. The groups are provided on and linked to another table on the base. Each group has a different maximum number of people that they can accept in their group. I’d like to create a formula to use that when the maximum number of registrants for that group has been met, it will automatically move the participant record from the accepted participant record type (column) in the Circles table (our group table) to a waitlist record type (column) for that group. Here’s the form --> https://airtable.com/shrVhQh9RC40kuqhj

I have an attachement link that has a bunch of attachments but every now and then one of them looks like the below, but works when clicked. It seems to usually be .pdf’s. Any way to avoid this?

I need some help editing my original formula which is: IF(IS_BEFORE({Actual Site Walk}, TODAY()), “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Forecasted Site Walk}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: In-progress​:running_man:”)) I’d like to add/change the formula to show “DUE SOON” for dates in the Forecasted Site Walk field that are within the next 14 days, but keep the rest as well. How do I do that? Thanks!

I am using the AND() formula to apply multiple filters through filterByFormula query in the API via Adalo. Each formula within the AND function works independently, but when combined, neither works. There is a discussion open on the Adalo forum with details: Adalo – 22 Jul 20 Filter (airtable populated) list by search term? @bgschust Try to change from double quotes “ to s