Formulas

Ok so I’ll try to get this all straight. Obviously the goal is limit the number of times records have to be linked, I’m hoping someone can offer a different perspective. The Background: Everyday we send out an email record from our table REPORT. Report is basically the combination of the BREAKS table, which can have 1-4 every day. BREAKS is composed of mags from the MAGS table, each break usually has between 1-10 mags. Each Mag has NOTES from the NOTES table. So thus far its been going great, you make a note, use a lookup to tag the MAG table, adds note to each mags, then the Mags is tagged to the Break table where all the mags with the all notes get rolled up, which in turn the breaks are all tagged to a single day and at the end of the day all that data rolls up into a single email report which is in HTML and uses lookup/rollup tables from all the above to fill in proper data. The Problem: Mags started sharing the same notes, issues that occurred on both mags

Hi all, I love the visual usability of Airtable, but am restricted by a total lack of understanding formulas (or Excel!). I’ve read all the Airtable guides, but can’t figure out my specific request and the formula needed, or whether it’s a formula I need or something else! Apologies if stupid question, but: I’ve made a table of content for social media and other media, listing content title, type, platform to post on, due date. Currently, it looks like this: Name . Content Type. Status . Due Date . Gifs for Facebook . Creative Idea 31/8/2019 (Imagine details filled out further, I can’t link to or insert a screenshot) I am trying to work out a way to group these under three groups - either ‘This Week’, ‘This Month’ or ‘This Year’. I’d want the entries to be automatically populated into these groups dependent on the due date I manually enter. I

Hello! I was hoping someone could create a formula for me. I am new to the formula area and what I want to do is FAR beyond my brain power. Wanted to know if this type of formula was possible… Fields: “Date” (which is a date field) “Day of Week” Field (which is a formula using DATEFORMAT to get the day of week) “Complete” (which is a Check box) The formula I am hoping for would replace my “Day of week” section: INCLUDES: • Dateformat to show date of week • IF statement that says “Next Week” if the date is next week • IF statement that says “Completed” if the Complete box is checked I am creating a task list and I want this all in one field, so I can group. I want the tasks to be organized by the day of the week, for it to say “Next Week” if the date falls in the next week, and for it to say “Completed” if the box is checked. Is this possible? Your help is MOST appreciated! Thank you!!

I have asked my guests to enter mobile number and name in a form. Now in my base, the mobile number will be a unique value that will be used for linking to another table. But I can’t link a column with a formula (concatenate) & if I convert phone-number or a text column into the linked column - then a new guest will not be able to enter data in the form but will have the option to choose someone else’s number only. Any suggestions?

Hi all, I am creating a Landing Page testing and analytics tool. Basically, on a table called ‘Components’ I have taken all components you would expect in a landing page and divided them into records, organized by their type (for example, “Hero Unit”, “Benefit”, “CTA”, etc…) Then, on a separate table called “tests”, I link components into records, with five sections linked to components (labelled ‘Top Section’, ‘Section 1’, ‘Section 2’, etc…). I then compile components into various sections (sometimes randomly, sometimes planned). These basically run as tests through a CMS that allows me to create lots of iterations of landing page. I gain some basic analytics (visitors vs. conversions) that I then want to be back traced to the original component (as analytics will only be for the whole page, not for sections of it). In this way, I’m hoping every test I run (and have conversion numbers for) will help me understand which components are most greatly respons

Hi Would really appreciate some help creating a formula. I am trying to run (modernize) a non profit social society’s events calendar / booking system using airtable. I have 3 tables in the same base. Table 1 (Master) - This has a sign up form linked to it for people (both members and guests) to can sign up for the events (single or multiple). The selection of events is linked to the (Events) table. The sign up form has a check box for users to select if they are Members. Table 2. (Events) - this is the calendar table with date and event name. (separate columns) Table 3. (Members) - List of members with their names and membership numbers (and other membership data) (all in separate columns) If a member signs up for specific event, I need a formula to look into the (Members) table and compare their names and return their membership number into the master table. Appreciate any help people can offer? Craig

Hi, I’m trying to get one field to return a value based on the number in a different field. I’ve tried both uploading and linking to a screenshot but it’s not allowing me to do that. This is the Nested IF formula I wrote: IF({Number of ELLs}<21,“0-20”,IF({Number of ELLs}>20,“21-50”,IF({Number of ELLs}>50,“51-75”,IF({Number of ELLs}>75,“76-100”,IF({Number of ELLs}>100,“101-300”,IF({Number of ELLs}>300,“301-500”,IF({Number of ELLs}>500,“501-1000”,IF({Number of ELLs}>1000,“1001-1500”,IF({Number of ELLs}>1501,“1500+”))))))))) The formula won’t return any values other than “0-20” and “21-50”. Does anyone have any insight on why this might happen?

I’m struggling to get the desired results using the Substitute formula. Maybe I need to try the Replace formula. But thought I’d ask here in the meantime. I have a list of tasks. Each task starts with a client code and project code. How can I write a formula to update the client code and project code for each new client/project? For example, if the one task is “CLI-PRJ-Send thank you to client.” I would like “CLI” replaced with “4CR” I would like “PRJ” replaced with “Blog” So the new task would be “4CR-Blog-Send thank you to client.” Thanks in advance for help on the topic!

Hi I need to get results in days : hours bettwen to dates. I have {date start} , {date stop} and when {date stop} fincion should count to Now() Funcion IF({date stop}="",DATETIME_DIFF(NOW(),{date start},‘hours’)/24,DATETIME_DIFF(date stop},{date start},‘hours’)/24) It shows results in decimal how many days but now in DD:HH I tryed: DATETIME_FORMAT( IF({date stop}="",DATETIME_DIFF(NOW(),{date start},‘hours’)/24,DATETIME_DIFF({date stop},{date start},‘hours’)/24),‘DD:HH’) but I get ERROR Any advice ?

Hello all! Tried searching, but I couldn’t find anything that answered my question. I believe my formula is correct, and it doesn’t throw an error, however, every time I input it from Notepad ++ it limits it. Basically I’m trying to assign point values if certain criteria are selected from a multi-select form. I was able to convert it to a stringify data. So there are nine options. Option 1, Option 2, and Option 3 are selected. 15 points Option 1, Option 4, and Option 7 are selected. 10 points Option 6 and Option 7 are selected. 5 points Option 9 is selected 0 points. Etc. I’m okay with doing multiple, nested IF formulas, but again it limits it? Any reason why? Thank you!

I’m sorry if im not clear enough i’ll try my best to explain what i want to achieve. I have a table called Courses that contains a list of courses in row (like Math, Physics… etc) Each Course have a date (like 1/1/2019) and Students can register to a class (through form). a record will appear like (separating column with ‘|’ symbol): student1@email | Math | 1/1/2019 but students can register to multiples class: student2@email | Math Physics | 8/8/2018 1/1/2019 I wish to list students that attended the Math class on a specific date like: student1@email | Math | 1/1/2019 student2@email | Math | 1/1/2019 and not student1@email | Math | 1/1/2019 student2@email | Math Physics | 8/8/2018 1/1/2019 Using a filter with “not Physics” will remove the entry of student2 If i click on date entry 1

I have an Airtable with thousands of rows, all of them with unique links. Each link has an associated author who has a Username and Email. In a view, I’m grouping by Username and then I see multiple entries for that same Email. That’s fine, but my goal is to see only one entry for that Email, for that Username, in this specific view. How can I go about achieving that? Thanks!

Hi all! I’ve run into an odd issue that I’m hoping someone out there has found a workaround for :slightly_smiling_face: I have a table consisting of recurring tasks, where each task is assigned a value ($). A zap creates assignments based on those tasks and values in a separate table. I need to be able to override the value of the task in the new table; which is pretty straightforward… but I’m running into issues if I want to override the price to be $0.00. Airtable seems to treat Currency fields (and probably number fields too, haven’t tested it) the same whether they are BLANK() or have a value of $0.00. Here is the formula I’m using (in a rollup field): IF({Override Task Price}, {Override Task Price}, SUM(values)/{Step Count}) Has anyone found a way to force Airtable to recognize ANY entry in a Currency field, even if it’s $0.00?

I have a rollup field (or lookup field) that usually contains an array of 8 numbers. I would like to have a formula that gives the sum of the smallest three numbers. Right now, it seems that it is impossible without an external application (Zapier). Are there any array formulas that would help me to achieve that? Note that these are numeric values and don’t necessarily have the same “length”.

Hi All, I am trying to go around this nested formula and hope someone could help me try understand why is not working. I have three {columns} that indicate if a condition is either 1 or 0. So: column 1 can be 1 or 0 column 2 can be 1 or 0 column 3 can be 1or 0 I have a fourth formula column where I want to indicate the following: IF column 1,2,3 are all 1 then show “status A” IF column 1,2 are 1 and 3 is 0, then show “status B”, IF column 1,2,3, are 0, then show " status C" I have tried this formula but it wont go through: IF( OR( FIND(“1”, {column1} ), FIND(“1”, {column2} ), FIND(“1”, {column3} ), ), “status A”, IF( OR( FIND(“1”, {column1} ), FIND(“1”, {column2} ), FIND(“0”, {column3} ), ), “Status B”, IF( OR( FIND(“0”, {column1} ), FIND(“0”, {column2} ), FIND(“0”, {column3} ), ), “Status C”, )

Hello, I’m trying to make a formula that counts the difference between start date and end date, however if end date is blank calculate the difference between start date, and current date. Sorry if it sounds simple, i’m very new to airtable! Thanks Matt

When entering a new record were I have sort perimeters, the new line covers data already entered, as I am entering the new record. I need to see the previously entered line while entering the new record… Is there a way to stop this? Tony SIms

Hi I am trying to add a formula that will show a renewal date 3 years from the subscription date (yr 1) but not show anything for implementation period or yr 2, yr 3. IF ({Impl /Sched Fee} = Subscription Yr1), (DATEADD({Inv Date}, 3,’ years’), "None "))) I continue to get errors on this formula

Hello, I have a table of People. Each person is linked to a Property via a lookup field. I want to add a field to the People table that assigns them a value based on which Property they’re linked to and when their Date Created is. So for example: for all the People in the table who are linked to 123 Main St, I want the first (by Date Created) 100 records to have the value of 1, the next 100 People records to have the value of 2, and so on. Does anyone have any ideas for how to go about doing this? Thanks so much!

As a relative novice with IF Formulas, I am hoping for help with the following. My table has columns for First Name, Last Name and With First, With Last. I am using Airtable to track RSVPs and would like a Formula field that provides the following result. When Last Name is not blank and With Last is not blank, enter 2. (John Smith is bringing Sally Smith, so 2 people are attending). When Last Name is not blank and With Last is blank, enter 1. (John Smith is coming by himself, so 1 person is attending). I appreciate any guidance on how to achieve this result. Thank you!

Hi! I am trying to do a COUNTIF type formula but I cannot find anything to work. Not sure why I cannot upload images or links to this post. Basically, I have a lookup column that looks up info from another table and shows an array with emojis (checkmark, x, triangle); so, for example, the first cell shows :heavy_check_mark: , :heavy_check_mark: , :heavy_check_mark: , :x: , :heavy_check_mark: , :heavy_check_mark: . I want the second column to count how many green checkmarks are in the first column; that is, I want it to show the number 5. Any ideas?

I’ve searched the forum and tried to figure the formula out on my own but I’m not even sure if this is possible in Airtable. Any help would be appreciated. I have multiple columns where entries are either numbers or blank. I’d like to be able to generate a phrase that puts all these numbers and field names together as text. xfieldname is the name of the column. All cells in the column are numbers or blank. xvalue is the number in the column. Final field is text If xvalue > 0, return “xvalue xfieldname” If not, return blank [insert comma] If yvalue > 0, return “yvalue yfieldname” If not, return blank Final output is: xvalue xfieldname, yvalue yfieldname I’ve tried: IF({Set of Pins}>0, “{Set of Pins} set of pins”, “”) Which of course produced “{Set of Pins} set of pins” instead of the actual number in the column. Example: If the “pairs of socks” column has “2”, the “hats” column has “3

Hello AT Community I’ve got a formula partially working but I’m having some difficulty tweaking it to just the right look. Looking for numbers in the SAL field that result as a negative to show the emoji - so far so good. How do I make positive numbers in the ‘test’ field show as a percentage with only two numbers behind the decimal and with proper rounding up? Thanks in advance for your brilliance!

Hi, I have a table with 3 columns, “Usage”, “Clients” and “Status”. “Usage” is a single select and can only be “Platform” or “Client”. “Clients” is a link record that allows me to select multiple client records from another table. “Status” is a lookup field returning the status of each client from the client field, it displays the results in a comma separated list. Possible values for “Status” are “Live”, “Suspended” and “Terminated”. I need to write a formula that satisfies the following conditions: If “Usage” = “Platform” set the cell as “Active” If “Usage” = “Client” AND the value “Live” is present anywhere in “Status” column then set the cell as “Active” If “Usage” = “Client” AND the value “Live” is not present anywhere in “Status” column then set the cell as “Inactive” If “Usage” = “Client” AND the “Status” column is blank then set the cell as “Inactive” The formula’s I’ve tried so far don’t work. I’ve tried using IF(

HI, all! Looking forward to your kind help. My end goal is to sort out people who have attended 3+ workshops. I have a workshop business, and we have three main ticket types: Deposit, Remaining Balance and Pay in Full. I am working on a way to automagically sort our orders table to show only people that have attended 3+ workshops. That means either Deposit + Balance OR Paid in Full. I figured if I can make a formula that uses logic to see if Deposit and Remaining Balance fields both return “checked” then… something. And this is where I get hung up writing the formula. I’m sure I can then use another field or view to examine that result and the Paid in full to find a total quantity to be 3 or greater. Some people sometime pay in full, and sometimes put down a deposit and then their remaining balance. So I have to examine both fields to determine their total attendance count. Essentially, I am having a hard time achie