How to create a product expiration date base with notifications

[Caligent]

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).

Needs:

• 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!

[Mohamed_Swella1]

Hi @Caligent,

Welcome back to Airtable Community! :slightly_smiling_face:

It seems you are already on the right track. What I would do is instead of sending the record I would send a link to the View that needs action.

Caligent:

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.

Create a view for each interval. Each notification interval should be a separate view, so you should have 5 views (60,90,120,150,180). This view filters a formula where the days till expiration is the required numbers.

Caligent:

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

Using Zapier or Integromat, Send a link to the view to the relevant recipient. The zap should trigger once a new record is added to any of the above views. This way they can see all the records that needs action.

Caligent:

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.

You can identify the recipient on the zap. This is if the recipient is always the same person. Or are you sending this email to the suppliers?

Caligent:

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

This is why I think your best option would be to send a link to the relevant view instead of sending the records. Alternatively, if you send the records then each one will be in a separate email.

Caligent:

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.

You can create a Checkbox for this status and remove this from the view.

Feel free to come back with any questions you might have.

BR,
Mo