Formulas

Has anyone else found that the SEARCH and FIND formulas count unicode characters (like emojis) as two positions within a string, as opposed to one position. This means that both functions seem to return the incorrect index if the matching string starts after any unicode chars in the text to search. Short of stripping out any unicode characters, does anyone have ideas for other workarounds?

I have volunteer applications imported from 123formbuilder to airtable using Zapier. Some parts of the application have radial selections, where an application can choose a predetermined option, or they can choose “something else” and enter a custom value in a short text field that becomes available. When this data is sent to my base, there becomes two columns, one that populates with a primary selection and a second one that will either be blank or has the applicant’s custom response in it. I’m trying to organize this data into one column that either pulls from the first column if the second column is blank, or chooses the second column if it is not blank. What is the best way to accomplish this? I’m fairly certain I need an IF formula of some sort, but I don’t know enough to know if there is a better way. An example of this can be viewed here: https://airtable.com/shrje2Jcz0ww2BrP1 I can’t share the actual table due to all the personal information we collect but the sample tabl

Maybe its the new year but I am having a trouble trying to work this out. :slightly_smiling_face: I need a cell to work out 7 postal days from a specified date and add it to a number based on a date column. for example. date column has the date of today entered. 16th jan. 7 working days from today makes that the 27th Jan then it needs to add on another 14 calendar days and display the date it works out. Im scratching my head here! lol thanks all!

I have two tables. Table 1: Client Table 2: Appointments Each appointment is tied to a client. I’m looking to see how many days from the client’s last appointment. E.g. the client’s has 3 appointments however his last appointment was 1/1/2020 then the cell would display ‘14’ since that was 14 days ago. Any ideas?

I have a Project table with a Completed field which looks at a Final Date field and via a formula checks if today is past the Final Date field it marks the Completed field as YES for the Project record. I will be adding a trigger via Zapier to email someone that the Project record is complete. I need to add to the Completed field formula the ability to know if today is a weekend and if so to wait to mark the field as YES and trigger the email on the next weekday, Monday. Thank you in advance.

I have created a database in Airtable to keep a log of my Airbnb guests, income, etc. I’m using the Zapier Email Parser to grab data from email I receive whenever we get a new booking. In the email we’re provided with a variety of data including the date of checkin and checkout, in the following format: Nov 14, 2019 Nov 17, 2019 The challenge is that these dates cannot be imported into an Airtable DATE field… they can only come in to a TEXT field. What I want to do is have this raw date “Nov 14, 2019” converted into an Airtable DATE field instead of a TEXT or FORMULA field type. I can easily have this data added to my checkin and checkout fields, and use the following formula with date specifiers: DATETIME_FORMAT((date),‘YYYY-MM-DD’) which will turn “Nov 14, 2019” into “2019-11-14” Is there a way to then take that date and have it populate an Airtable DATE field? I don’t mind having an extra column if needed, to convert this but I want a native Airtable DATE field to be automatically

Using the Airtable API, so I want to make this query concise as possible. I want to filter using formula to remove entries that contain specific values from one of it’s fields. For example: I want all entries where Field A is not equal to (1,2,3, or 4). Not sure how to structure the formula to accomplish this.

Hello! On my website we offer consultations and have just switched to booking those through airtable to get exactly the information we need in the initial form imported directly to a base. What I would like to do is have a date field that is set up to calculate what 1 hour prior to the meeting is so I can have Zapier send an email reminder to my clients. Is this possible?

Hi, I am trying to use the FROMNOW() function to tell me when a transaction has expired within the last month. Can someone explain why this formula does not return accurate results? IF(FROMNOW({Expiry date}) <= 1, ‘month’, “Recently Expired”) It works (i.e. it doesn’t return an error) but it is returning “Recently Expired” for every transaction that has expired, even ones that expired years ago! Help please! :slightly_smiling_face: Mark

I am trying to create a nested if formula. I am trying to build the following formula: If a # value is over 1,000,000, the field would fill with “Mega Influencer” If a # value is 100,000-999,999, the field would fill with “Macro Influencer” If a # value is 10,000-99,999, the field would fill with “Micro Influencer” If a # value is 1,000-9,999, the field would fill with “Nano Influencer” If a # value is BLANK, the field would fill with “Need to add follower count” I have come up with the following formula and it is not working:

Hi All, Brand new here. (I tried attaching a screenshot but can’t so… ) I have a Related Field column where I’ve added some ‘Components’. Those Components have a Price field within their table. Next to the Components field I have a Components Total field. What’s the formula required to display the total of the Components in the total field as they are added? Boy I hope that makes sense. Thankyou.

Hi, is there a possibility to create a hyperlink in the text that is written in a cell of the “notes” column?

I have two date fields and would like to pull out the months and years in separate fields from one or the other. I managed to get the single IF working but not the nested function. Here’s my formula: IF({Date A}, DATETIME_FORMAT({Date A},’YYYY’), IF({Date B}, DATETIME_FORMAT({Date B},’YYYY’))) which I based on: IF([logical argument1], [value1], IF([logical argument2], [value2], [value3])) Any help would be appreciated.

EDIT: It’s not letting me upload pictures, anyone know why? I have 2 tables. 1 called Members and 1 called Appointments. Here’s the members table: Each member has a Name, Membership Type, Shared with (linked to existing table), and another column linked to ‘appointments’ table. Here’s the Appointments table: You’ll notice a the field “Total Appointments per Active Member” Anyone that has membership type ‘shared’ means that I don’t count them as an actual member. I want to link them to the original member. And since I want to track each appointment by the main member, I’d like to figure out how to add the members ‘shared’ total with the existing membership. E.g. John Smith has a total of 3 appointments (his 2 booked, and 1 from his shared member Jenny). How would I do this?

I’d like a formula field to return =today() if another field is blank, but if the other field is already populated, to use that date instead. Any help crafting this?

Does anyone know how to stop the cells I copy information into (i.e. drag cell down the column to save the same in all) from merging into one cell on the copied page?

Hi, I’m trying to keep track of how many hours I spend on different activities per day and I would like to make a pie chart using the summary bar to see the total hours I spend each day per category of work? I have 1 field for date and 1 field for, say, finance, another for, development and another for personal time and I will add one record for each day. I need to be able to chart, overall, what percentage of my time I spend in finance, what percentage in development and what percentage personal. Is this possible? thanks

Hello Everyone, I would classify myself as a mid level Airtable user, but I am having an issue and am curious if what I am trying to do is even possible. Basically I am wanting to do a LookUp If formula with a linked Field. I have two tables with a linked field. In table 1 I have a list of items, some with check marks, some with not. In Table 2, I have a column linked back to Table 1. In Table 2, I want to be able to pull a list of the items that have a check mark. I know this sounds pretty confusing. Does anyone have any ideas?

Hi all In early stages of assessing whether Airtable is a good fit. I work with a large freelance base at our creative studio, and need to allocate freelancers across several projects. For example: Laurie will be allocated 10% to project A, 20% to project B, and 70% to project C. Is there a way to do this so I can see in the Calendar setting: Laurie’s project split across the 3 projects Which these projects are, which may have different date durations At first glance at the Contractor template, I can duplicate the freelancer several times - but this can start to look messy when the calendar fills up and does not clearly show the assigned project or % allocation. Thanks in advance for any tips!

Hello, So I’m attempting to find the string “Active” as so: Yet it’s not picking up the string for a number of records (ex. should be 1 for the first record): Why is this happening? Thanks!

Hello! I am trying to build out a formula that will allow for the following fields to populate based off of a due date: “Due this week” within 7 days of due date “Due next week” 8-14 days of due date “Upcoming” due after 15 days “Due Today” “Overdue” I have reworked my formula over and over for the past 4 hours and can not seem to get it to work. Any help would be much appreciated! Thanks! Jessica

Here’s my situation. I’m using Airtable to visualize information about my direct reports on an engineering team. It’s around a dozen people, all working on various things. I’m using Airtable to give me different views: for example, how much of their learning/development budget they’ve spent, or what projects they’re working on, etc. I have one table for people, one table for projects, one table for tags about the projects (things like “infrastructure” or “public-facing” or “front-end”), etc etc etc. So here’s where I’m currently stuck. Each project has a lead, which is a field in the project’s entry in the project table. What I want to do is have a field in the people table show whether that person is a lead on a project, and what project, and have that automatically populate. I don’t want to have separate data sets - I don’t want to have a field on the person that says “lead” where I have to go in and manually select the project, because that could get out of sync if I make cha

Hello everyone :slightly_smiling_face: , I’m using airtable with graphql and i need to do a product list with ITEMS & LINKS (those are Lookups from two fields of my PRODUCTS table) Here is my table (CATEGORIES table): https ://image.noelshack.com/fichiers/2020/01/7/1578236429-capture-d-ecran-2020-01-05-a-15-44-28.png (delete space after https, links seems to be forbidden) In the formula field, i’m looking for a solution that can do something like that but i’m stucked "allCategories": [{ "name": "category name", "products": [ { "item-1", "link-1" }, { "item-2", "link-2" }, ] }, ... ] As it’s my first website with Airtable, i don’t know how to proceed. May i can sort the data directly in airtable, may i can do this in my v

I have a complex formula which generates a value with double quotes (") in it. When I try to copy the field and paste it outside, the double quote is being copied twice. For example, if the text shows: Test"Test the copied text once pasted shows: Test""Test The field I’m copying from is a roll-up field. Anyway, I can avoid the double double quotes? Appreciate any help I can get

Hi All, Trying to fix a goof in our Airtable. Backstory: We programmed a certain formula to add in the year a record was created into the record name(i.e. 2019 =19). We pre made some of these records in December, and now need to duplicate the records and have all the duplicated records keep the same name. I hope you all have realized the problem with this… If you haven’t all new records now automatically signify it’s 2020 and no longer 2019. My Idea is to add in a check box to the records we want to fix to say "If box = 1 then replace “DATETIME_FORMAT(CREATED_TIME(), ‘YY’)” With “19.” Here is the Original Formula: IF({Class} = “STR”, “ROS” & “" & {Instructor} & "” & {Class} & “" & {Strength ID} & "” & DATETIME_FORMAT(CREATED_TIME(), ‘YY’) & “" & {Playlist}) & IF(NOT({Class} = “STR”), "ROS” & {Instructor} & “" & {Class} & "” & DATETIME_FORMAT(CREATED_TIME(), ‘YY’) & “_”& {Playlist}) This would spit out something lik