Formulas

I am using the CRM template as a base for a more robust CRM solution. I’m trying to create a field for the most recent conversation note from the most recent interaction. There is a field in the main “CONTACTS” table titled “Interactions” that links to an “INTERACTIONS” table which includes “Date” “Conversation Notes”, and “Contact”. So the Interactions Field in the Contacts table lists dates for each of the calls/interactions with the corresponding contact. I also have a field with the date of the latest interaction. But I’m trying to pull the Conversation Notes from the most recent interaction into a field for each contact in the CONTACTS table. I’m fairly new to Airtable and at my wit’s end. Any and all help is appreciated.

Hello. I need to add and subtract items that are not numbers nor dates. How can I do it? To illustrate it, I have changing groups of people working on projects, so I have a first column for people originally assigned, then a second for people who left in the middle of the project and a third one for people assigned also in the middle: [A] Original composition: “John” “Jack” [B] Removed: “John” [C] Added: “Jill” (All above are linked to the ‘People’ table.) I would like to have a [D] Current column where I can get “Jack” “Jill” as result of a formula, which should essentially work as D = A - B + C, i.e., D should contain items in either A or C but not B. How can that be done, please?

Hello, I have an attachment column with mp3 files. I need to know the length of each mp3 file. Is there a way to do this by using a formula? The API does not provide anything I can use to this. Best, Kim

As you can tell by my question, I am new to Airtable :slightly_smiling_face: How do I rewrite this formula so it does not result in …#ERROR when I don’t have dates in some the cells used in the calculation? I would rather have that cell remain blank until I have all my dates entered into my table. Thank you! DATEADD({Speaking Date},-1,‘days’)

Here is my current formula: IF(Store=“A”,(({Price}/100)*10)) This formula gives me the calculation of 10% of the figure in the Price column if the store in the Store column is A. So far so good. But how do I add store B to that formula? Say I have store A, B, C, D etc but I only want the formula applied if the store is A or B in the Store column? Thanks in advance.

Hi, I am creating a base for a bar which has registered members coming in to order drinks every evening. I have created a table for recording the various members’ tabs. This tab has to record the details of each member’s drinks consumed/ordered on that particular day. My challenge now is effectively recording the quantity of the drinks. Especially when a member orders the same drink more than once. The drinks are listed on a separate table which is connected to the ‘Tab’ table through a link field. Now when a particular drink is selected once, it cannot be selected again. Also, in the event that the buyer orders more than one type of drink, recording the quantity bought for each type is proving hard to do. Any thoughts as to how to go about it?

I’m looking to generate a formula that lists the date a record is moved to a specific selection from a single select field. Our company does video production and I want to track the actual dates projects are moved to a particular stage like “delivered” or “rough cut”. Currently, I have to go inside each record and look at the activity tracker. There must be a better way without creating another field. Thanks!

Hello, I have a number with decimal (for exemple 12.5), I want 12.90. The field is a number field. Exemple : 25.5 > 25.90 18 > 18.90 Thx for your help

I set up a database to help me organize battery cells and figure out which are good and bad. I have a table for each make & model’s rated specs, a table with the results for the each battery cell’s capacity test (each test is a record and a battery cell can have multiple tests/records) and a table for each battery cells’ details & summary. In the battery cells detail table I want a field for each battery cell that is populated with the average result of all it’s capacity tests from the capacity tests table. How do I achieve this? P.S. - The value in question from the capacity test table is a number/integer (i.e., 2400).

Hello ! I made a formula to get all the holidays for the current year automatically and I can’t use that value directly with WORKDAY_DIFF in another formula. I have to copy and paste. If anyone has the answer I would love to have it, thanks a lot !

Hi :wave: If anyone can help with button field… In a single click I want to achieve to trigger a webhook and open different url. I manage to have button trigger Integromat webhook, it successfully opens url saying “Accepted”. Now, is it possible to open different url(google docks) in a same click after the webhook is triggered. Some sort of redirect or something… Is there a way to do this? Thank you for your help, Pjero

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!