Formulas

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.

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!

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

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!

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?

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

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.

DATETIME_DIFF([date1], [date2], ‘units’) I saw this formula but how do I modify it to include my fields and a result in years for the units?

I have a field in my table that is a linked record from another table. In this field are various records all consisting of just numbers, I would like to know if it is possible for me to add up these records and output them to one field in an hour format. To clear up, this is a base in which tutors submit a form on a student they just tutored. I need the tutoring hours to add up for me to know overall how many hours of tutoring they have received.

Hello Everyone, I am wondering if it’s possible to set up a field to show me the exact number of people present in my base on a certain day. Each record in the main table has an arrival date, departure date, group size. I have a second table that I want to be able to display the number of people present each day of the week. The system I have in place right now assigns an arrival week number and year with this formula WEEKNUM({Arrival Date},‘Monday’)&’ - '&YEAR({Arrival Date},‘Monday’) then I simply link the table and do a rollup of the Group size and display the number of people present each week. The issue with this is that if they arrive on a Sunday it assigns them the previous week number and rolls them up into the previous week. The other issue is if they only stay for 3 days it still displays them for the whole week. I would love to come up with a way to display the exact number of people present each day rather then week. Any thoughts or advice is much appreciated.

I want to convert the long text field to HTML coded field in airtable using the formula like I did in Google sheet whose formula is like this - Can anyone help?

Hi! I have a form and one of the fields is a formula, in the message that is displayed when completing the form I want to show this formula, is it possible?

I had a client that wanted 4 field field two fields should be able to subtract each other automatically The result of the two fields should be recorded in another field The recorded field should be recorded in another field but it should be a record above each other

Hi everyone, first off, my skills are a bit basic, so I hope you have patience with me… I have a text field in which multiple numbers are being output by an automation and separated with a comma (it displays the file size of attachments.) My goal is now to get the sum of all of these numbers in a formula field (I want to display the total filesize of all numbers combined). I have tried using VALUE(Internal Notes) but in that case the numbers are just mashed together and not being calculated at all. So instead of “100200” I want this to display “300”. Can anyone help me out on what to do? Or should this be resolved using scripts or something else? Any help would be much appreciated.

Hi everyone, I did some searching and did not find an answer. I am searching a text field to see if it contains one of a set of words eg. Search for design, designer, product designer, experience designer, UX if found - set result to “Design” I have similar sets of words I want to search for and mark if a result is found How do I go about doing that? Cheers, Subir

I have to following situation : How to create a formula that will return “True” or “1” if at least one record appears in both fields ?

Hi! We rely extensively on AT for updating “downstream” tools. We previously had a complex workflow that involved checking off a box when the update was made and then using a series of formulas to say “if the field was modified after that checkbox was made, the downstream tool needs an update.” Because we have so much data in AT, this was dozens of formulas. I’d like to use the “date modified” field instead, but am not sure how to or if I can store the date modified information or if it will only show the most recent one. In other words, is it a use it or lose it? If the field is modified 9/19 and then again 9/20, is there a way to see both dates by creating a formula? Thanks!

I have 3 columns in my table that contain an amount range e.g. 3500;4800 What I want to do is split that string out into Budget From & Budget To fields, like this: I’ve managed to do the actual splitting how I want using this formula for the Budget From: IF( FIND(";", {Budget range PER PERSON $}), LEFT({Budget range PER PERSON $}, FIND(";", {Budget range PER PERSON $})-1 ), {Budget range PER PERSON $} ) & IF( FIND(";", {Budget range PER PERSON £}), LEFT({Budget range PER PERSON £}, FIND(";", {Budget range PER PERSON £})-1 ), {Budget range PER PERSON £} ) & IF( FIND(";", {Budget range PER PERSON €}), LEFT({Budget range PER PERSON €}, FIND(";", {Budget range PER PERSON €})-1 ), {Budget range PER PERSON €} ) But what I want to be able to do is append the correct currency symbol, which I can’t work out how to do, so something like this: IF( FIND(";", {Budget range PER PERSON $}), LEFT({Budget range PER PERSON $}, FIND(";", {Budget range

Hi there - I need help building a formula that tracks if tasks have been completed early, on time, or late. I have a column where I mark a task complete, so it will have to start with If status = complete, etc. I then have a column each for “Start Date” and “End Date” (projected end date). Then a separate column for “Completion Date” (actual end date). I cannot for the life of me get all the parentheses right, this is what I have, but it’s not functioning correctly: IF( {Status}=“Complete” IF( IS_BEFORE({completion date}, {end date}), “Done Early”, IF( IS_SAME({completion date}, {end date}), “Done On Time”, IF( IS_AFTER({COMPLETION DATE}, {END DATE}), “Done Late” ) ) ) ) Any help would be appreciated! (Simplified example attached)

Hi there! I’m looking to replace text a field with a figure from another field. I can get this to work if I reference the code via another field but I can’t figure out how to insert it via a formula (so that I don’t have to fill a field with this dummy data every time). Not too familiar with markdown/the symbology etc sorry!

I’m back with a puzzle it wouldn’t have occurred to me to even attempt if I hadn’t seen some miraculous-seeming formulas early on in my Airtable forays. I sell books that I inherited from my dad. In my Books table, there’s an Author field that sometimes lists names first then last (Mark Twain) and sometimes lists names last, first (Twain, Mark). There’s no consistency. The thing is that there are different circumstances that call for different formatting. So my modest dream for this situation is to have two additional fields, both formula fields, obviously: First Last Last, First This will allow me to rollup one way for online marketplace inventory submissions (first last) and another way for professional association catalogs (last, first). I’m guessing making note of the comma will be an integral part of these formulas. Am I wrong to think this is a puzzle that can be solved on Airtable? TIA for your puzzling skills!

Hello- Any idea on how to remove the - When the Campaign Brand is field is empty so it doesn’t show: -CAMPAIGN NAME:CAMPAIGN TYPE And only shows: CAMPAIGN NAME:CAMPAIGN TYPE When the Campaign Brand is blank IF( {CAMPAIGN NAME}, CONCATENATE( {CAMPAIGN BRAND}, IF( {CAMPAIGN NAME}, " - " & {CAMPAIGN NAME}), IF( {CAMPAIGN TYPE}, ": " & {CAMPAIGN TYPE}), IF( {CAMPAIGN THEME}, " - " & {CAMPAIGN THEME}), IF( {EVENT NAME}, " - " & {EVENT NAME}) ), ‘EVENT’ & ’ | ’ &{EVENT NAME} )

Hi! Im trying to create a formula from one column where all of our members manually inputted their location (NYC, LA, SF) and input it to another column where I have them as multi select. So ideally any record that contains New York City will fall under NYC, Los Angelas will fall under LA. Has anyone done something like this before? TIA!

Hey all, I’m attempting to create a formula that will automatically create SKUs from several different columns, but I’m running into a few issues. Below is the formula I have so far: SWITCH( {Brand Code}, "Kala","KA", "UBASS","UB", "Makala","MK", "Ukadelic","UK", "Kala Percussion","KP", "Kala Elite USA","ELT" )&"-"& SWITCH( {Product Type}, "Acoustic-Electric","AE-", "Guitar","GTR-" ) &SWITCH( {Type Code}, "Solid Top (2)","2", "All Solid (3)","3", "Solid Body (6)","6" ) &SWITCH( {Top Wood Code}, "Cedar","C", "Spruce","S", "Striped Ebony","SE", "Koa","K", "Acacia","A", "Mahogany","M" ) &SWITCH( {Back & Sides}, "Mahogany","M", "Rosewood","R" ) &SWITCH( {Size Code}, "Tenor","-T" )Preformatted text I want the {Back & Sides} portion of the formula to only show up if it doesn’t match with the {Top Wood Code}. For example in row 3 I have a ukulele that has its top, back, and side wood listed as Mahogany, but my current formu

Hi! I am working on a content management dashboard. I have a single choice field with multiple types of assets, and I have an expiration date field with expiration dates for each asset. I want to create a formula field named Needs Review, that would return different values. For example: If asset type is blog or newsletter, then Needs Review field should show Yes when expiration date is within 60 days. If expiration date is later than 60 days from today it should show No. If asset type is video or webinar, then Needs Review field should show Yes when expiration date is within 90 days. If expiration date is later than 90 days from today it should show No. And so on with a few other types of assets. I also want to add some emojis for the answers in the Needs Review field. I tried writing the formula using IF, AND and DATETIME_DIFF() but I can’t find a way to format it correctly. Do you have some advise on what’s the best way to write this formula? Thank you!