Solved

How do we get this formula right? Now it always returns 0

Hi, I have a base where we key in the ID numbers of each person in a column - Males have odd numbers and females have even numbers. Would like to create a column that automatically tags them as either Male or female based on this data but I cant seem to figure our what formula to use. Could someone help me? It would be of great help. Examples: XXXXXX-XX-5557 = Male XXXXXX-XX-5556 = Female.

Hey AT Users. Has anyone come across a good solution for either timestamping an updated element in a field or generating a timestamp for when a computed field contains a value vs when it is empty? For context: In our base there is a rich text field which includes a check list of about 10 items (done to eliminate the need of 10 separate fields, as there is already a LOT of data to process in this base). Using the following formula I can only return a timestamp for when the entire field was modified, not the particular element in that field: IF(FIND(" Milestone “,{Milestones Checklist}),DATETIME_FORMAT(SET_TIMEZONE(LAST_MODIFIED_TIME(), ‘America/Toronto’), ‘M/D/YYYY LT’,”")) If anyone has a work around for either timestamping when each of the 10 elements is marked with an x OR knows a workaround for timestamping a computed field - then I could add an IF(FIND(" Milestone",{Milestones Checklist}),“Yes”,"") field and then timestamp WHEN that yes appears. Any thoughts???

Hey Guys, I have used a formula that I copied from an answer here to create an end of month field. I have transaction dates for sales and i want to get the end of month date for each transaction date. It works for all months except when a transaction takes place in any december for some reason, in any year. For any other months, it works fine. This is the formula that I am using: DATEADD( DATETIME_PARSE( DATETIME_FORMAT( {TX DATE}, ‘YYYY’ ) & ‘-’ & (VALUE( DATETIME_FORMAT( {TX DATE}, ‘MM’ ) ) + 1) & ‘-01’, ‘YYYY-MM-DD’ ) , -1, ‘day’ ) Can anyone try to pinpoint what’s wrong with it please? Thank you Hady

Hello! My goal would be to automatically add photos from Table 2 (where they are imported in bulk) to Table 1 (with the right item order). The only link between the items in the two tables is a code/name column. I tried my best experimenting with formulas in Airtable and GSheet, but I couldn’t find any solution. I copied a script from an older community topic: Programmatically copy images from one table to another , but it doesn’t seem to work. Can someone please help figure out what the problem might be, or offer another solution? Here’s a simple version of my base (Restaurant): https://airtable.com/invite/l?inviteId=invyOwrzT6i00qbss&inviteToken=1c708dfb92651fb230f375f758d4d172ad56602b0347fccf8ac86f495d5a8522 , where the pasta dishes (Menu Item Name) should get the photos in the Attachments column from the Image column in Table 2, connected by the Name and Code columns. Thank you!

Thanks for taking a look in this thread. I am looking to create a zapier step that checks for a row in a table which contains 1) a certain ID (exists as string), and 2) to see if a certain string exists as part of a rollup. How would I write the formula for this? This is what I have so far… it’s good for finding rows that have only one item in the rollup, but if I need to find a certain item within a rollup, it says none exist. AND({ID}=‘abcdefg123’, {Rollup}=‘xyz999999’) Thanks again! Happy holidays!

Hi I’m looking for a formula to convert a field from First Name Last Name to Last, First. “Steve Smith” to Smith, Steve". I see a lot of people have ways to go the other way… thanks!

Hi all, I’m diving into the wonderful world of Airtable scripting with a custom CSV import script that does some data interpretation and creates links to other tables. Starting point was the CSV import example. I managed to get that working and added more functionality. Now, I keep getting an error that I cannot seem to solve: But the editor shows no red markings, and the first line of my script is a comment! Here are the first few lines of my script: // Timesheet CSV importer by Emar Vegt output.text("Note: this script does not handle duplicates yet. You'll have to sort them out yourself."); // Ask the user to import a CSV file containing a header row let csvFileResult = await input.fileAsync( 'Upload a timesheet CSV file', {allowedFileTypes: ['.csv'], hasHeaderRow: true} ); // The file importer will automatically parse contents for many file types, including CSV files let csv_rows = csvFileResult.parsedContents; // Edit this to the name of a table in your base let timeshe

Hello, I use this script for move record from table 1 to table 2, All data is moved except data of type dropdown list (Ville). I show this error: Can’t create records: invalid cell value for field ‘Ville’. Could not find a choice with that ID or name let table1 = base.getTable("DATA"); let table2 = base.getTable("Con"); let result = await table1.selectRecordsAsync(); for (let record of result.records) { if (record.getCellValue("Prét")) { await table2.createRecordsAsync([ { fields: { 'ID': record.getCellValue("ID"), 'Source': record.getCellValue("Source"), 'Agent': record.getCellValue("Agent"), 'Nom complet': record.getCellValue("Nom complet"), 'Téléphone': record.getCellValue("Téléphone"), 'Ville': record.getCellValue("Ville"), 'Prét': record.getCellValue("Prét") }, } ]);

Is there a way to reset an input field? Say someone pulls up a record selection input and then they click off so nothing is selected the user is no longer able to go back and select something different. As far as I know you can’t reset the input, remove the element and re-add it to let the user try again. I tried using a do while loop so that it could do the input instruction again if the variable is null. If it isn’t possible to reset the input or remove the element and re-add another input, is there a way to re-run the script with a button or some other event?

I have a table with 290 records and 10 fields that need to be updated yearly and I am struggling knowing if a record has been updated or not. Essentially I need help creating a system to track if records have been updated and if they need to be. My table consists of fields that either update all the records at once yearly, or get updates spontaneously throughout the year (sort of like a company yearly earnings). You know it is coming but they don’t release them all at the same time. Any suggestions of what it could look like? or how to think about this?

Hi all, I have a couple fields in my base where the values are reviewed every few months (i.e. risk score increases or decreases according to mitigation measures). I would be interested in plotting how for example the risk score for a particular risk has changed over the course of the project. I know I can expand view and see how that record has been edited over time but I was wondering if there is a way to monitor changes to a particular field so that the data can be extracted automatically for graphs and tables etc… The only other thing I can think of is creating a new field every time the values are reviewed and then hiding them from view but that would just create so many fields over time. Thanks in advance for your help, Camilla

Is there was a script I can use to erase all attachments in an attachment field with the click of a button or even with an automation?

Hi there, we run our school’s school information system (SIS) on Airtable. Our faculty and staff have a daily schedule that is fairly consistent from workweek to workweek, but does include some variation. I want to the ability to copy a week’s worth of events from one week to the next, at which point I can make micro adjustments where needed. It’s far too cumbersome to individually copy every event, every day, and then adjust both the start and end (time) fields on every single record. I assume a scripting solution here is the right direction to head, but, being very new to this feature, I wonder if others with more experience would agree. In essence, i’m attempting to mimic the features of some of the most popular employee-scheduling apps (e.g. Sling — getsling.com/employee-scheduling). My setup is essentially this: Table called “Calendar” Date and time field called “Start” Date and time field called “End” Several other fields specific to our organization Grid view Calendar view For

I am currently writing an airtable automation for my organization that is trying to run a script on records that are a certain age (currently thinking week old records). In order to do so, I was thinking of using the “When a record matches condition” trigger, and using the When field on the Created date for the record, and checking if it is before one week ago. The main point I want to check on is if this is one week from the time when the automation runs, or if this is one week from the time the automation was made. Likewise, how often will this automation run?

i thought it would be something like DATETIME_FORMAT(TODAY(-30 DAYS), ‘MMMM’) => October or MONTH(TODAY(-30 DAYS) Its the subtracting days part i dont understand simple i know but im at a loss to make the stupid red letters go away OR how can i find out what date was 30 days ago and format that? i also need this for 60 days and 90 days to populate on a notice thats printed from page designer and sent certified mail. but i am competent enough to parse those myself once someone lets me cheat off their homework for the first one.

Hello, I have text that I would like to paste text into a table. I’d like each line of text to get split across two columns. I know that I could probably get to this to work with a CSV import but I’m wondering if there’s a way to get the result I want with copy/paste. Here’s an example of the text I would be pasting: Line 1, Another boring day in the ocean. Line 3, Another boring day in the ocean, SIR! Here’s how I would like it to appear in a table: Here’s how it actually appears in a table when I paste it: I would either like the paste function to recognize the , and split the text into two cells at the comma (like a CSV import would do). Or I would like to paste the text then highlight column A and split the text at the comma into two columns. Are either of these things possible?

Using a Form Entry Repeat a Record with a Date Change Records are being added by various people using a form. I am currently asking them to maually repeat their entry for up to 13 times with only the date changing. I am trying to work out how Airtable can recognise a new record with a value in NO of Weeks and automatically add a record for a specific number of times with the date changing for the same number of times My columns field headings are “DATE” “NO OF WEEKS” “COUNT DOWN” “CLASS” “NAME” As another record is added the DATE needs to add seven days and will stop repeating when it has been repeated to the value of “NO OF WEEKS” Is it possible for the table to recognise a value in NO OF WEEKS and run an automation or duplication with only a DATE change? Can somebody tell me if this is even possible but ideally, tell me how.

Bonjour, je me suis aperçu que dans le type de champ “attachment” la possibilité “link” ne fonctionne pas (seule la partie du lien derrière le dernier " / " est conservée et par conséquent le lien ne fonctionne pas). Selon moi, cela devrait fonctionner que le type de champ “link”, où là il n’y a aucun problème… Si c’est un bug, peut-il être réglé rapidement ? Merci pour votre retour.

So I’m looking to employ a formula to provide a word count for my SEO keyphrases, which range from 2 to 8 words. Below is the formula that I thought would work, but I’ve had no such luck…does anyone have any thoughts as to what’s going on here? Thank you in advance! IF(({Keyphrase}=BLANK()),0,(LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase},’ ', ‘ ’))+1))

Hello everybody. As the title says, I’m generating receipts with Airtable and I’m looking for a solution to generate a unique number for each one. I was looking at automation to maybe grab the last record and on the receipts # field do a +1, but I think this could be simpler with autonumber. Greatly appreciate your input.

I’m stumped on how to add up duration fields (segment run times from different tabs) to add up a total run time comprised of multiple segments. I’m working with 3 “segment” types here. The shortest segments, “Tab A” are linked to the medium one “Tab B” So, records from “Tab A” are added together to get a run time for some “Tab B” segments, but not all. Some “B” segments have no elements from “Tab A,” So I created a function field that adds up “Tab A” durations, along with a duration field just for segments unique to “Tab B” Our longest, “Tab C” are made up of records form both “A” and “B”. But for some reason, the total run time for “Tab C” is not adding up! What I want is a total from A-Unique TRT, B-Combined TRT and B-Unique TRT to add up to a total duration time in the “Total TRT” field. In the screenshot below, the first record should total 10:00, but it’s showing as 0:01. The formula I am using is “SUM({B-Combined TRT}+{A-Unique TRT}+{B-Unique TRT})” Is the zero in Combined TRT ca

I have a Rollup field that takes a series of decimal percentages from another table (e.g .68%, .72%, and .55%) and then calculates the average (.65%). I have the formatting of this field as Percentage and precision of 1.00. Unfortunately, when Airtable passes that result to the 3rd party app I am using (Stacker), it includes more than 2 decimals. I want to stop the number of decimals at 2. I think I probably need to convert this percentage to a string and then concatenate it with %. Anybody know of an easy formula to do this? Or a different solution?

I am using Stacker as a front end for my Airtable base. In Airtable, when I use the rollup and formula functions to calculate or show currency data that I format with precision of 1 show no decimals are showing. Also, i do the same with integer data that I format with no decimals. However, when the data is pulled into Stacker it shows the decimals for each. So the value of 54,528,713 in Airtable displays as $54,528,713.48 in Stacker and the value of 180 is Airtable displays as 180.32 in Stacker. Does anyone know of a way to fix this or use a formula (like the TRUNC function in Excel)? If so, I figure I could add a field next to each field that needs the decimal removed and use the this function to create the value with no decimals and then pull that into Stacker. Thanks for any insights.

Hello everyone, I have a base with information about individuals (i.e. Date of birth / death etc.) and I would like to configure a field that would let me know if any of these individuals were born or died or published something significant on a specific day I am logging on the base (“today”). Is there a way to configure this? Thank you in advance for any assistance!