Solved

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?

For a table with organizations I have a field {funded by} which lists all the organizations the organization is funded by. These organizations come in three types listed in {organization type}: government agencies, businesses or nonprofits. Now on the basis of the field ‘funded by’ and the fact that each of the linked records in that field have a feature that specifies whether they are government agencies, businesses or nonprofits, I want to use formulas to create three separate fields, ‘funded by government agencies’, ‘funded by businesses’ and ‘funded by nonprofits’. So I’d like to use a kind of formula like “for each item in {funded by}, if its value in {organization type} is ‘business’ then add to this field {funded by these businesses}.” How can I best do this? Am I missing something obvious?

I recently asked for help in this community and someone shared two example bases with me to try and help answer my question. I am now trying to delete them but I have no option to do so. How do I get rid of these bases?

Hi there, I want to return a value of “Morning news” when the time from a live date falls between 5AM and 9AM. This needs to be irrespective of date, so I made a field that parses the time from the live date field. Currently I’ve got: IF(AND(VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) <90100, VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) >45900), “Morning news”, “Other”) But that’s returning an error. I tried it without the Value parameters too.

I’m trying to figure out this formula, which I based on a different formula that I got to work successfully, but am hitting a wall. Basically, I want the formula to reference 4 date fields (Edit Start, Format Start, Production Date/Time, and Ship Date). If there are values in any of those fields, I want it to return, as a string, the date, and either "Edit Start: ", "Format Start: ", "Production Start: ", and/or "Ship Date: " ahead of each date, as applicable. So, a full string with all dates filled in should look like: Edit Start: XX/XX/XX; Format Start: XX/XX/XX; Production Start: XX/XX/XX; Ship Date: XX/XX/XX However, one or more of these date fields may be empty at any given time, so I want the text string to adjust accordingly and not show the lead-in if the corresponding date field is empty. Here’s my attempt, which won’t compute. “Edit Start: ” & DATETIME_FORMAT({Edit Start}, ‘L’ ) & IF(AND({Edit Start}, OR({Format Start}, {Production Date/Time}, {Ship Date})), "; ") &am

Hey all :wave: , I don’t find a solution for my stock tracking system. I have a database with all the products: Then I have a database with my orders to the suppliers linked to my product table. Each order has multiple products. Until this step is perfect. :ok_hand:t2: But I need to add quantity to each product :confused: 2 Items of X 3 Items of Y 1 Item of Z Do you know how I can add that? If I insert a general #number field is a quantity for the order and not for each product item. Thanks.

Hello, I am trying to create a prefilled form that automatically populates the current renewal date for a piece of software. I am running into an issue where the date that shows up in the form is exactly one day behind from what it should be and I can’t figure out why. As you can see in the above screenshots, the link generates with the date “2023-01-14” but shows up in the form as 2023-01-13. Any clues as to why this is occurring will be much appreciated! Thanks Doug

Hello, Airtable community. I would like to know how I can automatically add the records in Table A to a single record in Table B. I already connected the tables and what I want is that every time a new record is added in Table A, the records are automatically added to the connected field in Table B. I tried Automations, but I couldn’t figure out the process. I appreciate any guidance in advance.