Formulas

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 precision? Thanks for the adv

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})`

Is there a way to only have a formula to show in one cell? I have a smaller table where I want to pull totals from two other tables then total those results to display…ie wages less expenses…is there a way to calculate this in only one row to yield the result across 12 months displayed horizontally? Emma

Hi! I have an alarm rent business and I am building an ERP with Airtable to manage stock of devices, kits and availability. One thing I want to do, is to create unique ID reference for each kit (a kit have some devices) and for each device. This reference is a concatenate of words and numbers. For example, for kits I want to use the version and the number dependent on each version, and when the version change, the autonumber start again in 1. Ex: Kit Version A Kit number 0001 Kit ID: A0001 Kit Version A Kit number 0002 Kit ID 0002 … I want to automate this, when I manually change the version, the kit number (autonumber) reset automatically to 0001 again: Kit Version B Kit number 0001 Kit ID: B0001 So I have a column to name the version, and have other column to autonumber, but I need to make a formula to restart the autonumber each time that I manually change the version. Do you think is it possible? This is the example base: My example base

I have 2 tables, one with projects, one with people. In projects table I have a view with only the active projects. For each project, I define who works on them. In the people table I would like to see how many active projects each person works. There is the “Count” option and only show options for one view, but that doesn’t seem to list the Count to that view. Any possible solution you could think of?

Hey Guys, Trying to figure out how to leave a cell BLANK. I’m tracking soccer games. Here’s my formula: IF({Home Score} > {Away Score}, {Home Team} & " " & “Wins!!”,IF({Away Score}>{Home Score},{Away Team} & " " & “Wins!”,IF({Home Score}={Away Score},“Draw”))) The first part is ok. I get the right values for HOME / AWAY & DRAW. the problem is with future games, where the “score” cells are still empty, I get “Draw”. technically it’s true, but I’d like to leave the cell empty or insert a placeholder text instead. Thanks in advance.

Hi there, I have a table with a Name field and in that field (in different variations and location in the text string) is a series number as either 001 or 1. In a Series No. field I am including a formula that extracts just the series number. I am trying to find a solution to write a more elegant formula solution that does a FIND against the Name field and if it find a number from 1 to 100 then it will return that value in the Series No. field. Here is the formula I have at the moment but I want to avoid having to replicate this out from 1 to 100. I know there must be an easier way to do this but just don’t know how to go about it, especially in Airtable. IF(OR(FIND(“Series 001”,Name), FIND(“Series 1”,Name)),“001”, IF(OR(FIND(“Series 002”,Name), FIND(“Series 2”,Name)),“002”, IF(OR(FIND(“Series 003”,Name), FIND(“Series 3”,Name)),“003”, IF(OR(FIND(“Series 004”,Name), FIND(“Series 4”,Name)),“004”, IF(OR(FIND(“Series 005”,Name), FIND(“Series 5”,Name)),“005”))))) Any thoughts?

Hey guys, I’m fairly new to IF statements, and looking to create a really simple IF statement by saying: IF [collaborator = Meg], show “Design” I have a column of “Collaborators” (Meg, Rob, Sarah, David, Josh) and a column of departments (Design, Analytics, Research, Strategy, Development). If Meg is selected as a collaborator, show design in the department column, if rob is selected in collaborator column, show analytics in the department column … etc … Any help would be greatly appreciated .

I have a table with a Lookup field that returns a date from a linked record. I’ve confirmed that the Lookup field contains valid date values. The MONTH() and YEAR() formulas work as expected with the Lookup field. But the DAY() formula only returns an “ERROR!” message for each record. Any suggestions?

I’m trying to gather information from employees about what teams they are in and how many hours they are available to spend on them that week. I’m using a form to collect the names of the teams using a link to another spreadsheet where they can select from a list of all the teams. They then have to input the number of hours they have available to spend on that team further down. I wanted to add dependencies so that these would only show once you have selected a team but that’s not possible so I am trying to find out if I can select all of the teams they are in by checking if the hours they have inputted for a team are more than 0. Any ideas would be greatly helpful thanks :slightly_smiling_face: