Solved

This formula worked fine, until we brought on an additional team member in another country. I am now tracking this data for 3 team members in the US, UK, and Germany. I think I ultimately have 3 issues I’m trying to resolve: I’ve seen the great U.S. and U.K. Holidays tables created by user W_Vann_Hall, but my company has a number of additional days off outside of federal holidays I’d like to include. I’m going to go ahead and assume there is no easy way to do this. I don’t see one for Germany, and I’m not up for building that myself. But would it be possible to alternatively upload .ics files with the holidays for each company into a single table (in ISO-Format), and link those records for each team member? Assuming #1 above is more advanced than I’m capable of at this time, and #2 isn’t possible, is there a way to double nest the IF statements in my current formula to include 3 sets of holidays? Here is the formula: IF({Date Contract / Redlines / Feedback Returned to Stakehold

I have a situation where I need to display the linked records in a field like this (including the double “” on either end): [““recFMVIujI5jqSiSD””,““recf8rGUjcM0li5f4"”,”“recztuGU7Tl4GSSYU”"] Is this possible? I have this code to format it, I just need the ID part. '["' & SUBSTITUTE( {Relations}, ', ', '","' ) & '"]' Which displays: [“record1”,“record2”,“record3”] Instead of the record primary field name, I need it to display the record ID in a text field as formated above. I have a linked record column, then I added a new text field column that grabs the name from that linked column but I need it to be the record ID. Does that make sense? (replace the name with the record ID in the brackets)

Hi, I need to update field iloscZetonow to 5 each week. Where is the mistake?

Greetings Airtable Community, I am seeking help with formatting the formula below that combines data from two multi-select fields: {College}&" and "&{Department} Example of what the formula returns: Humanities and Department of Religion {College} is always be populated, but {Department} is sometimes empty. When this is the case, the formula returns: Humanities and Is it possible to edit the formula so that if {Department} is empty, the " and " will be removed, returning just the name of the College? Thank you very much!

https://videos.pickleball.ist/conversations/31657156-59b7-5196-9007-377d0b5a1ac8Video until 1:40 shows the context of what we’re trying to accomplish. After 1:40, shows the details of how we tried to set up our automation.We already have a “Zap” set up through “Pabbly Connect” where all new orders create a new record in our ORDERS table. We want a Airtable automation that looks at the records in our PLAYERS table and checks to see if the user that created the order, already exists (based on the phone number being the unique identifier).Consider that phone numbers have been entered in a variety of formats.Please see the linked video to see exactly how we’ve set up the automation… but we can’t get it to work.Any suggestions?

Actions are not executed although the conditions are met and the automation is displayed as runs successfully. If I then run the automation again in test, the actions are executed. Trigger: When a record is updated Conditional Actions: Condition: IF lookup field value contains <text> AND multiple select field values has any of <selected value> Find records Which meet the condition above. Update record Record ID: <record ID> linked field: <List of ‘Airtable record ID’ from step 2> How can this be?

I have a client that I am working with: has a content production company and they have their production timeline split in phases like Shooting , Pre production , post production and Delivery with start and End date like this: What I want to do is to break it through into separate unique records so I can group them together in a Gantt chart and able to see each phase inside the same project like this gird view : Gantt Chart view Is that something I can automate if I have all dates starts and ends in one record , that would be great to automate , because they still want to use the same structure and not to break it down to this as insertions .Thank you guys for your assistance . My Best

Hi. I’m looking to track how much time (in days) a lead spends in each stage in a pipeline. I’m basing it on start and stop dates of time in stage. The formula I’m trying to accomplish is this (hope it makes sense): Is the start date blank? YES: Do nothing, stay blank. (end) NO: (ask) Is the end date blank? —1. YES: Subtract today’s date from start date —2. NO: Subtract end date from start date I came up with the following but I just get an error that the formula isn’t correct. IF({Start Date} = BLANK(), “”, IF({End Date} = BLANK(), DATETIME_DIFF(TODAY(),{Start Date}, ‘days’), DATETIME_DIFF({End Date}, {Start Date}, ‘days’) What’s not working?! Definitely new to AirTable and formulas in general so if there is another way to approach this, I’m all ears! :). Thanks!

I have a concatenate function in a table’s primary field. One of the fields it combines is a rollup of companies in a Companies table. Typically, it’s one company per record, occasionally two. To strip the pesky quotes I get when there’s comma in a company’s name (e.g. Habor Lights, Inc.), this formula only partially works: SUBSTITUTE({COMPANIES}&"", '"', '') When two companies are rollup up comma-separated, the formula strips that comma which is in fact needed Company 1: Toyota Research Institute Company 2: Abbot Ventures Rollup: Toyota Research Institute, Abbot Ventures With formula applied: Toyota Research InstituteAbbot Ventures Can I improve on this formula or is there a better approach altogether? Edit: tried the in-rollup approach suggested in this thread – Close, but no space between company names. SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(Companies)), '"', '') Result: Toyota Research Institute,Abbot Ventures

Hello, I can’t select cells to include in final result. Please help with how to fix it. Thank you!

Hello! Thanks in advance for any help. I am trying to make a formula that will extract a text string from a URL. In this case, the URL is a playlist on Spotify, and the text string is the playlist ID number. Spotify Hang on Little Tomato Denny P · Playlist · 1 songs The ID comes after the playlist/ and before the ?, so in this case it would be 5napFAiwbcMwne6jUfmspg I know this should be fairly easy with REGEX or something, but I’m not up to speed on that stuff yet, so any help would be appreciated! Thanks!

Hello All! I’m using a formula to concatenate different dates an area was worked on as the record ID. In one tab I have all the offices and all of the associated data with them, and in the other I have a submissions page that’s linked to the office page. My problem is some of them concatenate as “AreaName” - Date and others are AreaName - Date. I have attached some screenshots for a visual. Thank you for your help!

I’d really like to create an automation where if a record matches a certain condition, an email is sent every two weeks. So basically, I’d have two triggers, record matches condition + scheduled time. Is that possible?

The problem is every time I bring up the table it adds today’s date in there what I would like to do is only add the date once when the record is created.

I would like for dates in the Automation to appear as July 4, 2022, for example. Instead, they appear as 2022-07-04. I have the date field formatted as ‘Friendly’. However, this does not appear to translate to the automation. I thought about creating another field that formats the friendly version of the date as text. But, that seems a bit cumbersome for each of my date fields. Any workarounds that I’m not considering?

Hi there, I"m new to Airtable and I keep seeing this “Invalid Filter By Formula” error. I’m not sure what parts went wrong but this is my code: Are there other methods to get a single user ? THANK YOU for your help ! THE CODE export const findUserByEmail = async (userEmail) => { const userRecord = await table .select({ filterByFormula: `user_email=${userEmail}`, }) .firstPage(); if (userRecord.length !== 0) { return userRecord[0].fields; } }; The ERROR AirtableError { error: 'INVALID_FILTER_BY_FORMULA', message: 'The formula for filtering records is invalid: Invalid formula. Please check your formula text.', statusCode: 422 } My temporary workaround solution is to use JavaScript filter method - probably not ideal but I haven’t figured out other solutions yet. e.g. const allUsers = await table.select().all(); const filterUser = allUsers.filter( (user) => user.fields.user_email === userEmail );

I’m wondering if you can help. I have two tables ‘Vehicles’ (a list of all vehicles, their type etc) and another ‘Bookings’ (dates, booking purpose) that has a linked field to ‘Vehicles’ allowing me to link multiple vehicles to one type of booking (via the primary field, which is a vehicle identifier unique to each vehicle) Having a separate table for bookings saves me from having to continuously make endless date columns for each new booking. I need to build an automation that does the following Booking Table Recognises when a new record has been created and completed (currently signalled through a manually selected checkbox, easy enough to do!) Booking Table Searches the linked field and recognises the vehicles that have been linked Vehicle Table Find the records for each of those linked vehicles Vehicle Table Link the booking ID to the vehicle record under the ‘Bookings’ Column Happy to use an extension if that will do the job. Thanks!

Hi! I am trying to build a formula but I haven’t quite found the solution in the community. I have a table with different customers information, with fields such as “name”, “surname”, “email”, “phone”, “company”… I would like to create a separate table in which I can count how many customers I have from the same company. So let’s say I have: Table 1: customer 1 — Company A customer 2 — Company A customer 3 — Company B customer 4 — Company C customer 5 — Company C customer 6 — Company C In table 2, I would need to show: Company A — 2 Company B — 1 Company C — 3 I have the company field built as “single select”, since I work with a limited and specific list of companies. Not sure this makes any difference in terms of formulas. Thanks a lot in advance!

Hello Community! I am trying to figure out the following with a nested IF formula- returning a value based on the name of the project. I have 8 different projects to categorize into one of 3 categories. I keep getting an error message saying to check the formula - what am I doing wrong? IF({Project}=”Hours”,“Personnel”, IF({Project}=”Sales”,“Finance", IF({Project}=”Marketing A”,“Finance”, IF({Project}=”Marketing B”,“Finance”, IF({Project}=”Expenses”,“Finance”, IF({Project}=”Diversity”,“HR”, IF({Project}=”Life Event”,“HR”, IF({Project}=”Executive”,“HR”,"Unknown”)))))))) Thank you!

Hello community, my record has a date and looks up a roll-up like the below example. I’m trying to figure out the right formula to extract the string below: Rollup: …5. 04.05 Vila Falo,6. 05.05 Alvero,8. 07.05 Gjirokastra… Record date: 05.05 Current formula: MID({Rollup}&“”,FIND(DATETIME_FORMAT(Date,‘DD.MM’),{Rollup}&“”)+5,XXX) Output: Alvero,8. 07.05 Gjirokastra… Desired output: Alvero XXX is the part where my monkey brain fails to come up with a solution to determine the length of the string to be extracted. Your advice would be appreciated. :slightly_smiling_face:

Just started working with a dental lab and I’m trying to create a base for targeted outreach to dental offices. After being out in the field today I have entered my notes and assigned a rating to each office. I would like to use 4 stars or 5 stars ratings to trigger an email automation to respective teammates about these particular offices.

Hi!, I am trying to use the REGEX formula, but I cannot use it propertly. I need to extract names from a string. To make it easier the names are written after a hashtag #. This are examples #TOM write the essay of a client Complete the packing list for #Peter From an old post I managed to use this formula REGEX_EXTRACT({Transaction Name}, “#[^\s]+”)) written by Justin Barret This formula kind of works, but is giving me the answer with the hasthag in front #TOM #Peter I needed it without the hashtag. Any clue about how to do it? I am getting lots of trouble to understand the sintaxis of the REGEX formulas. Thanks!!

Is it possible to round up those really long decimals? Thank you!

For example, I have a date field, some of which are filled in, some of which are not. I want to make a second date field that is a time offset from the first. I want this formula field to stay in date format so it can be exported to a calendar, etc: However, when the first field is blank, the second field shows an error instead: How can I change my formula to output “absence of date” when the first field is blank? Using IF(BLANK( to return "" or 0 breaks the date formatting:

In a table of orders, each order is related to a client. Is there a way to look/roll-up data from other orders of the same client on the same date?