Solved

I have the following formula that spits out a number of days since the last event for an entry and if no event is found, it returns 9999 instead: IF({Days Since}=0,‘9999’,DATETIME_DIFF(TODAY(),{Days Since},‘days’)) My issue is that the formula field doesn’t recognize this result as a number and won’t let me format and filter as such. Is there any way I can return a result that allows formatting as a number? I tried using BLANK() in place of 9999, but the issue is that I need to filter results that return a number greater than say 90 or 180. So blank results get filtered out when I would want them to be filtered in. And this is part of a longer filter so I can’t use an OR statement in the filter.

I’m trying to import data from an API. I’m able to authenticate via my terminal, but I’m trying to write a script to automate that process (the access token is only good for 24 hours, so they recommend refreshing the authentication before every request or once per day). As I understand it, fetch() is the way to do an HTTP POST. I’ve written a function to do this, and I keep getting a 401 Unauthorized error. It looks like nothing after the url is getting passed on. I’m sure this is some kind of syntax error on my part, but I’m not familiar enough with JavaScript or API’s to know what it is. Any help would be appreciated. Code is below: async function authenticate() { let authentication = await fetch(authenticate_url, { method: 'POST', header: 'content-type: application/x-www-form-urlencoded', data: `grant_type=refresh_token&client_id=${clientID}&client_secret=${client_secret}&refresh_token=${refresh_token}&username=${username}&password=$

I’ve got a rollup field tied to connected records with dates, and I’m not 100% sure how it works. My goal is to have this rollup field only show the most recent date. So if it is rolling up from two entries, lets say: 1/1/2020, and 10/10/2020, I only want to see 10/10/2020. I’m assuming I use the MAX() attribute for that…but I’m not sure. Can anyone confirm what the appropriate aggregation formula is to display the most recent date of several in a rollup field?

Hi all, I came up with the following design for the internal BI system. The first table is used to store the payments. (Payments table) I also need to calculate the monthly sales statistics for each sales representative along with their quota (a predefined value that differed from month to month and added manually each time) For that purpose, I have an additional table. (AM table) The combination of (sales_rep, month, year) creates the unique id for the AM table and the linkage between two tables works very smoothly. However, I believe there is room for improvement since I have to Link unique-id-am field to AM Quota manually every time. I just drag the cell to AM Quota as you usually do when copying the column. I want to automate this process and write a script that will perform this action for me in the add new row event. I thought of doing something like that, but obviously AM Quota doesn’t allow me to do that due to the conflicts of type. let table = base.getTable("Payments"); le

What is the syntax for an IF statement that only generates output if two conditions, in two fields, are met? Thanks for your help!

I want to hit an API much like the currency conversion example. However, the example updates all the records in one go. Instead, I would like to fetch the API result for just the one record that was either created or updated. The record ID is available in the input, so that should work fine. But what about the table name? I happen to be partitioning this data into multiple tables (each with the exact same schema). The table ID or name doesn’t appear to be available from the trigger/input. And every example I’ve seen hard-codes the table name in the script. Since the automation is triggered on a record which exists within a table, I believe a reference to the table should be available, but I can’t seem to find it. Is there any way to get the name or ID of the table within which the record was created or updated?

Hello everybody :slightly_smiling_face: I would like to create an gmail automation with a reminder mail in response to a previously send email. My example : Trigger = a checkbox “send invoice” is valid Action 1 = send email with invoice Action 2 = send reminder email in response to the first mail (if i check some checkbox like “reminder mail”) Could this be possible in airtable? Thanks !

I have a Search block that’s set to check three tables in our base. Two are formatted similarly (both are a kind of inventory), while the third tracks duplicates between the other two. What determines which fields (other than the name) show up in these preview cards in the search block when multiple tables are being queried? The fields shown do not match the order of the fields in the tables queried, and they’re not in alphabetical order. I’ve already found this question and it does not address my issue: Fields showing in preview of record from Search block Apps What determines the 3 fields in the screenshot (Product Title, SKU (Supplier), Supplier UPC) per record to show below? [CleanShot 2020-05-17 at 15.31.17@2x] Initially thought the fields reflected follows the order of the fields/columns in the View that’s being searched, but that’s not it. Thoughts?

Hi all, I read a number of articles on the forum regarding the linking of fields across tables, but cannot figure out how to handle the following situation efficiently. I have an Excel file containing data collected over a long period of time, about the various components and subcomponents found in a given system. After importing the file into Airtable, I have two tables: Table 1 lists all components in the system Table 2 lists all subcomponents in the system and shows what component they’re associated with. Note that each component can be associated with 0, 1, or multiple subcomponents. Subcomponents are associated with only one component. I’m trying to add a “Subcomponents” column in Table 1 where, for each component, the cell would show all the associated subcomponents (if any). At this point, I’ve added a “Subcomponents” column in Table 1 and linked it to Table 2. However, I’d like for Airtable to automatically populate each cell with all the right subcomponents. For the time bei

I am looking to filter properties for owners that live out of state. I get information from my data source in the format below. What’s a Regex formula I can use to separate out the City, State, and 5digit ZIP?

Hi folks, I’m trying to find a numerical value from the “Title” field and have it appear in the “amount” (e.g. 27.21) I’ve used this formula from looking at a few post’s but it only copies the values before the decimal: MID( {Title}, FIND( “For”, {Title} ) 6, 50 ) Another formula I found only copies the values before the decimal: IF({Title}, VALUE(RIGHT({Title}, LEN({Title})- FIND(“0”,“1”,“2”,“3”,“4”,“5”,“6”,“7”,“8”,“9”, {Title}))), 0) What formula do I need to get the full total which in this example is 27.21? Thanks in advance! :winking_face:

Greetings, folks: All I want is to create the next record: let nuevoRegistro = { 'ALUMNOS': [{ "id": regAlumno.id }], 'ANHO': [{ "id": registroAnho.id }], 'CENTRO': [{ "id": registroCentro.id }], 'isAlta': { name: "Yes" }, 'CLASE': { name: clase }, 'CURSO': [{ "id": regCurso.id }], }; With the following function: let crearRegistros = async function (tabla, nuevoReg) { return await tabla.createRecordAsync(nuevoReg); } And what do I get when I run this? await crearRegistros(tabla, nuevoRegistro); U: Validate this value before parsing I couldn´t find any info about scripting errors. I hope someone did and would like to share what I’m doing wrong. Thanks!

I would appreciate it if anyone could help with the following scenario I have 2 tables, Table A is client lead table, Table B captures data from a form. when a clients answers are captured in Table B I need those answers to be updated for that client in Table A. thanks in advance

Hi, I would like to return all rows that contain a specific term. For example, I have rows that have two terms, [Default, Bar]. Using AND(term='Bar') will not work, but using FIND('Bar',term) will work. Except that it will also return results that have the term “Barber” as well. Is there a way to select rows that contain a term? Like FIND([Bar], term) for example? Thanks

Hey all, wasn’t sure where to post this question in the community… I’m looking to order entries so that most recent entries show first. In the screenshot below, Airtable keeps the oldest entry at the top no matter what I do. How can I invert this? Thanks, Christopher

I’m a little bit lost on this one and hoping for some help from the community. I’ve been using this automation without issue for months, but recently something changed and now it’s throwing me an error. (I’m guessing something updated on Airtables end). I have an automation which runs whenever a customers record is updated (which includes a rollup field of their orders). I then run a script to take the customers linked order records and sort them, so that newest orders show at the top. Up until this point, everything is working as expected, and the output of the script is a sorted list of the linked record ‘names’ (which in this case are Order ID’s, like 12345). The next step, ‘Update a record’ is where I’m now getting the error everytime this automation runs. It should (as before) take the output of the script and update the linked ‘Orders’ field in the customer record that changed, triggering the automation. That’s all it does, not changing any other fields. Now, I’m getting an

Hey everyone! Today we had an issue where a few of our automations started failing due to all of our “Link to _” fields breaking. We have been using these fields as a main link between tables, and they have all started failing with the same error: Field “_ ID” cannot accept the provided value: Cannot modify a computed field. Does anybody have any idea what might have happened? This all started happening around 1:30pm EDT today, and it is not just one table, but at least 4 or 5 tables. Any insight into what might have happened would be very much appreciated. It is a very big workflow issue for our company as this drives a lot of what we work on a daily basis. Thank you!

Hi Community! I would like to display a record that looks like a gallery view, but that is static so you don’t have to open it in order to copy information from it. Can you think of an app that will do this? Thanks in advance for any assistance. mary

Hi, If I have a column with the field type “URI” it doesn’t actually check that the inputted data is a valid URL. Is there a way for me to define a custom field type with the API/Apps. The ideal situation would be a custom field type that is validated by Regex. Thanks in advance (:

Hello! I am trying to make a formula to calculate X number of days before a Launch Date. I do not want to exclude weekends, however if the Due Date happens to fall on a weekend, I want to make it that Friday instead. I know about the DATEADD({Launch Date},-7,‘days) formula, and I’ve seen the WORKDAY function but not exactly sure how to make this work. So when calculating the total days before launch I DO want to include any weekends, but if the Due date falls on a weekend, it should add an extra day or two to make it that Friday… Thanks!

Hi, I need to calculate the age of a Deceased. I assume a formula is needed. I have Date of Birth and Date of Death to work with and I need the know the age in Years, Months and Days. Any help would be greatly appreciated.

I obviously don’t know what I’m talking about, so apologies in advance. I was inspired by this Team Schedule using Airtable blog post where they have a formula column that delivers “team member + Status” so I’m trying to create a formula that achieves something similar, but is a little more complex: My columns are: Brand = lookup from another table example “Client 1” Project = lookup from another table example "Awesome Website" Person = lookup from another table example “Betsy” Status = single select example “Working” or “OOO” My goal is to get the formula to deliver [Shortened {Brand} name | {project}] - Status] or {person} - Status where the result EITHER shows: shortened brand name + project + status (example C1 | Awesome Website - Working) OR person + status (example Betsy - OOO) I’ve poked around several topics and the formula database trying to figure it out but I don’t know coding syntax or complex formulas. Here’s what I have so far: Attempt #1 (kind of works?) {Brand} & "

Hello all, I would like to use an If formula: Everything before a certain date should be “True”, everything after “False”. When I write it like this, it doesn’t work: IF(InvoiceDate<3/8/2021,1,2) (InvoiceDate is a Field in Type “Date” with Format “European”). Do you have any idea and could you help me please? Thank you very much!

Hi everyone, I desperately tried for 3 days to solve this problem, could you help me ? I have 2 forms I ask my clients to fill. each form fills a separated table in the same base. Form 1 and Form 2. to do the folllow up I would like to create a “follow up table” to see which companies filled form 1 and form 2, and to be able to send reminders to the companies that just filled form 1. I tried with automations ( find records based on the company name) but it did not work. I would like to have a " follow up table" with the name of all the companies that filled either form 1 and form 2, and being able to see in the fields Form 1 and Form 2, if they filled both. ( the ones that just filled 1 form would receive an automated email if I manage to create the automation :slightly_smiling_face: )Could you help me ? thanks a lot !

Hello. Nice to mee you. I have this probrem.Preformatted text please let me know. //get table info let table = base.getTable("A2_複製"); let queryResult = await table.selectRecordsAsync(); let record; let stocks = new Array(2); //I wanna set to array[i][0~1], but I can't... Error tell me "TypeError: Cannot read property '0' of undefined" for(let i = 0; i < queryResult.records.length; i++){ record = queryResult.records[i]; stocks[i][0].push(record.id); stocks[i][1].push(record.getCellValue("*印刷型番")); } ; console.log(stocks);