Formulas

I've been trying to automate my CRM to automatically update the date of my last interaction with someone by linking my Outlook calendar meetings to records of people in my "People" table and then have the most recent meeting date show up in the People table under "Last Interaction".I tried adding a People column in the Outlook calendar table linked to the People Table with a lookup record to search for the contact in the attendees list based on their Primary Email but it's not pulling the people.Ultimately, I am trying to have the Calendar table search for the attendees based on their email in the People table, then update the records for the associated contacts column for "Last Interaction" in the People table.Any ideas on how to link the tables and automatically update the date of our last meeting without having to manually add the attendees?

Hello everybody! I am trying to create some buttons in a table with a lot of different information. It looks like this:   The issue is: right now every button leads to the same URL. I want each (or at least) some of the buttons to direct to other URLs within the same table. Isn't that possible?

I was setting a "Happy Christmas" automation. I could have set the automation time trigger to "YEAR-12-31", but using the last working day as a trigger is better. Firstly I searched on the community for this kind of formula. I could not find. So, I wanted to share with you.The weekday number of the 29th day is important, since if it is friday, this day should be trigger date, since 31th is sunday, etc. Also, if you have a better formula or find a problem in my formula, please share your comments. IF(AND(WEEKDAY(YEAR(TODAY()) & "-12-29") = 5, YEAR(TODAY()) & "-12-29" = DATETIME_FORMAT(TODAY(),"YYYY-MM-DD")),1, IF(AND(WEEKDAY(YEAR(TODAY()) & "-12-29") = 4, YEAR(TODAY()) & "-12-30" = DATETIME_FORMAT(TODAY(),"YYYY-MM-DD")),1, IF(YEAR(TODAY()) & "-12-31" = TODAY(),1,0)))   

Hello, from a booking days main table, I create a view for records filtered on the next day called D+1. Then, I create a "Tomorrow" table with one single row linked to the main table to make a summary by rollups of certain datas : clients list, quantities, and so on. For this link field, I choose the option "limit selected records to a view", which is the D+1 view. So I guess I’m just going to summarize the D+1 view filtered records, but... No ! The rollups summarize the ENTIRE main table without taking into account the view filter !I am not looking for a solution because I have already found others but I do not understand what the "limit selected records to a view" option is usefull for since this limit is not taken into account by rollups. Any ideas ?

Hi, I'm trying to merge two columns that have attachments. I have 1 column that includes the attachments where the user is the primary contact (1- Attachments- Purple Due in one week), and another one where he/she is the secondary contact(2- Attachments- Purple - Due in One Week). I need to merge those columns to create a "total attachments" column for users.The attachments are "white" because are just white pages of word (for now).I tried to use ArrayJoin, Concatenate and also &, but the result is the same.How can I solve this problem?? Thank you very much!  

Hi dear friends.I would appreciate your help regarding time differences.The clock should be +2 as you can see in the picture but for some reason when I do the formula it takes me back 2 hours and I couldn't figure out how to use the timezone formula.I would appreciate your help on how to write the formula correctly so that it works.Thank you! 

I thought I was being clever but my formula did not work. Cells are blank. Why?SWITCH(  {Decimal},  "0.75",  "3/4",  "0.50",  "1/2")

Hey friendsI have a column of customer full names.I would like to separate this column into 2 separate columns - one for first names and the other for last names.I used the following formula (screenshot attached) to separate the first and last names, but there is a problem:If someone entered only their first name without a last name, the column remains empty and I don't understand why.I would be very happy for your help in this matter!Thank you 

I am new here, so please forgive me if I ask a question that might have been asked before.I keep track of my software subscriptions with Airtable, some are monthly based and some are yearly. Therefore, I created a single selection field with two options (monthly, yearly) and a date field that shows when the subscriptions are due. Now I would like to automatically update the due date after it has passed under the condition of monthly or yearly. I had something in mind like: IF ({subscription} = "yearly", NOW(), DATEADD( *1, 'year')({subscription} = "monthly", NOW(), DATEADD( *1, 'month')I guess it's terribly wrong, since it never worked 😭 

Hey friendsI have a connection to Airtable that transfers every new lead to my table.I have a date column and an hour column, but I don't know how to separate it, so every time a new lead is updated, it is only updated with the date and the hour remains empty.How can I separate them so that both columns are updated with the relevant details?Thank you!

Hey, I have been looking for a while and I'm stuck.I am organizing group calls with 3-6 people per call.  After each call I want to each participator to fill out a form, giving feedback for each other participator on that call. The challenge I'm running into is how to collect feedback for each participator from each other participator. I went down the rabbit hole with prefilled forms, but I cannot connect the actually rating to each person. Maybe I've just hit the limit for airtable forms, but I'm curious if I'm missing anything obvious. Happy to explain more if this is unclear (however I was reluctant to share screenshots with personal detail)

Hey All,I'm mocking something up and wondering if I've designed these tables incorrectly.I'm trying to create a connection between two tables (1. "Adjustment Funding Matrix" and 2. "All Adjustments Info," and specifically the "Policy Threshold" and "Final" fields both of which are in this latter table.)My goal is to make it so that based on the thresholds set by the "Adjustment Funding Matrix" table, depending on the financial amount in "Final," I'd like to make the "Policy Threshold" field in "All Adjustments Info" display the information in the corresponding "Policy" column from "Adjustment Funding Matrix."Is that possible the way I've set this up through a conditional relationship, a trigger, or something else? Thanks.

I have a table containing info on contacts. My goal is a list of e-mail adresses from that table.The wanted e-mail adresses will typically be a subset from the main table. Here are among other things two fields saying something about every contact and these fields are the type "Multiple select".I would like to make a formular by which one can decide the wanted subset of e-mail adresses and then get them as a kind of list to be easily inserted in the to-field in a mailproram. The choise should be made with the mutiple select-fields.Does anybody have ideas on how to do that? Any hint will be deeply appreciated!

Doing a simple add 2 or 3 days to a start date. DATEADD(Start,3,'days')   ---Note: the field names are purple but no longer add the {  } ??? I tried both ways. That adds just fine, however, when I change the 3 to a lookup field called Days formatted as integer it seems to subtract a day. or not any at all with different variations. DATEADD(Start,Days,'days')Can a number be pulled from a lookup field be used like this or do I need to cast the value somehow to a #Numnber format? When I use a field in the same table that is a number (integer) it works as expected. DATEADD(Start,number,'days')  I've tried adding in timezone same for all using GMT and America/New York UTC -5. same issues

Hi, I am stuck with a riddle ) I'm a newbie still don't know much. Any help is highly appreciated.I have several tables:1. Customers2. Products3. Currency4. Warehouse5. Accounts6. Operations8. Operations_Detail9. Acc I have two things that I need:1. when I create sales invoice in Operations, I need to create several records in operations with same AnalyticTag (column) as in created Invoice, but different Expense category (column).1.1 These records must be either profiled, or either from template - but this is different story I guess. 2. Also, when I create customer or Accounts, or add new currency - I need to create rows in ACC table. For example, I have Customer 1, Account 1 and Currency 1, 2, 3What I need is, when creating customer or account record, or adding new currency record, have a table in ACC like this:row 1 customer 1 currency 1row 2 customer 1 currency 2row 3 customer 1 currency 3row 4 account 1 currency 1row 5 account 1 currency 2row 6 account 1 currency 3&n

Hi everyone,I need urgent help for something I can't solve. It's like search function in excell.Job to be done:- If EAN of Record 'x' in table 2 =  EAN found in a record in table 1 THEN "Handle record" in table 2 = Handle record in table 1.Here is a screenshot of Handle2 that needs to be filled in with the correspondant "handle" in another page.To search for it, i am looking for same Variant BarcodeOn the page ONBOARDING, we have the variant barcode, and the right handle we want to copy in the first page with same ean.  Thank you for your help. It's very important for us.BestMichael 

Hello,I have a Lookup field called "Last Update" linked to a Last Modified Time field. I have created a formula that return minutes of difference between my "Last Update" field and the actual datetime.I would like to return this value into another field that is not a formula (maybe i'll use a number or a single line field).Can someone help me to do this ?Thank you !

Hi All,I have a Calendar table within a base that we use to assign calendar events to Airtable users. We also share the calendar link with everyone so the calendar will populate on their iPhone calendar and such.  Because of this, we want to show what event is assigned to which user in the calendar event ID (labeled "Event" in the screenshot below).  We currently do this with a concatenate formula but it will show each users full name, which makes the Event ID very long.  Current Event formula is as follows and works fine: CONCATENATE(IF({Tentative?}=1,"(t) ",""),Project, " ",{Service Call}," ", RMA," ",Title, " ", {User (Assigned To)})In short, I can not find a way in which to swap out each users name for their initials in the form of custom text (not just taking the first and last initial from the users name since two users have the same initials "JR").  I am looking for some sort of formula to insert into my existing {User (Assigned To)} spot in the for

Can someone help me out.  I'm want Airtable to return the "Column name" after he compares 4 adjacent cells in the same row. I expected to be something like...if(@A>@B AND(@A>@C) AND(@A>@D), "A")if(@B>@A AND(@B>@C) AND(@B>@D), "B")if(@C>@A AND(@C>@B) AND(@C>@D), "C")if(@D>@A AND(@D>@B) AND(@D>@C), "A") Thanks in advance!

Hey guysI have a brain drain problem with how I should set up a dynamic date formula, I have 2 tables in a base, one for odd weeks and one for even weeks. They both just contain 5 records, one for each workday. I have separate fields where the current date is shown, current weekday, current week number and if the current week is odd or even (it shows 0 for even and 1 for even). Now to the tricky part.I want to show in a field in the even table (week 50), lets say today Wednesday the 14th of dec. I want todays date to be visible in that field, on Tuesday the 13th should be visible and on tomorrow the 15th and so on. And I also want if the dates in the odd table shows next weeks date, and then when it becomes an odd week the even table updates the dates to the week after the odd. Any of you geniuses have a solution in mind?

Hi airtable communicty 🙂Here is a small challenge I face.I created an inventory for an auto shop. How to show the last price added for the product? Example: Product AA - we bought for $10Latter with $30And after a couple of weeks price decreased to $20 Now I can make only average or max. Here is a screenshot how I made MAX value added for this product.I found a formula like LAST_MODIFIED_TIME() But I don't know how to use, to display last price of product addedThank you  

Hello Airtable friends,So I have finally sent out my customer surveys and now I find that I forgot to find a way to "score" the responses!?!So, I have a Customer Satisfaction survey that has several "3-option" answers along with several "5-Star" options. No I have collected their responses but don't know how to "score" them!What I'm looking for is "IF('previous questions name'=first answer,1,0)Am I at least headed in the right direction here??As always, THANKS!dg

I'm trying to detect overlaps between my records and display data from the respective other records they're overlapping with, but am struggling yet again with creating the right regular expression for the REGEX_EXTRACT. Currently I'm...Creating an identifier field that helps recognize overlap (where identifier is equal = overlap).Creating an data field that includes the identifier and additional data that I want to display.Roll-up the data fields related to the linked record. Look up the roll-up on each record.Apply a REGEX_EXTRACT to the roll-up to extract the data I want to display. IF(List,REGEX_EXTRACT( List&"", "(?:" & SUBSTITUTE(Identifier,".", "\\.") & ")([^,]*)" ) ) Result:When record A & B overlap, it correctly shows the data of record A on record B and vice versa.When there's no overlap, it shows it's own Data and I cannot figure out how to avoid this. I'm trying to exclude itself from the list.To show multiple overlaps, I th

Hi, Im struggling to improve my data base and i ve found something that i don´t even know if its possible to be done. I have a formula field which shows a check , made with IF function, depending on the conditions of other fields. But the issue is that sometimes, i need to show a check also when those conditions are not fulfilled, i mean to edit it manually. The thing is that only happens on some records that fulfill a determined condition, so my idea is to look for that condition on the corresponding field, then allow the manual edition, and then count those records in the summary count.Is that possible?Thank you very much in advance.   

My data record structure{    "id": "rec6CtSsmlVJe48Uj",    "createdTime": "2022-12-07T11:55:53.000Z",    "fields": {        "VOTES": 5,        "ID": 2,        "CONTENT": "....",        "AUTHOR": {            "id": "...",            "email": "test@",            "name": "test"        }    }}How can I use AUTHOR.id I wan filter by ID and AUTHOR.id in one queryID=1, ID=2 and AUTHOR.id=5How can i write it?