Formulas

I have a table with shipment tracking ID and Carrier, so i want following solutions. if Carrier = Fedex then “track1” if Carrier = DHL then “track2” if Carrier = Blue Dart then “track3” if Carrier = DTDC then “track4” if Carrier = FedexLite then “track5” I can do it with basic if function but got stuck with multiple conditions.

I’m trying to count specific records that appear in a “Lookup” field. The problem with applying a “Count” field to the “Lookup” field is it counts ALL the linked records that appear in that lookup field. Lets say the Lookup field is pulling the names of multiple people from Table A into Table B. In one of the records of Table B, lets say the following values appear in the Lookup field. Brooke H Brooke H Brooke H John M Ronny W Ronny W I want to apply a formula to the Lookup field that counts the amount of times “Brooke H” appears. With the above data, the formula should return the value of “3”, since Brooke H is listed 3 times. If I were to apply a “Count” field to this, it would give me a value of “6”, counting all the “records” that appear in the Lookup field. I want to figure out how to count specific values, not total records. Any tips or suggestions in much appreciated!

Hi I wondered if anyone might know what this formula does, it was created for my scenario in Integromat: {{replace(toString(14.data); “/^([^\n\r]*[\r\n]){12}/”; emptystring)}} I am just curious to know. Thank you, Mary

Hi, I have these functions in two fields: Field “Dani”: DATETIME_DIFF({Od},{Do},'days')-1 “Od” field is a date without time, and “Do” is also a date. Sometimes they could be the same dates. Field “Test date function”: IF( Smena=1,Dani-1, IF( Smena=2,Dani-1, Dani) ) “Smena” field can be only one of these three numbers: 1, 2 or 3. Would it be possible to run the IF function of “Test date function” field only if the date range between “Od” and “Do” include Friday(s) and/or Saturday(s)?

I have table with pin code, city and state, Have second table with contacts In contact record, if I type pin code then how can I get city and state value filled in related field from linked table, IF function not works for me as pin codes are 26k records.

I’d like to be able to have a team member track and log a specific time in/time out for a specific airtable. Kind of like logging hours. Can this be done? I already have a seperate “Date” column setup. Thanks!

I would like some type of variable drop down in a form where users can add references. E.g. a prompt, “List up to 3 references.” And then up to 3 pairs of fields asking for (Name), (Phone number) What’s the best way to structure the base and form for this

Hello, I need with some a formula I’m trying to calculate, I need the percent (%) difference between two values fields and would like to format it so that if the percentage comes back as infinity, that data would be removed from the record so I can view the summary percentage of that field in my dashboard block. This is the formula Airtable told me to use to get the percentage difference: {PURCHASE PRICE}/{LIST PRICE}*100 …which shows up like: ie: Sale Price/List Price100 = List to Sell Data % $575,000/$500,000100 = 115% $575,000/$0*100 = Infinity …but then my dashboard block can’t show the avg % of the sale to list price. Any help would be extremely appreciated!

Hi, I am working on a check in/ check out system for a local studio. I’ve hit a problem that I can’t find a solution for elsewhere. The image below shows the problem. The second row should show a “ :white_check_mark: ” as the Last Check In is 1 minute after the Last Check Out, however the difference between the two times (using DATETIME_DIFF) is showing as 58 minutes. I think the problem is caused by DATETIME_PARSE on another table not correctly setting the timezone (see below). If I have “GMT for all collaborators toggled on” it shows the correct time (as above) but is still being treated as the correct time + 1 hour Any ideas what is going on here? Or how to get around the parsing issue? Any help would be greatly appreciated! Ross

I have an applicant tracking system. I want each recruiter on my team to have access to their own base, so I can track the metrics for each recruiter (how many people they onboarded each week etc) but also compile the data in a global base that shows all applicants. I want the recruiter’s access to be silo’d to their own base. Also, the global base should have a field that shows which recruiter added the record. How do I do this?

I want my applicant tracking system to automatically load the rows FOR THE CURRENT MONTH. I want all other entries hidden somehow. Users send in entries via a form, the data is sent to a base, and then staff members operate on the data. But there should be some way to set DEFAULT views so that the entire 1000+ rows are not loaded each time. How do I do this?

I have a base for a video library which, among other information, contains fields for URL’s to the videos and two “checkbox” fields; one for “sharable”, and one for “sharable with restrictions”. I’ve created created a view to show just the “Sharable” and “Sharable with restriction” videos. I want to share this view with others, but I don’t want them to see the URL’s for the records marked “sharable with restrictions”, only the other information associated with that record. Is there a way to hide just the URL if the “sharable with restrictions” box is checked?

Hi everyone, I’m using Airtable to help reformat some .csv attendance sheets that are a pain. There is only one export option that comes in as Person ID for the primary field, then each event comes in as a text field with an X to mark attendance. I’m going for a way to count total # of attended events in a month. I’ve been using the FIND function to find “X” and give me a total, but I haven’t figured out the correct way to FIND across multiple fields (ie - all January events, etc.). I’m new to formulas, so I’m not even sure I can. This works fine for a single event, but I need to find a way to include multiple event fields. FIND(“X”, {Jan 8, 2019 AM Meeting}) Thoughts are appreciated! Thanks!

Hi all, My goal is to display in Field 2 the first occurring month (in a year) based on the pulled months from Field 1 (lookup field). Rather than using date formulas (tables are a little more complicated), I’m trying to create a formula to capture with strings: IF(FIND("January",{Field 1})>1, "January,",IF(FIND("February",{Field 1})>1,"February,",IF(FIND("March",{Field 1})>1, "March,",IF(FIND("April",{Field 1})>1, "April,")))) However as you can see from the screenshot, my formula is outputting the farthest right month in the lookup field - first record is showing March when it should be February. My initial design was to use the nested IF formula to work chronologically… but doesn’t seem to be working as I intended. For this example I’m only using four months of the year, but will build out the formula with the remaining 8 once I figure this out. Any help appreciated!

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?