How to create a training due date from two tables

[Carrie_Brightbi]

I am creating a base that will track my staff’s required training classes. The classes vary in their renewal times, some only have to be taken once, some are required to be taken yearly, every 3 or 5 years. I created the following tables: Staff - contains Name and other pertinent info. Classes - lists all the required classes and their expiration times in number of years: 1, 3, 5.

I created a table for training records and linked the staff and class names to this table, along with training date (date the staff took the class) and if it is the newest record (as there will be multiples.):

This is what I would like to happen and I don’t know where to start:

I would like to set up an automation that sends out an email when a staff is within 90 , 60, and 30 days of the class expiring. This would entail taking the Training Date from the Training table and the expiration time from the classes table using a formula to add the years to the training date and return the expired date. Then take that expired date and subtract 90 days compare that to today’s date and send out an email if it is within 90 days. How do I do that? Automation, formula??

[ScottWorld]

Welcome to the community, @Carrie_Brightbill!

You’re on the right track so far! And yes, you would actually need to create BOTH a formula AND an automation for each expiration email.

There are many different ways of doing this. Here’s one easy way to do this:

Let’s say your expiration date field is called {Expiration Date}.

You could create a formula field called {90 Days Before Expiration Date}, with this formula:

DATEADD({Expiration Date}, -90, 'days')

That would give you the date that is 90 days before the expiration date.

Then, you would create an automation that triggers when a record matches conditions, and the condition would be when the field {90 Days Before Expiration Date} is today.

That’s the basic concept. However, there are some added complexities which you might need to consider:

• You can’t really control precisely when that email will fire. It will fire around 12:01am UTC time if your base is active at the time, or it will wait until somebody starts using your base which will “wake up” the base. To make sure that your base always wakes up at a specific time, you can create another automation that is set to trigger on a daily schedule, and you can have that automation do anything random & meaningless in your base, such as finding records. That will “wake up” your base, and make sure that your other automation fires.

• There are also ways to create formula fields that contain BOTH a date AND a time, so you can get more fine-tuning over the time that the automation fires by using that formula. You would still have the same problem with the base needing to be awake, so you would still want to wake up your base on a schedule.

[Carrie_Brightbi]

Thanks for your response. Do I need to set up another table to bring in the staff, the class, the training date and the expiration time period to then do the formula? I don’t know how to create the formula pulling data from multiple tables (3 to be exact.)

[ScottWorld · Accepted solution]

You can bring in values from other tables by using lookup fields.

To learn more about lookup fields, I would recommend checking out my free Airtable training course:

LinkedIn

Learning Airtable Online Class | LinkedIn Learning, formerly Lynda.com

Learn the basics of Airtable, a spreadsheet-database platform, including how to create tables, use different types of fields, leverage views, work with records, link tables, and more.