Formulas

I have a Learning Course table that tracks courses with either a single session or dual sessions via checkbox fields. I have links to fields in this table that identify whether an enrollee has completed the relevant sessions(s). From there I have a checkbox field that indicates whether, based on the attendance, whether an enroll has effectively completed the course. I tested a formula field that runs the checkbox field for the dual session completion, but I’m struggling to create a formula that accounts for the single session field. I initially thought it was an IF(OR(AND())) type of configuration but nothing seems to work. Here’s what I tried: Works: IF(AND({Session 000 Output}>0,{Session 001 Output}>0),{<TRIGGER> Completed Course}=1,{<TRIGGER> Completed Course}=0) Doesn’t Work: IF(AND({Single Session}=1,{Session 000 Output}>0),IF(AND({Dual Sessions}=1,{Session 000 Output}>0,{Session 001 Output}>0)),<{<TRIGGER> Completed Course}=1,<{<TRIGGER&

I have fields with extracted text from a contract and what I need to do is extract every vehicle registration number in the text field. The formula I have will only extract the first valid number. Thanks in advance!

Hi, I am trying to prefill a form using the following formula: CONCATENATE({Applications URL}, ‘?prefill_Which+role+are+you+applying+for?=’, {Name of opportunity}) The ‘Name of opportunity’ field contains more than one word, but the formula is only pulling the first word. Here is an example of one form’s URL: Airtable Apply to our roles today! - Airtable Fill out the "Apply to our roles today!" form quickly and securely on Airtable. When the ‘Name of Opportunity’ is actually ‘Logistics, Freight and Warehousing - DSV’. How do I ensure that the form populates with all the words? Thanks!

Hi Airtable Community, We are using the “enable rich text formatting” on a “long text” field, using bullet points to indicate points of attention or statuses. Is there any way to use this rich text and do the following: Filter out first / last 3 bullet points by using a formula? Filter out items under a bullet point matching a certain category, eg. text in bold - by using a formula? Screen shot attached where I’m looking if I can filter out basis eg. “Week 13” or filter on bold. Many thanks!

I am attempting to collect data from employees that tracks incident reports over time. This roughly works as follows: Form1: "Report New Incident" This form collects information about an incident such as location, date, classification, etc. and assigns it a new ID. ID | Date | Location A | 1/1/19 | Plant X B | 1/15/19 | Plant Z Form2: "Update Incident" This form asks the user for an incident ID and then some additional information is added to the original incident (new information or updates to old information). ID | Date | Status A | 1/3/19 | In progress A | 1/5/19 | Solved The idea is, the employee who is updating on an incident does not need to reenter information (such as location, classification), but should provide some updated information (update time, current status). Having two tables ensures they contain unique information with the exception of ID. Unfortunately, I can’t join together based on ID with some sort of JOIN query. Using lookups partially solves this, but then it g

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 circles now, I just can’t get my he

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 comparing them and finding if they fai

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, working as intended: IF( OR( {LM DS

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