Formulas

This is sending me nuts. I feel like there should be a way of doing this, but can’t get my head around it. I’ve got one table with Risk Assessments on, and another with Risk Treatments. I want to be able to start a record in the Risk Treatment table, and then use a link field to say which Risk it’s associated with. So far so good. Now I want a Risk Treatment field in the Risk Assessment table to show which risk treatments are associated with that risk. At the minute, whenever I add a risk treatment, I have to manually add the risk in the Risk Treatment table, and then go back and manually add it to the Risk Assessment table too. The problem I find is that the Risk Assessment table doesn’t link to Risk Treatment table until I manually create complete the link field. But the ref number field matches the linked Risk field on the Risk Treatment table. So that relationship has been established, but it appears to only be one way. See, I’m talking myself in circ

Hi everyone, I’m trying to solve a basic issue with Airtable formulas… I want to subtract the value of one cell from another. For example, in excel, it’s subtracting cell E2 from E3… In Airtable, I learned about the Record ID that I can get from the cell URL, however I don’t see examples of how to write a formula referencing the values of that cell. Can someone show me how to subtract the values of 2 cells, that are each specified by a Record ID?

Hi everyone, I have a fairly complex base tracking financials, burn rate, and runway. I have run into a snag trying to come up with a formula that will automatically update based on what month of the FY we are in (for burn rate). For example, I have a table specifically for burn rate. If I want to see our burn rate YTD, I have to manually update the formula with the current month (burn rate = (starting balance-ending balance/# of months)). Starting balance - ending balance is an easy part but currently I need to manually update the divisor (our FY is from Jan to Dec, so right now I am dividing it by 8.5). How can I get it to update automatically? Can anyone help?

I have a two separate Count columns and I’d like to add them together to find the total count. Is this possible?

Hello! I am trying to make a formula based on an IF statement. IF one cell = something, then perform this formula. For example, IF( {Frequency}, ‘Annual’,({Cost/12})) I would like to have multiple “IF” statements & formulas. Is this possible?

Hello guys ! So, i would like to create a formula that count days from a specific date. Something that would add 1 everyday. I hope im expressing my problem correctly :nerd_face: Thanks

Hey after some help on the best way to calculate a total from 2 different entries in a table. So I am trying to develop a scoring/judging system for a school project. One of the sections is they submit a deign and they have to submit 2 identical copies and we mark them as A and B and we check them both against a set of regulations. I currently have a table that has the team listed twice one entry is for their A copy and the other entry is for their B copy. There are over 30 regulations they need to comply with, I have a column that totals the points scored for A and B separately but I need a way of combining them. I can’t just add them or do an average or take the lowest score. The way we judge it is if they fail a regulation for both copies they only incur the penalty once. If they fail it with only one copy then its still just the penalty once. What can happen is copy A fails regulation 5 and copy B fails regulation 7 for example I need a way of compari

Hi everyone! I’m new to learning how to use the deeper aspects of Airtable and how to use linked records. The problem I’m having … I have a multi-question survey, and am trying to calculate the percentage that people answered a question “yes”. However, some people skipped the question, so I need to remove these from the % calculation. My goal is to determine what percentage of responses were “yes” from those who actually responded. I tried by making a lookup table for each question that would only show answers yes/no (so no blank answers), and then the formula COUNTALL ({my lookup field name}) to first tell me how many responded, but I am not getting the correct answer. Does anyone have recommendations for how to solve this? Thank you I

Hi Expert, i have a field which keep a product volume, for example , 20g, 30ml, 45piece, 150l, 8pack. i just want to extract the numbers, so the after formula field will be , 20,30,45,150,8. i try to think and search for a while but have no idea how to do it , any advise?? thanks

Hello, I am trying to use Last Modified Date for particular fields to evaluate whether those fields were updated today, and if so, concatenate those updated values, separated by commas, and output those into my formula field. The final formula result will be included in a daily email digest of changes to my database, intending to show the recipient which of the specific fields were updated. I first tested the OR function by creating a simpler formula just to make sure I had the right idea, and this does return “Updated today!” for the one record that I updated today, with “No update today” returned for records that don’t match (the ERROR values are due to many of my records not having Last Modified Times yet, as those fields are new). I initially had trouble getting the dates to match up properly before realizing that I needed to convert the Last Modified Time fields via DATESTR() to make sure that I was matching text strings only. My IF/OR formula, worki

Table 1 is a list of clients with attributes. The table is as follows: | Company Name | Contacted? | … | … | | Company A | :green_circle: | … | … | Table 2 is a list of interactions with clients The table is as follows: | Contact person | Company Name | … | … | | Jeffe Bezos | Company A | … | … | Company A in both tables are matched together (as in I select from the drop down list the Company Name in Table 2). How would I make the “Contacted?” column in Table 1 automatically add this green emoji, if Table 2 has a match of the Company?

I currently have this: DATETIME_FORMAT({Date of donation}, ‘M/D/YYYY’) & “—” & IF({On behalf of corporation?}, {Donor’s employer}, Donor) I have another field named “Member Donor”. If the Donor field is blank then I want it to pull the Member Donor field.

Hi All, Having trouble figuring out how to use IF, AND, concatenate for this situation: If {Field 1} then return “Mary”, and If {Field 2} then add “Bob” to the end of “Mary”, and then If {Field 3} then add “Jake” to the end of “Mary” and “Bob” It continues for all combinations of Field 1,2, or 3. This was one attempt: IF( {Field 1}, ‘Mary’ IF( AND( {Field1}, {Field 2} ), ‘Mary and Bob’ ) )

I’m building a credit card base where we store expiration dates. One formula will simply tell us that a card is set to expire (aka when status is “Expiring”) within the month. At this point I have an automation set to email client with expiring card message. Now I’m trying to set up a second automation triggered by a formula field to let me know what the card is now “Expired”. So far this formula works to tell me if the card is Expiring or Valid. IF({Expiration Date}<TODAY(),“Expiring”,“Valid”) However now I want to add an “Expired” status too. Yet when I do this entire formula it doesn’t work. IF({Expiration Date}<TODAY(),“Expiring”,“Valid”), (IF(DATETIME_DIFF(TODAY(),{Expiration Date},‘days’) > 30, ‘Expired’)) What am I doing incorrectly?

Hello community! I’m trying to implement a simple badge system within an volunteer program. Basically, when volunteers pass certain checkpoints I’d like to award them a badge e.g. ran workshop X >> badge 1 ten total workshops >> badge 2 The pivot table extension creates a visualization of what I want to do where… row = volunteer name and columns = count of each workshop But I want this to perform this calculation within the base itself (perhaps using arrays in a formula field?) with badges being awarded via once certain conditions are satisfied. If there’s anyone out there that thinks they might be able to help but don’t yet have enough information pls let me know and I would be happy to try and explain further. Thanks very much, Alex

Hi, I have a database of professionals for whom I want to create a formula to search for the appropriate therapist using MAKE There are categories where I want to search all searchable categories, and there are categories where I want to search one or another category. for example- I am interested in looking for a therapist “for children” in the area or “Tel Aviv” or Jerusalem. AND( SEARCH(“children”,{categorys}) OR( SEARCH(“Tel Aviv”,{categorys}) SEARCH(“Jerusalem”,{categorys}) ) ) But apparently the formula is incorrect. Would appreciate help

The Count field lets you count the number of linked records. For example, take the screenshot below Line 8, I want the count field to count the number of linked records in Team 1-4 and then add them up. Is there a way to do this? Seems like this should be easy but may have reached the limit of Airtable. This is essential to the business use case I want to get Airtable for my organisation. Any work arounds are appreicated too.!

Hello all, Looking for a formula that takes input from another field to determine which item in the lookup to return. For example; LOOKUP FIELD | OPTION FIELD | RETURN FIELD Apple, Orange | 2 | Orange Apple, Orange | 1 | Apple Thanks all

Hi all, I have three columns: Member Number, Referrer, and Referrals. I am trying to count how many records contain the Member Number in Referrer, for the Referrals field. In other words, if I have 100 member records, and 5 records have “24” in the Referrer field, I would want the Referrals field in Member Number 24’s record to say 5 (the amount of times they reffered members). Does this make sense? Any ideas on how to do this? Thanks so much! Adam

Hey here, I’m tring to find the second to the max value of a array, but i can’t find any functions like Large in Excel. Is there any way to implement this function? Thanks for your help

Hope you can help. I have a table to record customers and products purchased. Each product comes with 6 months warranty. In the table I have a date of purchase column. I need another column that displays how many days of their 6 month warranty are left since the date of purchase. My question is: is there a formula that can do this? Many thanks in advance.

I have a formula that is taking a Start Date Field with value 9/10/2022 2:30pm GMT I am trying to get the time of the event in 24 hour format using DATETIME_FORMAT({Start Date},“HH:MM”) The result is coming up 21 minutes short and giving me 14:09 vs. 14:30. Makes no sense.

I’m trying to create a forecast model where we start, for example at $1000 in period 1, which grows at $250 every period. The growth is cumulative, so I’m unsure how to create this.

Hi team again, I need a quick hand, block with this atm In a table I have the following to fields File - Attachment : It could be an image, gif, video or audio Type - Multiple Select: Details de type of the image based on manually set options (image, gif, video or audio) Any ideas on how to set the Type based on File extension? If you’re wondering, the Type field will be used in a user facing filter to search by file types.

I know how to test for the presence or absence of a value in another field, e.g. IF( MiddleName != BLANK().... But it does not seem to work with a field that should return a number result, when the parameter being fed to the formula is empty. Here’s what I think is the standard Airtable formula for an Age calculation: DATETIME_DIFF(TODAY(),DOB,'years') Works great so long as there is a date in the DOB field. If DOB is empty, on the other hand, this formula returns “NaN” (“Not a Number”). That’s ugly. So I tried IF(DOB!=BLANK(),DATETIME_DIFF(TODAY(),DOB,'years'),BLANK()) But get the same result. Apparently BLANK() isn’t a number value. This wouldn’t be a problem in FileMaker, but if it were, I could coerce the (empty) result to a (non) value of type Number. In FileMaker these are both valid expressions: GetAsDate("") GetAsNumber("") Bu