Formulas

Hello, Here is a screen shot: What I am trying to do is create a formula for In Stock so that when Condition is New, Quantity decreases by 1 (and if the Condition is not new, it stays with what is in stock). However, as you can tell with Row 3, that is not the case. My formula thus far is = Quantity & IF({Condition (from Maintenance)} = ‘New’) - 1 What am I missing? Furthermore, if Condition has ‘New’ twice in the column (or however many is in the Quantity), such as Row 1, in stock should be blank or 0, or if there is nothing in Condition, In stock should equal Quantity. I have it so when stock gets to 1, the status changes to Order More, and would like it to change to Out of Stock when it is 0 IF({In Stock} = 1, ‘Order More’) & IF({In Stock} = BLANK(), ‘Out of Stock’) Is this doable or do I need many more columns and filter / group items?

Hi, How can I change time format 8:00 into 0800? Thanks Erwin

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’m trying to combine and format data in order to use in a report…would like to separate with tabs if possible to get the spacing and format to look nice and consistent. (Spaces are not working out correctly if record data doesn’t have the exact number of characters.) I’m trying to doing something like the following, but can’t seem to find anything that produces a tab. I’ve tried “\t” and “ALT+09”. Any suggestions? {Data 1} + TAB + {Data2}

I feel like this question has been asked before, but I cannot find an answer or post that really applies. I have a form where users can input/sort options from one table (a hacked ranking system). Once they the form is submitted, I then want to figure out an automattion using basic text fields, or have multiple formula fields pull the records 1 by 1 into the separate fields in the order they’ve been input by the user’s form submission. A, B, C record order Field 1 = A Field 2 = B Field 3 = C I’ve looked at arrays, converting to strings, tried the string lengths by using the shorthand info, this seems like something I used to do all the time in others, but it’s very clear arrays of data are not well managed in this?

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

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

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 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?

Hi, all! I’m facing a challenge to do the following: Column A with multiple choices (All, Sheep, Goats… up to 12 more) Column B with multiple choices (Universal, Black, White) Column C with single choice (F, SE) Column D with multiple choices (3 months, 6 months, 9 months) Because I have more than 500 records I need a formula to do the same as filtering manually. May be the topic should be divided in two parts: I want to have a formula that can tell me if a certain combination for a record is “true”. If it is true, we can name it (All-Sheep-Black-F-3-months), then the formula displays the name of the combination (All-Sheep-Black-F-3-months). I found that in excel this could be done with ISNUMBER and FIND but Airtable doesn’t support ISNUMBER. The problem for me here is that I cannot use AND and OR in columns with multiple choices because it doesn’t work or I just can’t make it work. For combination All-Sheep-Universal-Black-F-3-months this is what I use: IF( AND( OR({Column A}=“All”,

I am trying to create a button that when clicked, opens a gmail link and populates the email address and body. My table has a lookup field called “PrimaryContactEmail” that simply pulls from the link record “Primary Contact” and brings in the email so that I can reference it in my formula. The formula I am using is: “https://mail.google.com/mail/u/0/?view=cm&to=” & ENCODE_URL_COMPONENT(PrimaryContactEmail) & “&su=” & ENCODE_URL_COMPONENT(“IPM Scoutek Demo Devices Shipped”) & “&body=” & ENCODE_URL_COMPONENT("…") The weirdest thing happens though. For rows that HAVE a value in PrimaryContactEmail, the button is disabled (greyed out) but if the PrimaryContactEmail is empty, the button is clickable! I thought MAYBE this is some kind of spam prevention thing, preventing me from emailing users outside of the base, but experiments (such as switching to the phone number) show the same behavior. Please see attached image. Why this conditional button behavior, I

Hello, I as making a Base and there are 4 sheets. Is there something i can to for some of the columns of the sheets be linked? Example: We made a CRM about out clients. In one of the sheets are all the oportunities and who of our Hunter Team brought this oportunity. Then, we made a Sheet that tells us more about the client (website etc). I would like to link the Hunter in both sheets for that same name of client.

Hi everyone, I did my best with this using just the Help pages but I can’t seem to get this formula right. Basically, I have a spreadsheet where I have listed my credit card statements when they come in, including the date that the bill is due. Then I have a field where I state whether the bill is “Paid (in full),” “Paid (partially),” or “Unpaid.” Lastly, I have a formula field to determine whether the bill is overdue. This is the formula I have so far: IF(AND(DATETIME_DIFF(TODAY(),{Balance Due Date},‘days’>0),OR(Status=“Unpaid”,Status=“Paid (partially)”)),“Overdue”) What I’m trying say with this formula is: If today’s date is later than the balance due date, and the bill is only Unpaid or Paid (partially), then I want it to spit out “Overdue.” However, with this formula, it ignores the DATETIME_DIFF command and always spits out “Overdue” even if the balance due date hasn’t passed yet. Is there a way to fix this?

Trying to auto create a unique key in a table used for tracking grazing on a cattle farm. So my current formula is “(Pasture & DATETIME_FORMAT(Date, ‘M/D/YYYY’)” Where “Pasture” and “Date” are both fields in the table. Any help?

Hello guys, I have this string : DECO > OBJET DECO > AUTRE OBJET DECO and I would like only : AUTRE OBJET DECO so only what is after the second >. I tried with : RIGHT( {Catégorie}, FIND( “>”, {Catégorie} ) -1 ) RESULT was " DECO" (The last 5 characters) I really don’t understand why only the last 5 characters.

Hello Community, When checking the API Doc, I can see Airtable is providing size and thumbnails dimensions for an Attachment Is it possible with a formula (not script) to pull properties from the first attachment? I tried several things like: Attachment[0].thumbnails.full.width … but didn’t succeed :confused: Regards.

Hello! Newbie on Airtable, so I’m pretty sure this is a really daft question, but after looking through the help and this forum for about 2 hours and not finding anything, I’m now starting to wonder if my goal is even possible! I have 2 tables: An Authors table has name/contact details/author image fields. A Content table, which had a load of fields, including a ‘link to another table’ field to display the ‘author name’ field from the Author table. What I want to do is to have a field in the Content table with a tick (or yes/no or some sort of indicator - I kind of don’t care what it is!) which shows based on whether there is or isn’t an image uploaded in the ‘Author image’ field on the Author table. So kind of this (which I know isn’t actually a formula, just trying to lay out what I’m trying to achieve!): if field ‘author image’ in table ‘author’ is not blank, then show :white_check_mark: in field ‘got author image’ in table ‘content’ Any ideas? Is it possible? Or am I trying to ma

Hi Every one i want a formula like this: IF({TaskIcon} == BLANK(),“https://png.pngtree.com/png-clipart/20191120/original/pngtree-pen-icon-isolated-design-png-image_5045590.jpg","null”) TaskIcon is an Attachment field. i want that if {TaskIcon} field is blank (No attachment in the field) then my DefaultIcon field set to a url Please Help Someone

The following formula in the Airtable blog post here, under the heading “Find the next occurrence of a specific day :hourglass_flowing_sand: ” with the description: “if you just need to know the nearest Tuesday to a given date—even if that Tuesday has already passed—this simpler formula will do the trick:” is as follows: SET_TIMEZONE(DATEADD({Date},2-(WEEKDAY({Date})),‘days’),‘America/Los_Angeles’) (It is suggested that the 2 in the formula relates to finding the nearest “Tuesday”, and can be replaced with any other day from Sunday (0) --> Saturday (6) ) I noticed this formula break when the given date is a Sunday and you are looking for the nearest Thursday (4), Friday (5) or Saturday (6) (which would occur before the given date). Since WEEKDAY({given date}) of a Sunday resolves to 0, The formula will never return a day in the past. More generally, if given date is Monday (1), this function will never return a day more than 1 day in the past (e.g. Saturday) if given date is

Hello, I receive time data in UNIX format in a text field on airtable. I would like to create a formula that allows me to have a date that I can understand, in the same basic format already on airtable. Could you help me? Thank you in advance I’m living in Switzerland, Zurich time

Hi AirTable friends, I have records that each have a long text field which is TOO long. That long text field is called “Excerpt.” What I’d like to do is create a formula field called “Trimmed Excerpt” that copies the content from “Excerpt” field but limits the word count to, say, 50. What’s the formula for that? I Googled for a solution but came away empty-handed. Thanks to those of you who take a crack at this one!

Can you do simple references within formulas? Current formula: IF({Frequent escalation?} = 1, “However, {he/she} frequently escalates, and so {he/she} needs a significant amount of support.”, “{C He/She} does well there with minimal supervision and supports by adults.”) The formula is working, except the pronouns are not. They are of course referencing other formulas, and there is a checkbox called “Frequent Escalation?” If not (siiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiigh) any suggestions on how to get around this? I am eventually wanting to get to more advanced texting formulas, but need to figure this out! Thank you so much!

Hi everyone, Now that I have Airtable all up and running with formulas etc. I have hit a snag. I understand we cannot link between bases but we can link within a base. I’m trying to create a formula that will decrease my stock column by 1 when a NEW appliance is installed into an apartment via the Maintenance tab under the Comments column. Am I able to do a IF formula in Inventory to read the IF Comment under Maintenance reads NEW - 1 from Stock? I don’t know if the formula will work because the Comment field is typed in.

Here’s the formula I’m using: IF( {Number Field 1}!=’’, ({Number Field 1} / {Weight (number field)} + {Number Field 2}), IF( {Number Field 2}!=’’, ({Number Field 1} / {Weight} + {Number Field 2}), ‘’) ) My goal with the formula: I’m trying to reach an inventory count as a result of this field. A) If the inventory count is low, we can manually enter the exact count of the part into “Number Field 2” B) If the inventory count is higher, we get an approximate count based on weight. So we enter the weight into “Number Field 1” of the total part. It calculates total weight vs the part weight to give us a part count. C) If for some reason during the process we weigh the inventory, but find more of that part, we sometimes add the exact count on after. So if there’s numbers in each of the field, we need the formula to account for both. The result of the formula “works”, as in the numbers are right. But it isn’t a number output. So for us to use the result of the field, or format the output, it