Solved

Hi All, I’d like to create a unique number for a record based on the year and week. So for example, I have a date field which I use as YEAR(Date)&" - "&WEEKNUM(Date). So if the date was today this would give me an output of 2020 - 43. Now, I want to add a counting number for each record with a date in that week in order of when the record was created. So the first record created with a date in that week would be “2020 - 43 - 1” and the second record created would be “2020 - 43 - 2” and so on and so forth. But when the week changes to the next week I want the counting number to start again, so it would then go “2020 - 44 - 1”, “2020 - 44 - 2” and so on. I just don’t know how to do this and would appreciate any help thanks.

Hi, I need some help, I am counting the total hours worked of my employees, But I need a formula that won’t count the lunch time or that some how if table (lunch in) or (lunch out) is empty it won’t say NaN. some people take lunches, some don’t this is what I have it like this. I have it to count hours worked before lunch and after lunch and add that all up. Screen Shot 2020-10-19 at 1.25.01 PM|700x99

Happy Friday, Airtable community! I’ve got a formula that works, but only sometimes. Its purpose is to cut news sources down to just their names, no taglines, no section headings. While it’s a long formula, the sources within it are in alphabetical order so it shouldn’t be too hard to find the ones that are giving me trouble, namely: Al Jazeera, Literary Hub, Mother Jones, Open Culture, Rolling Stone, Slate, Teen Vogue, The A.V. Club, The Guardian, The Intercept, The New York Review of Books, The Progressive, VegNews, and Washington Post. Hoping someone can see a pattern. Here’s the formula: IF({Source}= “action’s Email Updates - Tracked Events from GovTrack.us”, “GovTrack”, IF({Source}= “Al Jazeera - Breaking News, World News and Video from Al Jazeera”, “Al Jazeera”, IF({Source}= “Animal welfare | The Guardian”, “The Guardian”, IF({Source}= “AP News - AP Top News”, “AP News”, IF({Source}= “Art and design | The Guardian”, “The Guardian”, IF({Source}= “Article – The Nation”, “The Nation

I’m writing a small package for the julia programming language intended to wrap the airtable REST API to make it a bit easier to access within scripts. Julia has a pretty strong culture of including automated testing of packages, and I was wondering if there’s a sort of dummy table with an open api key that could be used for this purpose. I can create a dummy account and use that, but wanted to know is there a sanctioned way to do this if not, is there any reason not to make a dummy account

Hello everybody, I have an email I need to mass send it using SendGrid app. I am using Html and inserting some {Field_Names} in it. When using the html, {Field_Names} will not display the record. Can anybody help ? I am attaching the code here: body { background-color: #7F3F98; font-family: proxima nova; } h3 { color: gold; text-align: left; } p { font-family: proxima nova; font-size: 16px; color: gold; } a:link, a:visited { background-color: gold; color: #7F3F98; padding: 10px 15px; text-align: center; text-decoration: none; display: inline-block; } a:hover, a:active { background-color: red; IT IS TIME FOR YOUR YEARBOOK SELFIE! This message is from PROUD IMAGES® - Your School Photography and Print company. Hey {First Name}, This year we are doing selfie style photos for the yearbook. ***For optimal results you will have to open the link above from your MOBILE PHONE. Step 1: Click Yearbook Selfie!. Step 2: Enter your unique school Student or Staff ID

Can anyone help provide a solution to this formula? I’ve tried it with ABS and IF. (2*{width}) + (2*{height})

//Can someone look at this simple example I cooked up. I expect Object.keys to return keys and //Object.values to return values. its easy to setup I just used a new blank template, named it Table and //put some names in Name and Done’s in Status. dictionary = ; let table = base.getTable(“Table”); let queryResult = await table.selectRecordsAsync(); for (var record of queryResult.records) { if (record.getCellValueAsString(“Status”) == “Done” ){ dictionary.push({key: record.getCellValueAsString(“Status”), value: record.getCellValueAsString(“Name”)}); } } output.table(Object.keys(dictionary)) // Why doesn’t this output keys output.table(Object.values(dictionary)) //why does this output keys and values? // this is the output I am getting

Howdy, So I have a table of devices called “Devices Log” and I let people check them out to do testing with them. ‘Devices Log’ has a field called “Owner” which is a link to a directory of testers who can check out and own devices. The check out functionality is to submit a form on a different table called “Device Checkout Log” which includes linking the device being checked out, and the owner checking it out. Now on the record for each device is a linked history of who checked out a device and when, which is useful for when digging into ownership is necessary. But I want to have a sperate field listing the current owner only. Is there a way to make an automation so that when a new record is submitted, the ownership of a device is automatically updated to reflect the new checkout?

Hi all I need some help with formatting a nested IF statement as I can’t understand why it’s not working. Here’s the background: I have a {Start Date} field and an {End Date} field - both are formatted as “Date fields”. I want to check the current day - TODAY() against these dates and print out an emoticon depending on the result. I can get this single IF statement to work: (If the END DATE is AFTER today, then print “ :horse_racing: ” (running!), if not, then print “ :heavy_check_mark: ” (completed!) IF(IS_AFTER({End Date}, TODAY())=1,“ :horse_racing: ”, “ :heavy_check_mark: ”) However I want to add a third condition for the future: (If the END DATE is AFTER today, then print “ :horse_racing: ” (running!), If the START DATE is AFTER today, then print “ :crystal_ball: ” (future!), if not, then print “ :heavy_check_mark: ” (completed!) So I have this which doesn’t work and throws up an error: IF( IS_AFTER({End Date},TODAY())=1, “ :horse_racing: ”, IF (IS_AFTER({Start Date}, TODAY())=1

Could really use some quick help from the community here! Don’t be frightened by the length of this ticket. We think there is probably a very simple answer here that we aren’t seeing. Fairly urgent as this automation is critical to the business. Our team has created an automation meant to update records in a table called UNIFIED WORKSPACE DATA when a record is updated in a different table called WORKSPACE PRODUCT DATA. The automation watches a field called “stripe_customer_id” (type: single line text) in table WORKSPACE PRODUCT DATA – when this field is updated for any given record in the table (this is the trigger), the automation then updates a field in the UNIFIED WORKSPACE DATA table called “CUSTOMER ID” (type: Link to another field) with the “stripe_customer_id” that was updated in the WORKSPACE PRODUCT DATA table (this is the action). The Record ID for the action is found in a field called “workspace_id” (type: single line text) in the WORKSPACE PRODUCT DATA table. The reason we’

I’m getting a weird behavior when iterating over Records, it looks like the code is skipping items. I’m thinking it is either my code or a cache bug. here is an example

Do you have any idea why I failed to send this? I just set this up and I’ve used it only to send two emails, so I don’t think it’s spam-related.

Hi all. Still learning the in’s and out’s of scripting, so please bare with me. I have a linked field, called {project name} that produces a name based on a formula. The name has the following format:" text1 * text2 " - so two words separated by an " * ". I’d like to append the text and remove everything from the " * ". So the new text would just be “text1” I can get this far: SUBSTITUTE({Project Name}, “*”, “”) - but this just removes the *, and not all the letters after. I think I’d need to use LEFT or RIGHT in some capacity, but that only looks at letter position? Could someone please point me in the right direction? Thanks

Hey all. I did some searching through the forum and support but don’t see this scenario. I’d like to use a Button Field to decrement (remove) one unit another cell (Quantity on Hand). The process would be to scan QR on a label made by page designer. On mobile you’d tap the button, REMOVE 1 Button action subtracts one from the QOH cell of that record.

Hello! We use a simple bid and job numbering system to create new bid and job numbers, and we have a table in a base where we input a new record for each new bid and job. Is it possible to have the scripting app do this for us automatically (or after clicking a button or run)? The recipe for bids and jobs is as follows: XYY.MM#.### where: X is 5 for jobs, and 4 for bids YY is the 2 digit year such as 20 for 2020 MM is the 2 digit month, such as 09 for September and #.### is a running counter of the respective bids or jobs that we have, such as 3232 for bids or 6933 for jobs. Here are two examples: 520.096.933 is a job that was created in September 2020 and is the 6,933rd job that we have ever created. Is this possible to do with a script?

We use linked records to create sub-tasks (the tasks are in one table, sub-tasks are in another table where the entries link to a task) However, when you look at all the subtasks for a given task, you cannot filter out “completed” subtasks. As a result, it crowds the view. One solution is to unlink the subtask once it’s completed, but then you can never go back and figure out what task that subtask belonged to Is there any field we can create in the Subtask table that will capture the Task it is linked to, and then preserve that information even when it’s unlinked? Thank you!

Hi! I have been using Airtable for a bit now, but just upgraded to Pro plan and am new to trying to ask for help on here. I just found the fantastic Fancy Finances base on Universe and have been going through trying to update it with all my info so that I can use it to track my $$. I have read all the informative instructions, but am a bit confused because when I am inputting income as negative values in the Deposits table, as per the instructions, it doesn’t seem that they are going anywhere? From what I gather in the other tables, the Deposits, when added as a “negative” value, should increase the running balance under Accounts; entering “positive” values under the Expenses table should and is decreasing the running balance under Accounts. I don’t see any way to contact the creator, so I’m asking for help here. Accounts table: I updated the Starting Balance and it reflected the accurate value. When I added transactions under the Expenses table, the Running Balance and Sum o

Hi, I’m new to the airtable community, and slowly understanding formulas, but I can’t figure out how to write a script to do the following. This is my base: https://airtable.com/shrVDFgXXjqANyMWd When I new record comes into this base, I want to set a trigger that checks the bid amount against the current “MaxBid,” and if it is ≤ to it, I want to then make changes to another field in that record. In this case, if the “Bid amount” of the new record is ≤ the “MaxBid,” I want to unlink that record from the “Item bidding on” so that it doesn’t pass unto the other table. How do I write and IF() script for this? Any help would be greatly appreciated. Thank you, Christhian

steps to reproduce: 1 create a synched table, say Users and create one user 2 in another db add the synched User table 3 create a table, say Exam, that has a field that links to the user 4 verify that you can, from the Exam table, link a user from the synched table 5 from the API call the create method on the Exam table with the field user : [ user-rec-id ] 6 notice you receive an error 422 ‘Record ID user-rec-id does not exist’ expected result: You should be able to create an Exam record from the API with a user linked to the synched User table

Hi! I have a project management base with a series of dates for different stages of the production process. I want to have a series of corresponding “Function” fields that would flip to “Past Due” when a deadline passes. I am very new to Airtable and have had difficulty with writing date-based formulas. Thanks in advance!

I have a form where users can post their time-based results. To make sure data is valid I use 3 fields in the form representing hours, minutes and seconds. So I end up with 3 integers in my table. How to I make a DURATION format (h:mm:ss) out of these fields?

Hello I want to be able to send the client a grid view of a script to a client, and I want him to approve it or not, as well as put comments, but without having to enter my entire base. It is possible?

Hi! I’m at a loss for how to write this formula correctly, even after referencing multiple other topics on here. What I’m working with: TABLE 1: “Original Release Date” (formula field calculating from multiple Date fields in Table 1). TABLE 2: “Deadline Date Add” (currently manual number field, but I’ve tried it as a text string with '- and ‘days’ incorporated to the field as well) TABLE 3: “Master Due Date” I need a formula to subtract the “Deadline Date Add” number from the “Original Release Date” and populate a new date in “Master Due Date.” So if “Original Release Date” = 10/12/20, and “Deadline DateAdd” = 10, then I want “Master Due Date” to return 10/2/20 Currently I’m referencing all fields from Table 3 (Table 3 looking up “Original Release Date” from Table 1, and “Deadline Date Add” from Table 2. All date fields are date only, and don’t incorporate time of day/zones. Any help here would be greatly appreciated! Thanks!

I’m having trouble understanding why this isn’t working. Essentially what I’m trying to do is find out how many authors were published each calendar year and each fiscal year. There are multiple issues published each year and each fiscal year, and sometimes they don’t line up. Each issue links to a record for the year and FY, like so: (this is my issues table) I have a second table for the year and fiscal year records: stats by year & fy. What I’m trying to do is a rollup that will count how many author records are connected to each year and FY. There are some authors that will occur over multiple magazine issues, so those duplicates need to be weeded out from the total number of unique authors for each year/FY. However, when I use the COUNTALL(VALUES) rollup, it seems to only be calculating how many issues are connected to the record, rather than authors: Here’s what I’m trying: I’m not sure why this isn’t totaling “All authors”, which is the table field I’ve selected. I’ve tri

Hello, I have two fields Phone and Phone Extension. I need a formula that evaluates the following conditions: if Both are present, show Phone if exist, otherwise show Phone Extension if exist if only Phone is present, show Phone if only Phone Extension is present, show Phone Extension if both are blank, show it as blank I’ve been racking my brains out trying to figure this out and I know it’s probably stupid simple, but I spent far to long working on this. I appreciate any help anyone can provide.