Formulas

Hey everyone! Yes, I know this has been answered before and I’ve read several posts. I’m not an airtable expert and I do want to get rid of the #ERROR! result when {Monthly Rental Fee Renewal Date} field is blank: IF(IS_BEFORE({DeStaging Date},{Monthly Rental Fee Renewal Date}),“No renewal charge”,CONCATENATE(DATETIME_FORMAT({Monthly Rental Fee Renewal Date},‘MM-DD-YY’)," | “,{Client Name},” | ",“Rental Fee Renewal Date”))

Hello, I need to validate zip codes in applications against a list of zip codes within certain city limits. For example, if an applicant submits a zip code and it’s not listed in my other table containing a list of eligible zip codes, there would be a column flagging me something like need verification or ineligible. Is there a formula or some way to do this? Thank you!

Example I have a field with the following data: {“blog”:“abc dot com”, “twitter”:“xyz dot com”, “facebook”:“pqr dot com”} I want to use the “keys”, viz, blog, twitter and facebook to extract abc dot com, xyz dot com and pqr dot com respectively. So if I have the key “blog” in a formula, I want to store the corresponding data ie “abc dot com”. Any ideas how to do this?

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 O

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

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 toget

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