Formulas

Hello everyone, first time in this forum but I’ve been using Airtable for a while. I have an ecommerce and when there is a new order I get this information in my “Purchased items” (cant change this). count: 1 productName: 102 productSlug: rollin-102-bleu rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantImage: variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable count: 1 productName: 102 productSlug: rollin-102-bleu rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantImage: variantName: 102 Gravure personnalisée (+20€): Sans gravure variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’} variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure I would like to extract some info for ecah product, for example the count, productSlug, pr

i will combine: LEN({Long Text}) + IF(LEN({Long Text})>280, “ :x: Over Limit”, “ :white_check_mark: Under Limit”) The result should be: example: 150 :white_check_mark: Under Limit

Hey, I have a scenario in Integromat that integrates with my Airtable CRM and it triggers when someone buys my event ticket. When they buy the ticket, they fill some info like CNPJ and the contact email in a form. Then the integromat scenario runs, it need to find in Airtable the CNPJ (rollup field), if it matches the CNPJ in the field, great! if not, it should find the contact email in the field Email Contato 1 (rollup field that join values from many contacts), if the email matches, great, this is the right record, but if not, it should find the contact email in the field Email Contato 2 (rollup field that join values from many contacts), if it is the right record, great, but if not, it should be empty. I made a formula in integromat but it is not working as expected. It is below, but since integromat formulas change a bit because we can change the data before it goes to airtable, i’m also sending pictures. share.getcloudapp.com – 13 Nov 19 share.getcloudapp.com

Is it possible to have a formula take into account whether or not a given field is hidden in the current view? For example, I have a Name (“Bob Smith”) field and a Role field (“Developer”). I want the formula to show the Name if it’s not hidden and just the Role if the Name is hidden. This allows me to share an anonymized version of the record on a publicly shared view, while using the Name field internally when managing data.

Hi there After searching here I have successfully created formulas to manipulate an email field and now I want to know if those formulas can be combined so I only need one extra field. I get email addresses like this … teacher1@plc.nsw.edu.au pupil2@students.acc.edu.au pupil3@student.ncc.vic.edu.au teacher@covenant.nsw.edu.au pupil5@chairo.vic.edu.au pupil6@student.ncc.vic.edu.au pupil7@students.acc.edu.au pupil8@students.acc.edu.au teacher@frsa.asn.au teacher@acc.edu.au and I want to end up with JUST the domain and with the “student” and “students” part stripped out (e.g. acc.edu.au, or frsa,asn.au I can end up with the domain by using this … RIGHT(email, SUM(LEN(email), -FIND("@", email))) And I can get rid of the “student” and “students” by using this … SUBSTITUTE( SUBSTITUTE( {email}, “students.”, “” ), “student.”, “” ) Is there anyway of joining these two so that I only have one field? I tried the “&” but I just end up with two domains. Many thanks! And thanks to those who po

My formula is ({Fee Before Disc} *Discount) - {Fee Before Disc} Despite it being a positive number subtracting the discounted amount, it ends up being a negative number. Example: 1800 -40% disc =1080 but it shows up as -1080? Why would this generate a negative number? How can I fix this?

Hi. Let’s say that every record has a field named: Cost. The sum of the cost of all records appear at the bottom. Can I use that sum in a formula? For example if I want to calculate the percentage of every record’s cost ( when 100% is the sum ) Thank you Ilias

Hi, we do weekly reporting through Airtable - but we would like to group the reports by the meeting date (every Monday). Typically a record is submitted on Friday (can also be any other day), and we would like to set the ‘Meeting date’ automatically to the next Monday of the submission date. Now we have to add it manually. Is there a way to use the Submission date option to fill in the next Monday in the ‘Meeting date’ column?

Hello! I have a formula which builds record names based on the values of other columns: {Name} & " (with " & {Secondary Name} & ") " & " - " & {Event} & "\n " & {Year} & " | " & {Room} Result: John (with Jim) - Conference 2020 | Green Room However if there’s nothing in “Secondary Name” it displays: John (with ) - Conference 2020 | Green Room How do I get it to remove the "(with ) " when there’s nothing to fill it with? The secondary names are linked to other records so unfortunately I can’t just add the brackets to the column entry instead of putting them in the formula. Thanks in advance!

Hello, I am trying to create a formula that automatically assigns a Team based on Column 1 which contains company names. I am running into trouble, it seems, due to the length of my formulas. I am wondering if anyone can assist. Below is the formula I created in Word, it works in Airtable when I shorten it to cover only half the companies. I have tested with several different orders to make sure it isn’t just the next company in the series that is causing an error. I have about 20 companies I am assigning 1 of 2 teams to. SWITCH({Distributor Name}, “Company Name”,“Team 1”, “Company Name”,“Team 1”, “Company Name”,“Team 1”, “Company Name”,“Team 1”, “Company Name”,“Team 1”, … “Company Name”,“Team 2”) I have also tried IF formulas such as the below, however no matter how I’ve formatted them they automatically cut it short and only follow the first 2 expressions. IF({Distributor Name}=“Company Name”,“Team 1”), IF({Distributor Name}=“Company Name”,“Team 1”), IF({Distributor Name}=“Company Na

Help! I have two fields, one contains a date, the other contains a frequency. Based on this I want to have a third field populated with the date of the next occurrence. the first IF statement works and correctly sets the date, the second is ignored and no error. It just does not populate the field at all. I am using the following: IF(Recurrence = “Weekly”,DATEADD({Invoice Due Date},7,‘Days’, IF(Recurrence = “Monthly”,DATEADD({Invoice Due Date},1,‘Month’))))

hey, whats the formula to subtract a week from a field with a date? thanks alot!

Question: New to Airtable. Looking for a way to use an if then statement to assign Collaborators or employees. Example: If we are installing a specific type of account, autofill the “Assign to” field. New account: Showroom - auto assign to: Natasha. Not sure if this is possible or if there is a workaround. Thanks!

I’m not sure if this can be done but here’s what I’m looking for: I have a base with two basic tables: Jobs and List The Job Table has the following fields Job Name, Job Number, Custom Calculated Field (CCF) 1 Formula (CCF 1 Formula), CCF 2 Formula…CCF 6 Formula, and a link to the List Table The List Table has the following fields List Name, Customer Inputed Field 1 (CIF 1), CIF 2…CIF 8, a link to the Job Table (allowing only a single link), then there are Lookup Fields for CCF 1 Formula…CCF 6 Formula. Everything up to that point is pretty basic, but what I’m trying to do is create 6 fields CCF 1…CCF 6 that will actually perform the operations/functions shown in CCF 1 Formula…CCF 6 Formula. Here’s a link to the base. Link to Base I can get the CCF 1…CCF 6 fields to repeat the text of the formulas, but I actually want them to perform the operations within the text…can this be done? Thanks in advance for any guidance or assistance you can provide! Tom

I want to reference two fields in my IF but I just can’t seem to make it work… IF({Due date}>TODAY(),"+",“OK”), IF(Status=“Complete”, “Complete”, “OK”))

I need a simple formula for working out a suburb and it puts out a selected output. eg: if the suburb gorokan or toukley is in the field, the formula puts in KPM1. IF the suburb is hamlyn terrace it puts in KPM2 etc etc This will apply to 5 different outputs ( KPM1 KPM2 KPM 3 KPM4 LM1) based on the suburb. IF({Suburb}=“gorokan”, “KPM1”) This formula works great for the single suburb but I cant get it to output KPM1 if I put multiple suburbs in there I know it will be nested IF formulas but I just cant get this first one to work as expected. It will be simple im sure! lol

Hi! I’ve recently begun using Airtable for record keeping and project management. I have been able to do all kinds of fantastic things so far and am really loving Airtable. Most questions I’ve been able to find a previous discussion about and solution for, however, I’m stuck on this problem: I have two tables, [Tasks] and [TaskLog]. [Tasks] has a {Status} column, as well as a column for {StatusUpdated}, which lists every date that I’ve updated the status for a particular task. Example: If I updated a task from Pending to In Progress to Complete on three different days, it would include those three dates. On [TaskLog], I link to the individual tasks I worked on that day. This works well but has to be done manually. What I would like to do is for [TaskLog] to be able to auto-link to [Tasks] if any of the dates in {Tasks::StatusUpdated} matches the {Date} in [TaskLog]. Does that make sense? I know that part of the reason I haven’t been able to find the answer from searching may be that I’

Hi, Not sure where to start, would like to know if it’s possible to receive notification for users on the phone once a profile has been updated. Can Airtable write scripts? I’m using Thunkable mobile app.

Is there any way you can access a field in a different record? As an example, I collect records to manage fuel consumption. For each file of the tank, I make a note of the date, the car mileage, the total cost of filling the tank, and the price per litre. I need to calculate the difference between the current mileage and the previous mileage – is there a way this can be done? This is simple in both a spreadsheet or using a SQL Query against a normal database, but I don’t seem to be able to find a way using AirTable. Any help appreciated?

I’ve tried to follow along with the other questions on this topic, but I keep getting “Invalid formula”. I have three fields (that all link to another table). “Ayes”, “Nayes”, and “Absent.” If “Adams” appears in the “Ayes” column, the target field should be “voted for”. If “Adams” appears in the “Nayes” column, the target field should be “voted against.” And if “Adams” appears in the “Absent” column, the target field should be “did not vote on”. Here’s what I have: IF(Find(“Adams”, {Ayes}, “voted for”), Find(“Adams”, {Nayes}, “voted against”), Find(“Adams”, {Absent}, “did not vote on”) Thoughts?

Hello, I am getting below data, but I need to filter data where ‘Start Date’ is between 01-Jan-2019 till 31-Dec-2019. How can this be achieved? { "id": "recxxxxxxxxx",* "fields": {* "Priority": "N",* "Month": "2019-03",* "Week of the Year": "10th",* "Start Date": "04-Mar-19",* },* "createdTime": "2019-01-11T22:22:09.000Z"* }*

I have a sheet where interaction type field is a Single Select list Date of Interaction field is a date The following formula returns a date in the format 2019-11-05T00:00:00.000Z IF({Interaction Type} = “Meeting”,{Date of Interaction}," ") How can I get the date just to show as 05/11/2019

Goal: Calculate Character count limits for ppc ad sheet. Character Count field is the number of words in the copy for that ad Character Limit is the number of words allowed in the ad IF({Character Count} < {Character Limit}, “ :white_check_mark: PASS”) IF({Character Count} = {Character Limit}, “ :white_check_mark: PASS”) IF({Character Count} > {Character Limit}, “ :x: FAIL”) Please help! Thanks in advance

Hi everyone! I am having a hard time with some formulas due type of cells and blank cells. Hope you can help me. This is the Nested IF I wrote: IF( Cuota=PagoEnero, “Pagado”,IF(PagoEnero=BLANK(),“No” , IF(PagoEnero=0,Cuota-PagoEnero ,IF(Cuota-PagoEnero>0, Cuota-PagoEnero)))) The formula returns the same result if the cell is blank or is =0. I think this is because the statement will always be true in the second one but i dont know how to fix it My second issue is that, I do the same formula multiple times (Enero, Febrero…) and i need to summarize only the results that returns a number (not text) and make a sum For example: Enero=0, Febrero=No, Marzo= , … Returns: Enero CONCATENATE(IF(Enero=BLANK(), “”, IF(Enero=0, “Enero”, IF(Enero>0, “Pagado”))),IF(Febrero>0,"",“Febrero”)…and so on) I think due the column “Enero” is a formula does not recognize the result as a number, this is not working. I have tried to change text to number and it fixed but brings other problems. Could anyo

I have two fields (both are rollups - sum’s) that results in numbers: Projection Views Actual Views I have set up another field with this formula: SUM({Projected Views}-{Actual Channel}) But the formula returns a NaN. Can someone help me understand what the problem is here and how to resolve. The rollup fields draw on two separate tables, if that makes any difference.