Formulas

To explain my project, I have two tables :- A "Movies" table that stores a list of movies, one of the fields in this table is an Attachment field that contains the movie poster image.- A "Catalogs" table that has a link to the "Movies" table.The goal of my project is to create catalogs by selecting the movies I want to include using the link to field, and then, by clicking a button, launch a Make scenario that retrieves the selected movie list and generates a catalog in PDF format using Eledo. The data insertion into Eledo is done in JSON format (example: {"title":"Movie1","description":"Description1","img":"URL1"},{"title":"Movie2","description":"Description2","img":"URL2"}).Before the Airtable changes, this project was easily working because I could simply retrieve the movie image URL using a formula for the "URL" value. However, since the changes, this is no longer possible.I do have a solution, which involves manually replacing the JSON for each movie in the Make scenario and setti

I have a sort of complicated question about how date formulas treat the concept of a 'week.' This formula also considers the week to be Sunday thru Saturday:DATETIME_FORMAT(Date, 'w') For a record dated Sunday June 18, the value returned here is 25. (The same value as today, Wed June 21.) – However, consider this formula: IF(AND(VALUE(DATETIME_FORMAT({Date},"w"))=VALUE(DATETIME_FORMAT(NOW(),"w")),YEAR({Date})=YEAR(NOW())),"Y", "N") This formula reports whether a given record's date falls within the current week. So, a record dated Sunday June 18 would get a "N" if the week is Sun thru Sat. But! A "Y" is returned, because this formula is considering a week as Mon thru Sun. Does anybody know why this inconsistency would occur? Thank you for your help!

I have a single select field for "horoscope." Before allowing the user to select a horoscope for a record, I want to check to see if a birthdate field exists and if I can infer the horoscope. How could I make the single select field be conditional while maintaining its functionality if a birthday doesn't exist.

I have data that essentially looks like this:I want to use a formula to populate the last column with the type (in my example, food) based on whether or not other cells in the row are greater than 0. So IF (column A, B or C is >0, return "Fruit") AND IF (Column D>0, return "Fungus" and so on). I can get it to return one of the types but not all. Ideally they would be separated by a comma.I also sometimes have a blank cell and sometimes I have a 0. I know I can do a simple IF statement and have columns for each type, but is there a way to do this in one column?  Thank you for your patience, I am new to airtable and airtable formulas!

I created an automation to log the date/time for every status change in a given project.There are 4 status options:* Todo* In Progress* On Hold (waiting for information from external source)* DoneMy goal is to calculate the hours spent actually doing the project, which would always have "In progress" as the start date. See screenshot below.The tricky part is that the projects always have a back and forth between status: (...In progress -> On Hold -> In progress -> Done...)Would it be possible to get the difference between "In Progress" and the next status?I found this 5 year-old thread that tackles a similar problem, but it's not quite the same thing I'm trying to achieve here.https://community.airtable.com/t5/other-questions/subtracting-last-two-created-linked-record/td-p/51592

I have no idea where to start on a formula for this. :stuck_out_tongue: What I would like to accomplish is for Airtable to compare two start times and two end times and tell me which of them matches up. One start/end time would be for an event or concert, and the second would be for employee availability. I want to know if an employee has a start/end time that overlaps an events start/end time. If the two times match I would like Airtable to tell me which employees are available during that event or concert. I have two tables, one table is for the event and its information then other table is for keeping track of employees available time. Table 1: is for events and projects. It has a column that has a start time and end time. Table 2: is for employee availability. It also has a column for start time and end time as well. What formula should I start building out. Should I combine the tables together to make it easier? Any suggestions to get me headed in the right direction or if you k

Hello!I'm trying to create a field to display the month name. My start column is EU dates like that : 10/6/2023Precision, sometines the number of the month is unique "6, 7, 8" but from october, it switch to a double number "10, 11 or 12".Any idea ? 🙂

Hi guys! Thanks in advance for your support.Right now I have several tables in a database, one of the tables contains the events that occur, and another table contains the types of events that exist (in order to count them). The question is, how can I count only the events that have occurred in a specific month or have been involved in that month? For example, if I want to know the number of events that have occurred in May, but an event started in April and ended in June, it is not going to count if I use a conditional which searchs for event started or ended in May.Do you have any ideas of how to implement it? 

This is driving me nuts. I have a long text field (rich text turned off) that seems to have multiple line breaks. Formula I used was Substitute(Field,“\n”,“”) and while it seems to work it also doesn’t. I tried with putting in a value instead of “” so I could see it working and it is working but also airtable seems to be keeping paragraphs anyway. Any help would be greatly appreciated.

I want a percentage bar where if desktop, tablet and mobile is ticked then it will show full 100% filled percentage bar Here’s my table link: Airtable Webflow Development Checklist - Airtable Explore the "Webflow Development Checklist" base on Airtable.

Hello dear AirTabler, we are working in Influencer Marketing with AirTable and want to create a form that allows Creators to automatically upload their content.In the AirTable we map the complete campaign handling, so here are already the names of the influencers.The form should be neutral, i.e. no linked field can be created so that the creators can select their name and then upload content.How can we make it so that the creators enter their @handle in the form and the content is then automatically entered into the existing column of their name?

Dear Airtable Hive Mind,I have an Actors table and a Movies table. Each Movies record refers to the Actors table via multiple Link fields, i.e. Movies.Actor1, Movies.Actor2, Movies.Actor3,..., Movies.Actor10 in order to capture the full cast, each of which is a Link to the Actors.Name field.Q1 Each Actor is a member of a particular Acting Association (Actor.ActingAssociation field). How can I pull the Acting Association for each Actor ideally into the Movies table, i.e. Movies.Actor1Assoc, Movies.Actor2Assoc etc. Or do I need a new table, and if so, how to do it there?Q2 How can I gather a distinct (unique) list of Actors into a new Movies.AllActors field that lists each actor noted across that Movies record (10 Cast Member Actor fields as listed above), ideally as objects rather than text, that will automatically update when a Movie.ActorX field is edited. Plus a related column called Movies.AllAssociations showing the distinct list of Associations for the Actors noted in Movies.

Hello guys, i have a table where i calculate some values which can also have a Zero.That means that i sometimes get 3/0 which leads to a Nan error.I already tried a couple of formulas but i do not get it. So my question right now is how can i put the cells which would have Nan as a value to 0? I would be happy for a solution. Thank you.

Hello you!I'm looking to remove 2 characters of fields, from the right of the cell. I have a list of prices, like that, no currency :1570197401549I'm looking to get this :157 Є97 Є15 ЄThe deleted numbers are the cents 🙂Cheers! 

HelloI've managed to create a formula that adds x number of days to a start date and pushes the result to a work day if the end date is Saturday or Sunday:IF(AND({Direction start date},{Number of days},"days"),WORKDAY({Direction start date},{Number of days}),"days"),{Number of days})The result seems to work fine, however the formating isn't great as the answer is is like this 2023-06-02T00:00:00.000Z .  I've clicked the format option but that doesn't let me do anything.  Any suggestions would be gratefully received! 

I have a table that has numerous columns of date fields that tells me when a person first attended a specific workshop. (this is a linked field and reads from the individual workshop tables)I want to be able to have a column that shows the earliest date of all the dates that this person(record) attended - essentially to give me the date they started engaging in workshops with us.Does anyone how if this is possible and if so what formula to use(I tried a MIN formula but it just gave me numbers rather than the date and I couldn't see an option to format it as a date)

Is there a way to automatically add a date and time stamp when I type something in the notes? For example [date] [time] called xyz and left message… [date][time] emailed abc about xyz…

Why do I get a total of 2 days when it's clearly only a 1 day difference?    

HiI have in my AirTable a list of Names. Lets say I want to find all results with last name "brown". I am making a search for people with formular like this:filterByFormula: LOWER({TABLE_ID})="brown"How can I do it that if I search "bro", or even "own", it will also send the result of someone with name "brown". Meaning I want to receive when the String contains the seach word, not just when its an exact match.Thanks

Hey everyone, I need some help with a simple formula I can't figure out.    "/Volumes/" & SUBSTITUTE( {Dept. Server Folder}, "\\", "/" )    I am trying to pull the content of another field on the same card. I want to add the plain text /Volumes/ to the front, then substitute instances of "\\" with a "/". Our team has macs and PCs, and I am trying to solve a bit of a headache that our content managers feel.

HiI added the extension: Search to the air table,This link: https://airtable.com/marketplace/blknmXKkZjB7scdSq/searchThis is a great search function to search all the fields in the table. Is this available in an api format, not just on the web interface of the AirTable? Thanks

Hello,I have a standard rate sheet where I share different charges to my clients. Sometimes, some clients have better rates than others, thus I have to have one rate sheet for each client in excel files.I want to create a single general rate sheet and create a rules table, so that I can add/subtract a specific amout or a percentage to a specific client. After that, I would need a kind of dynamic rate sheet where I can see the resulting rates for a specific client.The formula that I am thinking about is: Base rate + extra value + extra percentage.The last 2 values should be extracted from the rules table for the specific client, This is the structure of my database:TABLE 1: Ratesheet Columns: Name, Rate, Min, Max, Tags, Notes, Rules (link)TABLE 2: Clients Columns: Name, Notes, Rules (link)TABLE 3: Rules Columns: Name, Add value, Add percentage, Clients (link), Ratesheet (link)

HiI have 8 fields with numerical values and I want to create a "summary" in a new field, which would show per each record which of those 8 are the 3 top ranked fields (name_field&value). Is that possible?Thanks   

Hello Airtable Community,I have 2 tables: Attendance and Performances. People will fill out my ticket request form, submitting their preferred number of tickets for Performance Dates 1,2,3 etc.  This gets recorded in the Attendance table under their school name and quantity goes under "Number of Tickets." I also have a Performances tab that tracks total number of tickets available for the performance. I want to use a rollup or formula to connect the two tables and deduct the total SUM of tickets claimed from a Total Tickets Available number that I set (right now it is 1,000 tickets.)Ideally I am able to make this appear in both tables: Attendance and Performances. Can you help me?   

Hello, I currently have this question needing to solve "If a branch's average monthly gross revenue in the last quarter is higher than $1500, its revenue split rate will be 80%, or else it will be 70%"I want the result to show Percentage 80% and 70% as result because after that I will use them to calculate the net revenue. ASAP. Thank you.