How do I send a notification of training expiring to other contacts?

pamwats

I have a sheet set up tracking the completion and expiration date of training for personnel. Each row has the contact info for the person it relates to. How do I send a notification to that person when their training is within 30 days of expiration?

I've tried several workflows, and it keeps sending a notification with ALL rows that have expiring dates, and includes all the columns, instead of the column that met the criteria of "expiration date within the next 30 days."

Thanks.

Andrée Starå

Hi @pamwats

I hope you're well and safe!

Can you maybe share some screenshots of the Workflow and Sheet? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. 

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Deepak Choudhary

@pamwats

Please create 2 helper columns

1st column Name like "Due Days" with formula =[your date column]@row - TODAY()

2nd column Name like "Send Alert" with formula =IF([Due Days]@row <> "", IF(AND([Due Days]@row <= 31, [Due Days]@row >= 0), "Send Notification", "Dont Send"), "Dont Send")

then create a workflow to trigger for "when rows are added or changed" and trigger if "Send Alert" column has a value "Send Notification"

Hope this helps!!!

Andrée Starå

@pamwats

You should only need to have one workflow that would trigger 30 days before the expiring date column.

Make sense? Is that what you tried?

pamwats

pamwats

@Deepak Choudhary - with that formula, to whom would the notification go?

pamwats

@Andrée Starå The issue is that the workflow is sending all rows that meet the criteria to the notification recipients. I need it to send 1 row to the contact listed in that row.

For example, if 3 personnel have expiring training I currently get the text below. What I need is three separate emails -- 1 to Doe, 1 to Smith, and 1 to Test.

 

EM Research Regulatory Spreadsheet

 

Row 12:

Doe

Message: The following personnel have regulatory training or documents expiring in the next 30 days:

Doe

 

Row 49:

Smith

Message: The following personnel have regulatory training or documents expiring in the next 30 days:

Smith

 

Row 84:

Test

Message: The following personnel have regulatory training or documents expiring in the next 30 days:

Test

 

Last Name

First Name

CV Signed Date

CV Expiration

CITI HSP Completed Date

CITI Expiration

GCP Completed Date

GCP Expiration

Andrée Starå

@pamwats

It should look something like this instead.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!