Solved

Here’s my code: IF(AND(Stage=“Closed Won”,(DATETIME_DIFF({Created Date / Time},{Opportunity Close Date},‘days’)-1)<=0),1,IF(AND(Stage=“Closed Won”,(DATETIME_DIFF({Created Date / Time},{Opportunity Close Date},‘days’)-1)>0),DATETIME_DIFF({Created Date / Time},{Opportunity Close Date},‘days’)*-1,"")) The output is a number but in a text format (I can’t sum, avg, etc) and it’s formatting to the left of the column. I put Value() around the whole thing and I’m getting errors in the records when doing that. Created date: Date the record was created Opportunity Close Date: Date the record was marked closed won I want to be able to sum and avg etc the column. Any help?

Hello everyone… I have two tables, the KPI table and the Months table. Each row in the KPI table has a month that is in the Months table. I want every time there is a change in the status column to ‘A’ in the month table, the month column in the KPI table also changes. I have tried to use automation, step 1 ‘when a record is updated’ then step 2 ‘find record’ on KPI table which is stsrec is N and ‘Update records’ KPI table based on automation step 1 but the result failed. Any suggestions on how to create an automation to change multiple rows based on a single row change? Thank you for the response Pic Table KPI Pic Table Months Pic Automation

I would like to use a scripting automation to check if a record exists. i.e. Does John Doe exist already in the Name column? The results should be simply yes or no. I’m not great at scripting and am looking for some fast track help!

We’ve created an address book of sorts, and I could use your help in creating a dynamic single-select field. Here’s the scenario: We have two tables: (1) tbl_organizations and (2) tbl_individuals. Any given organization may (and often does) have multiple individuals linked to it. However, we’d also like to designate one of these individuals as the primary contact for the organization. Here’s it written out as an example: Depp Organization is a record in tbl_organizations. It is linked to 3 records in tbl_individuals (Bob, Sue, and Timmy). Foo Organization is a record in tbl_organizations. It is linked to 2 records in tbl_individuals (Sally and John). I would like a create a field in the tbl_organizations. This field will be called something like “PrimaryIndividual.” PrimaryIndividual will populate with the individuals linked to their organization record (but no other individuals). As an example, for Depp Organization, the options will be Bob, Sue, and Timmy whereas for Foo Organizatio

Hi! First of all, I am french so I hope you’ll understand my question. :hugs: I’d like to calculate how long has an employee been with the company, so : since entry date (fr: date d’embauche) until now/today. I already have a formula that calculates the number of years (fr: années) and another formula that calculates the number of months (fr: mois) but I’d like to have these informations in one formula. Is that possible ? Thanks !

Hey everybody, big Airtable fan here, we are managing a lot of processes for a concert venue with this awesome tool. Now we’re stuck with the following problem: People send data (some text fields, some attachments) via a form. This information is written into it’s own table (let’s call it “updates”). It can happen, that several people send different data to the same fields, for each submitted form a record is created. From “updates” this data is transferred into our “main table” via Automation UPDATE RECORD on trigger WHEN RECORD IS CREATED. We’ve also built a way to always keep the existing submitted informations if more is submitted to the same field by another form submission. So far so good. Uploaded Attachments can be JPG / PDF or similar, those shall be saved to the “main table” as well in the equivalent field. Now it’s getting tricky. As far as i have read it’s not possible to move the attachments from one table to another with an automation, so instead we are working with URL

Hey All! I am trying to check to ensure that all timesheets have a corresponding scheduling item but my FIND formula is only working half the time. Any help would be appreciated! IF(FIND(Timesheet,{Scheduled Dates (from Task)})>0,"",“Missing!”)

Hello, Given: var Airtable = require('airtable'); var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('app6wt6DBFn40vPRg'); base('Project List').select({ // Selecting the first 3 records in Master List: maxRecords: 3, view: "Master List" }).eachPage(function page(records, fetchNextPage) { // This function (`page`) will get called for each page of records. records.forEach(function(record) { //do stuff }); fetchNextPage(); }, function done(err) { if (err) { console.error(err); return; } }); How can maxRecords be determined dynamically via an API call? Is this the only way? Get the number of records in a table via API API Yes, a webhook with a script action. Even with a very full table, this will run in about 5s, whereas, an API process might require 15 seconds or more and will slow even more at scale. I tested the following on 37,000 records with 58 fields. I believe that by selecting fields: you are essentially ens

Hi All, I have a field which is pulling in from JIRA and displays like this: 154, 152, 157, 155 There could be more or less numbers. What I need is to create a new field which is the highest number in this field. (So it will display 157 in this case). The issue is the rollup feature is displaying 0. I assume this is because this is being viewed as text. Any ideas how to solve this? Cheers Simon

Hi guys, I have two tables. Table 1 is called Contacts (contains contact and deal information), Table 2 is called Questionnaire (contains a series of long text questions) and is linked to table 1. I want to set it up so that when a contact completes my questionnaire through the link I provide them, the data they have provided in the questionnaire form will be automatically attributed to their record in the Contacts table. I’m hoping that by doing this I will then be able to view the contact and from within the contact, open up their questionnaire responses. I have considered having them in the same table however the contacts table already holds a large amount of fields. Through my research of this issue I have found and followed a Airtable guide called “Use case: update records via a form”. From what I understand, this method uses a field in the Questionnaire form that is prefilled and labled “please do not change this portion of the form”. Unfortunately I cannot use this method as it

Hi Community, I want to use a formula to calculate the age of a product DATETIME_FORMAT(TODAY(),‘YYYY’) - {year produced} Some fields of {year produced} are empty, so the formula comes back as the age of 2022 years old. Is there some way to exclude the empty {year produced} fields? The year produced, has the number field type. Thanks in advance!

As the title says. How would this be written differently to avoid this error? My test environment only had 10 records, tonight was its first scheduled automation run and it grabbed 95 records for this process. // After Club Automations let table = base.getTable(“Children”) let view = table.getView(“Present”); let query = await view.selectRecordsAsync ({ fields:[“Pts this week”, “Spent Today”, “Total Spent”, “Points”, “Attendance”, “Last Club”], sorts: [ {field: “Pts this week”}, ] }); let totes = 0; let today = new Date() for (let record of query.records){ // Multiply 'Pts this week" by 10, add to ‘Points’, Subtract 'Spent Today, write new total to “Points” field totes = 10 * Number(record.getCellValueAsString("Pts this week")); totes = totes + record.getCellValue("Points") - record.getCellValue("Spent Today"); await table.updateRecordAsync(record, {"Points": totes}); // Add ‘Spent today’ to ‘Total Spent’ and clear ‘Spent today value’ totes = record.getCellValue("Spent Today") + recor

I am trying to automate the calculation of an award based on an age category (“PVSA Age Group”) and a number of hours volunteered (“Total PVSA Hrs.”) (see chart). I think I need nested IF statements with an AND, but not sure. It’s not working so far (because I have a day job hello.) I need an output based on these two variables. Here is the first part of my formula, attacking only the second row (we have no contenders for KID category in the first row, just the remaining 3 rows): IF( AND( {PVSA Age Group}=“Teens”, {Total PVSA Hrs}<50 ), “No award” ) & IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<=74 ), “Bronze” )& IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<= 99 ), “Silver” ), “Gold” That doesn’t work. Once I get it working, I need to figure out how to concatenate the other two rows (for different age categories.). Do I eliminate the subsequent AND statements, to save them for the concatenation?

I have used this formula to separate data that is simple 4 letter abbreviations and it works perfectly. However, the data is also associated with dates and times that I need to separate and have readable and separate in an email automation. When I use the above method, the dates (which read fine in their native field) come out looking like this Has anyone run into this before or have a solution?

I’ve set up an automation that consists of a trigger (which works), a script to get the automationTime (which finally outputs the current date in ISO format), and an Update Record Action (which should capture the outputted Date—from the script—and input it into an empty Date Field of the same format). I don’t know why it’s failing, and what invalid input it’s referring to. Some assistance would be greatly appreciated. Please see screenshot attached for reference. Thank you!

I setup my first automation and it seems to have worked according to the ‘history’ but when I look in the table where it supposedly created a new record, the new records aren’t there. I don’t have any filters on, I searched and sorted in various ways with no luck, and just can’t figure out what it could be. Can anyone help? thank you!

I am trying to use REGEX_EXTRACT to convert a username into an Email address. For example: WSSUMITS\smithfred => smithfred, then I can add the @domain.com to the result. The regex formula I came up with, through trial and error, is: REGEX_EXTRACT({Asset Last Logged In User}, '^(?:WSSUMITS)?([^:\\/\n?])+\\') However, I’m getting ERROR in the field. I don’t have much experience with regex. Where am I going wrong? Thanks in advance.

Hello! I’m writing a custom app to print information from my base on adhesive labels using a Zebra TLP2824 Plus printer, via Zebra Browser print. I’ve written the script below to gather, format and print the required information, but my amateur at best programming knowledge is holding me back a little bit. The functionality I’m going for is the following: once a button in an Airtable view is pressed, labels print for all records linked to a parent record. This works when the app is loaded from the button itself, but if the app loads without the useRecordActionData already loaded, I can’t get it to load without it throwing the following error: Rendered more hooks than during the previous render. Which is obviously right, because I’m accessing more data than requested initially, but I can’t figure out how to work around this! I’d really appreciate some help. Thanks! import {initializeBlock, useBase, useRecords, useRecordById, useRecordActionData} from '@airtable/blocks/ui'; import ZebraB

On my AirTable, it sends out an email when a view updates because the view in question is made to track employee records that are past their respected due dates. I would like an email to be sent out every Monday and Wednesday to the specific employee/collaborator, alerting them to follow-up on the past dues–until they no longer appear on the list. How can I do that? Drew

I am trying to solve the following problem. I have thousands of registration numbers that always start with a “T” and are followed by 10 digits. This is how they are delivered to me T3090728379. But they all need to look like this T-309072837-9 which translates to the following format T-[9 digits]-[1 digit] The last digit can be anything between 0-9. What formula could I use to reformat these fields in Airtable? I managed to substitute “T” with “T-” but I do not know how to get a hyphen in front of the last digit. Is this something one would use the LEN() function for? (I have never been able to fully understand how this works…) I’d appreciate any help. Thank you so much in advance. This is how far I got: IF(STRING,SUBSTITUTE(STRING,"T","T-"))

Hello. I have built several Interfaces from a particular Base in my Workspace. However, I need to move that particular Base to a new Workspace. When I move this base will the associated Interfaces also move or will I have to recreate them all again from scratch (shudder!)?

Hi, I need help to split this string into four pieces and put them in four new columns… Maybe split two times with different delimeters? {“email":"mail@gmail.com”,“firstname”:“John”,“lastname”:“Hong”,“phone”:“1234567”} Column 1: mail@gmail.com Column 2: John Column 3: Hong Column 4: 1234567 Thanks in advance!

Hi all! I’m on a fairly time crunched project and am stuck on solving a problem. Specifically, I am fetching data from an API using DataFetcher that is a table of products that have columns with “tags” for the product. There are a total of six columns with different tag names in each column. What I’m hoping to do is in a separate table entitled “Tags” to a have a column key where each record is a unique value of any tags in those six columns in the other table. Ideally, this would also update automatically when datafetcher runs and updates the other source table and new tags are added. Is this feasible? Many thanks in advance for any consideration.

Is it possible to color code a record based on a due date, but just the ones that are in the current week? Example: Today is Thursday, so all the records with a today or tomorrow (Friday) due date would be colored red. I know that color based on “within the next week” or “within X number of days” exists, but I need the records for the current week only. Thanks!

I’m using Airtable to build a list of donors for our non-profit organization. The records are identified by the name of the donor. Some donors make multiple donations throughout the year. How can I reflect these frequent donations without building a whole new record? Thank you so much.