Solved

Hi guys, have been sitting with this a couple rounds now and can’t get it right. Im extracting a telephone number from a column in Airtable, but when there is no phone number or the word message I get the famous #ERROR. How can I get Airtable to just show it as blank? My formula looks like this: IF({Telefon 1},(LEFT({Telefon 1}, FIND("Message", {Telefon 1}) -1)),BLANK()) Any advice is welcome Regards Jesper

Hello community, I already searched through the forum and found a helpful formula - with a minor problem based on that formula that I am trying to fix. Our datetime_format is D-M-YYYY. Background: We have a field called {start date} for when a new client relationship starts. Let’s say one client started on 1st September 2022 (past date) and another client will start on 1st November 2022 (future date). We have a report automation that we want to send out every year. The first report will be sent out one year after the client was onboarded, that process starting now. That means we do not want to send out multiple reports for a client that started on 1st July 2010, instead they will receive their first report on 1st July 2023. In our understanding we need a date formula adding one year to the {start date} and also consider the current year. Formula: IF({start date}, DATETIME_PARSE(DAY({start date}) & “-” & MONTH({start date}) & “-” & (YEAR(NOW()) + IF(DATETIME_FORMAT(DATEA

So I have the following: IF( TODAY() > {Date Due}, “OVERDUE” ) I have a Column named ‘Days’ for days left to do my task, so I made the following, using the nested IF tutorial: IF( TODAY() > {Date Due}, “OVERDUE”, IF({Days} < 5, “Soon”) ) I’m trying to figure out how to add my ‘done’ column to this but I keep on getting errors. So far I tried: IF( TODAY() > {Date Due}, “OVERDUE”, IF({Days} < 5, “Soon”) IF({Done} = Yes, “Done”) ) but it says invalid. idk how to return text from a multiple choice??

Hi Airtable Community, I have a base that receives and manipulates data coming in using a webhook. Data comes in as a single record (so each webhook brings in a single record of data) and that single record needs to be processed completely before the next record can be considered. In other words, the automation needs to run to completion on one record before the automation starts again for the next record. The automation consists of 1 create record action followed by 5 script actions, and is initiated by the receival of a webhook. The automation runs as expected when tested, however I have started to notice missing data assignments that should have been completed by one or two of the script actions. I suspect this may be because a number of webhooks are received within quick succession, which leads to automations kicking off before the previous one has been completed? Does this sounds plausible? And if so, are there any suggestions on how to resolve this issue? Cheers, Ryan.

Dear Airtable Community, I have created an automation that has multiple actions: Triggered by a webhook (beneficiary data comes in with various traits) Creates a new record in Table 1 with this new data as is (no manipulation) Runs a script that assigns a unique ID to beneficiary (based on this new record in step 2) Runs a script that assigns a household ID to beneficiary’s household (based on this new record in step 2) Runs a script that cleans record (created in step 2 with new assigned variables created in steps 3&4) and then creates a new record in Table 2 I have tested the scripts and they all run as expected - however, in steps 3 and 4 I use the .updateRecordAsync function: Step 3: var recordID = inputConfig.NewRecordID; await tableRaw.updateRecordAsync(recordID,{“Household Member ID Generated”: idFinalString}); Step 4: var recordID = inputConfig.NewRecordID; await tableRaw.updateRecordAsync(recordID,{“Map Block Code”: codeBlock,“Household Unique ID” : HhUniqueID}

Hello. Let me preface this by saying I am not great at scripts, so your patience and clear explanations would be greatly appreciated. We currently have a script (no idea who wrote it ages ago) in one of our tables that works by the pressing of a button. I am trying to get the same script to work by triggering it with a checkbox so I can use it in an automation. Thinking I could just copy/past the script, but that clearly doesn’t work. I get the error : TypeError: input.recordAsync is not a function at main on line 2 I know I have to make some changes for it to work properly, I’ve seen similar responses but couldn’t figure out exactly what to do. The original working script that is currently triggered by a button is: let mapperschedule = base.getTable("Mapper Schedule"); let record = await input.recordAsync('Pick a record', base.getTable('Mapper Schedule')); let topaste = record.getCellValue('Update Mapper Schedule Name - To Paste'); let name = record.getCellValue('Name'); output.markd

HEy All I’m trying to create a CURL API request that Searches for records that are {WEEK} = “24” AND{PAGE} contains “22”. My PAGE field is a look up so it might have more than one page number in it. If PAGE has one one record in it I can use filterByFormula=AND(%7BWEEK%7D%3D%2224%22%2C%7BPAGE%7D%3D%2222%22) I think I need to use SEARCH or FIND but I cant work out the syntax. Thanks in aadvance

Hi all – sorry if this has been discussed before, I had a search around and couldn’t find anything. I’m using an Interface as the front-end to an Airtable → Zapier → Webflow CMS integration. The interface contains a bunch of fields for content — headlines, body text, banner images etc – that I want users to be able to tinker with. However, I’d like to prevent changes from being sent to my Zapier automation until a user clicks “Save” … rather than the cells updating one-by-one as the user works their way through the interface. Is there a straightforward way to make an Interface only commit the changes to the base on a button click? Or is my best bet to do this manually … i.e. make my Last Modified column only update when a certain button is pressed? Many thanks in advance Dave

I am using pre-filled forms to collect records from users. The form fields include linked records. I’ve just realized that anyone with the URL can simply remove the parameters at the end of the URL link to view the base form URL and as a result, will be able to see all of the linked records in those fields. For example, we have a link for a vendor to add a bid. It is pre-filled with that vendor’s information from a linked field. If the vendor is smart, they can remove the parameters from the URL to see the base form and then view all of the vendors in our table from that field. How can we collect data from users with pre-filled parameters in a way that they cannot access the base form?

Hello, I apologise in advance for my simplistic coding effort. It has been 40 years since I dabbled in Visual Pascal and PHP :slightly_smiling_face: I am writing an update script for use via Airtable web form submitted updates. The record created in the update table is polled by the script and only the completed fields in the update record, ie only fields that were filled in on the web form, are updated in the original record, contained in a separate (original) table. A series of “if-then-else” statements determine which fields in the original record require updating and an array named fieldsArray, containing the field name and the associated field value for each field that requires updating, is constructed. The recordID of the original record (that is to be updated) is passed to the script via inputConfig and I construct an updateRecordAsync() function using that recordID and the fieldsArray. My problem is that I cannot work out how to construct the updateRecordAsync() function

Hi community, I’ve built a labor tracker where we will track hours worked per Person/Project/Day. Based upon an 8 hour day, I want to calculate the percent of their day worked on that particular project. For example, if Veronica works on Project P-035.1 for 4 hrs, she submits that through a form by entering her name (linked field), the date she worked, the project (linked field) and the hours she spent on that project that day. In the case of working 4 hrs, the percentage worked on that project for the day should be 50%. I’m hitting a total blank on how to do that with a formula. (right now the % field is just a manual thing, because I don’t know what the formula should be) After that, I’m hoping to figure out how to calculate percentage worked on the project for the Month, because that’s how our budget predictions are created, and I want to compare and contrast predictions with actuals. Any help would be greatly appreciated!

Hi! I am trying to create a simple If formula that shows how much my clients owe me. So if the amount billed is equal to the amount paid, show £0 else show the balance. My issue is, I am trying to append the £ sign before the balance calculation but it doesn’t work. IF({Amount Billed}={Amount Paid}, “£0”, ‘£’ & {Amount Billed}-{Amount Paid}) It only works if I place the £ after the balance. IF({Amount Billed}={Amount Paid}, “£0”,{Amount Billed}-{Amount Paid}&’ £’) Any help on how I can accomplish this?

I need to compare two date fields to get a specific result. I am not an expert at Airtable, so my explanation isn’t very good :). My base is for tracking training. I need to mark a month “excused” if a person’s hire date is after the meeting date and then mark the month as completed or missed if their hire date is before the meeting date. The hire date is 9/1/2022, and the meeting date is 10/1/2022. That person is required to complete the monthly program. Any suggestions on a formula? - Wendy in Michigan.

Running into a problem creating an order tracking form. I have 4 tables Customer Info - name, address, etc… Product List - products and prices Orders - each row has customer name and product and product quantity purchased. Primary field is a formula concatenating Customer name and product they purchased Now I’m creating a 4th table Order summary by customer. Primary field is an Autonumber. Second column is a linked record to Customer Name pulled from the Customer Info table. What I want to do is group by customer, everything they ordered and the total they owe. Where I am running intotrouble is I can’t pull the Quantity field from the Orders table using a Lookup; I don’t know why. Please help! Thank you

I keep getting the “Microsoft reported an error while processing this action: Id is malformed” error. I tried creating an autonumber field called “Event ID” that I reference in the Event ID automation field. Is this an ID I have to manually enter from a source? Thanks

I am connected the API of Airtable to Power BI but at the time of displaying the data it only shows me 100 records when the base has more than 1000 records.

I have 4 columns in my table, each containing numbers. In the Formula column called ‘Above Threshold’ I’m trying to create a formula that says if any of the values across the 4 columns is >100 output ‘Above’ and if <=100 output ‘Below’. So something like this IF(({Total users (last 30d)},{Total users (prev 30d)},{Total users (last 90d-120d)},{Total users (last 120d-150d)},)>100, "Above", "Below") That formula doesn’t work, so is there a way of doing it without using nested IF statements?

I have a table with Rating field which can have value up to 7. With current API I can get value for each record, but I can’t get metadata for that field, to know possible max value. Is it possible at all?

I am trying to create and if and statement for one field being empty and the other being filled. My fields Problem Category Closed Date Closed date always needs to be filled for me to provide a return to my field Charge Type. If the problem category is blank then I need to return “Marketable”. If the problem category is filled then I need to return “Problem”. I have tried multiple If And statements always getting an error or undesirable result. The latest one I have gotten is as follows If (And( Problem Category = BLANK(), Closed Date != BLANK()), “Marketable”, “Problem”)

Hi, so I am trying to make a formula that fits my gardening schedule for the upcoming spring season. I have the pro plan if that makes a difference in what I am limited to do. Here’s an live example of the problem I am having so far. I will plant my cilantro seeds on 3/22/23. It takes 4 weeks till harvest. So I have 4/12/2023 as the ending date. I will need two other harvest date’s after that. The problem is that I need formulas for each plant which is a total of 40. They all have different dates and different weeks till harvest. Would I need different formulas for an auto-fill each time I put seeds into the ground or am I able to create conditions for it to all work? Thanks.

Heyy everyone, I wonder if you can help, I am not the best at Airtable so rely mostly on learning as I go and your threads. I currently have several columns which count the time(days) between Date 1 and Date 2 but now I am looking to make it exclude a list of public holidays, currently, it just measures working days. So this is the formula currently - IF(OR({DATE 1} = BLANK(),{Date 1}<{Date 2}), BLANK(), MAX(WORKDAY_DIFF({Date 2},{Confirmed Date 1})-1,0)) But I can not seem to figure out how to make this formula not count the below, I’d have to implement this into several columns with similar formulas ! 01/01/2021 02/04/2021 05/05/2021 31/05/2021 30/08/2021 27/12/2021 28/12/2021 03/01/2022 15/04/2022 18/04/2022 02/05/2022 02/06/2022 03/06/2022 29/08/2022 19/09/2022 26/12/2022 27/12/2022 02/01/2023 07/04/2023 10/04/2023 01/05/2023 29/05/2023 28/08/2023 25/12/2023 26/12/2023 Any help is welcomed :slightly_smiling_face: Thanks

I think I should be able to do this but I’m not sure how. (I’m definitely an AT beginner!) I have a base for managing summer rentals. I’m pretty much treating it as a spreadsheet with each record a tenant name and then a bunch of info in fields… Lease, total rent, deposit, deposit received, etc. I’ve used this base for two years. I have a formula for figuring the lodging tax BUT it has changed this year. 1) I don’t want to change the formula since that will impact my records for the last two years 2) how do I set up AT so that I can reflect changing tax rates over the years? I think I should have a another sheet that calculates the tax and then pulls it into my main sheet based on the year I’m working in. create a table for the taxes and then a lookup formula? I’m not clear on how to do that. On the main sheet is a column that is empty which is where I think I should be pulling in the tax info…?

Hi We would really appreciate some assistance with one of our formula fields. Background - also see screenshot: In our first column, we have a date field called “Lifetime Last Payment”, which we manually complete. In our second column, we have a formula field called “Lifetime Next Payment” with the following formula: IF({Lifetime Last Payment}=BLANK(),BLANK(), DATEADD({Lifetime Last Payment},1,“months”)) Which automatically calculates by taking the date from “Lifetime Last Payment” and adding one month. Our third column has a formula field called ” Lifetime Payment Status” with the following formula: IF({Lifetime Next Payment}<=TODAY(),“ :triangular_flag_on_post: Change Last Payment date”, BLANK()) In our fourth column, we have another formula field called “Final Lifetime Instalment Date”, which calculates the final date of payment for each individual line based on the instalment criteria. Requirement: We need help adding/updating our third column formula: IF({Lifetime Next Payment

I am having a bit of an issue because right now I have an automation that takes that takes the URL from the image as a formula and when the automation is run, puts the image url in the attachments field of another table. Right now this works great, however, I know that Airtable has an update to the attachment field coming soon that will make those links expire every 3 hours. Is there a way to have an automation take an image and put it in an attachment field other than with the link method I am currently using, so it will not impact me after Airtables Attachment update comes out? Thanks in advance for your help!

Hi all. I have a fairly quick problem. I’m a relative novice to JavaScript and coding, so forgive me if this is a silly question. I found some code that will duplicate a record and copy all fields, with the ability to exclude certain fields. I can fill in my table and exclusion fields, and it works as intended. The original thread is here: Script to duplicate a record with select fields only or create new item based on another Extensions We’re using Airtable as a PIM for clothing and we sometimes do modifications that creates a new style code. A lot of the information is the same (as it’s just a modification), but I don’t want to copy over any linked fields or certain notes on an item. I was wondering if it was possible to create a script that would let you select an item you want to modify and create a new item with select information from the base item. The links are in the same table FYI. I am looking to do exactly what was described in this post. T