Formulas

I currently use the following formula to produce a unique identifier in my my primary field: "PHDEV-" & {Fx_Year} & "-" & REPT(0,3-LEN(CONCATENATE({Fx_Autonum}))) & Fx_Autonum This works as intended, but I'm at a loss understanding how I might expand this formula to do the following:  1) use a different autonumbering value for records based on a field value (e.g., if location is "PA", add suffix "-PA" AND autonumber, and if location is "MA", add suffix "-MA" AND a different series autonumber. Because I'm currently relying on the field formatted to autonumber to add the number, it will issues numbers like 001-PA, 002-PA, 003-MA, 004-PA, 005-MA whereas i want it to number those records as 001-PA, 002-PA, 001-MA, 002-MA, 003-PA 2) reset the numbering count when a new calendar year is present. Is this possible? or will i need to create a new tab for each location/year? Thanks in advance!

Dear Airtable community,I have read all the posts on the Arrayunique function, and I still have not succeeded in presenting Member Names with no duplicates.Table 1 : Tasks with a multi-select field to describe who is in charge of that task. Tasks are linked to Projects. My Table 1 looks like this :TaskID | Multi-select Field Members      |  Link to Project  |  Lookupfield from Project : Members |Task1      Member1, Member2, Member3                 Project 1                   Member1, Member2, Member3, Member2Task2     Member2                                                 Project1                    Member1, Member2, Memb

Hey there,I need some help with my airtable. I know it's possible but I'm relatively new and eager to get into database work. I'm now working on a database for my record label and want to create reports for my artists regarding their streams that they can get via a monthly e-mail that I will later automate. I'll break down the workflow to better explain it. And here is the Airtable for you to look over: https://airtable.com/shrQtfIpRU8JlMScQ1) At the end of every month. I got to the Music Distributor's dashboard (MDD), which only I have access to, and go look at the stream count for the selected period (every month). The table that is presented is each individual track NOTE: I cannot filter the table in my (MDD) meaning the order in which the tracks are presented is only based on stream count. So the order is ever-shifting.2) With a plugin, I scrape the HTML of the MDD's table and convert it into csv and import it into airtable as a new table, "Month YYYY." Meaning each month has

I have a cell containing values that are semi column separated and have a value after the :How can I split this info into multiple cells?IDLocations1location A:1,Location B:3, Location C:92location A:4,Location B:10, Location C:44I know all Locations, so I can pre-create all needed columns if this would be necessary (beter if it can be done automated of course). How can I automatically copy the info to the right columns?So needed outputIDLocation ALocation BLocation C1139241044Any idea how to achieve this? 

I'm pretty sure I've seen this covered before, so forgive the reposting. I want to create a field that generates a flag icon/emoji if a checkbox field is checked.I'll use this to add the icon to my field title (using Concatenate) so it draws attention. I've done the same thing using the SWITCH formula, but I can't figure out how to do it with a checkbox field.TIA! 

Hello! I have 4 date fields, and for a given record, any of the 4 could be filled in (or several of them, or all of them). I've written a formula to return 'Y' if any of the 4 dates fall within the current week. Here's what I've got:IF(OR({Date 1}, {Date 2}, {Date 3}, {Date 4},IF(OR(((DATETIME_FORMAT({Date 1}, 'YYYY')&DATETIME_FORMAT({Date 1}, 'WW'))) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW')),((DATETIME_FORMAT({Date 2}, 'YYYY')&DATETIME_FORMAT({Date 2}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW'))),((DATETIME_FORMAT({Date 3}, 'YYYY')&DATETIME_FORMAT({Date 3}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW'))),((DATETIME_FORMAT({Date 4}, 'YYYY')&DATETIME_FORMAT({Date 4}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW')))),'Y', 'N')))I added the first IF(OR( argument to allow the second IF(OR( to run, no matter how many of the date fields have data in

How would I go about setting up a formula that only activates IF a checkbox is checked?If I have selected a checkbox, I want the total to be divided in half, otherwise, I want to keep the whole total as/is

Hi, please help to understand whether it is possible to implement the following:On the screenshot, there is a table in which data about the status change and its time are entered via a webhook. I calculate the duration between events. Now I need to make a report for the previous day, in which I indicate how many hours the status was online and how many hours the status was offline.@kuovonne can you help?

Here’s an examnple of what i’m trying to do One cell has a single select item to choose from… In this example i will say i can choose from “A” and “B”. if i choose “A” then the cell to the right of that would change the single select items in that cell, and allow me to choose either “blue” or “green”. if I choose “B” then the single select item dropdown changes and allows me to choose the colors “red” or “orange”. Is doing something like this possible?

The field entries are £20.49 and £13.66 (both fields set to 2 decimal places) but in a third field when I use CONCATENATE this is what I get:Summary: Headline £15000 pa Actual £9975 pa £20.491666666666667 £13.661111111111111How do I get £20.49 and £13.66 ?tiaps - I daresay this (sort of) question has been asked before but I have no idea what to search for. 

Hey,I want to do a simple if statement to show TODAY for the date today. It works for a date field but not for a rollup date field. IF({Date Rollup (from Stock)}=TODAY(),"TODAY","")

Not sure if this is a air-table or zaiper question hopefully someone can explain to me. This is what im trying to do:Uploaded video To frame.io where the name of the video is the same as a Unique ID in airtable ( called Asset ID ) in Airtable search for a field ( Video Edit ) Input text or link  to inform that video has been uploaded I have tried to find the record using a search function and my zap looks ok but when i run it it does not update to the correct field.when i test the last part of my zap ( input text ) it will only place it in the first record and not the one attaching the unique ID i have tried the following SEARCH functions also FIND SEARCH(({Asset ID}))!={Edit}SEARCH(({Asset ID})) = {Edit}not sure if it is that or when i go to set up my action in the RECORD section i am picking the wrong dropdown.i thought it may be “custom” “zap search was found” but didn't work , and i have also tried multiple other options  any input would

Hi guys and fellow airtable experts. I am creating an inventory system in airtable with a stock receipt table that will record any changes to my main table's item stocks.  It can be additions if I receive goods and subtractions if I sell away the goods.Main table has a list of items.Main table fields:item namestock quantityStock Receipt table has a list of stock receiptsStock Receipt fields:stock receipt name (text)date (date)item name (linked to main table's item name)quantity adjusted (number, can be pos+ or neg-)  How do I make it so that when a stock receipt is created, airtable automatically compares the stock receipt entry and updates the main table's quantity for that item? E.g. Stock of item was 10. A stock receipt was created with quantity of" -1".  new updated stock of item is now 9.

Hello, I've looked everywhere and can't find a simple solution that works for me. I have a table where I link records and I want to take the linked records field and split it by comma and input those items into it's own field. Can someone please give me a formula to input into the columns?

Hey guys, this is it -I have 2 forms on the site -1. Form for capturing leads (cold)2. Another form at a more advanced stage that captures very hot leads.I have automation in MAKE that actually transfers all the cold leads that come from the first form to the Airtable list.In each line for each lead in Airtable there is a checkbox whose purpose is - did the same lead also fill out details in form 2? (in order to understand if it is a hot lead) because it is also possible to reach form 2 without going through form 1 and therefore I do not want to create an additional column for those who fill out the form 2 but to update in the same column of the lead the person who filled in form 1. (so I will not have duplicate columns with different email or something like that)I managed to create an automation in Make that checks whether the incoming lead's email is in the list and if so to update, and if not to create a new one.My problems are as follows:1. Sometimes there are leads that use differ

I am looking for help, hopefully I can explain this well enough.In my Airtable, I have a multiple select field where you can choose different available mediums.  (Video, animation, poster, pdfs etc). And I want to create another column that automatically changes depending on what is selected.  If video or animation is selected, a V would appear in the column next to it.  If a “poster or PDF” is chosen a G would appear.  I cannot figure out what Formula to use.Basically I want it to be “if this is selected” then “show this”I tried creating a formula column and addedIF(Medium, 'Video/animation', 'V')IF(Medium, 'PDF', 'G')but it doesn't work so I assume i am doing this incorrectly, I'm new to formulas and building these tables, so any help is appreciated. 

Not sure if this is a air-table or zaiper question hopefully someone can explain to me. This is what im trying to do:Uploaded video To frame.io where the name of the video is the same as a Unique ID in airtable ( called Asset ID ) in Airtable search for a field ( Video Edit ) Input text or link  to inform that video has been uploaded I have tried to find the record using a search function and my zap looks ok but when i run it it does not update to the correct field.when i test the last part of my zap ( input text ) it will only place it in the first record and not the one attaching the unique ID i have tried the following SEARCH functions also FIND SEARCH(({Asset ID}))!={Edit}SEARCH(({Asset ID})) = {Edit}not sure if it is that or when i go to set up my action in the RECORD section i am picking the wrong dropdown.i thought it may be “custom” “zap search was found” but didn't work , and i have also tried multiple other options  any input would

Hello Community! I have to preface this request by saying that I was asked to come up with a way to track issues, stumbled across Airtable, and purchased an account. Airtable has been a lifesaver but I have zero knowledge on how to create formulas.I built a table and created a form for people to submit their issues but now I need to track how long it takes the team to complete a ticket. I would like the days-old filled to stop counting once the Status field equals complete. Would anyone be able to help me with this? Below is a screenshot of the modified bugs template that I've been using.   

New to Airtable / Formulas and Automations 🙂 Hopefully someone out there can explain what im assuming is a basic SEARCH formula to me 🙂 I have created some automations using Zaiper to update a record based on a UNIQUE ID ( Video ID ) but i can only create a search function for that specific ID.  Example Attached 🙂How would i go about creating a formula to SEARCH for a ID value that is 1 more than the previous one.ie i can find "SC1" but i would like to create a formula that could find "SC2" , the 3 and so on. As i have a file naming convention that is sequential its needed to update each record through-out the workflow process. 

Is there a workaround to use rich text markdown for a formula that provides a URL? More context: My formula provides a lengthy URL, and it triggers a slack notification. Would love to mark down so the slack notification doesn’t take up the entire window! Thanks in advance for your advice.

Hi all! I’m using part of my AirTable Workspace to track hotel lodging needs for my organization’s yearly event. I have the data built where I assign an attendee to a property and room type, and include their check-in and check-out dates. Is there a formula to pull each night of stay for the date range without me having to manually build out a report listing each night of stay for every individual? Or is there a way this can be built in automations or interfaces? For example, John Smith is checking in on March 22 and checking out on March 24, so his individual nights of stay would be March 22 and March 23. I’m looking to pull this data so I can see what the nightly pick-up is for each property by room type. I started using the day calendar as a sort of “list” of nightly pick up of rooms, but since it shows up on the check out date, it’s not quite accurate. Thanks for any suggestions you may have! I’m happy to schedule a GoogleMeet meeting to visualize things together if you’ve built so

Hello! I have a table with Recurring Records, representing daily or weekly tasks. Some of these tasks have sub tasks to them. When I complete a task (Record) it is sent to a 'Done' view and then Recreated automatically in the Todo view. However only the name of the Record is saved and used again, not the data from the Fields in the Record, eg. sub tasks. Is there a way to save this so that I don't have to write them manually every time the Record is recreated?

Hey friendsI want to check who has watched the videos on my system and I have some automation that was up until now transferring to Google Sheets and now I'm switching to AIRTABLE.If I want it to mark me with a V in the checkbox, what should I write?I would later like to add IF on the same simple checkbox and check the quality of a lead.Hope you understood.I'll be glad to receive your help,Thank you! 

Hey guys, I recently built a “Wellness Log” base in Airtable that tracks all kinds of stuff: When I get up and go to be every day, when I eat each meal, how much H2O I drink every day, how long between each meal, and star-based ratings of my mood, mental focus, and energy/stamina. What I have no idea how to do but really WANT to do is build formulas that calculate change over time using entries from other days. Specifically, I want to calculate the amount of sleep I’m getting every night, as well as net weight loss/gain since I started tracking my weight in the base. Regarding the nightly sleep calculation, I have a “RISE” and “BED” field in each record that is a Date field type that includes the time in 12 hour format. I want to create a formula to identifies the preceding record and calculates hours of sleep based on the amount of time between “BED” yesterday and “RISE” today. With the weight loss/gain I would need to ID the first record that has a value in the numeric weight field,

Hi there, I'm new to Airtable and trying to figure out how everything works. Let's say I have a cell that as the following information I inserted below: Ingredients- 4 salmon fillets- 2 tablespoons of melted butter- 2 tablespoons of olive oil- 4 cloves of minced garlic- 2 tablespoons of freshly-chopped parsley- Salt and pepper- Lemon wedges (optional)Instructions1. Preheat the oven to 375 degrees Fahrenheit.2. Place the salmon fillets onto a baking sheet lined with foil.3. In a small bowl, mix together the melted butter, olive oil, minced garlic, and parsley.4. Brush the butter mixture onto the salmon fillets.5. Sprinkle salt and pepper over the fillets.6. Bake the salmon for 20 minutes, or until the fish is cooked through.7. Serve with lemon wedges, if desired. Enjoy! How can I separate all of the "ingredients" into one cell? And all of the instructions into another?