Formulas

I have a couple of fields that are populating a page designer block to generate booking confirmations for clients. I have a projects table and a client contacts table. In the projects table I have a field that links to client contacts, and then a looks up field that populates contacts first name only for the purpose of addressing them in my page designer block. My problem is that sometimes I have multiple contacts on a project. See: I need to populate my page designer block with “Kelly and Chris”. I’ve tried this substitution formula: SUBSTITUTE({Contact first name look up}, “,”, " and") It appears that you can’t run such a formula on a lookup field. Help would be appreciated. Thank you!

I have one table Evals where I have a true or false question that I want to count in another table called Eval Summary. In Evals, I created two formula fields that seem to be working fine: IF({Would you attend a similar conference next year?}=“True”,1," ") IF({Would you attend a similar conference next year?}=“False”,1," ") I’ve linked the Evals to the Eval Summary table with linked field and created a rollup field linking to that table and formula field with SUM(values). But it’s just giving me NaN and I can’t really figure out why. Any help is appreciated and thanks in advance!

Weight ====== Cost If weight = 0.5 Kg. then cost is $50 for each additional 0.5 cost is $45, Weight has 0.5 Kg slab, i.e. 0.60 comes in 1Kg. slab. How to create formula to calculate per slab charge in Airtable ?

Please help! I did the most idiotic thing you could imagine — I deleted my entire workspace! I have one base there that is my entire business. How can I retrieve it? I know that there are snapshots but how can I locate them when the workspace has been deleted? Please help… the tech support could take days to get back to me and I have clients to deal with.

I’m trying to do a “max” date rollup, where it returns the last date. This works on a regular date field. However, I only want to track this for “in progress” projects so I’m using a SWITCH formula to return the dates from records in progress. Once I try to rollup the formula field the “max” values aggregation no longer works. Any workarounds or ideas? Thanks!

I just started using merch wizard. its not very clear how to use the service. Which videos show how to use the latest version ?? Also why doesn’t the 14 day trail let me use most of the features. like advanced search and dog finder etc.??? I have questions on how people use the service. Your starting user experience leaves a lot lot to be desired…

Hi all, I’m sure I’m just missing something easy but here is my problem. I have an employee start date field and a field with numbers of days. I have tasks that need to be done 5 days before the employee starts, 1 day before the employee starts, and so on and so forth. So I want to calculate a field using the Start Day and Number of Days to calculate the Due Date of the task based upon number of days. So if my start date is 6/20/19 and my Number of days field is -5, I want a formula to return 6/15/19. I have tried the formula below but I get NaN returned. Thanks for any guidance and help! DATETIME_FORMAT({Start Date},‘MM/DD/YY’)+{Number of days}

Hi! I have little to no knowledge on spreadsheet formulas and codes, so the formula function of Airtable baffles me. Thus, I am completely floored on trying to figure out how to create an IF Formula on a 4th column based on the data of 3 other Columns/Fields. I’m creating a database for art collections, and I am trying to keep record of what artwork goes where. Thus, I have 3 columns, “Buyer,” “Shelf Location” (in the gallery), and “Exhibition” (where the artwork is loaned for an exhibit). I am making a 4th column that makes for a quicker view for other people looking at the database and I want it to reflect the following: IF({Buyer}!=0, “Sold,” IF({Shelf Location}!=0, “Inventory,” IF({Exhibition}!=0, “On Loan”)) But when I input this formula or some variations of it (I tried adding AND/OR), I always get an error. By the way, “Buyer” and “Exhibition” columns are linked to other columns for further details on the matter. I really appreciate anyone who can help! Thank you!

Hey folks, I’m trying to make a check in/ out system for a music venue/ studio so they can see which members of staff are on site at any given time. I’ve used two forms where staff can check in and out which populates a table. That side of things is working fine. The following formula shows whether someone is currently checked in: IF(AND({time in},{time out}),"❌",IF(AND({time in},NOT({time out})),"✅",BLANK())) I’d like the formula above to also show an :x: for those who haven’t signed out on a previous day (So the :white_check_mark: s are only for people signed in on the current day). I’ve tried to do this by comparing the most recent check in to the days date using the TODAY() function but it appears to be including the previous day as well (which I really don’t understand). Hopefully that makes sense. I’ve been staring at Airtable for far too long today and might be going mad! Any help will be greatly appreciated. Many thanks, Ross

Can we combine 2 things in an “IF” statement using “AND” or “&”? For example, what if we wanted to compare 2 different sets of date fields. In other words, if I was using plain English, I would want to say something like this: IF ( IS_AFTER({Event Start Date}, {Location Start Date}) AND IS_BEFORE ({Event End Date}, {Location End Date}), "Yes", "No" ) or I would want to say something like this: IF( {Event Start Date} > {Location Start Date} AND {Event End Date} < {Location End Date}, "Yes", "No" I know that both of those formulas are invalid formulas, but I’d love to say something like that in Airtable, using Airtable’s syntax. Thanks! :slightly_smiling_face:

Hello! I’m using airtable to keep track of memberships. I have a column with “Member since” date and I want every month after that we need to make a new package for them. Once that that has passed I want it to show the next month etc. I’m using this formula to calculate “next package” date: IF(DATETIME_DIFF(TODAY(),{Member since}, ‘months’)>1,DATEADD({Member since},(ROUNDDOWN(DATETIME_DIFF(TODAY(),{Member since},‘months’)/1,0)+1)*1,‘months’),DATEADD({Member since},1,‘months’)) It is working well except for the first renewal. If they joined more than 2 months ago the formula works. But the members that joined between 1 & 2 months ago are showing a next package date in the past… Anyone that can help with this?

Hi, I am struggling to solve this problem. I have several date ranges that represents treatments for my customers. I would like to group the customers/treatment chronologically, so that I can see the how many customers I have each month. I could do this by creating a formula for the year DATETIME_FORMAT({Seuraava jakso alkaa},'YYYY') and one for the month DATETIME_FORMAT({Seuraava jakso alkaa},"MM - MMMM") There are two problems with this: This formulas are showing always only the next treatment of a customer. If next treatment time changes, of course the past treatments ar not appearing in the group anymore. Problem to solve: Could it be possible to have in the group all the treatments (past and future) chronologically by a customer (start date is OK, don´t have to be a range)? I hope this is understandable as I’m not a native English speaker. Thank you in advance, any help will be most appreciated. Cheers, Attila

I am trying to upload an email form Evernote into airtable (via attachment - not zapier) and its only uploading 1 picture, not including the email or the 4 other photos. Any suggestions?

Hi, Iam making an App in which I need a online rank list.Is it possible to arrange data ascending order automatically when a new data is added to Table without my interaction. Sorry for my Poor English! :grinning: :grinning:

Hi fellows, I am really stuck trying to solve this problem. Any help will be most appreciated. I have several date ranges that represents employees leave/absence. In order to grant vacations i need to check how many employees are out. So I have a employee date range to be compared with several date ranges. For instance: Range to be compared: begin |-----------------| end b |---------------------|e b |----------------------|e b |---------------------------------------------|e b |---------|e So i need to filter (show matches 1,2,3,4) and count this matches. Any clues ? Thanks in advance.

Hi there, I’m trying to query my main table by grouping Enterprises (Company Name) to see which portfolio companies have them as either a lead or customer. I need to query such that only groups with more than one record (where count > 1) show up to be exported/printed. Is there a way to filter out by the count after they’re grouped? Or is the only way to do it with a manual counter and if so how should i go about that? Thanks

Hi there, I’m working on a database to manage shots in a video piece. In the most basic form, Table1 has a list of every unique shot with timings and other info. Eg.as below (the actual tables have far more fields). Table1: SHOT __ TIMING 1 __ 01:00 2 __ 01:05 3 __ 01:14 4 __ 01:22 My 2nd table relates to a series of notes, and each single note relates to a single shot from the 1st table, but a shot can have multiple notes. E.g. Table2: SHOT __ NOTES 1 __ Low Quality 1 __ Not cleared yet 3 __ Fixed 4 __ Cleared 4 __ Low Quality 4 __ Awaiting Update So using a Look-up on Table1 and Roll-up I can manually add and display all these, but I’d like the Look-up to auto-complete / auto-populate with all the entries on Table 2 that contain the same numbers Eg… 1 __ 01:00 __ Low Quality , Not cleared yet 2 __ 01:05 3 __ 01:14 __ Fixed 4 __ 01:22 __ Cleared, Low Quality, Awaiting Update Am I missing a trick on how to do this? Shot no. 1 and 4 having multiple records makes this more complex, too.

Can a SWITCH() formula be used to output different criteria for different options and/or combinations of a multiple select field? Essentially, I want to say: “If multiple select field contains Option 1, return Result A. If it also contains Option 2, return Result A, Result B.” So far, when I’ve made a SWITCH() function, it will only return if a multiple select field is one thing or another but won’t return if it’s both things. How could this be built without a zillion characters of different scenarios written out in the formula?

Hello, I want to have a field that concatenates the Due Date field with the Description field. It is showing up as this: 2019=05-31T00:00:00.000ZCafaro Service My formula is: CONCATENATE({DUE DATE},Description) We do not have the time format selected in due date, so I’m not sure why it is generating this way. We would prefer a simplified date be shown in Month/Day/Year and have that displayed in front of the project description. Thank you!

hello to all. im trying to export a base as CSV, and i need to have the name of the base change via formula. i want the name of the CSV file to be the date of creation and a couple more informations. how can i use formulas on the name of the base?

I am trying to create a view that is filtered for the last event/row that occurred > 90 days ago. This is for my fleet maintenance tracker I use in AT. Potentially, the structure I have created for this base may prevent me from accomplishing my task. With that being said, I log maintenance in AT via entering a new row for EACH service. On each row, I post the date of service, type of service, and vehicle (along with some other nonessential data). I am trying to create a simple view that displays vehicles whose LAST oil change is > 90 days. Any thoughts?

I’m struggling to make the bars of my bar chart ordered from low y-value to high y-value. Even though my view is like this, the bar chart orders the x-axis in alphabetical order. Anyone have a tip?

Hi - new to Airtable. I have a small web design agency and we manage hosting for some of our clients but not all. Consequently I have a ‘Hosting’ field with 2 just two options, ‘Managed’ and ‘Not Managed’ I then have username and password fields. When ‘Not Managed’ is selected in the Hosting field, I would like the Username and Password fields to be auto-populated with ‘not known’ rather than fill this in manually every time or left empty. When ‘Managed’ is selected there should be no action so we can fill in username/password manually. Can this be done?

Starting recently images appear cut off in all lookup fields on IPad. They look ok on a iPhone.image|690x178 Any idea what happened and how to fix it? Thanks

Hi AirTable Community - I have a grid with one single select column indicating contributors (Freelance Writers) and another column indicating Word Count (#). The Word Count field will be an integer, but the cost calculation will depend on where the word count falls within a numerical range. I’m hoping to generate a formula that generates the cost of the article based on the word count. It would read like this: If {Freelance Writer} is “option from single select column” and {Word Count} >=500 and {Word Count} is <=700, Cost “150” but I am striking out. Any advice on what I’m missing?