Formulas

Okay guys, ive seen a similar post about this but didn’t answer what i was looking for. I am wanting to take a url (google.com) and add it to a “open URL” button then another record have another url (youtube.com) and add it to another “Open URL” button. What formula could i use to target the url inside of the button field? SCREENSHOT: https://snipboard.io/A3ucNd.jpg

Hi everyone,I always found Airtable formulas a bit tricky, so I built a Chrome extension called Airtable AI Formula Builder. It lets you describe what you want in plain English (e.g. “combine first name and last name”), and it instantly generates the correct Airtable formula.👉 Extension link: Ai formula builder It’s a freemium tool: you can try it once for free to see how it works, and then unlock unlimited usage with a small upgrade.This is the very first version (MVP), and I’d love your feedback: What formula types do you use the most? What extra features would make this more useful for you?Thanks a lot, every bit of feedback helps me improve it 🙏

We have an intake form to generate URLs with UTMs parameters. I have created a new form with additional functionality. During testing, I need both the old and new forms to work.My current Formula field concatenates the URL (Root Link) with the UTM parameters and values. There is also a check to determine if the URL provided in the Root Link field contains a "?". If it does, then the concatenation output is handled differently:IF(FIND("?", {Root Link})=0,{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})My new form has the option to create some additional link types based on content. If you choose that option, the

In airtable form the column should be visible when 2 column values together should be 100%. 1st Question:For example, as shown in the screenshot below, in the Airtable Form, when a user updates Tag 1 and Tag 2 so that their total equals 100%, Tag 3 and Tag 4 should no longer be visible in the form. If the tags do not total 100%, all three remaining tags (Tag 2, Tag 3, and Tag 4) should remain visible. 2nd Question:Is there an option or way in Airtable forms to prevent users from submitting the form if the total Tag does not equal 100%? 

@ScottWorld , I went through a few options to look at ways to just highlight the duplicate entries as they get entered into a database. I noted u came up with a simple one but there were no steps written down. We have a unique patient ID which is in the primary column , we dont want to duplicate a patient file. Every time we enter we wish to have a look and see if there is a duplicate entry and delete if its highlighted. makes sense?

Just wanted to pitch this, and see if you are seeing this as much of a need as I see it. I am a heavy Notion user, which I quite a lot but I overall preffer Airtable except for one thing. NOTION Formulas as far superior. To give an example, you can use dot notation in Notion formulas  sum((Pagos.filter(and(current.Tipo=="Cuota",formatDate(current.Date,"MMMM")==(formatDate(today(),"MMMM")))).map(current.Monto)).first())!-->Here I am getting fro my list of members, all the categories they belong to, and from there all the events there are, and from there all the dates. All within one single formula. This would be so great in Airtable.If I have a level hierarchy Company, Office and ContactThere is no way to bring the contact email to the Company record unless i create a roll up of a rollup so I am creating 1 extra field in Office, whereas in Notion I could simple go for:Office.map(current,Contact.Email) I can even apply filters making it much easier to set up.Am I the only one with thi

I’m trying to get text and currency to combine and show properly with 2 decimal places even if they are 0.00.I have a product field (with text), a value field (with Currency)If i did a simple Formula {Product} & “ - $” & {Value} the result makes $18.30 from {Value} turn into $18.3 and $20.00 turn into $20, removing any 0’s after the decimal.Through research and help we can do the following equation:CONCATENATE({Product}, " - $", IF({Value}, ROUND({Value}, 2) & IF(FIND(".", ROUND({Value}, 2) & " ") = LEN(ROUND({Value}, 2) & "") - 1, "0", ""), "-.--")," "&{/Unit})This will put in 2 decimal places unless they are .00, then it reverts to suppressing the two 0’s after the decimal place, how do i get them back?

Hi 👋How can I get the average of the top/bottom 20%? For example, I have this table:Name | Sales Sabrina | $10 John | $5 Maria | $20 Ann | $100 Ben | $5 Sabrina | $10 John | $5 Maria | $20 Ann | $50 Ben | $1I want to see:Top 20% (average sales from top 20% sales): $75Average: $23Bottom 20% (average sales from lowest 20% sales): $3 Thanks!

I’ve got a base with an “Event Finished” formula field that works like this:IF(DATETIME_DIFF({End}, NOW()) < 0, TRUE(), FALSE())It resolves into a checkbox, and I use it to filter out finished events from admin list views. My base currently sees about ~15-20ish events a day, which have times attached, and the folks that need to monitor the views have told me they find it really annoying when finished events aren’t filtered out of a given list view.And it works fine! I could be using IS_AFTER rather than DATETIME_DIFF, but otherwise, it does the job. However, I know NOW is a function to avoid. Its inaccuracies aren’t a big deal to me (if the formula clears out an event 15 minutes later than it should have, that’s not the end of the world), but its computational cost is becoming more of an issue. I assume NOW continues to recompute for every record, even those that are well beyond completed at this point. I’m trying to optimize things so NOW gets called less, so here are some question

Hi there, I am wondering if it is possible to have small caps in an AirTable rich formatted field. If so, how can it be done? Can it be done with a formula? How about inline css? Would this work? <p><span style="font-variant: small-caps;"></span></p> Any help would be greatly appreciated. Thank you.

Hello everyone,Who can tell me the formula to get the attachment URL in the new https://v5.airtableusercontent.com/... format? The one below only returns the format https://dl.airtable... which no longer works. IF( AND( {Attachment}, FIND( ",", {Attachment} ) = 0 ), RIGHT( LEFT( {Attachment}, LEN( {Attachment} ) - 1 ), LEN( LEFT( {Attachment}, LEN( {Attachment} ) - 1 ) ) - SEARCH( "https://", {Attachment} ) + 1 ), IF( {Attachment}, RIGHT( LEFT( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ), LEN( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ) ) - 1 ), LEN( LEFT( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ), LEN( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ) ) - 1 ) ) - SEARCH( "https://", RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ) ) + 1 ) ) )  Thank you!

Hello,I really need tour help for this trick 🙏 !I have a single-select field where there is no text, only different color to choose, displayed in interfaces as a colored-circle.Now I want to create an other field, with a formula using the “value” of this previous field.But impossible to test the otion’s rank, for instance.I cant insert any text in my option, but I really need to reuse this result… Does somebody could help me ? Thanks ! 

All fields are operating as expected in the original base.  I have synced the table to another base and all fields operate successfully except for the date (rollup) field.  The date field is not syncing properly.  Below is how the rollup field is set-up in the original base.I have confirmed the date formatting in all bases are identical. Any ideas on why this might be happening? Thanks!  Rollup field in the original base (successful) Original Date Field  

Hi Team,Need help to get this function running.I’m trying to build a formula that could work as linked field.So far, I managed to create the formula to combine the columns into “Product” column. However, I still need to copy past to the column “Products” to link to the Products table.Wondering if there is any way to create formula on linked field to make it all in one.Or at least, a script to create the links without need to copy past every time. 

I have a table with 8000 records that lists out where in our warehouse we keep our items. I’d like to group the locations of the same item by listing the locations in one cell and deleting the duplicate records. How could I go about doing this? ExampleItem                                      LocationItem 0001                             Shelf 1AItem 0001                             Shelf 1CItem 0002                             Shelf 1BItem 0002                             Shelf 3AItem 0002                             Shelf 5CItem 0003                             Shelf 1A Desired outputItem                                      Location        Item 0001                              Shelf 1A, Shelf 1CItem 0002                              Shelf 1B, Shelf 3A, Shelf 5CItem 0003                              Shelf 1A

I am no geek when it comes to sheets, most of the stuff I do comes from watching a video on youtube. I have a retail booth at a flea market and I get  my items from various resources, Some items come from estates that I buy, some from garage sales, some from a local auction house etc. I use the cents in the pricing to help identify where the items were purchased. For an estate sale, the price would be, for example, 10.31, so I can look at the daily sales report and immediately identify every item sold ending in .31came from the estate sale. What I’m wanting to do is  list the different sources I use and have sheets track and total each item based up the cents.  So in the Cell that has a heading Estate Sales, create a sum formula with the criteria of searching the sales report looking for every item sold that ended .31 Thanks in advance for the replies.  If this isn’t possible, is there a way to copy and paste selected random cells.  When I highlight multiple cells using the ctrl tab, c

Hello! I am struggling to create an easy database template for importing data from a manual CSV export with some weirdness to it. Then an interface for data visualization that once was done using a platform that linked directly with the app generating the data. The app generating the data exports two CSV files, one for a list of “interactions” and one for a list of “buttons”, so I started with a table for each with the same fields. Each interaction row contains a lot of data pertaining to the user, so I added a 3rd table of Users in Airtable, and just made those fields in the Interactions table lookup fields to pull from Users. The Interactions can have up to 12 Buttons, and the export has several columns for each button - some of it is unique to the interaction and some applies to every use of that button, so I have put that info in the Button table and the fields in Interactions are now a lookup. The Interactions records also include date and time of the start of the interaction, and

Hi,One table has the entire list of our shows with an ad budget attached to each. Another table has an entire list of each ad placed, but our ads include multiple shows in each one so there’s already a linked record there, then a formula to calculate total ad spend versus how many shows are in that one ad so that we know how much is spent on each show per ad.Is there a way to use a rollup field or formula field to add up our ad spend per show and attach it to our show list in the first table? I need it to calculate correctly for each show, not just spit out the total amount.  

HIIMAGINE A RECORD…..PRIME = DRAWING 1234, MATERIAL = ABCD, LEVEL = 1 AND QTY = 5.NOW IMAGINE ANOTHER RECORD - SAME DRAWING AND MATERIAL, LEVEL = 2 AND 10 = 10 I AM USING QTY TO DO A TOTAL IN ANOTHER TABLE - AND IN THIS CASE IF I DID THAT I WOULD GET 15….WHAT I NEED TO DO IS TO FIGURE OUT A FORMULA, AUTOMATION OR ROLLUP ETC TO ACTUALLY SAY THAT I WANT THE THE QTY FROM ANY RECORD THAT HAS SAME DRAWING AND MATERIAL BUT ONLY THE HIGHEST LEVEL…..IN THIS CASE THE 10 ON LEVEL 2….AND THE 5 SHOULD DROP TO 0 (USING A NEW QTY DATAFIELD THAN THE ORIGINAL QTY).IF ANOTHER RECORD COMES IN WITH SAME DRAWING AND MATERIAL BUT NOW LEVEL 3 QTY 20….THEN THE RECORDS WITH 5 AND 10 SHOULD BOTH DROP TO 0….AND I COUNT 20 FOR ALL 3 RECORDS IN THE ROLLUP...AND SO ON….. LOOKED AT ARRAY FUNCTIONS BUT DO NOT SEE ANYTHING THERE I COULD USE AND CREATING A LOOKUP LOOPING ON ITSELF DID NOT SEEM TO GIVE ME A BASIS FOR GROUPING THE 3 RECORDS BUT SEEING COUNTS OF 0, 0, 20…. ANY IDEAS?   THANKS IN ANTICIPATION     MIKE 

I’ve tried many different ways to set up click to dial with ringcentral app, and it even worked the first time I set it up, but for some reason, its not working. I’ve tried creating formulas that others have suggested, buttons, etc… but no mater what I do, I always get this error in the address bar of the new tab that opens: about:blank#blockedIf I manually type tel:+16199309785 it dial no problem, excel the link i just typed even works but it won’t click to dial from the grid in airtable. I’ve tried chrome and edge, same thing. Any suggestions?

Hey all, I’ve got another (hopefully) easy question for y’all.IF({Rental Fee}="", "", {Rental Fee} / {Open Duration})Here’s what I’ve got. Open Duration is another equation where I used DATETIME_DIFF to calculate the number of months (success!). Rental Fee is a currency field where I plug in the numbers. What I really want to do is see how much the rental fee came out to per month.Cool thing is that the IF field is working well enough, I only get #ERROR! on the fields where the equation is applicable. Not cool thing is the whole #ERROR! message bit. 😂 If rental fee is $10,000 and duration is 10, then why am I not getting $1,000?I can’t figure out what I’ve done wrong. Any thoughts? Thank you in advance!

I’ve been trying to make formula to proper clean person name, but the result is weird. It’s like the LOWER() function dominates the whole result even though I put it in the middle… is this a bug?if I remove the LOWER() it only works if you dont meet with the all capital names (Like “JOHN DOE WHITE” would stay the same)I even tinker with internal AI and Copilot with the same results.I basically just want to make start with Capital, clean all unnecesarry whitespacesCan somebody help me?   

Hello,I am building a dashboard in which I include a control formula in order to make sure the AI reads well.However the behaviour is inconsistent. Check formula :IF({Amount (vat incl.)}={Amount (VAT incl.) (check)},"✅","❌") Calcul formula{Amount (vat incl.)}-{Amount (VAT incl.) (check)} QUESTIONWhy is one result -0 and the other 0 when the mechanics between formulas and parameters are the same ?  

I’ve got a very simple IF statement Facon is a check box, and it’s clearly empty.Material Supplier address is a lookup and is filled in in most instances. However, I can’t get this basic thing to work? Am I doing something wrong?

I’m trying to write a nested if formula in a CRM document. I have a column for the frequency I need to contact someone, a column with the date they were last contacted and a column that I want to say “Overdue” if it’s more than X days since my last contact. For instance, if I have the Frequency marked Monthly, I want it to say “Overdue” if it’s been more than 30 days since my last contact.Here’s what I have now, but it doesn’t say “Overdue” when it should. Everything in the column is blank.IF(AND(Frequency="Weekly",DATETIME_DIFF({Last Contact},DATEADD(Frequency,7,'days'))>0),"Overdue",IF(AND(Frequency="Monthly",DATETIME_DIFF({Last Contact},DATEADD(Frequency,30,'days'))>0),"Overdue"))