Formulas

There are 2 fields: the time of the last modification of the line and just a number. (last_modified_time and time) How do I create a formula for the “result” field to add the minutes from the last_modified_time field and the number from the “time” field? That is, the example should get: 38 + 100 = 138 (the field “result” should be 138) (101 is there for an example, do not pay attention. Instead it should be 138)

Hello, Quick question! I have a table with recurring tasks. Every week I have to redo these same tasks. I know the progress of my task thanks to “Multiple Select” (Ex: In progress, to be done…) but I would like this column to be reset at the beginning of each week. Is it possible to create a formula to set the status of these tasks to “To do” every Monday? Thanks for your help !

Hey Community, I’ve made this on Gsheet and I would like to do it on Airtable. Any idea? Thanks!!

Hello, please dont be too harsh with me I am starting with this and have a simple question I cannot solve by myself though trying different combinations of formulas. I have 3 fields : possibleloan1, possibleloan2, loanoutput. I want a formula that defines loanoutput. Loanoutput is possibleloan1 if possibleloan1 is greater or equal possibleloan1. if possibleloan1 is minor possibleloan2 , loanoutput should be possibleloan2. I tried for loanoutput: IF(possibleloan1 > possibleloan2, TRUE(possibleloan1), FALSE(possibleloan2)) Do you know what I did wrong? Airtable is not accepting the formula itself though I wrote it in accordance to the guidelines. Thanks in advance!

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 take the re

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” Seems complex but I’m sure it is possible, so I’d appreciated any help greatly. Thanks in advance, Otto

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 Name}, {Employee 2

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”," " ) &" Item Number " &Filenum &" Original Cost " &OrigCost Here then is a slink to a listing done “by hand”. NO I’m not trying to sell you something!!

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: Thank you! jen

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 registered under that ID. I am trying to use a Lookup field. The idea is to retrive just the latest Score. But it doesn’t work and it brings up all the Scores a

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” I’ve been trying to understand other answered inquiries that was posted here, but my knowledge of setting up the logic statements for formulas is very elementary

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?