Solved

Dear Community, Need help in trying to calculate the difference in months and days between two dates. Q1: is it even possible to calculate difference and return result in month and days? Or is it only possible in either months OR days? Q2: I entered a formula to only display difference in months, but choosing Jan 1 to Dec 31 is showing 11 months, and not 12 months. I get it that is subtracts and does not include Dec 31 in it’s calculation, but selecting the employement period dates (in our case) to be Jan 1 to Jan 1 would technically create confudion to the employee, as the period ends Dec 31. Any work around this. Super thanks in advance.

Hello Airtable Community, I am new to Airtable and I am struggling with a simple CONCATENATE formula: The DESIRED RESULT column is what I am trying to achieve. You can see that anytime I try to add “" as separators, but if one of the cells is blank then not to have the "” in it’s place. I tried this formula: CONCATENATE(CompName,"_", Length,"_", Dimensions,"_", Date,"_", Code) Not sure if CONCATENATE is the right solve. I have been able to do this in Google Sheets with this formula: =JOIN("_",FILTER(B2:F2,B2:F2<>"")) Been racking my brain for a whole week on this. I appreciate any help or guidance.

I’m hitting a wall, but got at least halfway to the solution! I have two scenarios that I’d want to return a given statement. Option A: Someone has a “Yes” in FCIHSEP and a “Fall 2021” in Cohort Option B: Someone has a “Yes” in FCIFEB and a “Spring 2022” in Cohort Either of those should meet the criteria to return the “ :white_check_mark: Paid” statement. I got it to work for Option A, but I’m a bit stuck on how to build in Option B as an alternate way to satisfy the statement. What I have so far: IF(AND(FCIHSEP = “Yes”, Cohort = “Fall 2021”),“ :white_check_mark: Paid”) Any help is greatly appreciated!

I have an automation set to send an email (via gmail) when a record is created (when a purchase is completed - this is one of the tables). This pulls from a number of fields across different tables, via look ups. All the fields populate fine in the preview, or when generating a test, but when an actual purchase is made and the automation is triggered for real, none of them populate in the actual email. I had to re-run the automation (and then it worked fine). The email is sent ok (and to the correct address), but in the body none of the variable fields are populated, and just appear empty. Is the email being triggered too soon? Ie, the fields haven’t actually populated in the record itself before the email is sent? Should I add some kind of delay step before the email is triggered (can’t see a way of doing that)? What else could be going on?

I have a Google Sheet with 3 separate columns for Day Year and month … and I would like to instead enter a Date field …It’s a simple table I’m moving to Airtable because it’s more user friendly IMO. … iut’s for about 2000 records. See the picture attached

So, I’m wanting to make an automation where when a new record is added, a custom ID is automatically assigned to that record that is prepended with a tag. So for instance, when a new record is created, it will automatically get assigned an ID like “FR-[date][number]”. So if multiple records were created today, it would look like: “FR-2022042601” “FR-2022042602” “FR-2022042603” and so on. not sure if this is possible, but any help would be immensely appreciated

Hi, I have simple automation that gets triggered once a record is updated. it populates 4 fields with data. Simple stuff, However, the execution time is more than 5 seconds! which is noticeable I can’t tell what causes the automation to take that much time to execute such a simple task. there is no computation or equation or loop. It just simply fills 4 fields with data. in contrast to apps. where I have a script app that gets executed instantly. if someone could please tell me if there’s something wrong on my end. More info the automation: the Name field that triggers the automation: short demo:

Hello all I have a single formula field called Threat Score I have multiple single select fields with answers of Yes,No, Na. I am trying to have a final threat score after all the single select fields are answered. For example Switch({Is there leaves, needles or other vegetation on roofs, gutters, decks, porches, stairways, etc.?}, ‘Yes’, 0, ‘No’, 4, 'Na’, 4 ) Switch({Is there any dead and dying trees, branches and shrubs or other plants adjacent to or overhanging buildings?}, ‘Yes’, 0, ‘No’, 4 'Na’, 4 ) If the user chooses no for both fields the formula field should have a sum of 8. How would I combine multiple fields into a single formula to come up with a final sum. Thank you Josh

Hi I’m newish to Airtable and I haven’t got to grips properly with Linking. As a newbie I’m using the ‘free’ version of Airtable. Do I need to have a paid account to link tables? If not please help, thank you very much in advance. Mike Scawen

I’m using a ‘vlookup’ style script below as part of an automation triggered each time a new record is created. //Substitute "Orders" for table name which contains values //on which you want to run the vlookup let mainTable = base.getTable("Litters"); let mainTableRecords = await mainTable.selectRecordsAsync({fields:["breed_string"]}); //Substitute "Product" for table which contains range to search in let lookupTable = base.getTable("Breed Landing Pages"); let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["Name"]}); //Replace "Item.barcode" with column name which has the values you want to look up for (let record of mainTableRecords.records) { let lookupValue = record.getCellValue("breed_string"); //Replace "Barcode" with column name which is the range to search in for (let rangeRecord of lookupRangeRecords.records) { if (rangeRecord.getCellValue("Name") === lookupValue) { let linkID = rangeRecord.id; //Replac

Hello, So, I need to convert attachment file URL to base64 string, as btoa() function does not work in automation script, I am running a scripting app that converts URL to Base64 string and the output of the scripting app will be entered into a new column I created as “base64 string” and using this value in the automation script. My question is how do I run scripting app every time a record is created? Or even better is there any way to convert attachment URL to base64 in the automation script?

Hello, I have set up a form that has a dropdown menu of dates. I’ve linked them to a table of dates. Things I need help figuring out: 1) I need the dates to only be available in the dropdown menu if they are at least 24 hours from the current date. 2) I need the table of dates to auto-populate for every Mon, Wed, and Fri and to each have a label depending on the day. 3) I would like to include a time with the date table as well, they are all at 4pm EST but I have had trouble with timezones before.

Hey, I need to create a formula something like this: If field_1 = “text_A”, then; If field_2> field_3, “Text_B”, “Text_C” Otherwise “Text_D” I don´t know how to do it and I couldn´t find from community or google… Anybody? Thanks //Mikko

Hello, I’m a bit new to Airtable and have recently been struggling with setting an automation between two separate tables. There are duplicate Unique Identifiers in each table and I was trying to configure a way that when one item in “Table A” was checked off as ‘received’ it would auto fill the checkbox for the same item in “Table B”. Currently I’m running into issues from being told “unable to render field referenced in this action’s output because they no longer exist in the selected table” but what is being listed is still in my table. I’m at a loss right now as I’ve tried to create an automation for When a record matches conditions, When record created, and When record updated. Thank you for any help!

I am using AirTable to organize my personal consulting business. I would like to sync my Google calendar with Airtable, so anytime a meeting is added I have the duration, my rate, and then the total cost. I’ve tried to sync the meetings, but it only tracks start and end time. How do I automatically calculate duration?

Hi, Say I have a base with 4 tables. A linked to B linked to C linked to D. If I want to visualise data in table D from table A I need to hoop trough table B and C with lookup fields to get this done? regards,

hi! i have a field that is linked to another table, and that field is a date. I’m trying to sort chronologically, but from what i see, it can only be sorted alphabetically. is there a way to change the settings of the date to yyyy-mm-dd in numbers instead of mm-yyyy now so i can sort chronologically? or any way to do that? thanks!

I have a script that takes a small amount of time to run. While it runs, I want to use the full width of view I am on, so I close the app slide out. That also stops my script. Is there a way to configure this from not happening? I would like the script to continue running while I get other work done.

I am trying to create a base to track contributions. I am tracking the contributors, different funds, and the contributions themselves. Each is in its own table and I am cross-linking the Funds table and the Contributors table in the Contributions table. I also have a roll-up for total contributions for each contributor and I have a roll-up for the total amount received per fund. I then created a form to input. This works pretty well. The problem I am running into is if someone contributes to multiple funds then you have to submit a single form per fund. I would like to be able to enter the amount given to each fund in one record. That could be done by just making a field for each fund type in the contributions table, but that prevents me from being able to get a roll-up of the total contributions to a specific fund in the Funds table at least as far as I can figure out. Is there a way to give the end-user inputting the data the ability to input all the funds being contributed to

HI Everyone I am sharing a problem today. Not sure is there any solution out there. Please response if there any solution for that. I am trying to get the latest available option for a select able field inside a loop of scripting apps script. But the problem is script provide me the initial instant of the table if i make any change runtime of the script then it’s not getting the updated instance even though i try to take a new instance of the table . Below is my code while(true){ var table= null table= base.getTable("Table 1") console.log(table.getField("Status").options.choices) console.log(await table.selectRecordsAsync()) //trying to make wait the loop var a= await input.buttonsAsync("test",["test"]) } And also want to mention that it’s getting the all the new records in new instance of the table or the updated records are also available on it. But the updated fields options are not available in updated instance of the table. May be it’s a bug from airta

Hi all, I’m stuck at this, probably somebody can help me out: I receive in Airtable a string of URLs like this: url1,url2,url3,url4 Now I would like to split this string and create in Airtable (in a separate table) single records: Record 1: url1 Record 2: url2 Record 3: url3 Record 4: url4 How do I set this up? I would really appreciate! Thank you! M

Hello AT Community, I am a bit stuck on understanding if this is something I am doing wrong or a system limitation. Here is the scenario: We have a CRM in AT that classifies a parent company as an Account and a member of that parent company as a Contact. Basic logic, and we have a linked relationship between both. When our marketing people go out and meet someone, purchase something for a client (lunch, etc.) we want them to update AT with what they did. This works fine when the Account and or Contact is already in AT. When it is not, we want to be able to add the new Contact and or Account on the fly off of the form and limit them going inside AT if possible. This scenario can exist when we have a new Contact that we have not had in our base, nor their parent company (Account). When we are adding the new Contact it is coming in from the form as a new field - which is what we want. The problem comes in when the person using the form realizes that there is no Account for this new Contac

I’ve tried everything and I’m so close, but I can only seem to nest 4 IF statements and can’t find how to get up to 5. Background: I’m trying to assign dollar signs to different price points. If a price is less than or equal to 50 = $ less than or equal to 100 = $$ less than or equal to 500 = $$$ less than or equal to 1000 = $$$$ more than 1000 = $$$$$ Here’s what I have that almost works: IF( {Price} <= 50, "$", IF( {Price} <= 100, "$$", IF({Price} <= 500, "$$$", IF({Price} <= 1000, "$$$$" ) ) ) ) I’d like to just add one more IF in there, but I guess when I add a “>=” it messes up the code and I get an error. How do I get that just one extra step? Thanks in advance!

Hi all, I have been playing with Airtable for a while now but not high level enough to figure out scripting yet. I’m a farmer and I’m looking to make an Airtable solution for my spray applications. I’ve got a few tables that hold the general info: ‘Blocks’, ‘Chemicals’, ‘Applicator’ I’ve got another table that is my ‘chemical manifest’. This one grabs the particulars from ‘Chemical’ and I add the particulars such as purchase date, amount etc… The ‘JOB’ table is the workhorse. It allows me to record the ‘Blocks’ being treated with the ‘Chemical Manifest’ with which ‘Applicator’. My limitations are: Sometimes I add one chemical to a job, sometimes I add ten. I don’t want to add 10 sets of fields related to each ‘Chemical Manifest’ My application rate’s are sometimes different than the general label rate on the chemical, I can’t figure out how to change the linked field (rather import the info and then change as needed) On entering the Job, I would like to reduce the Amount of availa

Why isn’t this 0? What is it. Look at the picture for details