Hi @jeyeline
Try this approach.
I have created a demo sharepoint having employee details with below columns
Now let's see how to create the flow
1. Use a scheduled flow type so to make it run everyday
2. use get items to get all items from SharePoint list
3. Use initialize variable to initialize years variables which would be storing aniversary years later
4. use another initialize variable action to initialize rulesjson variables which will be storing suffix details to be added to numbers/aniversary years
5. use apply to each to loop through each items of get items action
next steps will be done under the apply to each loop
5.1 - use condition to check whether the current date is employee's work aniversary date ?
Expression used- formatDateTime(items('Apply_to_each')?['DateOfJoining'],'MM-dd')
If the condition satisfies - all the below steps mentioned will be performed in yes branch
a. Compute the number of years using an expression in compose action ( i have named the Compose action as Number of years)
Expression: - sub(int(formatDateTime(utcNow(),'yyyy')),int(formatDateTime(items('Apply_to_each')?['DateOfJoining'],'yyyy')))
we will be assigning suffix to the numbers (like th,nd,rd) based on below rule
b. Use condition to check whether the numbers of years is multiple of 10 or it is >=11 or <=19
if the above condition is true - use set variable to assign the number of years with suffix like below
Expression: - concat(string(outputs('Number_of_years')),'th')
if condition is false,
Then use the unit digit condition to assign the suffix
use the expression in condition to check whether the unit digit of number of years is equal to 1 or 2 or 3
if the above condition is Yes - use set variable action like below
Expression: concat(string(outputs('Number_of_years')),variables('rulesjson')?[last(string(outputs('Number_of_years')))])
if condition is False - then use set variable with below expression -
Expression -
concat(string(outputs('Number_of_years')),variables('rulesjson')?['Default'])
(Both of the expressions used in set variable in above yes/no branch are using data from rulesjson variable to assign suffix based on the unit digit of number of years value)
c. use send email to send email to required person/employee using details from sharepoint like below
Results of the flow
if the employee date of joining is Feb 25 2001 -> then bot will send an email like this
Hope it helps !
