Solved

Hello! I am trying to set up a timesheet in Airtable and I want a formula or automation to assign a new pay period every two weeks. I don’t care if it is just a number (pay period= 1,2,3,4…etc.) or a single select field or something else! I just need to indicate a change in the default entry every two weeks. My first thought was to set up an automation and set the trigger as a scheduled time (every two weeks). But, I keep hitting a roadblock when trying to set up the action. I do not want to FIND, UPDATE, or even CREATE records… I just want the default field entry for new records to change every two weeks. In my mind, the sentence would be “Every two weeks, add 1 to the pay period number.” Any thoughts on how to get this done?? Thanks to anyone in advance!

I have a field called: Branch Name I need to extract text from it using a formula field. I’m just not sure how. Examples of data in the field: #2828 - Clarksville Branch - Clarksville, TN #4109 - Aiken Branch - Aiken, SC What I need to extract: Clarksville Branch Aiken Branch I tried this formula, but it doesn’t work (returns #ERROR!) and I don’t know enough about REGEX to troubleshoot: REGEX_MATCH({Branch Name}, '/(?<=-).*(?=-)/g')

I have one table that is a Roster of team members with various fields of data pertaining to each record. This table is imported for certain fields and other fields are manually imputed. The data on each record set has the potential to change at any given point. Changes are done manually. The second table is Synced data from an external source that contains some of the same data that is on the first table. I would like to setup a Validate field (checkbox) on the Roster table, this field will be checked if multiple data points (4 or 5 fields) matches exactly from the 2 different tables. Is there a way to compare this data across 2 different tables and if all matches correctly check the validation box? Additionally, if data changes in the Roster and now is different uncheck the box? Any help would be greatly appreciated.

I’d like to use Airtable to help me speed up the processes of responding to multiple RFP’s. Often a same or similar question is asked on multiple RFP’s. Rather than make a database I’d like to use Airtable to categorize and log the questions and answers that I often re-write from scratch each time. I would like to do a quick search in Airtable and and pull already answered questions or document content and pull it into a blank RFP template to partially automate the response process, and quickly access the questions Airtable database. Is this possible? Has anyone done it? Any know templates to start from? Thanks to you my Airtable Gods! :slightly_smiling_face: (not looking for an approval workflow at this time. may use my CRM for that)

Hi I’m creating a tournament ranking system. I have a table for each tournament played and within each table, I list all the players and their individual scores and then their ranking position and ranking points for that tournament. On a separate “rankings” table, I list all the players and lookup their score, ranking points from each of the tournament table. I can then add the total ranking points across all tournaments and sort them by male/female/adult/senior categories. However, the end of season rankings after 8 tournaments will be based on a player’s best 5 results/points scores out of the eight events. Therefore, I need to take the 5 largest (not necessarily unique) points from the 8 fields. I’ve looked at various formulas to try and find the answer (similar to LARGE function in Excel), but I’m stumped. Does anyone know how to do this? Is the answer to structure my tables differently? I feel database design is always my downfall :frowning: Thanks in advance.

Hello again all, I believe there is another topic titled “Using Automations to Check a Box When Record Matches Conditions”. (I posted there but got no answer) That thread seemed to have strayed a little off track. Because while “Update Record” IS a condition that ‘could’ be used, it’s only one option. And the steps seem to be different for each “Action Type” selected. So, if someone could walk me through another one, like “When a Record is Created”, I would greatly appreciate it! So, WITH A SCREENSHOT (please), can someone please explain how to use an automation to check a box? I’m putting true, false, one’s and zero’s everywhere and nothing is working! As always, THANK YOU! :pray: DG

I have built an automation that fires off an email to a key stake holder with information found in different fields. One of these fields contains a link. When the automation fires, the email is sent along with a slack notification. The slack notifaction contains the same information the email should, BUT the link in the email is altered by the automation and the link in the slack message seemingly works. The automation is grabbing the same URL from the same field. I am not sure why the URL is changes in the email but not the slack message. To add additional information, the email is removing the under-scores from the URL and the Slack message is not. Can anyone help?

Hello, So we have a “Survey Results” table that… wait for it… collects our “Survey Results”. It basically runs off an automation. The 'initial trigger is a checkmark in another table. When you tick the checkbox, the “Survey Results” table starts a new row by adding two items - a four-digit RO# and the subs email. Jotform then takes that information, and adds it to a form and sends it out. It then returns the results back to the “Survey Results” table… BUT IN A NEW ROW! This doesn’t seem that hard to figure out, but it sure has stumped me! Please advise, thanks DG

Hi All, I’m parsing JSON files into Airtable Long Text fields, and then setting up Formula Fields to relay the contents of that JSON. For example, the JSON data looks similar to this; {"NAME":"Colour","VAL":"Red","ID":"1"}, {"NAME":"Shape","VAL":"Square","ID":"2"}, {"NAME":"Size","VAL":"500","ID":"3"}, {"NAME":"Scale","VAL":"2","ID":"4"} At the moment, I’m using a fairly hacky method to display the data in a formula field, for example; ABS(SUBSTITUTE( RIGHT(MID({Log Content},FIND("Size", {Log Content}),17),4) , '"','')) But this is buggy as. Obviously if Size is more or less than 3 digits, the output fails or works simply by chance. What would be the correct way to write a formula that retrieves the entire value between the inverted commas, regardless of the length?

Hi there AT Brain-trust, I’m building out a pipeline dashboard using Airtable for my company, and I want to be able to show essentially a comparison of what the data was last month compared to what it is this month. For example, I would want my dashboard to read something like: Number of Opportunities (Current): 1 interest, 3 submitted proposals, 5 won, 2 lost Number of Opportunities (As of 30 days ago): 2: interest, 2 submitted proposals, 4 won, 1 lost I’m sorted on the current opportunities using the current state of data. I am also aware there are ‘snapshots’ of data that I can retrieve but not download (is that still true?). Even if the snapshot was downloadable, it doesn’t seem to be a very elegant solution because it means creating two sources of data. Fields I have that might be useful here: Date Created (record) Date Modified (record) Date Status Changed (i.e. when a user updates the opportunity stage, it triggers a date update in this field) Would really appreciate any tip

I am trying to make a simple inventory of 50+ items I have one table called Inventory which has columns “Name” and “Quantity”. I would let other people borrow my inventory, so there are a lot of ins and outs. Anyway, On occasion, I need to do an Audit of my inventory, so I want to make sure that my personal inventory matches with the global inventory system. So what I would do is download the global inventory and put that into the Airtable as a separate table called Global Inventory. This table would also have columns “Name” and “Quantity”. I was thinking to create a “Global Inventory” column under the Inventory table and use the formula that would compare the two tables “Name” and output the Global Inventory Number so I can easily see if it matches or not. I guess this would be like reconciling a bank transaction in a way. Any help would be much appreciated. I know how to do this in Excel with SUMIF but again, this is not the same here.

Hey there, i am new to Airtable and have only understood half of the features yet. As I had to learn, that it is not possible to hide views from specific users, I found the sync feature. I do have a database with a lot of personal information about people living I a camp. I was trying to find a way to give some of my users (in this case security guys) the ability to put in the date of the last Covid Test of a person. As this does not work in one base (because I can not hide views) I have done a sync to another base of al relevant information. Is there a way I can let a limited amount of users change information in one or two rows, without letting them see all the rows and letting them change all the rows? Right now I can only see the information in my synced base, but not alter them. Thx for any hint.

I have a contacts tab with all of my customers and their contact information. I’d like to add a ‘sales rep’ column. The sales rep information is on my second tab, interactions. Every time my sales rep enters an interaction for one of my customers multiple times, their name shows up multiple times on the contacts tab for that customer. I just want the sales reps name to show up once. I’ve attached an image to help illustrate what I’m talking about. Any help would be appreciated.

Airtable Grid view - Airtable Explore the "Grid" view on Airtable. I have created this database to catalog those buried in a cemetery. I understand that I can’t use formatting in a formula–unfortunately. In a perfect world, I need to format “short name” in quotes (or bolding) and “maiden name” in italics. Could any of the experts suggest a different approach if indeed formatting is not available in formulas? Thanks, Sam paynegap.info

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?