Formulas

Hi team, I’m attempting to prefill my email field question, and for some reason, the below formula returns #error when I include it, but when I remove the email field formula, all other prefilled data works. Is there a mistake in my formula? Is this due to the fact that the prefilled data is a lookup from another column tab rather than an actual field entry? My Formula: CONCATENATE({Form Response (GC’s Form Requests)},’?prefill_Customer=’,ENCODE_URL_COMPONENT({Customer}),’&prefill_ProjectID=’, ENCODE_URL_COMPONENT({ProjectID}),’&prefill_Bath+Type=’, ENCODE_URL_COMPONENT({Bath Type}),’&prefill_Your+Firm+Name=’, ENCODE_URL_COMPONENT({Assigned GC}),’&prefill_Primary+Email+Address=’, ENCODE_URL_COMPONENT({Email}),’&prefill_ParentRecordID=’,{Main Record ID}) Thank you in advance!

Hi Folks, I hope you guys are having a great time. I am working on a localization budget for a client , and they process payment per Episode not per title. a title could start in 2021 and end in 2022, so we should process this in 2 separate times. 1- from release date ( Available field ) let’s say 1/10/2021 till end year 31/12/2021 ( End of current Year 2- From Start of the new year till the Last episode release date ( available field ) let’s say 18/2/2022 ( End of New Year for last eps date ) We will be able to calculate No of days between the start and end of current year and calculate no of days between start of the new year till the last episode release date . I have added 2 new fields 1- Days before Year start ( should include No of days between the start and end of current year) 2- Days after Year Start ( Should include days between start of the new year till the last episode release date) I will then convert those days against the total days between release and last eps date a

I need to create an IF statement that works with the following logic: If the value of column X is less than the value of column Y, it needs to return the value of column X. If the value of column X is greater than the value of column Y, the IF statement needs to subtract the value of column Y by the value of column X. Can someone please help?

Hi there helpful community, I am currently facing the following issue: I have a list of requests from customers that I map to retailers in their area. I successfully calculate the distance between each customer and each retailer using the Google Maps Distance API. Now, I would like to automatically calculate the order of those distances, e.g. 1 = closest; 2 = 2nd closest, etc. I simulated this in the screenshot below. Instead of me assigning those numbers (1-3 in this example), I would like to calculate this automatically for all requests. Do you have any solutions in mind? In Excel this could be achieved by the rank formula I believe…

Greetings, I’ve created a simple base to calculate my stock (vapes) that i’ve ordered. However i am running into an issue. I have created three tabs (Current Stock, Sales (orders) and Orders from my Supplier). My issue is when i register a sale because when i put the items in product column (multiple records) the price at the “price column” shows only as 1$. If i only put one product then that is fine because the product is linked and the price shows okay but once i put multiple products on a single sale the price always remains the same. Hopefully i was clear enough and i can provide more context, info or pics if needed. Thank you,

I was wondering if anyone could help with my issue. I am trying to get a value in field W MIN from an array of numbers (field: W Unique). But I only need to return the smallest number larger than a given number (field: Thermal MIN). I am using this formula in the field I want to return that number: MIN(IF({W (from LED Performance Data) Rollup (from LED Type grouped by Output)} > {Thermal MIN (W)},{W (from LED Performance Data) Rollup (from LED Type grouped by Output)})) (the formula is entered in the field column (drop-down arrow and select customize field type) The same I need to achieve for the W MAX field comparing the values to the number in the Thermal MAX field. However, it comes back with an Error! and I am not sure why? Here is a screenshot of the columns and values. Any help would be highly appreciated.

I have included screenshots for context. I just want to get the total from the first screenshot to the spot in the second screenshot. Any and all help will be appreciated!

We have physical punch cards (time cards) that we are trying to review. I set up a base with a row for each employee’s card. Each card is for a pay period and the employee has multiple days worked per pay period. The base (redacting employee names and pictures of the punch cards) looks like the following: I want to find which employees worked together. I’d like to do this by choosing a particular employee and seeing who that person worked with and when they worked together. I would also like to see who worked together by choosing a particular day. I think this would have been easier had I built the base with each shift as a row, rather than each pay period. Nevertheless, is it possible to extract this information with a formula, lookup field, or rollup field? I feel like this might have been a job for an if-then formula along with index-match in excel, but I am new to Airtable and am not sure if a formula is where I should start. I am very grateful for your suggestions.

I want to get the unique value of the {Photos IDs} column, but ARRAYUNIQUE() doesn’t work, presumably because it has a newline symbol in it. What should I do

I am completely out of my depth here, so I apologize for dumbing what I’m doing down. Also, talk to me like I’m a child when explaining what’s going on :grinning_face_with_big_eyes: I have built a simple website with Webflow and use Wized as the backend. Wized uses Airtable as the database. One of the functions of my website is that it gives the user a unique email address where they can send messages. I want this unique address to be created by Airtable. I’m assuming i use the first and last name tables with this formula {first} & {last} to get the first and last name into a new cell. Now i have two questions: how would i add to this first and last name the “@mydomain.com” part of the email? i know this is something super simple, but just can’t seem to find the right one more importantly, how would i get airtable to check the same column for duplicate email addresses before creating this one? and if it encounters a duplicate, get it to ad a number (1, 2, 3…) to the end of th

Hi, IS it possible to sort this list of times, earliest to latest? I can only sort A-Z and this is what it returns:

Hi Anyone have any clever tips for removing special characters from text to make them filename firendly? For example < > { } ) ( etc. I could do it with a massive nested substitute formula but that seems clunky so hoping there is a cleverer way of doing it.

My datafile has a field with multiple tags that are joined like this: TagoneTagtwoTagthree Is there a formula possible, with help of regex, to put a comma between all lowercaseUppercase instances to seperate them?

For a table with organizations I have a partner field that links back to the primary column itself. Suppose an organization has 10 partners and I put them in the Partner field, then all those organizations will get that one organization in their linked partner field. But I would like for them to get all the organizations in their partner field, ideally minus themselves. Thoughts on how to do that?

I am trying to make a formula that tracks how long a record has a certain “Status”. If the status is “Open”, the timer starts. If the status is “Pending”, the timer stops, and stores the value that it was just before the field was switched to pending. I also want this timer to start incrementing again if the record is moved from “Pending” back to “Open” (so, not starting from zero, but starting from the paused time. I thought this should be fairly simple, but I’m not sure how to make it work. Thanks

Bit of a tricky one for me, im looking for a formula for this situation with a multiple select box: if ‘Jon’ selected =£18, if ‘Carl’ selected =£20, if ‘Jon’&‘Carl’ or ‘Carl&Jon’ selected £38 just a snippet of what im after, i have 7 opperatives on different rates, basically looking to then multiply by hours on site to get a final expenditure. Any help i would be greateful

Hello, I’m trying to do a fairly basic calculation that is conditional on a percentage field - if the percent is above a threshold, return a calculated number, if below return 0. My formula works with anything except zero for value 2. Here’s my formula: IF( (Potential*100) >= 490, {Estimated Points}*Potential, 0 ) Any help greatly appreciated.

I am currently using a workday formula to calculate an end date based on a “Start Date and an Average Length of Completion” for a task. My issue is with single-day tasks, if the task begins on any day other than a Friday, the formula works. If it starts on a Friday, it won’t calculate as a single day. Formula is: DATEADD(WORKDAY({Start date}, {Length of Days}), -1, ‘days’) tl;dr: Monday-Thursday formula works and returns the correct end date. Friday start date calculate a Monday end date.

Not sure if this was covered before. I’m trying to use the datetime_diff function in a formula as a trigger to send out an email 5 minutes before the meeting starts. The problem is that this function is very sluggish to update. In other words it might be 10 or 15 minutes past the moment it should trigger before it refreshes and shows the correct difference in time. But by the time it updates it’s too late to send the email out. Is this a bug? Or is there a workaround? Thanks!

I want to list each linked entry in the “facts copy” column as a separate bullet point in a bulleted list in another column. Here is the table: Here is the formula I tried to use unsuccessfully: REGEX_REPLACE(CONCATENATE({Facts copy}), “, “, “<br>⦁“) I am not a programmer so apologies for my lack of knowledge.

I know this is simple but I’m struggling for some reason. {Average Weight}/2000 * {Base Rate} + {Surcharge Calc} It adds the ‘Surcharge Calc’ which is a percentage just as a number. What I’m trying to accomplish: Average Weight = 10000, Base Rate = 15, Surchage Calc = 20% 10000/2000 X 15 + 20% = 90 I’m getting 75.20 Thanks

Hi, First: Airtable is an incredibly useful program, both for business and personal use. It’s a lifesaver that I’ve been using for years. However … I have need for Formulas for the first time and having trouble getting my head around the syntax for this task. I need to conditionally add and output the sum of 3 Currency fields based on the status of the Checkboxes: IF the status of {$VALUE 1} is checked - output of the Formula field is $VALUE 01. Otherwise the output of the Formula field is 0. IF the status of {$VALUE 1} and {$VALUE 2} are checked - output of the Formula field is ($VALUE 01 + $VALUE 2). Otherwise the output of the Formula field remains $VALUE 1. IF the status of {$VALUE 1} and {$VALUE 2} and {$VALUE 3} are checked - output of the Formula field is ($VALUE 01 + $VALUE 2 + $VALUE 3). Otherwise the output of the Formula field remains $VALUE 1 + $VALUE 2). For this task the $VALUES will either be hit in sequence, or they won’t. ie: We will never have a case where we need to

Hello, I would like to set up a calculation to allow me to multiply a number according to conditions. IF(), but I don’t know how to go about it. Here is the request: My column: ( hide) Inventaire Composant sortant (Unique) corresponds to a set of products: ex: 1 table. "I could have called this ( hide) Inventaire Produit fini sortant (Unique) → This is what I take out of my stock but as a product as a whole (single). The question is for my column: Inventaire composant sortant ( Calcul pied), I would like according to conditions to be able to say if table with 2 feet, multiplies (( hide) Inventaire Composant sortant (Unique)) x 2 (# (hide)Pieds), if table with 3 feet, multiplies (( hide) Inventaire Composant sortant (Unique)) x 3 (# (hide)Pieds), if 4-foot table, multiplies (( hide) Inventaire Composant sortant (Unique)) x 4 (# (hide)Pieds), idem for if 1 feet and if Blank(), leave the field empty This would then allow me to subtract Inventaire composant sortant ( Calcul pied) from ( hi

I have two values “PUt” and “SRt” With different values of these fields I need to output different “Profiles”. When I write a formula to compare these numbers, I get the wrong answer from the formula. Values: This is my formula: Logically, the formula should select “Profile 6” But the cell shows “Profile 5”.

Hi folks, Formulas aren’t my strong suit, but I’m hoping there’s a way I can do this. Essentially, I’m helping my school manage teacher assignments, so I’m building a simple database to help me organize and visualize this data. I have two tables: 1 for Courses and 1 for Teachers. I have them linked together such that if in the Courses Table I type in a teacher’s name, then that course will show up on the Teacher’s tab (and vice versa). In the Course tab each course has a numerical value assigned to it based on the number of times per week they meet. What I would like is a column that tabulates the total number of periods assigned to a teacher that is calculated based on the courses assigned to that teacher. Does anyone have any formulas that can do it? I was hoping a lookup field would be a sneaky shortcut, but no luck. See my attached screenshots (I’ve cut off the teacher names field for privacy’s sake):