I’m looking to create a base in Airtable to keep track expiration dates for products we sell. We are trying to replace our current expiration date tracking software with an Airtable base.
Our current software solution allows us to setup as many email notifications as possible as the expiration date nears. Each notification reminder can have its own specified days for the scheduled notification email to go out in addition to assigning a specific email template with customer subject line and content. Here’s what the settings look like in our current software.
What I’d like to do is setup automation using Airtable, Integromat and Gmail’s SMTP service.
The current base I created only has 4 columns, SKU (text), Supplier (dropdown), Expiration Date (date), and Owner (Collaborator).
I need the automation to run once daily checking each record for any products (records) nearing expiration at the intervals they are set to. The challenge is that not all suppliers will have the same intervals. Some might have 90, 120, 150, and 180 as notification intervals. Other suppliers may have 60, 90, 120, and 150.
I would still like to be able to use pre-written email templates coinciding with the specific intervals (ie. 90, 120, 150, 180) as each notification requires different actions that need to be taken by the recipient of the emails.
I’d like to be able to specify a different recipient for one or more of the emails. For example, have the first 3 notifications sent to one recipient and have the 4th one sent to another. The last notification requires a person with a different skillset than the recipient receiving the first 3.
If possible, I would like all affected records to be contained all in one email versus sending separate emails for each record scanned within the same day.
If a product (or record) is sold out, we would like to archive, or I guess, archive that record in another view. I imagine we can automate the archive of such records by creating a status column. Assuming that is accurate, I would NOT want the automation to run against those archived records as obviously there is no further action needed for products that have sold out.
Thanks in advance for any and all assistance!