Formulas

Here is my base I am developing to track VFX shots for a film.Table "SHOTS" is the shot in the film. "Submissions" is the version I get from a vendorhttps://airtable.com/appfa9Uq0xYTTe5oK/shrH1DETOqdRkJUjT/tbl299qf49ykQMspW/viwjFl7u86veCddkU?blocks=hide Ideally I would like to be able to load all the versions in to an array then return the latest comment when I am reviewing the latest version. The array should be sorted alpha numerically. I have been trying with little success to use ARRAYSLICE(ARRAYCOMPACT(values)), -1) Can you do this with the formula, rollup field or do you need to script this?

Hey all, I could use some help and I'm hoping this isn't too difficult. I am trying to add a formula that compares an event date to the date of the next time change. If it is before the date of the time change, it runs one formula, if after, it runs another formula. I thought this would be simple enough but I keep getting an error. The formula I am using is below:  IF(      IS_BEFORE({Session Date},03/10/2024),            "A working formula"),IF(      IS_AFTER({Session Date},03/10/2024),           "Another working formula") I know that the "working formulas" are correct because they work in other fields. I'm wondering if I need to format the date in a particular way or add a date field instead of using the specific date in the IF statement? Thanks in advance!

Hi! I'm trying to set up a formula for a "Production Slack Reminder" that would be based on the "Send Date." If the "Send Date" falls on a Tuesday, Wednesday, Thursday, or Friday, the "Production Slack Reminder" should happen the day before the "Send Date"If the "Send Date" falls on a Monday, Saturday, or Sunday, the "Production Slack Reminder" should happen the Friday before that weekend

I am struggling to add a holiday string in an existing worback formula that changes the date based on another field. I applied Workday successfully but cannot apply {Holiday String}&"" SWITCH({Channel Size},'Large Email', WORKDAY(DATEADD({Launch Date}, -16, 'days'), -1),'Medium Email', WORKDAY(DATEADD({Launch Date}, -12, 'days'), -1),'Small Email', WORKDAY(DATEADD({Launch Date}, -8, 'days'), -1))I found two posts about adding a holiday string from a lookup field {Holiday String}&"" and still cannot get it to work.https://community.airtable.com/t5/other-questions/referencing-holiday-string-in-a-formula/td-p/135086https://community.airtable.com/t5/product-operations/feature-request-workday-holiday-sets/td-p/144717 

We have a form our users fill out that requires selecting a few entries from a long list of items, which we've managed to clean up by storing items by organization name--each organization has its own multi-select field, and they're conditional upon that organization being selected, so the form works nicely on the front end.On the backend, we have a bit of a mess. There are nearly 100 organizations, and every one of them has a field. In order to easily collect the items that users are selecting, we have a formula field that concatenates every organization field. It's basically:{AA} & {BB} & {CC}which returns the selected values in whichever field.The problem is that the multiselect fields don't all behave the same with the concatenation. Sometimes they produce a comma and space between them. Sometimes they don't. My goal is to clean this up for our user facing team in our automated email notification to show which items they've selected in a nice, bulleted list.Any ideas?

Hello!I am looking for a formula that will update on a month basis to pull data for a meeting.I have a few dates I want to pull from - Target Start, Target Completion Date, and Dev start. I would like to label any records that have a date in the upcoming month (ex. March as our review is the last week in Feb) to be labeled as "Now." Then any content that has a date falling in the next 3-months to be labeled as "Near" and all remaining to be "Next". Any thoughts?? The date ranges for Now and Near could overlap, meaning it is being worked on over a longer period of time and should also reflect as Now.Thank you!!!!

I have 2 linked tables, Tasks and Sub Tasks. I want the Sub Tasks' status to define the status of the Task. I currently have a rollup that makes an array of all the statuses in a given Task's Sub Task. Then I have a formula that reads these arrays and creates a status. ITrying to create a formula that fulfills the following requirements:If all subtasks are "Done", the task status should be "Done".If any subtask is "In Progress", the task status should be "In Progress".If any subtask is "Done" and at least one subtask is "To Do", the task status should be "In Progress".Otherwise, if none of the above conditions are met, the task status should be "To Do".I am not much for coding so I explained this to ChatGPT and it gave me the following code. IF( BLANK({All Sub Tasks- Status}), "To Do", IF( FIND("In Progress", {All Sub Tasks- Status}), "In Progress", IF( AND( FIND("Done", {All Sub Tasks- Status}), FIND("To Do", {All Sub Tasks- Status})

I create a form (interface in Airtable) to collect information from user and store in "Table A". Output fields include "Age", "Location", "Claim", "Special", Compo". I have another "Table B" to have above 5 fields and addtional "diagnosis value" field. Table B is combination of  different conditions of "Age", "Location", "Claim", "Special", Compo" and output of "diagnosis value". My question is how to set up formula to link up Table A and Table B which can filter the result of "diagnosis value" according to User input in Table A under different combination of "Age", "Location", "Claim", "Special", Compo".If you have any idea, pls. share your thought. Thank you.

I am working on a formula that1. Trims2. Replaces Double Quotes (") with Apostrophes Quote (')3. Replaces Ampersands (&) with 'and'.TRIM(SUBSTITUTE(SUBSTITUTE(DESCRIPTION, '"', ''),'&','and'))The above works, except, if I change it to include the Apostrophes Quote ('), it gets an airtable error.  Any advice?Kevin

I struggling to correct a formula where I want the return value to appear based on IF AND statements.Goal I am trying to achieve:I DO NOT want the phone emoji to appear in the “follow-up” column if the “lead score” is below 5 (Eg: 0, -5, -15).Here is a short Loom where I walk through what I want to fix: https://www.loom.com/share/82b6cdb91b12403d96fdb047760e01e0?sid=bdca5f77-58d9-478e-bfbd-6395874742dcBelow is my current formula:IF(DATETIME_DIFF(NOW(), {📧 Inquiry received}, 'days')>=90, IF(NOT({🎉 Date Won}),"📞 ",""))What I want the formula to say:If the “inquiry received” date is greater than or equal to 90 days, if the “date won” is empty, and if the “lead score” is greater than or equal to 5, then the “follow-up” column will return a phone emoji.Screenshot with notes:Current formula:I DO NOT want the phone emoji to appear in the “follow-up” column if the “lead score” is below 5 (Eg: 0, -5, -15).

Hey,I imported my contact database into a table and am trying to separate these years so that "2019; 2020" would turn into multiple datapoints that can be filtered accordingly like "2019, "2020" etc. Is there any easy way to do this? Thanks!

Hiya! I'm relatively new to Airtable and need help with this formula. I need to find a formula that displays the date for the Friday of the week before the start of the next month based on a date in another field. Would also love to know what each element does so I can learn a little too rather than straight copy/paste. Thank you so much!

Hoping someone can guide me with setting up a formula to score tests that are reported on another table.Here's the current setup:Table 1 - record of all test takers by session ID, including fields for all of their question responses.Table 2 - question key  - records of all questions used in exams including a field for the correct answers.Table 3 - scoring - how can I set this up to pull response data from table 1 and cross-reference against the correct answers on table 2 and then display correct or incorrect, 1 or 0 etc for each right or wrong answer?Table 3 would then also have separate fields that would tabulate the raw scores for each section and covert them accordingly.  It's a relatively easy formula on a spreadsheet, so I thought it would be easy here as well, but it looks like Airtable doesn't support combining formulas with lookups?  There are over 100 responses in each test session, so I'm hoping I don't have to replicate each of those columns as lookups to be a

How can I count the number of records in the list "Attended Session & Challenge" that are specifically only from 2023? The attendance is based on a form they fill in that is in a different tab in the same workspace. help would be very appreciated! Thanks. 

we have a problem with the connection of softr and airtable. We have a part in softr where people can add new data and we need that data to be in airtable. However, in airtable the place where the data should go to is linked with other tabs.Since it’s a linked column softr doesn’t understand it. Is there a way for us to make it so that softr detects the linked column and when a new data is added it is shown in the correct place? or exist any formula that let us to copy automatically cells like in Google Sheets? 

Hi - I am having a heck of a time figuring this out.I have a table called "Days" that holds different tasks with various dates. Some tasks have the same date.In a separate table called "Crew", I assign the Tasks to the crew, and can rollup the linked records to only return the unique dates. However, when I try to count these, it adds the tasks up on the same date, even though I only need it once.Example:DAYS TABLE:Ben's Birthday - 3/9 Pickup John - 3/9Brenda's Birthday - 3/10 CREW TABLE:John - Linked to Ben's Birthday, Pickup John, and Brenda's Birthday RecordsRollup field: Arraying unique dates, only return 3/9 & 3/10 as desired. However, when trying to count unique dates, it is still counting all the records even if they have the same date. So it returns 3. Help!! 

Hi everyone, I'm trying to implement the following Excel SUMIF Formula: SUMIF('Tabella raggr'!B:B;T2;'Tabella raggr'!D:D) Basically I have to add up all the values in column D where the corresponding value in column B matches the value in T2.In the screenshots below you can find the B and D columns of the 'Tabella raggr' table and the T2 which is the criteria of the SUMIF function and it's on another table called 'SAP'.

Hi. I am trying to construct a formula in a Make.com search records module. The module is the trigger and I've been trying:AND({Ind Doc}!=BLANK(),(DATETIME_DIFF(NOW(),{Record Created}(),"days")=14))('Ind Doc' is an attachment field)Any help would be appreciated. TIA. 😊

TIA - this community is amazing and so helpful when I can't get my head around these more tricky ones. but I am learning more and more! I have a bunch of method steps for our recipes:COOK THE SALMONPat dry salmon with paper towel, remove the skin and cut into bite size pieces, discarding the skin. Add salmon, olives and courgette to the pot. Simmer, gently stirring occasionally, for 5-6mins, or until the salmon is cooked through and all the veggies are tender. Remove from heat. Check seasoning. "MAKE THE AVOCADO DIPCut lemon into wedges. Halve avocado, remove the stone and scoop out flesh with a spoon. Place avocado into a third medium bowl and mash with fork until smooth. Mix in aioli and (1/2 tbsp/1 tbsp) lemon juice. Season with salt, pepper and more lemon juice to taste. Set aside.TIP: If you prefer, add avocado to the salad in STEP 5 and serve aioli on the side. "MAKE THE HERBY VINAIGRETTEFinely chop (half/whole) pack coriander, including the stalks. Add to small bowl with (1

Hi everyone, would you please tell me how to convert a multiple date string field (i.e., 2/11/2024, 3/3/2024, 2/19/2024, 2/25/2024) to the correct format date field that reflects all the dates in the string field? Please note that the string field mentioned contains the date values based on a lookup function from its source field or table. Thank you.

I'm trying to build an IF Formula that seems like it would be straight-forwarded, but is proving to be more complicated than I thought it would be and has got me stumped. Both fields are Lookup fields getting a Rollup value from another table. I started with a basic IF formula, but the problem is it resulted in X $0 values being replaced by the Y value, when I wanted the $0 X value to be returned.IF(X,X,Y) Then I tried to a formula to say if X is equal to or greater than 0, and that worked, but it would not return Y if the X Column was blank. IF(X >=0,X,Y) So, I tried an IF / Or Formula. It still returned the X field correctly, but did not return the Y Field if the X field was blank. (Update: I originally wrote this wrong when I posted. I was trying to say if X is not blank & is equal to or more than 0, return X, otherwise return Y. Also tried with X !="" instead of Blank() - same result).IF(OR(X >= 0,X != Blank()),X,Y) I'm not sure why this is being

Hi all, I'm working on a donor management system for a non-profit org.  Donors make pledges to give $x on x recurring basis, usually something like $50 a month.I've got a table for Donors, one for pledges, and one for donations.I need to have a way to check whether pledges are being fulfilled by donors.  So if John Smith pledged $50 a month, has he actually sent $50 a month?But how do you search for what's NOT there???  I am stumped on how to approach this one!Any thoughts???

I have a table where different group clients submit weekly weights on a form.I'm calculating "starting weight - weekly weight = X". With each submission, I send them an automated email that records what their weight loss is, BUT even though their weekly weight loss differs, I want to only send them the max weight they've lost of all the weeks submitted.e.g. starting weight - 200lbsweek 1 submission = 194 (6lbs lost)week 2 submission = 196 (4lbs lost)week 3 submission = 197 (3lbs lost)So their record will continue to pull only the "194 (6lbs lost)" until a higher weight loss is submitted. How do I do that? The rollup fields are not working for me - or am I missing something! The current rollup field is reflecting all 0 but there are no zero entries.

I want to separate the following text in different columns :Column A : @Ben11//test-bug//Feb 22, 2024 3:54pm UTC//Budget//Test again//Test again//ModerateColumn B : @Ben11Column C : test-bugColumn D : Feb 22, 2024 3:54pm UTCColumn E : BudgetColumn F : Test againColumn G : Test againColumn H : Moderate

Hi!I have a countdown for our warranty cases, which needs to be completed in the 45 days or less. Our customer fills out the Form, and the countdown stars when the Form is submitted. So the first field is "Form date" DATETIME_FORMAT(CREATED_TIME(), 'D. M. YYYY')The second field is "Days" DATETIME_DIFF(TODAY(), {Form date}, 'days') And the third field is "Countdown"45-{Days} This works fine, but the problem is, that when the status is "sent" or "refund" the countdown don't stop. So the point is, that I want to know how long one case was open. I can also use just one status, let's say "complete" but now I stumble upon one problem.I don't have a specific date field for the end of the case (it depends on the case - some wants refund, some wants exchange or repair). So in this case I have two date fields (date of a refund and date of a shipment). So it's little complicated. Hope someone knows the anser to my problem. 🙂Have a nic