Email to multiple managers when an employee is reaching a milstone

Hi,
I think you should start with formula in Employees table to show upcoming dates. Then add this field as lookup in Managers table. You may also want to add condition in Find Records to send mails only for those managers, whose Anniversaries field is not empty

Example formula - i think to avoid counting last day of month and then IS_BEFORE , IS_AFTER,
you can simply compare YEAR-MONTH of anniversary with current date. Like this (suppose ‘begin’  is the name of field when emloyee started)
 

IF(DATETIME_FORMAT(DATEADD(begin,30,'days'),'YYYY-MM')= DATETIME_FORMAT(DATESTR(TODAY()),'YYYY-MM'), '30 days Milestone')

You can also extend it to other dates and make output more detailed

 

IF(DATETIME_FORMAT(DATEADD(begin,30,'days'),'YYYY-MM')= DATETIME_FORMAT(DATESTR(TODAY()),'YYYY-MM'), '30 days Milestone') &

IF(DATETIME_FORMAT(DATEADD(begin,60,'days'),'YYYY-MM')= DATETIME_FORMAT(DATESTR(TODAY()),'YYYY-MM'), '60 days Milestone') &

IF(DATETIME_FORMAT(DATEADD(begin,90,'days'),'YYYY-MM')= DATETIME_FORMAT(DATESTR(TODAY()),'YYYY-MM'),
Name&' :  '&DATETIME_FORMAT(DATEADD(begin,90,'days'),'YYYY-MM-DD') & ' - 90 days Milestone')&

IF(DATETIME_FORMAT(DATEADD(begin,1,'years'),'YYYY-MM')= DATETIME_FORMAT(DATESTR(TODAY()),'YYYY-MM'),
Name&' :  '&DATETIME_FORMAT(DATEADD(begin,90,'days'),'YYYY-MM-DD') & ' - 1 year Milestone')

By way, I just found that pressing F3 in editor window and then arrow  near Find lets you use ‘Replace’ and ‘Replace All’ . Before, I copy-pasted text in notepad for that. Great and useful feature!  That’s how you simply can adjust formula to your real field name:
 

Once it finds all the managers do a For each repeating group on each manager, then in the For each do another find records for all the employees for that manager that have milestones for that month.  Then insert that into the email to the manager in the next step of the repeating group.

Hey ​@jeans886!

As mentioned by ​@garebear, you’ll want to look into repeating groups within automations. Sharing the link for further context.

Mike, Consultant @ Automatic Nation

I was able to get all my dates in as new columns for the milestones, I also understand the repeating group, but I don’t know how to find records for employees for only that manager and not pulling all other managers as well without creating a separate grid view per manager.

In my screenshot, the View that is selected in this “Find Records” is the view with all employees that have a milestone coming up this month, so when I go to create the email in the next step, the grid I pull includes all managers and I don’t know how to only pull records for each manager to go in their specific email.

Lastly, is there a way to set a conditional action so that the email only gets sent to those managers when they have employees hitting milestones that month? I don’t know how to add a conditional logic when I have the repeating logic function on.

I never use views for triggering automations unless you need conditional groups for filtering, too many issues with them.  All my advice here is for using Find record steps and trigger at time (last day of previous month or first day of current month for example)

You can bring in the managers email through a lookup in the persons record. In the find records find the manager who has that person in their assigned people field.

On your last ask, when doing the Find records add in a condition that date in the date field is “within” “this calendar month” or “next calendar month” depending on when the trigger is scheduled to go.

I have the manager email lookup field already.

1. Basically I have these columns:

2. I have three grid views that are applicable in this case:
   1. Active: A list of our active employees
   2. Managers: A filtered list of active employees who are managers
   3. Milestone Master: A filtered list of active employees who are hitting a milestone in this month
3. My trigger is: “at a scheduled time every 1 month on the 1st”
4. My actions are:
   1. Find records based on either view of “Managers” grid, OR I can do based on condition that they are listed as a manager and are an active employee, either works
5. Then I have a Repeating Logic Action after that:
   1. Find records based on condition where “Milestone Type” is not empty, which is my field that only has data if the employee is having a milestone that month
   2. Send an email to the Manager Email using the list of values from the List of records.
   3. In the body of the message, I have it pulling a grid view of the employees who have a milestone this month using the “Find records” from step 5-1. 

However, to my understanding, this will send one email to each manager but with ALL the milestones listed instead of just the ones that they are listed as managers on in column 1-1. What step do I need to fix to send one email per manager and only their employees if they have someone for that month with a milestone? I feel like somewhere in step 5-1 that I need to find records using a smart search based on the manager name and who the email is going to? But then I still don’t know where to go from there and it doesn’t even seem like that’s possible.

I see what’s your problem.
Usually I avoid self-linking, so I thought you have a separate table of managers. The problem is that such link (in the same table) is not 2-sided, therefore it’s partially useless.
But anyway, it’s doable
Note that you cannot mix repeating group and conditional actions in the same automation. Here you need repeating group only

Organize a single field with milestone info (for example, concatenation of your 4 & 5)
Inside repeating loop, which iterates all managers, in your second ‘Find records’ use condition to find only people, whose Manager is the current email recepient. Something like this, pay attention to ‘Use data from’

Also, it would be wise to add a condition - when Milestones field is not empty. 

And the last step - email. Use address from ‘Current item’ as well.
In message text, use list of Milestones from second Find Records result. Ensure you selected correct Find Records (from two):

 

Each loop iteration will give you a different list for each manager .