Formulas

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?

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).

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?

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?

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

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.

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

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.

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

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

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