Formulas

Base is registration and records system for small private school that’s been operating for 60 years. The two tables I’m working on now are PARENTS and CHILDREN. In another thread here, I was trying to figure out how to copy addresses from linked records, but I think I’ve solved that question by using a formula field that calculates an address for each parent or child based on what records its linked to. In other words, if parent1 has an address, and parent1’s children and spouse do not have addresses, those records will all pull an address from Parent1. Minimizes duplicate entry and seems to work okay. The question My question is, in the PARENTS table, how can I identify marital pairs? I’d like to group them. One thing I have tried (that doesn’t work well)… For some reason (mystery to me), grouping by the Address formula field does not seem to work. Say Mary and John are married and reside together. Mary’s record, which was created first, stores the address at which she and her husba

Having to type formula strings when using something like Node.js to interact with the API is super difficult and tedious, especially with more complex queries. Since the formulas are strings the editor can’t provide any syntax correction tips about missing parenthesis or valid/invalid uses of various functions like FIND, AND, etc. Is there any easier way to create these queries? I put an example query I’m trying to create below which I’m completely failing to get working, although I have no idea why because there’s zero syntax feedback. I made it by joining an array of indented strings to provide myself with a little debugging sanity but that only helps so much. const filterConditions = [ "AND(", "OR(", `AND(FIND('${userId}', {uploader}), FIND('${teammateId}', {recipient}))`, ", ", `AND(FIND('${teammateId}', {uploader}), FIND('${userId}', {recipient}))`, "), ", "IS_AFTER({createdAt}, DATETIME_DIFF(TODAY(), -14, 'days'))", ")", ].joi

Hi everyone, I have 3 different tables: prospects (looking for a place) Landlords (renting a place) Matching table On the 1 and 2nd table, I have a column named “type of place” that is a single select option. People can then fill “House” or “flat” or “shop” for example. I would like on my 3rd table to be able to indicate in a column if there is a match between the answer of a prospect on table one column “type of place” and a landlord table 2 column “type of place”. I will then have a column : “Name of prospect” + a column “Name of landlord” + a column “Matching place” and the name of the matching criteria (eg: house). Someone knows how to do that? :slightly_smiling_face: Thanks a lot! Alix

Hi all I am trying to convert strings with numbers in them to just numbers. So the string is in that form R.N.A.(335,000) I just want the number The number ranges from 0 to 10,000,000 Thank you in advance

Hey clever people, I got following single line txt fields in my table: {Prefix}, {First Name}, {Last Name}, {Suffix}. I already use a formula to concenate this data: CONCATENATE({Prefix}," “,{First Name},” “,{Last Name},” ",{Suffix}) … this works fine. My problem/question, is it possible to make a formula that will put between {Last Name} and {Suffix} a comma if {Suffix} is not empty/null, instead of the space like above. If I replace the space with a comma it will obviously be everywhere, what I don´t want. For example: Prefix (Dr.), First Name (John), Last Name (Miller), Suffix (M.Sc.) - Formula Result: Dr. John Miller, MSc. Prefix (Dr.), First Name (Leroy), Last Name (Thompson), Suffix (null) - Formula Result: Dr. Leroy Thompson I hope you understand what I mean :grinning_face_with_big_eyes:

I am attempting to perform a task in which I’d typically use a Sumif formula. I’m trying to “sum” the extended price “if” the sales rep column equals the reps name. For example: Extended Price Sales Rep $400 Mike $250 Justin $150 Mike Result: Mike = $550 Justin = $250 Is there a formula / work around for this?

Hello! I have a form configured for my clients to fill out that asks in two cells for first and last names. In the table view is it possible either through a function or formula to have a cell that combines the names together to easily sort and identify my client list? Thank you.

In an Interactions table, I’m using a checkbox field to mark specific interactions 7-day follow-up. In a different table, I’d like to see if the most recent interaction for a contact has 7-day follow-up checked. MAX(values) works for dates but returns zero here. Thanks for the help!

Question: I need the ability to have an open text field show up called Text in my field IF the Item Description is Blank. So it would look like, CORPORATE REQUEST: ‘TEXT’. IF( {ITEM DESCRIPTION}=BLANK(),‘CORPORATE REQUEST’, CONCATENATE( {ITEM DESCRIPTION}, IF( {ASSET TYPE}, " - " & {ASSET TYPE}), IF( {COPY TYPE}, ": " & {COPY TYPE}), IF( {MEDIA TYPE}, ": " & {MEDIA TYPE}) ) ) Thanks!

I need to write a formula that uses a specific cell of data as a multiplier: IF({BoM Category}=“3.1-Infusion Ingredients”,({Formula %}*SPECIFIC CELL OF DATA),IF({BoM Category}=“4-Product Ingredients”,{Formula %},0)) Do I need to create an automation to do this? If so, what are the steps?

Hi there! I am a little stumped on creating these 2 formulas with a few different layers. All of my Commuity requests are about Iff statements- they hurt my brain, but I love them! If an If statement is not the best approach, please let me know :slightly_smiling_face: Formula 1 Display the word “Block” if a status is blocked, and if it does not say “Block” it shows the date/time/type of the webinar. What am I doing wrong here? If({Status})='Block','Block', DATETIME_FORMAT(SET_TIMEZONE({Start Date/Time}, 'America/Los_Angeles'),'MM/DD/YY h:mm A PT')&"-"&{Type}) Second Formula The second one works as is below but I want to add a second webinar type that would return the “Sponsor” name field in the results. If the webinar type is Sponsor OR Sponsored Survey, display date-sponsor-topic, ELSE display “date-webinar type-topic” IF({Webinar Type}='Sponsor',DATETIME_FORMAT(Date, "MM-DD-YYYY")&" - "&Sponsor&" - "&Topic, DATETIME_FORMAT(Date, "MM-DD-YYYY")&" - "&amp

I’m trying to count the number of female attendees in a training session - I’ve managed to get them into a text string (eg: ,Female,Female, ) but can’t count the number of times the word ‘Female’ appears. I just can’t work out the formula to give me a number - help !

I’m not quite sure how to do this but feel like there is a way in AirTable so I wanted to ask… I have a Base with three different tables filled with people. In Table C, I have a list of teachers that I have linked to a school leader, Table B, and in Table A, I have a list of District People that are linked to Teachers in Table C. Records are created in each table based on specific conditions. How can I help my team track their work? For example, if they go to Table A and lookup all the teachers associated with a district record on Monday and then a teacher that should be linked with the district record gets added on Tuesday, how can my team when they check on Wednesday distinguish between the teachers they saw on Monday verse the Teacher needing to be added on Tuesday? Hope this makes sense.

Hi! I want to have due dates as work days only, but the formula below gives 5 days before the start date, NOT 5 working days. IF({Start Date},DATEADD({Start Date},-5,‘days’)) Can someone please help me with the formula to make the due date of something 5 WORKING days before the start date? Thanks in advance.

Hi, I want to create a formula field that will add a “$” to those number that doesn’t have that. I tried using different codes but I still haven’t achieved it, can you help me? Thank you!

Hi, I amm having a strange issue with MID(X,1,5) returning more than 5 characters and LEFT(XX,10) returning more than 10 :slightly_smiling_face: Is this a known issue ? Any clue to make it work ? See attached screens Valérian LEBERT digi-studio digi-studio Digitalisez votre entreprise avec digi-studio

I have 2 date fields and would like to calculate the midpoint between the two (as a date). I have tried a few the NGS bit not had any success. Any ideas?

Hey, I have a column containing words from a enumeration: ONE TWO THREE FOUR FIVE Does any one know of a formula to automatically replace those words by their corresponding number? 1 2 3 4 5 Any help appreciated :slightly_smiling_face:

Hi! I have an Airtable form that generates a Chargify signup link. The sales team fills in the form fields and then it spits out a signup link for the customer showing the products, quantities, pricing, and then allows them to fill out their billing information. For some reason all of a sudden the link seems to be fractured. If anything additional is added beyond the base subscription, when the link is clicked it does not show those add ons. However, when you copy and paste the link into a new browser tab it does. Here is the formula being used: IF({Watermarking quota},IF({AUD?}=“AU/NZ”,{Signup Link with Coupon and Seats and Tracks and DS}&“&components[”&{Watermarking Component ID AU}&“][allocated_quantity]=”&{Watermarking quota},{Signup Link with Coupon and Seats and Tracks and DS}&“&components[”&{Watermarking Component ID US}&“][allocated_quantity]=”&{Watermarking quota}),{Signup Link with Coupon and Seats and Tracks and DS}) Here is an example

Friends how cam get the row values which are matches with colA values using api query Eg, i wamt the values of field11,field51,field61 For single i use this formula filterByFormula={colA}=“field11”

I need two dynamic fields, one for EUR currency and one for USD currency. What I want to do is when I input on one of them it automatically convert the value and set on the other. How can I do this with or without formula ?

Hello! I have an issue with a SUBSTITUTE formula I am using. I started with the following formula: SUBSTITUTE( SUBSTITUTE( {Email Body} & "", "{Appraisal Contingency Date}",{Appraisal Contingency Date} & ""), "{Conditional Approval Due Date}",{Conditional Approval Due Date} & "") It returns the following: Appraisal Contingency Expires: 2022-09-20T00:00:00.000Z Conditional Loan Approval Due: 2022-09-02T00:00:00.000Z Since the dates were not formatted properly, I updated the formula to the following: SUBSTITUTE( SUBSTITUTE( {Email Body} & "", "{Appraisal Contingency Date}",DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY") & ""), "{Conditional Approval Due Date}",DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY") & "") It returns the following: Appraisal Contingency Expires: Tue, 09/20/22 Conditional Loan Approval Due: Fri, 09/02/22 All seemed well, BUT, when there is a record that does

Hello. This seems like it should be an easy solve but I cannot for the life of me figure it out. I have a table in a base that automatically receives responses from a Typeform survey. It puts the email address (manually entered by person taking the survey) as the main record, and the responses follow in different columns. There is another table in this base where the main record is an employee name, and then we have Department, and 3 columns of 3 different email addresses (there are potentially 3 different ones for each employee, or 2 or 3 can be the same). I have linked the email base to the response base, and as the responses come in, I use a lookup to select the employee and pull in their department. I am trying to get this part to happen automatically. So, when an entry comes in, if that email address matches any of the three emails in the other table, it will bring that employee name in. Hopefully this makes sense and someone has a solution… Thanks!

Hi, I would like to add days to a start date but exclude Sundays. So if I have a Start Date, I want to estimate the End Date by adding some number of days to the Start Date while excluding days that fall on Sunday. So if I have a Start Date of September 1 and add 7 days, I want to end up with September 9 rather than September 8 as I want to skip over the Sunday in the 7 days. My formula is below: Estimated End Date = IF({Start Date}, DATEADD( {Start Date}, ROUNDUP({Scope}/1000,0), ‘days’), BLANK())