Formulas

Hi everyone, I’m new here, and haven’t found the response to my problem anywhere. One of my columns contains text with several tags. I need to split the text in that column, into several columns in which each column would contain one tag. Can anyone help with that? Thanks!!

In my base I got some images saved which I want to use in page designer.I believe I used to be able to use the Image URL in the page designers Image element. It seems still to work for about 24 hours and then the URL becomes invalid (which believe has something do with Airtables new security updates.)However is there still a way to URLs of an image in different attachement fields?

Hi everyone,I suppose someone else must have dealt with this before so here my question, hope someone can point me in the right direction!We run a few bars in our property and we connected our POS to Airtable, so every day I get all sales data imported. The data includes the exact moment (date and time) of the sale, as well as total value, tips and so on.We also use AT for staff planning and payroll so I know who worked exactly when and where.Now, I want to create a table that reports  how much revenue was made within one shift, based on actual clock-in and clock-out time of the person working. I can then use that for bonus payout calculations and analytic purposes.I have, within one base, the employee and shift details, as well as all of that sales data.Does anyone have an idea on how to approach this?thanks in advance!

I am trying to create a formal that adds a specific amount of days to a date field. The amount of days varies and is pulled using a lookup field. Initially, I was trying to use the DATEADD function but it doesn't seem to work when the added value is another field instead of a numeric value.The formula I have currently is DATEADD({Submission Date},{Health Plan SLA},"days")The field Health Plan SLA is a lookup field that pulls a number from another tab. I need this number to be what the formula uses. 

Hello!I have a list of records that all have a start date and end date column for a number of employees.  I am wondering if there is a way to calculate the number of employees on a given day.  All the employees have different start and end dates.  So for example I want to know the total working on March 1 vs March 7.  I know there is a way to do this in excel using an IF/AND formula but can't figure out how to do it in Airtable. Appreciate any and all advice!  

Hi there. I'm trying to make a custom auto number. The constant is kasse 00 and then want the autonember to be kasse 001, kasse 002, kasse 003 and so on. Can anybody send me in the right direction?And off cause will there be a problem when I reach kasse 010. It needs to be kasse 010 and not kasse 0010 best regards Thomas

I'm trying replace the following text with different values within [] with values from single select columns[art medium], [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]I thought this would be fairly easy, but somehow can't get it to work.I tried a Substitute() like this:IF(Medium = BLANK(),SUBSTITUTE(Label, "[art medium], ", "" ),SUBSTITUTE(Label, "[art medium]", Medium)) &IF({Main objects}=BLANK(),SUBSTITUTE(Label, "[main objects], ", "" ),SUBSTITUTE(Label, "[main objects]", {Main objects})) But it subs the text and then renders the remaining text. Does anyone have any pointers how I best go about this?

Hey, I am looking for a formula field to extract city, state and zip code from a full address fieldThis is how a full address looks like "New York, NY, 10031". I want to split them 3 ways using formula, how can I do thatI used this formula to get the City   LEFT({Full Address},FIND(",",{Full Address})-1)   

I'd like to count how many linked records have occurred with another linked record. I have a table where each record is a contract between a company and a governmental entity. The company's column is a linked record and the entity's name is also a linked record. Shown below is an example table.(again, company and entity's columns are supposed to be linked record) As you can see, The company AF Engineering has two contracts with the Ministry of Electricity and one with the Ministry of Health. I'd like to find a way to count those occurrences. It can be from the Companies table, the Entities table, or the Contracts table (the one on the screenshot.) It sort of like the dictionary data structure in programming: {Ministry of Electricity: 2, Ministry of Health: 1}.  How can I do that?   

Hello,I have a rollup field with the following output:FirstName1  Last Name1- XXXX Title Company , First Name2 Last Name2 - XX Title CompanyWithout going back to the original table to pull the "XXX Title Company"  I need a formula to help split and display everything after " - " for each value. Any help is appreciated!

Dear Airtable + Apple Shortcuts Mavens,N00B, here with a N00B question.Using Airtable's API Encoder, I crafted a GET URL with filterByFormula.curl "https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22" -H "Authorization: Bearer [redacted]"The resultant URL works fine in CURL.When I store the URL (minus the Authorization bit) as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22Shortcuts fails with a TABLE NOT FOUND diagnostic message.HOWEVER, when I store the URL as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_TableThe query works fine. The Table is found...except, of course, it returns all entries in the Items_Table, not restricted to item 'D123'.Baffled, I am.Any ideas? Suggestions? Thank you in advance for any assistance you can provide on this matter.   

Hi allIs there a way to delete the certain matching characters that might appear in a column? For example I have a column of data (800+ cells) of company names, and 90% of them are prefixed with a 'number' (that run between 1 and 9), followed by a 'period', followed by a 'space'. For example "1. Some Company Name" or "8. Another Company Name".10% of the data in the column may not have any numbers or spaces, OR some of the company names may not have a 'space' after the 'period' for example "7.Yet Another Company Name" or "And Another Company Name".Amy advice would be greatly appreciated.Stephen 

Hello-I am trying to roll up 10 date fields that are all deadline dates to a long text field in a bullet format.Ideally I would want the field name to be visible as well as the date like this (Field name/Date):- Designer due date: 1-2-23- Submittal due date: 1-15-23- Final proofing due date: 1-30-23I saw this from kuovonneIF( COUNTALL(ARRAYCOMPACT(values)), "• " & ARRAYJOIN(ARRAYCOMPACT(values),"\n• ") ) Thank you!Scott

I have a series of fields I want to concatenate together. The final result needs to include a semi-colon in between each piece of data, but not all columns have data. So I was working to do an IF statement to check if the last of the columns (far right column of data I want) had data, if so, concatenate it with the other fields. The formula I came up with (but is not a valid formula and I can't figure out why) is:   IF({Revenue Rolodex}, CONCATENATE({Revenue Rolodex},”;”,{VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({VIP Day Social}, CONCATENATE({VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({4M Event}, CONCATENATE({4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calenda

Hello everyone, I would like to create a form where people can add their event dates (event begin at 1st January 2023 and end at 2nd January 2023) and prevent the possibility of the end date to be before the start date (event begins at 1st January 2023 and end at 31 december 2022).To sum up I would like people to not have the possibility to make mistakes with their event dates.Thanks for your answers ! 

HiI am setting up a zap connecting stripe to airtable.I want the zap to find a record in Airtable that matches the stripe data in 2 fieldsTo do this I need to enter an AirTable search formula into the zapThe 2 Airtable columns I want it to search are “Email Address” and “Subscribed to”So let’s say that a change is made in stripe that fires the Zap.The zap will search Airtable for a record where both the “Email Address” field and the “Subscribed to” field match the data in both those fields in stripe. The combination of the 2 fields indicates it is the correct record to be updated with the changes made in Stripe.What search formula would get this result for me in my Zap?ChatGPT helped me get to this formula:"{Email Address} = {Stripe Subscription}[customer.email] AND {Subscribed to} = {Stripe Subscription}[items.data.0.price.nickname]"but I'm getting the message"Failed to find or create a record in AirtableError from halted execution: The formula for filtering records is invalid: Invali

I looked at the posts about progress bar formulas and I have used them but my result is not looking correct. In the post, they said you need to use chrome and not safari I am using the latest version of chrome and its sill not working. Can some one please help me.

 Hello everyone,I have to create a calendar of course sessions and I have a table in which I enter in one column the date, in one column the start time and in another column the end time because different days might have different times.I use the formula DATETIME_PARSE(CONCATENATE(DATESTR(Date),' ',{Start Time})) to create a date & time that can be displayed on the calendar. However, the result of the formula returns the wrong time, increased by one hour.Am I doing something wrong or is it a bug?Thanks for any assistance!

Hi, new to Airtable and I'm trying to parse out specific records from a lookup field and have Airtable display them in a new column. In one table called "Downloads",  I have a lookup field called "Guests" that is pulling records from a separate table called "Guests". The lookup field returns the names of all the "Guests" for a given entry. There a multiple guests per each entry. In the Guests table there is a variety of data about each individual guest (demographics, affiliation type, etc.). In my Downloads table, I want to create a new column that displays all the Guests names of a certain type (e.g. only Guests that have "Affiliation Type" = "Media"). How can I do this in Airtable? My attempts to create a formula aren't working as I can't get Airtable to return the Guests names. Thanks!

Hi everyone, I’m trying to figure out how to calculate the Mode (ie. the most frequently occurring number in a series). I need this for the purposes of turning crowdsourced data into something useful. For example, if I ask 10 employees of one company how many days of holiday they get a year, I want to return the value most frequently cited, not the average (mean) because that would be inaccurate. In google sheets, it’s simply the function: MODE() but Airtable doesn’t have this. Does anybody know how I might do this?

Hello, Is there a way to configure this formula to only return a time during specified business hours (7:30-4PM)? DATEADD(WORKDAY({Last Modified},1)) Thanks :slightly_smiling_face:

Dear Community,I am faced with the following problem:I want to pull data from a different table when the status in each table is the same.I am creating a text.Say the status in Table 1 is "First Text" then it should check in Table 2 for the row named "First Text" and pull the information from a specific cell in that roll.In sheets it looks like this: =IF(A4=Table2!A2;Table2!B2;IF(A4=Table2!A3;Table2!B3);0)How can I replicate that in Airtable?Thank you

I'm trying to clean up my text with Regex. Some ranges are listed as 3.0 - 3.0 which I want simplified to just 3.0 whenever the two numbers match. I tried using regex101 and MDN references, but Airtable seems to use some different syntax than either of those.The following expressions should be matching but aren't. Does Airtable not support these "back reference" expressions such as \g{n} or simply \n(\d.*) - (\g{1})(\d.*) - (\1)Desired Result:3.0 - 4.0 ==should remain 3.0 - 4.03.0 - 3.0 ==should change to 3.01.5 - 1.5 ==should change to 1.51.0 - 3.5 ==should remain 1.0 - 3.5 

Hi everybody !I would like to know how to add in column that features only the similar values found in columns A and B ?(In my case the A and B columns are Lookup from two différents tables but I guess it works the same).Thanks a lot for your help !

Hey guys, I've got a new challenge in my quest to create a base for tracking personal finances.  A quick review:TablesCategories (I call them buckets)TransactionsDesignationsTransactions are either entered manually or bulk imported via csv from my bank.  Each transaction is assigned at least one category via the designations join table.In the Categories table, I have assigned each category a monthly budget.  Then I roll up the designations amounts filtered by month (this is my current setup - I'm working on a different option).  Now, you would think that a simple third formula field for [ budget - total spent (roll-up) ] would give me the balance for each of my budgets for the month.  Should tell me whether I've received all my salary for the month or if I've gone over budget for eating out, for example.Here's my confusion:  there are situations in which categories may have counter-intuitive balances.  For instance, we have a category for gifts receiv