Formulas

I have a table of books with the following columnsBook Titleyear publishedAmazon LinkBookshop.org LinkI want to create a field that returns Book Title (Year Published) Amazon Bookshop, where the "Amazon" is linked abd "Bookshop.org" is linked.Is it possible to use a formula to return text with links? I tried creating columns for buttons, but when I include the button in the formula, the output is just the URL. For each author, I want to roll up a list of their books with the corresponding links. 

Hi there. I'm trying to create a formula that returns a numerical value (price) based on a dropdown field. So, if "Option Zebra" is selected in the dropdown, the formula returns a value of 250. If "Option Lion", then the formula returns a value of 300, etc. Would some amazing wizkid be able to help me with that?

Hi all! I am trying to find a method to tell me the Field Name of the last modified field, using a Formula. Is this possible without external scripting? I'd also settle for Field ID, although not most ideal 🙃Was hoping for a function like LAST_MODIFIED_TIME( FIELDNAME() ) or something.Basically, I have logs coming into a Logs table with 100(ish) columns, and I want to know at a glance what type of logs that particular one is 🙂Thanks in advance!Adam

I need a formula to verify a single-line text field only contains productId's separated by commas with <=30 comma separated productId's.OK: <=30 comma separated 8-character productIdsNot OK: >30 productId's; <8 or >8 character productId's, not comma separatedexample (with 5 comma separated productId's)correct: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9incorrect:7 productId's: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9,H4VPMWY7,MRKM1Y6F5 productId's with missing commas: B0D5FHXP B0D86NWH BCVPRWY7 DRKM1T5G GK3DHGH97 productId's with semi-colons: B0D5FHXP;B0D86NWH;BCVPRWY7;DRKM1T5G;GK3DHGH9 

Hi there,I have a team of account executives and I'm trying to rollup the amount of revenue each person manages during a 12-month period. Revenue is generated on a per project basis. Each project has a start and end date and a monthly retainer fee, which are recorded in the project's record in my project table. I have a formula field ("Project Value") that calculates the total project value.I maintain a second team member table and I link account executives to the projects they manage using a linked record field. Some projects are managed by two account executives (and are linked to two account executive records as a result).The problem is that when I rollup the amount of revenue each person manages during a 12-month period, for projects that have two account executives attached, the rollup attributes 100% of the project revenue to both of the employees linked (rather than 50% to each). Can anyone point me towards a solution? Many thanks.   

Hi,I have a formula that almost works. I'm trying to make my primary field a formula where it looks at "Column A" and looks at "Column B". If a there's a value in "Column A", enter that value as the primary field. If there's a value in "Column B", enter that value in the primary filed. If there's a value in both "Column A" and "Column B", combine those two values and place a "/" between the two fields. The formula I have looks like this: IF(AND({Column A} != BLANK(), {Column B} != BLANK()), CONCATENATE({Column A}, "/", {Column B}),IF(Column A != BLANK(), {Column A}, {Column B})) This does work. However this formula is adding the "/" character to the beginning of "Column A" and at the end of "Column B" when there's only one value in one of the fields. The results look something like this:If there's a value in "Column A" but not in "Column B":12345/75326/23457/ If there's a value in "Column B" but not in "Column A":/93664/30483/54214 I tried r

Hello,I'm learning how to pre-fill a form.  I really like it but it would be even better if I could prefill a form entry with multiple values/entries from another linked record.  Currently, it is only pre-filling the first value of cell.  The linked record in this formula is the Residents Column.I need them to stay selectable values, not text.  I can't expect the users to enter the text values exactly as they are written in the linked records. "https://airtable.com/---/form?"& "prefill_Job=" & ENCODE_URL_COMPONENT({Job})& "&prefill_Order=" & ENCODE_URL_COMPONENT({Order})& "&prefill_Teams=" & ENCODE_URL_COMPONENT({Teams})& "&prefill_Residents=" & ENCODE_URL_COMPONENT({Residents})& "&prefill_Description=" & ENCODE_URL_COMPONENT({Description})

I am using a formula to calculate a date in one field, and then trying to use another formula field to determine whether a contract is current, future, or expired using that date and another manually entered date field.  The field I'm using to determine if the range returns the wrong results if I use the calculated expiration date field, but if I use a manually entered field with that same date, the calculated range returns correctly.   ExpDate:DATETIME_FORMAT( DATEADD( DATEADD( LIC__EffDate, {Term (Mo)}, "months"),-1, 'DAYS'), 'M/DD/YYYY') ContractRange:IF(   ExpDate > TODAY(),   IF(   LIC__EffDate > TODAY(),    "Future",   "Current"),   "Expired"   ) ContractRange copy:IF(   LIC_ExpDate > TODAY(),   IF(   LIC__EffDate > TODAY(),    "Fut

Hello!I would like to tag some records based on the text content in a column (ie: if Title contains "Tour" tag Tours). Feels like it should be pretty straightforward but I can't figure out how? Thanks! For more context: I imported all of my team's events from Outlook and realized Airtable doesn't support the "Category" feature, so I want to add tags to these events so that we can break down the data. I figured I could probably do that based on title or other key words.  Thanks in advance!

I am trying to build a table to calculate pricing. But, every column (which is its own product) has different price points under it, based on square footage. How can I set up a formula that will calculate each drop-down option (multiple selection), per column and total that up at the end? See screenshot for referenceFirst column is interior photos. There are four potential options in that field, so I need a formula that will calculate based on the selection I make in each column. And the same goes for every other one. 

Hi there. I am trying to write a formula ("Duration of Pending Repayment") that calculates how long an unpaid repayment is overdue.Essentially, it will be:If "Repayment Due Date" is not blankAnd "Repayment Received Date" is blankThen calculate Today-Repayment Due date. This part I got with DATETIME_DIFF(TODAY(),{Repayment Due Date},'days')I cannot figure out how to do the If/And with blanks, not blanks. Any ideas, wizards? 🤓 Thank you in advance!  PS - Here's how the formula currently reads:IF(AND(NOT({Repayment Due Date})),{Invoice Issue Date},DATETIME_DIFF(TODAY(),{Repayment Due Date},'days')) 

Hi,Hoping that someone might be able to help me with a formula question (I'm quite new to formulas and have been fumbling along until now, but have reached the limit of my knowledge!)...I have a table of payments that have been made, each with the payment date. These Payments are associated back to a project budget and are rolled up so that, at any point, you can see the total Actual spend to date. I've got this all working well.I am now trying to add in a new field in the Project table which shows total Actual spend as at the most recent quarter end - and I'm stuck! I've managed to write a formula in the Payments table which formats the payment date into the year and quarter (so turning 2022-02-04 into 2022-1) but I now want to work out how to pull this through to the Projects table. I know that I could build a series of fields in the Projects table that rolled up (respectively) all Payments in 2022-1 and then all Payments 2022-2 etc etc, but this doesn't feel very sustainable as

Hello,  I am trying to create a formula to put together a date we expect something to be completed.Date to Client is entered by my team and is the first part of the equation.Client Timing is pulled as a lookup field from another table dependent on each client as is based in days.  Formula I have is Dateadd({Date to Client}, {Client Timing}, "days").  This equation is taking the client date (ex 12/2/25) and always subtracting one (ex 12/1/25.Is there a different formula I should be using for this type of transaction?

Hi - first timer here!I have 2 tables in 1 base.  Table A contains info about Recipes I'm creating. Table B is where I schedule those recipes for newsletter publication. Recipe names are pulled from Table A into Table B by linking fields. There are 3 recipes per date, so 3 linked fields. So far so good.I want to have the newsletter date in Table B pulled into the corresponding recipe record in Table A. See attached illustration - what formula do I need to replace the question marks with dates?

I have a table in Airtable with two fields: "Child" and "Mother" (just an example to explain the structure easily). The "Mother" field is a linked record to the same table. Here’s a sample dataset:ChildMotherRaviSnehaKaviSnehaBalliPrernaMalliPrernaSnehaMotherPrernaMotherI want to add a new field, "Number of Children", that counts how many linked records (children) each mother has. The expected output should look like this:ChildMotherNumber of ChildrenRaviSneha0KaviSneha0BalliPrerna0MalliPrerna0SnehaMother2PrernaMother2I tried using a Rollup field with COUNTALL(values), but it counts each linked record separately instead of summing up the total number of children for each mother.How can I achieve this without creating another table? Any suggestions or workarounds would be greatly appreciated!

I'm looking to send out an email when a record is updated to interface-only collaborators. I want the email to include a link to the record in question.I've got a link to the record using the following formula:"https://airtable.com/appxxx/pagxxx?xxxx=" & RECORD_ID()  But I need to link to the Details section of the record with this link. I.e., my Interface uses the 'Sidesheet' details pane. When I click the email link, I want to be taken to the correct Interface and correct page and have the sidebar pop out too. What do I append to the above formula to make the correct sidebar pop out?

Hello everyone.I'm a Korean user, but there are still few AirTable users in Korea, so it's hard to find a solution, so I wrote to the community for the first time.I'm trying to use automation to send messages to Slack that mentions multiple users.I've tried several ways to do this, but while I know how to mention a single user, I haven't found a way to mention multiple users yet.I've tried the following formula and automation, but it didn't work.1. @Mention using a Slack username   <@first.last>   2. @Mention using Slack user's ID   <@memberID>   3. Dynamic @mentioning   CONCATENATE("<@", {Slack member id lookup}, ">")   All of these methods worked when I linked only one record, but when I linked multiple records, they didn't work correctly.ex.)when linking sigle record--------------------------------------------when linking mulitple recordDoes anyone know how to solve

Hello, I am attempting to create a “email merge” document template using Airtable that allows me to: Use rich text formatting (bold, bullets, etc. for email formatting), and Use reference fields in the text (for example “Hello {First Name}, your User Name is {User Name} and your Password is {Password}.” If I try to do this in Page Designer, I can do #2, but not #1 that I know of. If I try to do this in a Long Text Field, I can do #1, but not #2 that I know of. Any ideas? Thanks, ML

I'm wondering if a formula has a way to reference the field name.  For example I want to name a field the number 10 and then use a formula which calculates if {other field} < {thisfield.name}.Is that possible?

I'm managing 5 websites, and have created one base with 5 tables - each reflecting the blog planning for one website (syncing with the website-specific bases). I've now created a table that collects all the posts from all the websites, so I can see where everything is at in a blink of an eye. What I can't wrap my head around is the dates: I now have 5 date fields (lookups), but I'd like to be able to sort things by date (or display them in a calendar, etc) - regardless of which website it's for. I created a formula field to merge the dates, but that displays them as "2015-01-01T00:00:00.000Z"... I know there's a DATESTR([date]) formula, but that gets me an error message (am I using it wrong?). Any help would be greatly appreciated! 

Hi,I have one column in my Jobs Table that pulls the Job address from my Table of Customers, its a Formula and seems to work fine it puts the correct Job address into the Jobs Table if the customer has a different Mailing address, it looks like this: IF({(J) Address?} ="Yes", {Mailing address (from Customers)}, {(J) Street} & " " & {(J) City,St,Zip})I have a second column in the Jobs Table that I need to JUST have the City Name appear in.  I have tried to write the formula, and it just returns an Error Message, it looks like this: IF({(J) Address?}="Yes", LEFT({(M) City, St. Zip (from Customer)}, SEARCH(",", {(M) City, St. Zip (from Customer)})), LEFT({(J) City,St,Zip}, SEARCH(",", {(J) City,St,Zip}))) Is there a better way to pull the City out of the Job Address Column?I feel like I'm beating my

Hello there smart folks! I am building a base for small film productions, and the industry has some odd practices I have to work around. One of those is tracking scenes on scripts in page length in fractions - specifically 8ths.For my personal tracking I have a column that just holds the size in the total number of 8ths (so instead of showing 1/8 page it is 1, 1 and 3/8 pages is 11) which I can then total into total number of 8ths.I now need to convert that back into pages in fraction form to display on one of my reports. So say I have 10 scenes over 5 3/8 pages (so my total is 43). How do I create that 5 3/8 to display?I hope this makes sense!

Hi, I am very new to scripting and managing a base for content production.  Want to create a script that will update a series of dates based off the publication date changing.  I'm getting errors and not sure why.  Any help would be appreciated.  ThanksHere is the scriptlet table = base.getTable("Deliverables");async function updateRecord() {let inputConfig = input.config();let recordId = inputConfig.recordId; // Fetch the record ID from the previous step// Check if recordId is valid (a string and not undefined)if (typeof recordId !== 'string' || !recordId.trim()) {throw new Error("Invalid or undefined record ID. Ensure the input provides a valid record ID.");}// Fetch the specific record from the tablelet record = await table.selectRecordAsync(recordId);if (!record) {throw new Error(`Record with ID ${recordId} not found.`);}let publishDate = record.getCellValue("Published Date");let Series = record.getCellValue("Series");if (publishDate) {let updates = {};if (Serie

I am working on a job board project and have this challenge. I have gone through some of the community posts but cannot figure out a solution.The marketplace is continuously updated with titles, urls etc.I want to convert the URLs to hyperlink. As well, I want to create a solution that will apply to the entire field. I saw a post but I think that is valid for permanent  urls, not where every record has a new url. Is this possible? Any help will be greatly appreciated.

Hi!I have a table where I am tracking when a certain item (Lot) needs to have its lab testing updated. Each record has a {Lab Status} single select field, a {Latest Lab Date} date field, a {Next Lab Due} field that calculates a date 6 months out from the date field, and a {Send By} field that calculates a date 1 month prior to the next due date. The rest of the fields are linked to records in another table of the same base but are not pertinent to this.Record   Lab Status   Latest Lab Date    Next Lab Due     Send ByLot AA    Up Next        08/01/2024          02/01/2025        01/01/2025There are two functions I would like to implement, if possible, either into the table or with automations:1. (automation or formula?) When we enter into a given month, I would like for any records with a Send By date in that month to automatically update the Lab Status to 'Up Next'. F