Formulas

Hi there, I would like to use a formula in the primary key column of an airtable data set to create a unique ID, or autonumber, but i want that number to recalculate when i insert a new row anywhere in the master data set so that the ordering is retained. if you use the autonumber field type, it will create the record using the next available number, then all my views start to loose the ordering i want.is there a way to write a formula that will re-calcuate the primary key column to retain the existing order, with the new record in place.  For example: 1 TestA2 TestB3TestC If i insert a row between TestB and TestC, the primary key would show as follows:1 TestA2TestB3Testnew record4TestC 

I'm trying to write a formula to remove the "➟" if the preceeding field is empty.Current Formula:Origin& " ➟ " &{Destination}&" ➟ "&{Backhaul From}& " ➟ "&{Backhaul To\\Return}& " ➟ " &Origin &" - LANE #" & {LANE#}  Here is the output of the above formula.  Formula above works great, (example 1 below) but if there is no data in {Backhaul From} and {Backhaul To\\Return} the result is example 2 with 2 extra arrows.  Example 3 is the desired results.

I looked for other solutions on the forum and either I'm too much of a novice to know how to solve it or it didn't work for my problem. I have a rollup field that is collecting multiple dates. I'd like to have it only return the earliest date in the list.  Is that possible?  I tried using MIN(values), but only got a 0 in the field.  Thanks!

Hey all! I have a problem that I hope you can help with. I have a massive amount of data that I need to put into Airtable that I received in the below format. However, I need to put each individual instance into a linked record field in AT, so the 1-5, will need to be 1, 2, 3, 4, 5. The amount of data I have will take me months to manually import one record at a time. I am hoping to create a formulate that will pull out the hyphenated numbers and return a value of GKM5L1, GKM5L2, GKM5L3... etc for the first line so that I can copy and paste values into AirTable instead of entering manually. I would really appreciate any help! Thanks!

I have addresses with several fields, some fields are not always necessary.MemberOrganizationMail Address2nd Mail AddressCityStateZipo CodeTrying to get a multi line field that has no blank lines.Here's the formula I am using. I cannot find a way to get rid of the blank lines. What am I doing wrong?I also included a screen shot of the existing multiline field.Thanks in advance for any help, Scott TRIM( IF( AND( Organization, {2nd Mail Address} ), Member & "\n" & Organization & "\n" & {Mail Address} & "\n" & {2nd Mail Address} & "\n" & City & ", " & State & " " & {Zip Code}, IF( AND( Organization, {2nd Mail Address} = BLANK() ), Member & "\n" & Organiza

Hi! I am struggling to come up with a formula for this problem I am having:I need to create a formula on Airtable that will list all the Sundays that fall between a field called Created Time (date), and another called Start Date (date that is after the Created Time).For example, if the Created Time was today: 03/19/24And the Start Date was a month from now: 04/19/24I would like this formula field to display the following: 03/24/24, 03/31/24, 04/07/24, 04/14/24If any of those 2 dates are a Sunday, they should be excluded from the formula.Can this be done? Anyone have any tips on how to do this? I am trying to find a workaround as Date fields aren't very dynamic when used in Automations, and would like an automation to auto-populate a linked record field (within my "Events" table) with all the Newsletter emails I have on a separate table's calendar for Sundays.

Hello, I am new to formulas so I was hoping someone can help me with the formula below. I am trying to create two new formula fields.In the first new formula field, I am trying to do the following: If the 'Category' field is Finance and the 'Owner' field is Jackie, Anton, or Jalen, then have the new field return "Assigned" If the 'Category' field is Finance and the 'Owner' field is not Anton, Jackie or Jalen, then have the new field return "Not Assigned"In the second new formula field, If the 'Asset Type' field is Marketing and 'Attachments Resource' field is empty, have the new field return Awaiting AssetsIf the 'Asset Type' field is Marketing and 'Attachments Resource' field is not Anton, Jackie or Jalen, have the new field return Agency OwnedAppreciate the help in advance!

Hey! I'm trying to figure out how to add a portion to a formula that would ensure that it's set for 90 days after a particular field, or before June 1st. The current formula pulls from a date field for when they registered (Date Registered), and adds 90 days. How would I add a restriction onto this formula so it's 90 days, or until June 1st?  DATETIME_FORMAT(DATEADD({Date Registered}, 90, 'days'), 'MM/DD/YYYY')

I'm using this formula in a Deadline Alert column: IF({Deadline} < NOW(), "Late", IF(IS_SAME({Deadline}, TODAY(), 'day'), "At Risk", "On Time")) I have a separate Status Column to mark tasks Done. Is there a formula to show "Done" (instead of "Late") in the Deadline Alert column when "Done" is selected in Status? 

Hi, I have 3 Fields “Name” “Results” “Position” I will like to compare the values in “Results” Field and position it from ‘1st - last row’ (Field “Position”) If “Field Results” Row 1 or Record 1 is > Row 2, then Row 1 = “1st” else If “Results” Record 2 > Record 3, then Row 2 = “2nd” else if “Results” Record 2 = Record 1, then Row 2 = “1st” What I want the “Position” Field to do is, compare if Results Field Row 1 is Greater than Row 2 then Position Field Row 1 is equal to “1st” And if Results Field Row 2 is equal to Row 1 then Position Row 2 will be “1st” Or if Row 2 > row 3 then Position row 3 = “3rd” Finally if “Results” Row 1 = Row 2 then both "Position Row 1 and Row 2 will be “1st” But “Position” Row 3 will be “3rd” What formula do I use to achiev

Hey Airtable! I'm looking for some complex scheduling help. I'm using Airtable to schedule a multi stage music festival, and we're hoping to have a formula that will look for scheduling conflicts for individual performers at specific times. I have an example of how we have the scheduling started attached. The main record name is the act, and then we've created different set times for different stages across the three days. There are also individual performers listed for each set, since some performers will be playing in multiple bands. So basically, I want the formula to be looking for scheduling conflicts for individual performers during specific windows of time. Is this possible? Do I need to be building the worlds longest IF/THEN formula? Appreciate any advice!

Hey guys, I have a button in my Airtable that triggers a Zap in Zapier. I'm using this code: { "https://hooks.zapier.com/hooks/catch/14888220/30r6035/?recordID=" & RECORD_ID() }, but it's triggering the Zap twice. I'm also using multiple buttons, and it's not working as expected. Can you help me troubleshoot this?

I have 2 tables with a many-to-many relationship...Companies companyName, companyCountries (linked to Countries table)Countries countryName, projectPhase, countryCompanies (linked to Companies table)The projectPhase is country level, for example...GB = "go live"JP = "phase 2"MY = "go live"US = "phase 2"1 company can have multiple countries, and so on the Companies level I've created a rollup using ARRAYUNIQUE(values) but I've run into a problem with a scenario like this...CompanyA is live in GB and JP and shows "go live, phase 2" CompanyB is live in JP and MY and shows "phase 2, go live"I've read a bunch of posts on this behaviour and how the rollup retains the sort order of the original linked source (in this case Countries), but my Countries table needs to be ordered as it is, and there's so much variation in my companies data that regardless of t

Hi,We have a scoring system that rates functionality in an interface. So as not to bias the total score, if the functionality isn't present we ask evaluators to give it a -1.In Excel, the final score is determined by a formula that:Counts the number of criteria where the score is >= 0Determines a max possible score based on the number of criteria scored (e.g., there are 10 criteria in a category but only 9 of them got scored the max possible score is 9*3=27 (9 criteria scored * highest possible score))Adds the values for each of the criteria scored to get the actual totalI'm able to use sum to get the total score but I'm not sure how to get the number of criteria scored without using countif which Airtable doesn't seem to have.Is there a way

I’m trying to figure out if AirTable can solve equations with unknown variables. For instance, a break-even analysis based on data in the table. (Revenue per unit ⨉ Units sold) − (Fixed costs + Cost per unit ⨉ Units sold) = 0 (200 ⨉ Units Sold) − (1000 + 100 ⨉ Units sold) = 0 Where Units sold is the unknown variable (“x”), and I would like to know the value of “x” where the equation returns 0 (break-even). A normal equation solver would give: (200x)−(1000+100x) = 0 x = 10 So, could I get “10” to display using an AirTable Formula? Thanks!!

Is it possible using a formula or automation to compare two multi-select fields and show which items are missing from the 2nd? I'd like to have a 3rd field that would show just what was missing from the completed list. 

Hello! I'm creating an Airtable base to manage book club meetings. I have a column for the meeting dates (DD/MM/YYYY) which take place once per month. I'd like to have a "Reading Status" column that uses a formula to create the following labels:"All Done" for meetings where the date has already passed"Up Next" for the upcoming meeting"Not Yet Read" for future meetings that are not the next closest meetingI am new to formulas and struggling with how to set this up. Any help would be much appreciated. Thank you!

Hi All, I want to create a bibliography. I have all the pieces in different columns and want to use the concatenate formula to pull them all together. However the Title of the book needs to be in Italics, how do I carry over the italics from the cells, or adjust the formula to make only the title Italic? Author Surname, Initial(s). (Year) Title. Place of publication: Publisher. This is my formula: CONCATENATE({Author(s) Max 3},"(",Year,")","."," “,Title,” “, City,”:"," ",Publisher) Any ideas?

How do I add a column of numbers quickly without creating a formula?

Hello,I'm pulling in purchase order data for my company, and have a "Vendor Management Team Partner" associated with each purchase order record.I'm also reconfiguring these POs in a separate table, such that the email address of the PO requester is the primary key.If a requester has multiple POs, they'll have multiple values in the VM Team Partner column, so I'm also adding a Rollup for unique values.I'm then automating the copying and pasting of the unique values in the Rollup field into a Linked field for the VM Team Partner's email info.However, in only some of records, the Link field will display a comma at the end of the values, whereas the Rollup field will not.How is this happening when there is no comma in the Lookup field? I now have to add values in my "VM Email" table for both "John Smith" and "John Smith," which is an unexpected nuisance.Can someone help me understand how this is happening and how to rem

Hi,I hope you can help me, I've searched everywhere and I can't find an answer to my problem.I work for a training organization. I have a table with students. A table with training session dates. And a table with speakers.In the dates table, I make a link with the speakers (each speaker can participate on one or more dates)In the students table, I want to get the names of the speakers of each session, but in a deduplicated way (I want to use these names to print a certificate)But no matter what I do, I can't remove the duplicates in the names of the speakers.Airtable considers that the 2 or 3 speakers on that each training session date are different arrays.I tried the « arrayflatten » and « arrayjoin » fonctions (and a combination of these) but nothing works.Do you have any idea to ge me solve this ?Thanks in advance

Hi, I’m trying to add a formula where I divide one column by a number. Like: Column A: 5000 Column B: result Formula: Column A(5000) / 365 Any idea? Been trying to find a solution to make this work…really don’t want to go back to excel… :sob: :frowning: :sob:

Hi everyone, I am new to the Airtable and I'm excited to be part of this community. May I apologise in advance if I don't use the correct technical terminology.  I design surveys, impact assessments and evaluation tools for a criminal justice charity. I have created a survey using 'Forms' and in one section I have 10 questions that require word responses. For example one of my questions is 'When I am confronted with a problem i can usually find a solution'The multiple choice response can be 'Not at all true' [=1]. Barely true [=2]. Moderately True [=3]. Exactly True [=4]I need to create a formula for a separate column that will tally up all the numerical values and give me the overall score. Any advice on how I write this formula will be greatly appreciated. Cheers Justin    

Hello,Struggling to work out where I'm going wrong. I'm trying to use a formula to auto update the "next check date" of equipment based on the date it was last checked and a single selection box.Eg. Harness checked 1/1/24Check frequency MonthlyNext check date 1/2/24My current formula is as follows: IF(  {Check Frequency} = "Annualy",  DATEADD(    {Check date}, 1, "year"    ),    IF(      {Check Frequency} = "6Monthly",      DATEADD(        {Check date}, 6, "months"        ),        IF(          {Check Frequency} = "Quareterly",          DATEADD(&

Hi! I have data coming in from a webhook to Make. I am trying to create a formula in Make to search Airtable for number of times an email appears in a field on a table and limit the number of submissions to Airtable. For example, I want to limit the rows to 5 for this user based on email. When the user submits data via the webhook, I want Make to do a search on Airtable and count the number of time the email appears in the table, if email appears less than 5, allow data to be submitted. If more than 5, give an error or prevent data submission. Here is the formula I used. 8.FIELD__KPZIT488X is the value from the webhook for email."{{8.FIELD__KPZIT488X}}"={Email}I have tried to do this with forms, but no luck. Trying to see if it can be done with formulas on the backend.