Formulas

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 s

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 ca

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_FOR

I want to add a bullet or emoji to the beginning of each line. To make things more complicated I have a CSV file that I’m importing that has | as line breaks. With a quick formula I’m changing | to \n and then adding a the bullet or emoji after the \n but obviously this won’t change the first record. Is there a (simple) way to add text to the beginning of each line?

Hello! I have a Project Tracking workspace, with two tables - a list of Tasks, and a list of Projects. I’ve found it easier to manage the tasks by keeping them in a separate table. But in the Project table, I’d like to see “Incomplete” tasks, so I created a column to return only these incomplete tasks. I can’t seem to reference this column in my Project table. How can I reference a specific column? For some reason the Client table is the only one that is available under a rollup reference. Thanks!

Hello, Sorry for the novice question but i was wondering on how to take the first four numbers from a field of numbers itself. I did try the LEFT formula but it doesnt seem to work for me. Now, there is a field called Ticket with a string of about 10 numbers with the first four numbers being the year. I want to take those four numbers from that field and put them in another field.

hi hi can i trigger if a relation was set to another table and then do a calculation ( reduce and set an int field by 1) thx, gr. pit

hi, i want to have some kind of warehouse management. i have a table with products. the products have an inventory. in another table i have a relationship between them and i wanna reduce inventory with 1, if i give our chance a datefield. it’s this possible? greetings Pit

Hey all, I’m tracking scores of sports events. The main fields are “Home Score” (for how much the home team scored) and “Away Score” (you guessed it…) In soccer matches it’s common for a team to score nothing. i.e. “0”. I’m calculating: IF(AND({Home Score}, {Away Score}), IF({Home Score} = {Away Score}, “Draw”, IF({Home Score} > {Away Score}, {Home Team}, {Away Team}))) The issue is, if I input “0” Airtable ignores it. it’s a blank for calculating consideration. So, field {winning team} remains empty. The workaround is to input a “1” score in case of actual “0” and to give the other team a +1 on their actual score. i.e. if a game’s score is actually 0:1 I’d input it as 1:2 . Is there a way around it? I want to preserve the information integrity. Thanks.

Hey Guys, I’ve had this issue before, and was able to solve it. For some reason, it doesn’t work now. We’re betting on score difference, so: IF({Home Score}>{Away Score},{Home Score}-{Away Score},{Away Score}-{Home Score}) If the game hasn’t played yet, the {Home Score} & {Away Score} are empty, which results in ‘0’ So, I’ve tried checking if the records are empty or not. IF(AND({Home Score},{Away Score}), IF({Home Score}>{Away Score},{Home Score}-{Away Score},{Away Score}-{Home Score}) But I keep getting an error, that my syntax is wrong. After the above I’m running this: IF({Points Difference Bet}={Actual Points Difference},“Bingo!”,IF({Actual Points Difference}-{Points Difference Bet}<=5,“Very Close”, “Way Off”)) But the points difference can actually be higher than 5, but I’d still get “Very Close” Please help. Thank you.

I am trying to create a field that will pull from 3 other fields to generate a contract termination date. Each account has a install date (ID), contract term (CT), and renewal term (RT). For example: ID=1/1/2017 CT=36 months RT=12 months Calculating the initial end date is easy (ID + CT) but I am stuck on the renewal. I want to be able to incrementally add months to the term length depending on whether or not the end date (CED) has already passed. For example: SD=12/1/2016 TL=36 months CED=12/1/2019 (original end date) RT=12 months CED=12/1/2020 (actual end date once the original has passed) I have a formula adding the renewal term to the original contract (ID + CT + RT) but cant figure out how to add RT as many times as needed to make the CED greater than today IF(CED<=TODAY(), CED++). Any advice would be appreciated, THANKS!

Hi, I am looking for a formula to remove text between parentheses. For exemple, I would like to have : Le nord (Chili) > Le nord Le nord (Soudan) > Le nord Le sud (Soudan) > Le sud Thanks in advance for your help :slightly_smiling_face: !

I’m trying to write a formula that would find multiple parts of text (from a single cell) into a short formatted text (in a cell). Example: From “Nothing, we are a turn-key service, from site prep, permits, to completion” to “turn-key” From “The client has some responsibility like permits, site-prep, and transportation” to “door-step” From “We just send the kit of parts and the client takes care of the majority of work” to “kit-of-parts” From “We offer just the drawings / floor plan in addition or exclusively as a service” to “floorplan-only” I was able to get it to pull just one text but not multiple.

Hi, I have a table that connects pairwise my data in a kind of graph structure. Is it possible to implement a (recursive or not) depth-first search algorithm that will filter the connected records only for a given cell value?

I need to record weight data for observations that have different levels of precision because they span a wide range and must be measured on different scales. Is there a way to allow for differing levels of decimal precision across entries in a single “Numbers” field? For instance, to enter 4 sample measurements of: 3, 3.0, 3.02, and 3.020, Airtable makes me select a single level of decimal precision for all of the entries, so it ends up showing as something like: 3.000, 3.000, 3.020, 3.020 or 3, 3, 3, 3 or etc etc… You get the idea. In any case, the resulting entries do not accurately reflect the actual data. As far as I can tell, if I want to use this field for formula-based calculations, I must designate it a ‘numbers field’ and therefore must choose a single level of decimal precision for all of the entries. Is there a way to get around this without misrepresenting my data precision and without creating separate weight fields for each level of decimal p

Hey, I’m trying to calculate points difference. So, if the points difference <= 5 then I’ll give the player 2 ranking points. if the difference is great than that, he doesn’t get any points. I’ve noticed that Airtable doesn’t treat 5 and -5 the same. So, I’ve made two fields. Here’s my formula: IF(AND({Team Bet}=“Win”), IF(AND({Home Score},{Away Score}), IF({Points Difference Bet}={Actual Points Difference},“3”,IF(OR({Actual Diff - Diff Bet}<=5,{Diff bet - Actual Diff}<=5,“2”,“0”))))) But, I get nothing. Can anyone spot my mistake? Please help.

Hi, I’m using the created time field in my database when I receive a new messages that are stored in the database. Is there a way to modify it to display it right that? Dec 23, 2019 8:23 pm Thanks!

I’m trying to calculate the next due date of something based on whether the occurrence is monthly or annually. I’ve got this: IF( {Past?}=“0”, {Start Date}, DATEADD({Start Date},1,‘years’ ) ) And I’ve got a formula that calculates the 1/0 for the {Past?} field. That seems to work so far in terms of figuring out the next due date, but only on an annual recurrene. But there’s another dropdown {Recurrence} that is either Monthly or Annually. What I’d like to do is say, “If recurrence = “Monthly”, add 1 month…if “Annually” add 1 year.” I’m sure it’s some kind of IF/AND/OR but I can’t seem to crack the logic on that.

Hello, I have a column with up to 4 values separated by commas. I am trying to take the values from each comma break point and place it in its own column. For example: usgs, aauw, abernathiethomas, admiralty CARTOGRAPHER_1 (Column) would display: usgs CARTOGRAPHER_2 (Column) would display: aauw CARTOGRAPHER_3 (Column) would display: abernathiethomas CARTOGRAPHER_4 (Column) would display: admiralty There will never be more than 4 columns / values /commas, but there will be a minimum of 1 value but not always 5 values. I looked into the functions left, right, mid, search, len but can’t seem to make sense on how to make them work together to achieve this if at all posible. Thanks for your help!

Hi, I would like to have a field (or at least the data) counting the number of times another field has changed. Eg calculating the number of times a task end date has been moved. Any idea ? Thank you Fred

Hi all, I would like to combine two text fields into one. Each field has several entries that are separated with a comma (e.g. in one row field 1 contains “Germany, EU, France” and field 2 contains “Italy, Austria”). When I combine them with {Field1} & {Field2} it deletes all the commas so that it looks like this: “GermanyEUFranceItalyAustria”. How can I keep the commas and just combine the field`s text exactly like it is? Tried already joinarray and putting using {Field1} & ", "& {Field2} but still the same effect. Thank you!

Hello, I need help with a formula. I’m trying to combine some address fields to create a full address but it adds an extra space when an Address doesn’t have a {Address 2} such as apartment number. So i wanted to create an IF statement to check to see if that field is blank. Here is what I have and it not working it add all the address components together expect of the {Address 2}. Any suggestions would really help `IF({Address 2} = BLANK(), {Address 1} & " " & {Address 2} &" " & {City} & " " & {State} & " " & {Zipcode}, {Address 1} & " " & {City} & " " & {State} & " " & {Zipcode})`