Formulas

Hi Everyone, I have a base for auditing which asks if certain documents are present by linking to document records in another table. I’d like to then be able to collate a list of the missing documents (those not selected) for part of the feedback. Is there any way to do this? I’ve been going mad trying to figure it out!

What I’m looking for is to be able to send out a URL to collaborators that will bring them to the attachment view window inside Airtable so that they can view my attachment and leave comments. I have tried assigning a trigger & action in automations like this: (“Drafts” is what I named the attachment field") but all this does is open an image of the attachment in your browser. It does not open Airtable. PLEASE HELP!

Hello! This is similar to another post: Consolidated Count from another table by date field, however I am trying to group by 2 fields. Table 1 has 3 fields (Date, Shift, Count) Table 2 has 3 Fields (Date, Shift, Total Count) Table 1 has 6 records: 1/1/2020, Morning, 2 1/1/2020, Morning, 1 1/1/2020, Midday, 1 1/5/2020, Midday, 3 1/5/2020, Afternoon, 4 1/5/2020, Afternoon, 3 I want table 2 to sum up all of the counts by date and shift. So I would want table 2 to show: 1/1/2020, Morning, 3 1/1/2020, Midday, 1 1/5/2020, Midday, 3 1/5/2020, Afternoon, 7 How can I do this? I have Table 1 grouped by Date&Shift, but I was hoping to show this in a cleaner way in Table 2. Similar to

Hi I want build attendance system tracking time to work and out of work I want to use FORM where you can pick Name and slide checkbox IN. After work pick Name again and slide checkbox OUT. The problem I have is how to get the OUT record in some line where the IN record is so I can make formula and calculated the time?

A base used to inventory products. Current relevant columns are: Product Category (single select with option of Painting, Banner, Description (Formula) Product Name (text) Substrate (single select) Size These products are categorized Categories in this Column include: Banners Frames The Description Column is a formula that creates a meaning full sentenced based product description on the columns. So currently I have: {Product Category} & " " & “of” & " " & {Product Name} & " " & “printed on a” & " " & {Size} & " " & {Substrate} In this example it will output: Picture of Glacier printed on a 16 x 20 hardboard gatorfoam What I need is an IF statement in which if the product category is “Painting” the output will have the words “Printed On” as shown above otherwise this word is

Hi everyone! I have a work week that operates from Monday-Friday (24 hours). I am trying to develop a formula that would show the total hours remaining from the current time until the week closes each Friday at 11:59PM. This would have to reset each week. So far I have FORMULA: DATETIME_DIFF(NOW(), {FORMULA NEEDED}, “hours”) This is a part of a much bigger formula. Thanks for your help!

Hi, I would like to use the column number in a view in a formula. Example : If a record is listed as 5th is a view, I would like to use the number 5 in a formula. Is there an internal field giving me this number ? PS If this exists, is there a list of internal fields that can be used ? like RECORD_ID, …

I have three tables, Properties, Contacts, and Companies. Each table has a Last Unsuccessful and Last Successful contact field. Sometimes I have contact information on one or multiple of these tables (i.e. if a company owns multiple properties then I’ll create the linked relationship). I need a way to find the maximum date of when I last spoke to someone, whether I’m looking at either of the tables. On the table below I have two lookup fields and a natural date field for both successful and unsuccessful contacts. I referred to the thread below but not sure why I am getting an error.

I would like to create a formulas to add the specific school year if the date in the column “DATE” is between DATE1 and DATE2 For example: IF date is 5/20/2021 THEN in the column school_year I will have 2020-21 IF date is 9/20/2020 THEN in the column school_year I will have 2020-21 IF date is 9/20/2021 THEN in the column school_year I will have 2021-22 What formulas should I use ?

Hi everyone - I’m still very new to Airtable and ran into a formula error that I’m trying to solve. Ideally, we would like to group tasks by the week they are due. I was able to find the formula to output the week number but can’t seem to find one that works that will produce the following output: “Week of 06/21”. I did find the following two formulas from a previous post: Display the "week of" for a given date starting with Monday CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’)) DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’)) However, when I go to add the formula to my calculated field it returns an error. Could it be because my {Due Date} field is calculated as well? This is the formula that I am using: CONCATENATE(“Wee

Hi Airtable Community! In my Airtable base, I’ve got two main tables: one for stories written by kids, and another for notes that volunteers write to the authors. These tables are related through a one-to-many linked record setup: there can be multiple notes for a story, but not multiple stories for a note. I’m trying to make two rollup fields: one that displays the text of the first two notes a story receives and one that displays the text of every note after the first two. I’d like to email notes back to authors after they have two notes and then again when they’ve accumulated several more, and would use each rollup field as part of the automation for the email body. The additional wrinkle is that the rollup should only count notes that haven’t been flagged (and therefore shouldn’t be returned), and there are handwritten notes too. My thought was to add an Autonumber field in the notes table–each note will have to have a higher number than the last. Then, the

Hi Community, I have a cruddy report that cannot give me the data i need which forces me to calculate the number of days a person has been helping our organization and I was hoping there might be a formula that can help automate the calculation a bit, but I cannot wrap my head around it (terrible with IF formulas). The report is run once per month and will produce 4 different combinations of dates for the in and out field. Combination 1, OUT date is prior to the start of the month, and IN date is after the last date of the reporting month OR blank . This calculation would then be the same as the number of days in the month. Example for May (OUT is 4/20/21 and IN is 6/5/21 or blank, would = 31 days) Combination 2, OUT date is prior to the start of the month, and IN date is within the month of reporting. This calculation would

Hi everybody, I’d like to check if an attachement field is empty or not. Someone knows how to do this ? Tanks a lot. Fred

Hi there, Can anyone please help me convert the date in this format, Fri Jan 13 2017 00:00:00 GMT+0000 (Coordinated Universal Time) to this 01/13/2017 format? Thank you

Hello, I am using integromat and i want to “Search Record” based on “Parent model” and “Map years”. So i want to achieve Parent model equals Accord AND Map years contains 2012

I am new to Airtable. I am building a tool to collect behavior incidents in the school where I am Principal. After testing multiple tool, I came to the conclusion that I need to create my own application and Airtable expends the possibilities. Here is what I am trying to achieve: In a first tab called INCIDENT I can create incident record with one field being the TYPE of incident. This tab is using variables located in an other tab named TYPES. On this tab TYPES, I have two information: the name of the type (bullying, cheating, etc…) and the point associated to the incident (a number value). When a type or multiple types of incidents are selected, it shows the point on the next column. If two types are selected it will show point 1, point 2. On the next column I have created a formula to add the points. In one of the column I have a drop down menu which can select the name of the students responsible for the observed misbehavior. This f

I need help with an IF formula. I have a roll-up field that sometimes has 1 number and sometimes 2 or more numbers separated by commas. I have a formula that removes everything after the first comma but if there are no commas the formula field is empty.

I pull in data via CSV files (28 of them) every quarter. The “FilingID” field is always a unique number. The “DateSubmitted” field corresponds to the FilingID as the higher the number the more recent for a given Company (1B1) field. and the “IE!” field is always the same for a given company. What I am looking for is a formula or script that when a record with a higher numbered FilingID or DateSubmitted (whichever is easier) is added any record with the highest FilingID for a specific company (1B1) or (1E1) is updated to a “Current” status and any existing (older) record is changed to a “past” status. I really only need this for 1 table. Any suggestions would be appreciated.

Hi, I have a situation that I am stumped on how to handle. I have invoices that we track payments against. We use four fields to do this: “Invoices”, “Current Payment”, “Paid To Date”, and “Balance Due”. Here is where I need help: We report weekly on what we collected to management. When we get just one payment that zeros out the invoice as paid, this works fine and gives me the data I am looking for. It is when we have progress payments (and not just one payment for the entire amount) that I need to figure out. To complicate things, many times we get multiple progress payments, and we never know how many payments we are going to receive (usually ranges between 1 and 6 - the joys of being an insurance restoration contractor!). I want to be able to take the “Current Payment” and copy it into another field so I can report on it as collected during this period. I then want to be able to add that to the existing “Paid to Date” figure so I have an accurate “

TLDR; From an “events” table that hosts records from different events I need to determine the highest value in two columns grouping by an event_id. So being events referring to football matches, the highest value would correspond to the last record with source_id 't3_nk5nz4'. What I want to achieve A single row with a coulmn that shows what which is winner team or it’s a draw. What I’ve tried Database view Grouping Formula Linking tables records

Hello, New to airTable and I love it. In setting up a database, I am running into a problem. Links from a long text string have to be replaced with new links. Is there a formula that an do this or does this have to be done manually? Here’s the text: <h4>UNIT OVERVIEW</h4><p><a href="https://apostolicfaithchurch.box.com/s/q77dw02m7v5mu11fg301ixmtbni56ebb">Men Who Trusted God</a><br></p><h4>MEMORY VERSE IN ACTION</h4><p>WHOSO — Point to others.</p><p>PUTTETH HIS TRUST — At eye-level, hold fists on top of each other as though holding to an imaginary rope.</p><p>IN THE LORD — Make an L with thumb and first finger of the right hand. Touch thumb to left shoulder, bring across in front of body touching right waist.</p><p>SHALL BE SAFE — wipe sweat from forehead as though just having a close call with danger.<br></p><h4>MEMORY VERSE VISUALIZED</h4><p

We´re a learn platform with memberships. Each mempberships has its values in numbers. I need to replace/substitute those values into another values. This is what I want: 9=1 19=2 39=4 99=11 This is my formula: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({FOX XP AMOUNT},“9”,“1”),“19”,“2”),“39”,“4”),“99”,“11”) This is what I got: I think I need a solution to get the Formula know or read two digit numbers together. But I don´t know how. Would love if somebody could help me please.

hello guys!. I have an apparently simple situation to solve, but I’m still not used to the airtable formulas. basically I have a list of products and a product with the name “continue” which is an option that follows the flow of the system I am integrating with airtable. All products contact the Values ​​column. however I wanted the product “continue” not to concatenate. is it possible to insert this condition in the formula? The formula I’m using is: CONCATENATE(Products," ", “R$”, Value)

How can I calculate the # of years of service someone will have worked on their (future) anniversary date? The best I can come up with is # years they’ve worked as of today, but I want to list upcoming anniversaries and their years of service on those dates.

Hi there, I have a table with a single select field type called PROGRAM TYPE. It contains 3 different program types, which I’ll just call PROGRAM A, PROGRAM B, PROGRAM C. I also have a Date Joined field, and I’m creating a new date field called PROGRAM EXPIRES. PROGRAM A expires 12 months after its ‘Date Joined’ date, and PROGRAM B expires 3 months after its ‘Date Joined’ date. PROGRAM C does not need an expiration date and can be an empty string, or by default be set to 12 months as well. When I try: IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,‘months’), DATEADD({Date Joined}, 3,‘months’)) I get an error. I’ve also tried: IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,'months'), IF({PROGRAM TYPE}="PROGRAM B",DATEADD({Date Joined}, 3,‘months'), IF({PROGRAM TYPE}="PROGRAM C", DATEADD({Date Joined}, 12,'months'))) And still get an error. I’ve made sure all spaces/quotations