Formulas

Hey all! Got a question for y’all: Say I’ve got three tables. Albums Songs on each album (each song has an environment attached) Environments (this is a finite list of pictures with name labels that are used for reference in the ‘Song on each album’ table) In the album tab, I’m looking to create a field that lists/combines the environment used from the first and last track of each album to send out for reference. The first and last track is always called ‘FT’ or ‘LT’ in the ‘Songs on each album’ table. Any insight here? Found this, but it looks like more of a lateral way to do combinations (and it doesn’t have to be pictures – can just be the names of the environments/pictures, if that makes sense).

Hi There. I have a table for capturing fuel fill ups. In one column we have the opening ODO balance, the other is closing ODO balance. Closing Balance is what will be inputted by the user, the opening balance should automatically then become the same value as the closing balance. Perhaps this image might be a bit more clear of what i want to achieve. Apologies if the explanation is not clear

Hello! I’m populating my base with data from an external API and would like to update it regularly as new results come in without creating new duplicate records. I’ve read a couple of other topics that discuss that (like this one and this one), but am still struggling to figure it out. I’m using the [uri] field in the base records and trying to match them to the array of [uri]s from the api. If there’s a match, the record would update, and if not, a new one would be created. FWIW my knowledge of js is survivalist at best! Here’s what I have so far: // The loop for each result in the API for(let i = 0, l = data.results.length; i < l; i++) { // ids for current records let table = base.getTable("Observation"); let queryResult = await table.selectRecordsAsync(); const records = queryResult.records; for(let i = 0, r = queryResult.records.length; i < r; i++) { // grab cell value uri for reference against the api let record = queryResult.records[i];

I have this formula. If there is no date, the formula errors out. I need it to be the exact same formula but if the date field is blank, it still fills in all the other columns. Here’s the formula: DATETIME_FORMAT({Date}, ‘YYYY-MM-DD’)&" “&Type&” || "&{Fellow}&{External Guest} How would I change it to include blank-dates?

Hi Guys, Need some help, new to Airtable. I am trying to create a formula to update a next due date for monthly rent from a contract start date instead of manually updating the dates monthly. The columns i have is Contract start date and Rent due date. Thanks

Hey community, I am decently new to Airtable and am excited to get to know it better, but I am stumped on creating this formula. I’ve beeI have been trying to output the range of a number of days within a week when given the first day of that specific week. I need it to be formatted with the month like, “June 21-27” in it’s column. What I have is… DATETIME_FORMAT({Start Date}, ‘MMMM DD’) & “-” & DATETIME_FORMAT((DATEADD({Start Date}, 6, ‘days’), ‘DD’) I keep getting error messages like: Sorry, there was a problem saving this field. Invalid formula. Please check your formula text. I’ve been messing around with it quite a bit, but can’t seem to produce what I want. I am sure there is a solution to this simple formatting problem. Is there a way to do this using the “Date” field type? Or maybe I can only use DATE_FORMAT() once per line.

Hi Everyone, I’m new to formula’s and can’t quite work out the right answer. I have job listings that I want to archive once they have passed an expiry date. I currently have a status column that has a dropdown to select ‘archive’, once this is selected it goes onto another view (using a filter) but it would be really useful to automatically move this once it reaches the expiry date’, rather than doing it manually. Thanks in advance!

Good day, Airtable Community, I have a base that looks like this, And I would like to create a formula that displays true (or 1) if that day is today, so if today is Monday, all the fields that contain Monday will be 1. But I cannot find any way to do this with the Today() and other date functions. I strongly believe I am missing something. Can anyone shed some light on this?

Hi there, I’m trying to use the DATEADD function to calculate an end time for an event. My formula is: DATEADD({Timeslot StartTime},{Duration mins},‘minutes’) {Duration mins} can be a lookup field or a formula field. I’ve tried all formatting options for these fields but none are producing the correct output. If I make {Duration mins} a text field, the output is correct. I need to make the formula work with {Duration mins} being either a lookup field or a formula ~ What am I doing wrong? P.S. I have also tried to troubleshoot the issue by toggling the GMT settings on and off, all to no avail. Thanks for your help.

I have a set of product attributes, each attribute belongs to a group, example: Product - T Shirt The attributes would be Size > Medium Cut > Round Neck Color > Green What I would like to do is have ONE multi select column with all the attributes in. The output needed would be split into separate columns like so: Column 1: Attribute 1 name : Size Column 2: Attribute 1 Value: Medium Column 3: Attribute 2 name: Cut Column 4: Attribute 2 Value: Round Neck Etc… The multi select needs to be able to be in any order, I have made another table with each attribute and it’s attribute name and then used a LOOKUP to send that back to the main sheet. This returns a string of 3 attribute names. Is there a way to take the first value in the string and return it’s attribute name? I know this seems like doing it the hard way but it has to conform to something our website can digest, and that is: Attribute 1 name Attribute 1 value Attribute 2 name Attribute 2 value… Etc. It doesn’t allow for an

Grateful to those in this community who helped me set up my family’s bookstore inventory. It’s going well and I often recommend Airtable to others. So now I’m using this formula… Title & " • " Author & " • " Place & " • " Date But because a number of books do NOT indicate author, place, or date, I sometimes end up with this: Title • • • Date That’s not great for catalogs. Is there a formula I can use that will leave off the extra bullet(s) when one or more of the fields are empty?

Hello awesome airtable people … I am desperately in need of help after spending days going in circles. Background: I have a list of TASKS (podcast recording tasks in my case). Some tasks have DEPENDENCIES - I can’t do task 2 until task 1 is complete The list of TASKS is repeated for each podcast show. In Airtable, I have a table for my podcast shows and a table for my tasks. I have almost everything working the way I would want if only I could I could figure out how to update the field: Dependency Done? If I was to describe what I wanted, it would be a formula that says: If there is a dependency task listed for this record and that task is done, mark this as TRUE, else FALSE. I can get this work if there is only one episode by using a conditional lookup but as soon as I have multiple episodes, that doesn’t work. I’ve stripped down my table to share in hopes someone has a great tip. Help me Obi-Wan Kenobi, you’re my only hope! https://airtable.com/shrjFGmhEbifXVBge

I’ve looked all over for this, and there are formulas that get close, but nothing that quite gets what I want. If the date is equal to or past the date under “Deadline”, autofill the formula cell with “OVERDUE”, unless the Status column (single select) is tagged “Complete”, in which case it should say “Done” no matter what (before, on, or after deadline) Else-wise, the formula cell should be blank.

I don’t know if what i’m attempting is supported in rollup fields. I have a rollup field, Level, which uses my table’s linked field, Academic year, to pull in a field called, Students from my Academic year table: I’ve attempted a variety of formulas in the rollup using the FIND function, all of them return 0. Here’s some examples: FIND(ARRAYJOIN(values), "John Doe") FIND(ARRAYJOIN(values) & "", "John Doe") FIND(ARRAYJOIN(ARRAYCOMPACT(ARRAYUNIQUE(values))), "John Doe") Is it possible for me to get a result that isn’t 0? Or is the FIND function not supported in rollup fields? (I should add that I’m not really searching the name “John Doe”, but rather searching names that are present in the referenced field.) Thanks for any help.

Hey all, I have been testing out online DB’s, Knack, e.g., and created a formula to take three fields from inside the table and it generates a complete course number. Each of those fields were connected to their own tables. The formula wound up being a text formula is: {ILT Course Course Level.Course Level Number}{ILT Course Course Types.Course Code}.{Sequence Number} How can I do this in AT? I’ve seen the formula option for a field, and even the Link to another Field(tab/table) option so it seems it’s possible, but when I attempt it I don’t get exactly what I was expecting. I get NaNa, or something like that LOL. Any ideas?

This is an interesting little discovery regarding the VALUE() function. On the formula field reference page, it makes it seem like VALUE() will simply extract all the numbers from a text string. But this isn’t true under all circumstances. The VALUE() function will sometimes perform mathematical functions as well, if certain symbols are included within the text string! For example, the formula VALUE("**6hxyzb0**00987**") will extract all the numbers and return 6000987. This is expected. But the formula VALUE("**6hxyzb0%%00987**") will return 60009.87. The very presence of a percentage symbol in the text string makes VALUE() multiply the extracted numbers by 1%. Similarly, if the VALUE() function sees a dash in the text string, VALUE() interprets the dash as a minus sign and it multiplies the extracted numbers by -1. So the formula VALUE("123-XYZABC") will return -123. Interestingly enough, if it sees 2 dashes, it multiples the 2 negative ones together to get a positive 1. So the formu

I am using the Value formula to extract the year from a text field column. In most cases this is working just fine, however, a handful of entries are returning a random string of numbers that do not appear related to the text field that the formula references. Screen capture for reference attached. What could be causing this to happen?

Hi there! I want to send reminders on collaborator emails. I build a table where they create reminders for themselves. The email has to be sent at the selected time. I try to use automation trigger “When record matches condition” or “When record enters view”. In the “Reminder” table I have a “Date&Time” field and a field with formula: IF({Date&Time} <= NOW(), 1, 0). I noticed what the formula field is not updated instantly when the condition is true. QUESTION: How often is it updated? Is it a best practice to use such solutions?

I’d like to know if there’s a way to check/validate/verify the number being entered by the user when entering in the form. The tab(table) has preassigned numbers(should they be text or numbers for using elsewhere?), so I want to be sure it just automates a new number. Is there a way to do this? Also it does have to be two digits only.

Hey everyone, Hoping somebody can help here. I’m having trouble returning some info from a formula I have created. I have a table to show the duration of certain tickets that are received for problems. I have a Date Raised field, Date Resolved field, and a Days Outstanding field. I want the Days Outstanding to return the difference between Date Raised and Date Resolved when there is a value in the Date Resolved field, and I want it return the difference between Date Raised and TODAY() when the Date Resolved field is empty. I’m currently using this formula: IF({Date Resolved}!=BLANK(), DATETIME_DIFF({Date Resolved},{Date Raised}, ‘days’), DATETIME_DIFF(TODAY(),{Date Raised}, ‘days’)) It’s working fine when Date Resolved is populated, but returns NaN when it’s empty. I have created another field just to check that the today-date resolved formula works and it’s returning exactly what I want, it just doesn’t work when it’s inside the IF formula can anybody explain this and show me a solu

Hi, i have two bookmars “Objednavky privatni” and “Kaumy”. In “Kaumy” a need to add new column with numbers from “Objednavky privatni”. For example. “Objednavky privatni” is ORDERS in english, and Kaumy is company name. If they made a order, i will make a new record in ORDERS. There is 6 product, they can order in different quatity. I need to get these quantity number into new column “Kaumy”. In excel is very easy. In airtable is work for hours! Thanks for help. ImgBB V-st-i-ek Image V-st-i-ek hosted in ImgBB ImgBB V-st-i-ek1 Image V-st-i-ek1 hosted in ImgBB

Hello, I am sure this is a pretty basic question but because I lack of knowledge I want to ask you what I am looking for in technical terms. How do I describe technically correct what I am looking for? This is what I am looking for: I have a “list of zip codes” where each zip code is assigned to a certain value. This value is a tax rate. I also have a “database of clients” where there is always a zip code. I want to find out which tax rate applies to them according on the “list of zip codes”. 3.I have a already a field where I link from my “database of clients” to the “list of zip codes”. If I type in manually in each line the zip code it works! BUT: I created a table where I put the list of zip codes and tax values, I linked it from my table where I store my user data and now I can look up manually by typing in the zip code in my user table the tax value from the new table. But I want that “automatically” so that the zip code from the user table automatically is requested in the oth

I have list of jobs records in Jobs table. jobs table has a field called Industry (Multi-Select dropdown) dropdown and it has list of items… I want access those items from the dropdown to populate a new dropdown. I am using API call to get data from Jobs table

Hello, I’m having trouble creating an IF CHECKBOX formula. I’m trying to create a numeric score based on multiple checkboxes, assigning a value to each. I’ve successfully done the first using this formula. IF({FIELDA}=1, “80”,“0”) This puts the number 80 in my formula column if FIELDA is checked and 0 if not. What I need to accomplish is having other checkboxes with other values. Example IF({FIELDA}=1, “80”,“0”) IF({FIELDB}=1, “10”,“0”) IF({FIELDC}=1, “-25”,“0”) I’m looking for help creating the formula so if each box is checked, it adds another value that is calculated. In the above example, if all three were checked, the SCORE in my formula field would be 65 since 80 + 10 - 25 = 65. But, if the first two were checked the score would be 90. Any guidance would be much appreciated.

Hi would someone be able to help me create a formula to find the % difference of 2 numbers? Thanks!