Solved

Hi, I’m hoping there is a way to do this that’s just a bit above my creative thinking right now. The setup is this: I have a table of projects, with a linked field to records of individual yarns I have used in the project. I need to record the quantity I used of each of the yarns. These quantities will then feed through into a formula in my yarn records which picks up the quantity used in projects and recalculates the remaining available quantity. I’ve got this working for single-yarn projects, but I can’t see how to have multiple values associated with specific linked records. So for example if Project 1 has links to Yarn A and Yarn B records, I want to say that I used 100g of Yarn A and 50g of Yarn B. I then need to be able to lookup only the quantities used in projects of each specific yarn, in order to sum that and use the figure to calculate remaining quantity. This is my current project table structure.

Hi there, I have a communications calendar that I’m trying to have automate some email sends through the Gmail automation. I have my emails on that table as a lookup field, but the automation isn’t recognizing it as an email address so it’s failing the test. Is there a formula that will basically copy what’s being looked up and just format properly so that it’s an email address? Thank you for your help! Amy

Hi, I’m a bit new to this and not sure if it’s even possible. I have a table of ‘scores’ with multiple ‘players’. Each player has a score, up to 30 scores each. In another table I have a rollup which displays all the players scores. What I’m trying to do is find the 5 best scores for each player, add as a total, then divide by 5 to give an average score for each player. This should then give a leaderboard of the best players based on their 5 best scores out of X scores. I’ve managed to do this manually in Notion by adding a checkbox formula which when I enter the player score, I look at their top scores and check/uncheck as required. This is fine and works but I’m now looking to make it more automated with Airtable. Is this dooable as a formula or better as a script on a front end with js? So with airtable it works like this ‘Overall Score’ = All Scores + Checkbox = Total Score Average Score = Total Score / 5 Thanks in advance.

We use Airtable as a back-end for many of our major systems. A big part of these systems is the use of scripting apps in order to streamline many of our processes. We have one system to track shipments. We get data in at the end of our day and someone will upload that data into one of our bases. From here, we run a series of scripts that scrubs and processes the shipping data since much of it can come in very raw. I was in the process of working on one of these script today when all the sudden I was thrown the error “You can only send 15 mutations every 1000ms”. Now, I haven’t made any changes to these scripts today and have never seen this error prior to today. The issue is that we process large amounts of data and while putting an ‘await’, which we don’t normally do, in front of an ‘updateRecordsAsync’ function will allow the script to work, it takes us from a process that used to take 5 minutes to one that takes ~30 and potentially ruins our entire decision to move this process into

Hello everyone! I am trying to build an ATS (Applicant Tracking System) using AirTable. I basically have a Candidates table, a Companies table and a Candidate_Company table that links a candidate to a company including other infos such as the recruitment step of the candidate in the company. Whenever a new candidate is created, I’d like to automatically add rows in the Candidate_Company with the newly created candidate id and the id of all the companies. Same when I add a new company so it automatically add rows in the Candidate_Company with the newly created company id and the id of all the candidates. From what I’ve understand so far, Automations alone won’t be able to do that, it looks like I’ll have to combine Automations and Scripts, am I right? Do you have any article or tutorial that could give me some hints on how to proceed? Thank you! :slightly_smiling_face:

Hi all. Relatively new to AirTable, but loving it so far. I have a question about sharing data between bases. We have a “parent” base with text, images, tags. We’d like to create subsets of this base as “children”. Ideally, the users of the child bases would not be able to edit the parent base. And, if the parent base data is updated, then those data updates would automatically be reflected in the children. Is that possible? Thanks in advance!

Hello Airtable community! I am struggling with a formula and thought I would put it here since you all never let me down :slightly_smiling_face: I want the formula to produce the result overdue IF the contact’s sum of (“level of connection” and “Level of importance”) is greater than 5 and “Last Contact” is more than 180 Days The contact’s sum of (“level of connection” and “Level of importance”) is greater than 7 and “Last Contact” is more than 90 Days -The contact’s sum of (“level of connection” and “Level of importance”) is greater than 8.9 and “Last Contact” is more than 30 Days For reference here is what I currently have (Very far off) IF(DATETIME_DIFF(TODAY(),{Last contact},‘days’)>90,“Overdue”) THANK YOU !!!

Phew! Lets’s see if I can explain this clearly. Almost daily I go running. I keep a log of distance and time and get a kick out of recording and graphing the results. In Airtable, I’m using the chart app to chart several bits of data (DIstance, Time, s per km, s per mile, speed, etc.) For the straight forward data (duration, time) Airtable has no problem making charts for me, they look great! But, Airtable won’t let me make a chart with duration. No problem: the current workaround is as follows (example to calculate km/h): Insert Distance as Decimal in “Distance” field Formula -> convert to m (divide by 1000) (new field) Insert Duration as Duration in “Time” field Formula -> convert to s (divide by 60) (new field) In “km/h” field, formula {distance}/{time}*3.6 This then gives speed and so far has been carefully charted every day without fail But when I try to do something else (e.g. duration for fixed distance - mins per km) the fomula works for all previous entries but never t

hi. im trying to change the view with filter by api as i said. (outside airtable) i read the api document. and tried to “filter by formula” by airtable encoder. BUT as i understand it only support “GET records” which is not i want (to ADD filter in the view) is there a way?

I’ve successfully applied both those conditions distinctly: filterByFormula: {customerOwnerRef} = '' filterByFormula: {customerOwnerRef} = '${process.env.NEXT_PUBLIC_CUSTOMER_REF}') But I don’t understand how to make a logical OR between both conditions, I tried the following but it fails with INVALID_FILTER_BY_FORMULA error: filterByFormula: {customerOwnerRef} = '${process.env.NEXT_PUBLIC_CUSTOMER_REF}' OR({customerOwnerRef} = '')

Hey guys, Which field type should I use to enter page numbers for each of my table listings? I want to be able to do some calculations on them later e.g. average page number for 10 rows of book listings. I tried using the number (#) field type but it adds an unnecessary decimal place. So for example a page number entered as 350 changes to 350.0. I was going to use the single line text field but wondered if that would prevent me running calculations later on. Thanks in advance if anyone can help.

Hello! My setup is : two tables fisrt is made with my participants second is made of my categories (event A, event B,…) I am looking for updating the number of participants recorded in my first table in each event that are in my second table. So far, I could compute the total number of participant by event in table 2, but I cannot find out how to update this calculation while a new record is occuring. Because automation Update record replace the value : it seems impossible to append a record, nor to use a variable like “ALL RECORDS” (for example). How can I manage this issue ? Thank you !

Hi folks I’m having an issue with the Charts app. I’m setting up a simple scatter chart with x and y axes drawing from formula fields in my base outputting a percentage and a decimal, respectively. After initially setting the axes, the plot works nicely, and when I rollover the data points on the chart it shows me the points measurements alongside the primary field data for that entry, identifying the point. My issue is, when I then go to group the points by a third field (using the “group by” toggle in the chart’s Y-axis settings), while the grouping displays on the chart correctly, when I rollover any particular point the primary field data that appears on the chart is for a completely different point. Though, when I click on the point, it does bring up the base entry for the correct field it is supposed to represent. Am I doing something wrong here? It seems like a bug to me, and it reduces the usefulness of the charts app quite a bit for me. Would appreciate any and all feedback! C

Hi, is this possible? Thanks for your help.

I have a table with a lot of webshop orders in it. how can I make a view with all orders made to-date a year ago? So I can compare Christmas sale from last year with the sale this year to date. is there a function that checks for order date one year ago and back for example

hello, i want to copy my first field to second field using automation as shown in image below. any help please? my automation’s trigger is “when a record is created” and action is “Update record” i want to update my second field with my first field’s value

I’m trying to work out how I can have different members of a team view a spreadsheet of ideas (each row is an idea) and have them each vote on whether they like the ideas. I want to then show an updated tally of the number of votes each row gets. I was thinking maybe I have a multi-select column with the names of each team member. If you like an idea you add your own name to this selection. I’d then want another column which is a number, which I was thinking I could have a formula to count the number of items selected in the multi-select field. So I’m not sure what formula would work there. Or if perhaps there’s a better way of approaching this? Thanks.

I’m trying to further customize a formula (that’s already working) with additional functionality to detect when text is missing a certain element and, in such cases, to then add that element to the text in a different column, but can’t seem to get anything to work. Specifically, I have a formula that, among other things, takes a URL from, say, Column A, and then customizes/edits that URL in Column B based on criteria from data in other columns. In this instance, Column A is a URL field and Column B is the formula field, and Column B displays updated versions of URLs from Column A. So far the formula is working fine except I also need it to detect when a URL in Column A is missing its trailing slash ("/") and to subsequently add in that slash where it would otherwise go when the URL appears in Column B. I also don’t think it will be as simple as merely finding/replacing instances of <.com> with <.com/>, <.org> with <.org/>, <.co> with <.co/>, etc., si

SOLVED: SEE POST FROM KAMILLE I have a database of students in Uganda and I am building a separate table to help the administration track student temperatures (government requirement for school re-opening). The table is simple and the nurse will use a basic form (Select student name, enter date/time, enter temp). In the Student table, I’d like to add a field that shows the most recent temperature taken and the date. The Rollup field works well for the date using the MAX(values) aggregation formula, but I can’t figure out how to show the most recent temperature (as opposed to the max or min). Any suggestions on how to show the most recent number added within a set of values? I’ve seen a few other posts on this, but I couldn’t find a clarification. I realize the “Most Recent Temp” field in my screenshot is a text field. I’ve placed that there for illustrative purposes only until I can figure this out. Thanks, Dave

Building a payroll system and need formula help with splitting up total hours between Daily Hrs, time and a half pay, and doubletime. Here’s my setup: My “Total Hrs/day” is the actual total of all hours worked, calculated with: (OUT-IN)-BREAK Then, I want to limit the amount of hours from the “Total Hrs/Day” that can go into “Daily Hrs” to 8 hours of regular pay. I do this with: IF((OUT-IN)-BREAK<=8, (OUT-IN)-BREAK, 8) This means that if 10 hours were worked, “Daily Hrs” is capped at 8. I want the remaining two hours to populate into the “time and a half” to be multiplied by that 1.5x rate. The caveat is that I need this to be capped at 4 hours of overtime, before spilling any remaining hours from the “Total Hrs/day” into “doubletime”. IF( (OUT-IN)-BREAK>8, ((OUT-IN)-BREAK)-8, BLANK() ) I can then spill the remaining hours into the “time and a half” with this formula above, but I can’t figure out how to also cap it at 4 hours before sending the remainder to doubletime. I tried

I have 3 tables: Units - a list of all our technology units whether classified as inventory or leased to a customer Customers - linked to the Units table Unit Setup - this is a form to be used by the shipping department to go through shipping checklists AND assign a Unit to a Customer. I cannot figure out how to get the Unit Setup Form to update the Unit assigned to the Customer. I can get it to add a new linked record column… but I want it to update the main associated Customer record to the Unit.

While I’ve seen plenty of posts on formulas based on whether a checkbox is checked or not in another field, I’m curious as to whether it is possible in the inverse? I want to create a formula that will say, “If condition exists in some field, mark checkbox in another field.” Can this be done?

Hi! I am trying to set up an automation so that when we update the record, an email is sent out to a client with the updated notes. However, when I use the date link in the Automation, the email test comes back with “2020-10-30T18:04:00.000Z” as opposed to the US format (10/30/2020 at 6 pm). Am I missing something that is causing this to come out this way?

Hi All, I’m looking for some help in building out a formula field (Transfer Tax) that is formatted as currency. I’m new to Airtable but have a lot of Salesforce.com experience. The Transfer Tax field provides the dollar amount that will be charged when the dollar amount in another field (Purchase Price) falls in a certain range. The rule is: the transfer tax is $3.30 for every $1,000 of the purchase price. Essentially, a Purchase Price anywhere from $0-$999.99, the Transfer Tax field should be showing $3.30. If the Purchase Price is anywhere between $1,000-1,999.99, the Transfer Tax field should be showing $6.60. If the Purchase Price field is anywhere between $2,000-$2,999.99, then the Transfer Tax field should show $9.90. This continues for infinity. Ideally, I only need it for a purchase price going to about $100,000. In Salesforce.com, I would likely do this using something like the IF and QUANTITY logical and numeric functions. That said, I don’t think Airtable has the QUANTITY n

Hey everyone. I’m using this formula to calculate how many days it took for a response. DATETIME_DIFF({Response date},({Submission date}), ‘days’) & " days" I’d like to add a part to the logic that says if {Response date} is blank, calculate days between Submission date and today’s date. Any ideas?