Formulas

I’m trying to list the field names for which non-zero values appear in each record. I would have used a combination of Match and Index in excel… What would be the solution in Airtable?

Hi all, This has become a bit of a brainteaser for me and was wondering if anyone much more proficient than I could have a crack! Trying to formulate a field to return COST PRICE as a NEG value when the following options are true: There is a value in COST PRICE($) column AND the REASON column value = “DEFECT” Further, I need help creating a condition that applies the above only when the following is true for a certain supplier: The SUPPLIER column value = VR The GAME RETURNED column (checkbox) = TRUE Essentially, I want to have a negative cost-price returned in a separate column if the REASON is listed DEFECT and the COST PRICE is AVAILABLE, but for a particular supplier (VR) I only need this negative cost-price value returned when the GAME RETURNED column is checked. Current formula is: IF({COST PRICE}=1, AND({REASON}=“DEFECT”), {COST PRICE}*-1) and is returning cost-price for every reason code listed, instead of just DEFECT. Also doesn’t factor in the VR supplier condition. Wonde

Using the web clipper app, which doesn’t support the Date field. I can create a Month and Day field in the Clipper in which the user inputs the values. Can I then concatenate the Month and Day into Date type which results as an example 4/14/2022? I appreciate any help. Cheers

I have a base to keep track of tasks that need to be repeated every so often. I’ve been able to achieve this with the following formula, checking the modulus between today and the start date to see if it matches up with the repeat interval: IF(MOD(DATETIME_DIFF(TODAY(),{Start Date},'days'),{Repeat Interval (days)})=0,"Today is the day!","Not yet...") When the formula resolves to “Today is the day!”, an automation is triggered that creates an instance of the task on the to do list. The limitation to this is I have to put my repeat interval in days. How would I go about building a formula that does the same thing, but could handle different interval inputs, like every 2 weeks or every 18 months? I think I would need a number field and an interval field (e.g. 3 and "weeks"), then maybe a switch statement to handle the different cases? If anyone has any examples or could point me towards a tutorial, I would be most grateful.

I have a base with labor rates, which can vary depending on retail / wholesale, etc … screenshot 1 That table links to another table in the same base, which has a variety of categories. Might price category 1 at List Price, category 2 at 10% off, category 3 at cost +10%, etc … screenshot 2 Is there a way to have one “base rate” amount, and then have the variable rates calculate based on that? Right now, I just input them each manually. Would be nice to change the “base rate” and have all the variables automatically update. I realize I could do this in multiple fields, but that won’t give me the ability to have a linked record selection? Thanks in advance

Hi everyone, I’m trying to set a field value based on a formula : IF( OR( {Commandes} = BLANK(), {Date_heure_RDV} = BLANK()), "", CONCATENATE({Commandes} , " - ", DATETIME_FORMAT(Date_heure_RDV, 'M/DD/YYYY H:mm')), IF( OR( {SILGAN BOL} = BLANK(), {Date_heure_RDV} = BLANK()), "", CONCATENATE({SILGAN BOL} , " - ", DATETIME_FORMAT(Date_heure_RDV, 'M/DD/YYYY H:mm')) ) ) But when saving the formula… it just keep the first part. Is it possible to do this kind of nesting? Thanks for your help

Hey guys, I’m not sure how to do this, but I have a table where all sales are entered in an order line item format and I have a column for the invoice number field. I can’t change that because of how that’s exported from different distributors. How do I sum up the $$ for each order line item based on invoice number so I can get a roll up and pull a report on average sale per customer? thank you!

Excuse me but I’m trying to create a formula field but it always turns out not editable, why? I’m looking all over for the solution but can’t find anything.

Hi, I am struggling with a date formula: I want to calculate the number of weeks that a record will be early or late based on three variables. The Delivery Date, The Ex Factory Date and the Freight method. The formula below is the equivalent of what i would use in excel. I calculate it in days and then divide by 7 to show the number of weeks. The formula doesn’t seem to work in Airtable and i cant work out why this is. I was wondering if anyone would be able to give me some advice on this? =IF(OR(“Freight” = “Sea”; Freight =“Air”);IF(“Ex Fact” >0;”DC Date” - “Ex Fact” - 49)/7; IF(“Ex Fact”>0;”DC Date” - “Ex Fact” -10 )/7) In words this is what i’m trying to calculate is the following - if the freight method is “Sea” we add 49 days (7 weeks) to the Ex Factory date I want to know the difference between this date and the planned DC date. If the freight method is “Air” we add 10 days to the ex factory date. We want to know the different between this date and the planned DC date. Than

I have set up a series of tasks. Some of the tasks have a regular due date (DUE DATE), some of the tasks have a due date that is calculated (CALCULATED DUE DATE), and some of the tasks need to be done as soon as possible (AS NEEDED). I would like to make a formula that says IF the due date is not empty, return the DUE DATE. but if the due date is empty, look to see if the CHECKBOX is checked. IF the checkbox is checked, return the words “ASAP” but if the due date is empty AND the checkbox is not checked, return the CALCULATED DUE DATE. As an added bonus, I would like the dates in the final output to be written out in the format “Monday, 3/14/22”. As an added added bonus it would be cool if those dates could be workdays. I think this is getting complicated because some values are dates and others are words but I can’t quite figure out. Attached is an image of what I want to do with what info.

I synced my data with Jotform and the Date appointment is come in Text so I can’t find the way to sort it by Date. not sure there are anyway to help me with this. Thank you.

Hi There, I have two single select columns and I would like some of them to follow one another. Say for instance if I select “ON HIRE” in one single select column I would like the other single select column to automatically select “ON HIRE” as well? can this be done, any help would be greatly appreciated.

Hi, I am relatively new to airtable. I have a form asking people for their top 3 of something (let’s say food) and they input it in separate fields under primary, secondary and tertiary. That is all in one record for one person. Now I would like to list all of those favorite foods (maybe a couple hundred) in the primary field of a different table so I can present a view of all of the favorite foods. I haven’t been able to figure it out. Any help or direction would be appreciated! Thanks!

How do I write the equivalent of Excel’s PMT formula in AirTable field that produces the equivalent result in Excel? In Excel it’s =PMT(rate,nper,pv). In Excel I adapted it as =PMT(rate/12,nper12,pv)-1** which also produced the monthly payment as a positive value. I found this in the community: ({Monthly Interest Rate}*{Loan Amount})/(1-POWER((1+{Monthly Interest Rate}),-{Number of Monthly Payments})) I adapted it to this: ({Monthly Interest Rate/12*{Loan Amount}})/(1-POWER((1+{Monthly Interest Rate}/12),-{Number of Monthly Payments))/10 It was close but not exact and the small difference becomes a much larger difference in another calculation. Is there a way to do this?

Wanting to check Date of birth against a static date, if true emoji if not blank. If(DOB<=12/31/2017, :star: ," ")

Hi Everyone, Is there a Formula to populate the Alphabet Automatically. I have created a Auto number column and created 26 records and I need to populate the Full Alphabet Automatically. Is this possible or not? Thank you

I am trying to create a field that will show a flag when one of two conditions are met around a Platform Due Date: Delivery Date is after the Platform Due Date Submitted Date is within 56 days (or 8 weeks) of Platform Due Date I also don’t want it to show anything if there’s no Platform Due Date (field is blank), as this field/column doesn’t apply for all records. I’m not getting any errors, but it’s also not showing the flag for a record that was submitted 3/29/22 but was due 3/7/22. In this case, the submitted date is after the due date, but there will be future cases where the submitted date is before the due date, but still less than 8 weeks or 56 days before. Here’s what I’ve got: IF(AND({Delivery Date} > {Platform Due Date}, OR(DATETIME_DIFF({Submitted Date}, {Platform Due Date}, ‘days’ < 56)), {Platform Due Date}, “Field Is Not Empty”), “ :x: PAST DEADLINE”, “”)

Dear Airtable Community, First of all, thank you all for your knowledge and post shares, all of the comments have been incredibly useful to newbies like me and it has helped with my work greatly! I have a series of questions please: Right now, I have the following: ‘Product’ Table: containing details of product eg. Price, cost (linked to Sub-part and Calendar table). Example: Product A, Product B, Product C ‘Sub-part’ Table: containing various parts of each product with its specification eg. Picture, size, vendor details, quantity of each sub-part used in the Product (linked to Product and Calendar Tables). Example: A-1, A-2, A-3, B-1, B-2, C-1, C-2, C-3, C-4, etc. ‘Calendar’ Table: containing purchase order details eg. Quantity of ‘Product’ ordered, color, start date, end date, etc. (linked to Product and Sub-part Tables). Example: Order2022-01, Order2022-02 Currently, I have the lookup field of ‘quantity of each sub-part used in the Product’ in the ‘Product’ table, basically to let

Hi all, I am looking to identify if a team is onboarded. We define onboarded if at least one of the designated people on the team participated in a session. Take the example below (columns hidden to simplify the example). The first column (physician team name) is the name of the team. The team consists of three people including John Doe, William Smith, and Sarah Johnson. We can see that Dr. Doe onboarded and he is considered a “live” user. His two colleagues are not, but because he is live that means the entire team is live. The ultimate goal is to group by Live vs Not Live and then Team Name like the screenshot below. This doesn’t work though because Neuroscience Team shows in the live section because Dr. Doe is live, but then also shows in the not live section because the other two did not participate in training. How do I create a formula so the Neuroscience team only shows as live? We cannot filter out the other two individuals from the grouping. Does anyone have any clever ideas

Hello everyone, thanks in advance for any support. I would like to create a formula that pulls organization names and what state they are in and eliminate the space and have a dash between them. I.E. Akoutis CA would be Akoutis-CA. See attachment

Here is my formula IF({Dept} =Bath, “8.5”, IF({Dept} =Win, “10”, IF({Dept} =ODL, “0” ) ) ) My objective - if field “Dept” is Bath then 8.5, if it is Win then 10, if it is ODL then 0

Hi Airtable community! Hoping for some assistance with an If statement. We have a workback schedule for deliverables based on an event date Workback:Materials Due: WORKDAY(Date,-45)) Occasionally, we will need to adjust the deliverable dates if the date of the event is determined later than the typical schedule allows for. I created a “Manual Deadline” date field. Manual: Materials Due: DATE I would like to create an If statement Formula field for Materials Due that will populate it with the Workback: Materials Due date UNLESS the Manual: Materials Due field has a date in it, in which case it will display the manual date. Can someone please assist? My brain has trouble with if statements :winking_face:

I want to select the first and last values from an array. The array is the results of a “rollup” of certain values in another table. Depending on the value of the last element, I might need to select the second last element. How can I conveniently select values from an array? In Python and other languages I would be something like x[0], x[-1] (or x[-2])… Do I need to do “the painful job of FIND LEFT” etc. Or can it be done in a nicer way? Thanks!

Hello, I have created a form. The form is about a “person” and their parentage. On the form, it asks for a mother and father to be identified. Because it is a family tree type situation, it is for related people. I would like the selection from the base to already be in there or added with a form, like the one I have created. If a child’s name is put into the form, and the parents do not exist, I need a prompt to input the mother or father and their information will be entered into the database also. Kind of like, 'it doesn’t exist, so you’re going to create it at that time, to move forward on the form. Can someone help me with that formula? Thanks in advance.

Hi, I am looking to find the initial letters of a UK post code before the first number. UK Post codes start with 1 or 2 letters and are always numbers and then letters. E.g. ‘WA1 1AB’ or ‘SK2 3LP’ or ‘L1 1AA’ or ‘M2 3NK’ I am looking for a formula to return the initial letters before the first number. So for the above examples, I would expect ‘WA’, ‘SK’, ‘L’ and ‘M’ Any ideas?