Solved

Hi Everyone, I’m trying to set up a Project management Tool for our CPA firm. Do we have any other CPA firms in this community who use Airtable to manage their workflow? what template or format do you use? Also can anyone give me a customer service phone number for Airtable. I can’t find it online. Thanks

I have a lookup field called “Jobs” that returns fields from a linked record. In my case these are job descriptions. It shows them as a comma separated list, e.g. “CEO, director, manager…”. I would like to convert the commas into line-breaks with a formula. But SUBSTITUTE(Jobs,", “,”\n") returns an #ERROR! What am I doing wrong?

Hey AirTable Community Me again, I am currently using Zapier and WATI. I have a base of all my Leads, sometimes, Leads are created twice intentionally. When a record is created in AirTable, I want Zapier to notice. Then Zapier will tell WATI to create a Contact based on the details. Then Zapier will also tell WATI to send a template message to the person. The above is easy enough. The problem lies thereafter, since I can have Leads with the same Phone number, I don’t really want WATI to create two of the same contact and send the same message twice. So my idea is that after a record is created in AirTable, I want AirTable to look for another Lead that has the exact same phone number, but not the same Lead Unique ID. If the result is TRUE, then do nothing. If the result is FALSE, then proceed with the rest of the steps. I am slightly limited to Zapier as a tool for now since the WATI integration with Integromat/make is a little “wonky” and unintelligent (or maybe I am wonky and unintell

Hello Everyone, I am having a methodology issue.I am working on a personal price tracking project, in my case, it is computer parts.  But I believe this could translate to almost anything. I have multiple tables, one for each component type (CPUs, RAM, Motherboards, etc).  These tables display the characteristics of the product. There is an additional table that is called Updates, the updates table links to each of the component type tables, and tracks, price, link to vendor and date the entry was added. What I'm trying to achieve, is on the components tables, I'd like to show the most recent price for each component.   I've read a few examples that people have said about sorting by having a Rollup of the date:max(values)  However, I'm not able to discern how I will retrieve the price if I did that.   Can anyone orient me toward solving this problem?  Many thanks

Right now I have a multiple select field as follows: The script I’m trying to run has already identified the record IDs of all records with “Split Invoice” as a status. What I’m trying to do now is to remove that split invoice status and leave the rest (For example Estimated, Invoiced, those can all stay). What’s the best way to do this? All I can think of is to create an array of all statuses except for “Split Invoice” but I’m not sure how to write the syntax for that either. Code below: let table = base.getTable("Items"); let queryResult = await table.selectRecordsAsync(); var rec_IDs = [] var rec_names = [] for (let record of queryResult.records) { try { for (let status of record.getCellValue("Status")) { if (status.name === "Split Invoice") { rec_IDs.push(record.id); rec_names.push(record.name); } } } catch(e) { { continue; } } } // Script operations go here // As a fin

I have a board where I track ice cream names and the flavours they come in. Names are self generated where are flavours are common names such as vanilla or chocolate and so on.I want to use the npm airtable code here:https://www.npmjs.com/package/airtableand what I want to achieve is if a new flavour is added to the list of flavours, I want to also add this new flavour to the list of options available on the Airtable multiple select field. I am trying to mimic the code here:https://airtable.com/developers/scripting/api/field#update-options-asyncconst table = base.getTable("Tasks"); const selectField = table.getField("Priority"); await selectField.updateOptionsAsync({ choices: [...field.options.choices, {name: "Urgent"}], });However using the npm package it seems these functions are not available.I also tried using Curl as below:curl --request PATCH \ --url https://api.airtable.com/v0/meta/bases/{baseId}/tables/{tableId}/fields/{fieldId} \ --header 'Authorization: Bearer {myToke

New to Airtable here and trialling to see if I can work out a solution for my department.I have two bases, one that tracks People and the other that tracks Activities. Within Activities are fields for 'Participants', 'Facilitators' and 'Guests'. Multiple people can be added to each field.I am trying to create an interface that shows a count of how many people there were across all activities in the participant's column. However, I can only seem to get it to count how many activities there were to have any participants, which is a different number.I can see the next trap would also be that, if I was able to get it to count items within a column, it would count them regardless if they were original or not. This is desirable sometimes but it would be good to only count unique entries across multiple activities because often the same people attend multiple activities. How do I effectively say "count unique entries within the participants' column for all activities"?

We use a long text, rich format, field for explanation purposes however even with the height set at Extra Tall a lot of text is still out of view. Is there a way to increase the height for certain rows or a way to inform users letting them know to expand the record to read more? Not all fields require the expanded height and we really want to avoid spreading the fields so far, as it becomes harder to read all other fields.

I am trying to use Airtable as a resource to build concert programs for a music organization. I have one table (called Compositions) that contains what will be a very large list of compositions, and each composition may have anywhere from 0 to 6 movements. The movements are currently input as text fields. I have another table called Program Builder where I will be selecting from those compositions and the movements contained therein.I have successfully linked the Program Builder table to look up compositions from the Compositions table. However, for any given program that I'm building I would like to be able to select specific movements of that composition, but not necessarily select all of them.For example, I have the composition Cello Suite No. 3 in C Major, BWV 1009 which has six movements: I. PreludeII. AllemandeIII. CouranteIV. SarabandeV. Bourrée I / IIVI. GigueSo for example, when I'm using my Program Builder, I can select Cello Suite No. 3 in C Major, BWV 10

I'm very new to scripting and my javascript knowledge from college is a very distant memory, so bear with me. I'm trying to create a script that will essentially generate a mini-form with three fields from my table that will create a new record when filled out.I've been successful to a point but running into some issues. Code is below. Here are my questions:Is my syntax correct to create one new record with multiple fields filled through user input?How do I ensure the user input can be validated as a number for a currency column? My parseFloat() function is still causing errors (scripting block says it cannot create a record because it needs to be a number).Both Payment Month and Payment Year are requiring objects, which I assume has to do with the fact that both fields are single select. This is important because I have functions and automations dependent on those being entered the same way every time. How can I alter my code to satisfy that requirement?Thanks for any help! // pi

For folks that write their formulas in VSC (or Sublime Text or any other code editor), what syntax auto-coloring do y'all use? There's nothing specific to Airtable's syntax, so I'm wondering what folks have been to be most pleasing and/or function the best? 

Hi everyone,The question may be simple or have an obvious solution, but I am struggling with this and can't solve it. Here goes:I need help in assigning values (in €) to different companies in different projects. Let's say this is kind of a portfolio approach to a company's project.On sheet 1: I have a Project List with a lookup column to a partner list (on sheet 2).On sheet 2: I have a Partner List with indication on which projects they are participating.I would like to assign, on sheet 1, an indication of each partners assigned project value (€), and have that reflected (separately and total value) on sheet 2.One project can have multiple partners, and the same partner can be in several projects. Do you have a solution for this? Or is there a formula for this?Thanks in advance,J.

I have a base that currently has a single table which is populated via an integration with Jotform. There may be more than one record per customer and I'd like to create a view which will only show the most recent submission.I've got "last modified" as a field and I'd like to use that as a filter along with the customer's name.Is it possible? If not is there a workaround?Thanks

Hi, I'm trying to figure out how to work with attachmentsAfter looking at several posts on the subjectFor example regarding photos from my website or from Google Drive.If I upload these links in the attachment field, will they become invalid after a few hours?So currently it is not possible to display images in the table without using the automation that uploads the images each time?Or is the problem only in using the link, but the image will still remain displayed in the table?I would appreciate recommendations and insights.

I have a sub-task field (long text with rich text format- check box format) I wanna Insert a field called “Processing” to calculate the % of sub task I have done (checked), which formula should i use for that ?

Hello!I am getting error while requesting api - using your javascript examplevar Airtable = require('airtable'); var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('XXXXXXXXXXXXXXX'); base('Settings').find('recKrP4fJsCmnavbd', function(err, record) { if (err) { console.error(err); return; } console.log('Retrieved', record.id); });GET https://api.airtable.com/v0/undefined/Settings/recKrP4fJsCmnavbd? 404AirtableError {error: 'NOT_FOUND', message: 'Could not find what you are looking for', statusCode: 404}

Hi - I'm new to AirTable so I may have missed something, but if I duplicate an existing record (say a "Plate" in Product Database), change the name to "Cup" & other data to I end up w/ 2 records "Cup" & "Plate" in the table - as expected.  However in other tables where "Plate" was included as a linked record, I also now show both records in linked field in these tables. How can I prevent this behavior?

Hi,Who's gonna help to :- concatenate Row 1+2+3 with a limitation of 3 character per row and a total character of 16 ?I need to create unique reference (in e-commerce) called Variant SKU looking likeModele, Size, Color but as we are limited in number of characters (16) it would look like#MOD-SI-COLIn this view : Variant SKU will be a formula including#SAM (from marque (brand in french)-modele-vendortaking only the 3 first characters thanks guys 🍭

Hi! I am trying to solve the following:I have a lookup field that lists multiple dates multiples times (the same day in each respective record). Like so: 10/10/2022, 10/10/2022, 10/10/2022, 10/10/2022. This is the "Move In Date ~ Expansion" Field. I only want one date listed and clean like so "MM-DD-YYYY". So I created a Formula Field ("Move In Date ~ Expansion (Calculation)" Field and did an ARRAYUNIQUE formula, however, it shows up with additional 0's and is not clean. How can I adjust this formula so that it lists the date of the lookup field only once and clean like so"MM-DD-YYYY"? Or does anyone have any other solve here? Thank you!

Before starting this, I will say I have no background in writing script.  Any script/code I have posted was created using OpenAI API.What I am trying to create is an automation that upon an update in a selected Single Select Field will begin an automation that utilizes a script to input today's date & time in an adjacent Time/Date field.When I entered the code below into the Edit Script window and ran a test, the test failed with an error stating "ReferenceError: document is not defined at main on line 12" Can anyone provide feedback of what I need to change to make it work? var today = new Date();var options = {  timeZone: 'America/New_York',  hour: 'numeric',  minute: 'numeric',  second: 'numeric',  hour12: true};var timeString = today.toLocaleString('en-US', options);var dateString = today.toLocaleDateString('en-US');var dateTimeString = dateString + ' ' + timeString;var dateTime = document.createElement('div');dateTime.innerHTML = da

I am new to Airtable, but I cannot find a simple formula to calculate years of service for employees from the start date. I can get it to calculate years, but would like to see it in decimal format. For 8 1/2 years I would like to see 8.50. I am currently using the below...Adding the "months", did not do anything different. DATETIME_DIFF(TODAY(),{Start date},'years', "months") Any help is appreciated... 

Hi,I need to supply to my team of buyers the "best deal" for any given category and sub-category of products in our database. At first, it was easy to group by category / sub-category into a view sorted by price ASC. But over time, the database grew big and we now have 7000+ products splitted into 25 categories and 50 sub-categories ...that makes it long and tedious to scroll through this view to locate manually the cheapest product for each category/sub-category combo (about 150 unique combinations in total).My question: is there a way to get a view that'd output the best product per category / sub-category combo in a flat table format. In other words, this view would contain ONLY the 150 best deals.Many thanks!

Is it possible to limit the amount of rows in a table view for use in a chart? E.g. I want to see my top 20 accounts based on sales value. However when I create a chart, based on sales value, I cannot limit the results to only the top 20.

Hi Guys,After hours of trial and error, all errors, I cannot for the life of me figure out how to join data from two different tables.Table 1 has INVOICE NUMBER, CUSTOMER NAME, and CUSTOMER EMAIL, one Invoice Number per row.Table 2 has several rows with the same Invoice Number, and one item on each row. So, if the customer just bought one item, there would be only one row with Invoice Number and Item.If customer bought three items, there would be three rows, with the same invoice number on each row and one item per row in the Item field.What I want to do is to build a THIRD table that has Name, Email address, and Item, so I can then sort who bought a specific item, or all the items a specific client bought.But try as I will, I can't figure out how to do it and ANY help would be VERY appreciated.So, Table 1:Invoice 1 - Client 1 - Client Email 1Invoice 2 - Client 2 - Client Email 2Table 2 hasInvoice 1 - Item 1Invoice 1 - Item 2Invoice 2 - Item 3Invoice 3 - Item 1Invoice 3 - Item 5Invoice

Hello community!I am looking to display the amount of an hour of additional service to be applied to a price depending on the type of service (A, B, C or D).This number of hours can be 0, 1 or 2.I have already created a column that automatically displays the number of hours to be invoiced (0, 1 or 2).I have already created a column that automatically displays the type of service (A, B, C or D).Here are the possible scenarios :- If my hours column displays 0h and the product type is A, B, C or D, then display 0 €- If my hours column displays 1h or 2h, and the product type is A, then display 10 €- If my hours column displays 1h or 2h, and the product type is B, then display 20 €- If my hours column displays 1h or 2h, and the product type is C, then display 30 €- If my hours column displays 1h or 2h, and the product type is D, then display 40 €Can you help me please ?