Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
J
John_Krueger1Participating Frequently
asked in Formulas
Removing the tail end from URLs

Hey AT Community! Is there a way to remove the tail ends from formulas in bulk? Example- I have 3 urls in 3 different rows: https://google.com/index?test https://yahoo.com/example?test https://apple.com/ending?test Using a formula (or something else) I’d like to get rid of the tails so that I am left with: https://google.com/ https://yahoo.com/ https://apple.com/ I feel like this should be possible? Appreciate any pointers!

2410
B
Z
ZakNew Participant
posted in Formulas
Formula help with multi-product commission dependencies

Hello, I wonder if someone could perhaps help me with a formula I’m stuck on. I have a single select field with a number of products: Product 1; Product 2; Product 3; Product 4 etc. I also have a Premium (currency) field and a Commission (percentage) field. I’m looking at adding a Commission (currency) field with a formula that shows me the amount. However, some products are subject to tax at 12%, whereas others are not, and i want to get the commission amount after the tax has been deducted. How can I create this? e.g. IF Product = product 1, product 2, product 3, then (Premium * 0.88) * Commission, IF Product = Product 4, product 5, product 6, then Premium * Commission Thanks in advance

44
Z
M
Marcel_TanbicNew Participant
posted in Formulas
Extract a single phone number

Hey folks, need some help with regex… I have the following information and I just need the first telephone number. Is there a way to extract the information from Tel to Tel-Nr? Only need the first phone number of each record

108
Justin_Barrett
J
Jim_GallParticipating Frequently
posted in Formulas
Calculating Occurrences of records

Hi there Is there a way I can tally the occurrences of a record within a column? For example, if I have a column called ‘colours’ and it has five reds, three blues and two yellows, how can I count these and disp[lay within a record (probably on another tab called ‘Calculations’)? Thank you in advance.

709
J
J
JuanDSantosNew Participant
posted in Formulas
Color coding conditions

Hello, I am needing to color code based on two conditions. On this base, I have various fields, one of them being a multiple record selection for tutoring subjects required. From here you can select up to 5 subjects. Now I have various other fields, these have the hours that they have been tutored per subject, some can say 0 since they do not require any tutoring in those subjects. Now for the conditioned color coding, depending on the subjects they need tutoring in, I need them to meet 30 hours per subject. So if they meet 30 hours in the subject they require tutoring in I want them to turn green. Here is the tricky part, they can meet 30 hours in one subject that they require but, only 15 in the other, that would yield to the record not being marked as green since they only met 30 hours for one but not the other. Example: Student A requires tutoring in 2 subjects, she met 33 hours in one of the subjects but only 12 on the other subject this record would not be marked green. Student B

223
TheTimeSavingCo
N
NicoMGNew Participant
posted in Formulas
Remove commas without data

Hello, I’m trying to manipulate a multi-select field in a formula and I’m having a problem. I have for example: Data1,Data2,Data3,Data4, I would like to be able to transform this data to have: Data1, Data2, Data3, Data4 I tried with SUBSTITUTE, REGEX… Can you help me ? Thanks !

324
N
V
Viet_NguyenNew Participant
asked in Formulas
How to remove [1],[2] from list of data

I’d like a formula to remove the [1][2] instances from the column data

100
V
J
Jesse_WatersParticipating Frequently
posted in Formulas
Formula output is showing parenthesis when none exist

Formula is pulling linked input to create a unique name and for some reason, the formula is putting parenthesis around some of the linked names and not on other linked names. When I double check the linked table, the names are clean. Anyone know why this is happening and how I can get the parenthesis to not show up? See line 40 - 43 on first screen shot.

182
kuovonne
K
Kyle_ShafferNew Participant
posted in Formulas
Create a new view filtered with unique values of specific column

I have a table that are songs that we have worked, the artist, and other information. There are many duplicates as it is possible we work the same song and artist multilpe times. I want to create a view to show unique values only in the artist column. The way we have this set up with the API, Related records does not make sense in this instance. I know I can group by artist, but it is just not clean or prety. How can I create a view that just has unique values of a specific column?

1031
TheTimeSavingCo
Tiaan_Terblanch
Tiaan_TerblanchInspiring
posted in Formulas
Formula to indicate 21day prior to renewal

We have the following columns in our database: Date Joined - regular date Last renewed - regular date Next Renewal - formula (IF({Last Renewed}=BLANK(),BLANK(), DATEADD({Last Renewed},12,“months”)) However, I also need to include my month-to-month memberships. How do I add it to this formula? Status of Renewal - The formula should help us calculate the Status of Renewal field as follows: IT SHOULD SHOW: “Active ” if the Renewal Date is in the future with 22 days and longer from TODAY. IT SHOULD SHOW “Close To Renewal ” if the Renewal Date is 21 days from TODAY (the current date). IT SHOULD SHOW: “Needs Renewal ” if the Renewal Date is 22 days passed and longer from TODAY (current date).

106
Justin_Barrett
D
Dan_EmmerieParticipating Frequently
posted in Formulas
Airtable Lookup to Single Select

Here’s the situation. I’ve automated data coming in from a different platform that has a Single Select List. When the data comes in to Table 1, it’s an ID jargon that I need to have converted to a List Name in another Table 2 where ID to List Name is defined. I then have a lookup field in Table 1 that gets Table 2’s list name, this way it associate with the rest of the data imported, but I noticed it won’t allow me to do a lookup field as a single select so that I can use it as a kanban. Is there a workaround for this?

252
D
C
Caesar_IvarssonNew Participant
asked in Formulas
ARRAYUNIQUE to number

I use ARRAYUNIQUE in a Rollup to show the latest number for a field in another table. That field is a percentage field (I could make it numeric too, no problem) The percentage/number in the original field is possible to format neatly with 2 decmals. However the rollup ARRAYUNIQE (values) is showing all the decimals, which looks crap to be honest. But since it’s not numbers anymore, is there a way to make it look prettier? Right now my solution (which doesn’t work) was to make a new formula field and do this: "IF( {roll copy} = 0, BLANK(), "% "& ({roll copy} “show only two decimals”) ) Do you get what I’m looking for?

12
C
A
Alex_MaInspiring
asked in Formulas
Count records in another table

Hello, Looking for a method to count records in another table. In this case, I have a table with part numbers and their associated product. In another table, I want to count for each product, how many parts are in it by counting the part numbers from the first table. TYIA

293
V
Nicole_Merwin
Nicole_MerwinInspiring
posted in Formulas
Sorting Formula Output

I have a formula that brings in data from multiple fields, such as the attached. There are around 40 of these formulas, I would like to be able to sort the output by the date shown (highlighted portion of the formula). Is this possible without using a factorial approach as that is not scalable in reality. Looking for more of a ‘sort’ wrapping of the formula if at all possible.

581
N
G
Gdz91New Participant
posted in Formulas
Function IF - AND - NOT?

Hello, I am new to using Airtable. I have a question about a formula I want to implement. I would like to be able to do an IF function to check if a phone number is already assigned to the customer. If the phone number is assigned to customer 1, I would like to check if it is also assigned to customer 2, if so, a final check of the phone number for customer 3. If the first condition is not validated, I would like to indicate “Customer1” in the result column, if the phone is assigned for “Customer1” but not for customer2, I would like the result column to indicate “Customer2” etc… If the number is not in the list, and it is not assigned to any customer, I would like the result to be “Not assigned”. The purpose of this formula is to check to which customer a phone number has already been assigned (knowing that it can be assigned to several customers). This formula would be used during an API connection to check for a defined phone number and assign it to a customer who does not already h

62
N
T
Trev_CheeNew Participant
posted in Formulas
Help with a basic "Sum" calculation error

There seems to be an error in the SUM calculation of a list of my entries ( on multiple tabs also!). In the grid view, I click the “field name” to see the SUM of the added numbers but the total is always incorrect. I used a calculator to add the numbers up manuall and I get a differnet total. It must be a simple error, but I have not been able to find the solution. Please help because I’m trying to use Airtable for my business and these incorrect totals are causing a serious problem for my balance sheet.

94
B
Tiaan_Terblanch
Tiaan_TerblanchInspiring
posted in Formulas
Support required with if formula

Hi all I would really appreciate some support to come up with the correct formula for the columns in the screenshot below. I want the price in the standard pricing column to auto-populate when a selection has been made in the payment options column. For example, suppose I select 10 instalments in the payment options column. In that case, I want the standard pricing column to automatically associate $150 with this option and then automatically add the $150 to the standard pricing column. The same, for example, with the option paid in full, should automatically add $1500 in the standard pricing column. Your support with this is highly appreciated. Thank you

123
Kamille_Parks11
S
Scott_CalleryKnown Participant
asked in Formulas
Nested IF, AND/OR

Hi, I’m struggling with some nested IF, AND and OR formulas. I’m trying to find if a value is within a range of numbers, and is a multiple of 5. For the range, I have the formula: IF(Value A >= 90,"Good", IF(Value A <= 150, "Bad")) <<This works To check if Value A is a multiple of 5, I have the formula: IF(OR(RIGHT("" & Value A,1) =0, RIGHT("" & Value A,1)=5),"Good", "Bad") << this works The part I am struggling with is combining them together so that: If Value A is between 90-150 and is a multiple of 5 then “Good”, otherwise “Bad” Any ideas? Thanks, Scott

524
S
E
Educ-UpNew Participant
asked in Formulas
Bug on prepulate form field

Hello, I have a problem with the pre-entry of the “Modifier le dossier d’un parent” form and more specifically with the “Parent du dossier” field in the table “Temp. Transmission pièces justificatives”. I am following the syntax of your blog post, but nothing works. What’s weird is that when I duplicate the field and try to pre-populate the duplicated field, it works without a hitch. Could you please help me? Thank you.

122
E
D
Damian_EvansParticipating Frequently
posted in Formulas
Combining Concatenate and Regex

Hi, I have this formula: Centre & "-" & {Musician Educators} & "-" & Time Which is giving me Primary Record IDs like this: Dublin-Johnny Teacher-12:00-12:30 which is great, but what I really want is: Du-JT-12:00-12:30 So regex’ing the first two letters of the first field, and the first letters of the two names of the second (I can ensure all teachers are listed as simple first and last names.). It would make my Primary Fields a lot shorter, clearer, and easier to find when entering in data. Is that easily done? Thanks!

648
D
Karlstens
KarlstensBrainy
posted in Formulas
Is there a better method to concatenate multiple Rollup Fields and discard blanks?

Hello, I’ve been trying out a couple of ideas for table linking via Linked Fields and Automation. One particular idea lead me to creating multiple Rollup Fields, that then all need to be returned into a linked field via an Automation. There’s a few ways to do this. One method is scripting the entire solution within an Automation - but I’m exploring non-script methods - but my troubles are with the array construction that the Automation then copies. Here’s one example table - please excuse my spaceship terminology :grinning_face_with_sweat: :rocket: At the moment, I’m using the below formula, but feel that it’s just a bit too much? Is there not a simpler way to write this? CONCATENATE( IF({Chassis (Frigate Class)} ,{Chassis (Frigate Class)} & ", " ,""), IF({Chassis (Destroyer Class)} ,{Chassis (Destroyer Class)} & ", " ,""), IF({Chassis (Capital Class)} ,{Chassis (Capital Class)} ,"") ) An Array function that could be written as this would be amazing :sparkles

945
kuovonne
R
RashaKnown Participant
posted in Formulas
Convesion metrics based on single-select field (mult records)

I have an Interactions and an Investors table. There are vaious interaction types as investors move from cold > warm > diligence, etc. These interaction types are managed via a single-select and piped in via various automations. Two metrics I’m trying to highlight using a number element in the Interface Designer: Simplistically: Cold → Pitch conversions Pitch → Diligence conversions So far, I tried grouping interactions by investor and leveraing rollups in the linked Investors table. Don’t think that’s the way. In my mind this looks like No status (0), Cold (1), Pitch (2), Diligence (3). For any investor, there could be 2 colds, 2 pitches, etc. It’s not always 1 per intercation type. How might I go about deiving % of total interactions that converted into these two stages? NB: At this point in the client’s project, major restructuring of the base is not an option. Thanks friends!

76
R
J
Jodi_HarrisNew Participant
posted in Formulas
Where xfield meets this criteria, input this thing

Hi! I know this is very close to an IF formula, but my problem is that it’s a one to many, not a one to one relationship. I’m trying to avoid having to manually update who a manager supports every time that changes. So, in the screenshot below, I want a formula that can look to see where someone is listed as a direct support, and add the person who they support’s name into the “people they support” field. Is this possible?

63
J
L
LuyzNew Participant
posted in Formulas
Freeze Rollup Value

Hello So I basically have a rollup tracking the amount of products ive sold in total, i was wondering if theres a way i can freeze the value in the rollup so when i delete a record that contains an amount of products sold it doesnt affect the value in the rollup. so say i sold 5 items yesterday and 5 items today, the rollup would give me a value of 10, but if i delete the record that says ive sold 5 items yesterday i want the rollup to still give me the value of 10 and not just 5

11
V
S
Sean_MahoneyNew Participant
posted in Formulas
Formula for different pay rate on weekends only

I am trying to write a formula where if tasks occur on a weekend the normal pay rate is double. Currently it is as such HOURS*60 which would calculate the hours/normal rate of 60. however if the work is done on the weekend the pay rate would be 120. Airtable Grid view - Airtable Explore the "Grid" view on Airtable.

41
V

Badge winners

Show all badges
Terms & Conditions