Check if a Person is Having his/her 1st, 5th or 10th Anniversary

[AkshayManke]

Hi All,

I saw lot of threads related to this but those are not as per my requirement hence submitting this new thread.

I have a SP List where there is one column for user name and one column for the start date. I want to send notification for anniversaries when they are completing their 1st year, 5th year or 10th year.

Can anyone please guide on how to achieve this please?

Kind Regards,

[Rhiassuring]

Hi there, 

So, first, I'd create a Scheduled Flow that checks each night. 
I'd use the "Get Items" action, filtering on the day/month. 
From that, I'd then cycle through the resulting items, and filter based on years that are equal to today's year minus 1, or minus 5, or minus 10. 
Then I'd loop through those and email PERSON to congratulate them on the Today's Year - Their Start Year anniversary.

You could combine the filters, really, if you wanted to do day/month and years minus 1, 5, or 10. 

Cheers,

Rhia

[AkshayManke]

Hello @Rhiassuring,

Thanks for helping on this. Can you please elaborate the below step that which steps you/I will add to get the years completed as on today?

"From that, I'd then cycle through the resulting items, and filter based on years that are equal to today's year minus 1, or minus 5, or minus 10. "

Thanks,

Akshay

[Rhiassuring · Accepted solution]

Hi there, 

So here's what I'd do (and I've done it beginning to end, in case anyone else will benefit)...

🌠Step 1 - A list! 

Employee and StartDate are straight forward -- I opted to use two other calculated fields, to identify "are the date & month equal to today?" and "what is the year of the start date?" just to make it easier to process. I also created a view (which I called "Anniversary"), to filter on StartDateToday = Yes.

🌠Step 2 - 1, 5, 10!

I created 3 "Compose" actions, to identify what today's year minus 1, 5, or 10 would be. That way we can cycle through for only those years.

📅 string(sub(int(formatDateTime(utcNow(),'yyyy')),5))

The above looks at today's year (2022) and says, hey, what is 2022 - 5 years? Oh, it's 2017. Cool, now we know that people celebrating their 5th anniversary will have a year of 2017.

🌠Step 3 - A condition!

Since I don't care about any anniversaries that are not 1, 5, or 10 years old, I am using a condition to exclude them. It's looking at the StartDateYear, and then the Output from our Compose for each 1, 5, and 10 years. It's saying, "if it isn't equal to any of these, then, it goes into the "No" condition and is ignored." If it's a true for any of them, we move into the Yes column.

🌠Step 4 - Identify which year and email.

I didn't add an email - but - they would all use the same format, dropping in the number of years and Employee Email.   To get the number of years, I used a Compose to put the StartDateYear (since it's a calc column, it's picky) into, then referenced that compose in subtracting it from today's year. That gives me the final number.

sub(int(formatDateTime(utcNow(),'yyyy')),int(outputs('Start_date_year')))

I hope that helps. There are likely tons of ways to do this one, but, this was my approach...

[AkshayManke]

Fantastic @Rhiassuring, I will try to design the flow in the suggested way and will get back to you with the results in next 3 days.

Warm Regards,

Akshay