Sending an Email Reminder

[sajarac]

Hi there,

I have a share point list "Employees" and I would like to send an email reminder 3 days before and event occur like the birthday or a contract renewal. Both dates are in the past so that is why is getting tricky to figure this thing. I mean for the birthday we are now interested in the year just to match mm/dd with today's mm/dd, and the same for the contract renewal.

So I have created this complicated SPL where basically I am extracting the MM/dd from the birthday and Hire date and the MM/dd from today's and comparing values making an IF Statement. The problem is Sharepoint doesn't update a calculate field so my Current date keeps in the date that was saved or updated. I have tried many ways and none of them works. 

So, to summarize contract flow and BirthdayFlow will appear 4 days "Yes" until both days are exactly i.e 12/21 eq 12/21 that condition plus if active2 = Yes, will trigger the flow. 

I can't get this to work for the SPL side, I have tested and I can get the email reminder but if I update the SPL.

Someone could point me in the right direction?

Thanks in advance,

[ScottShearer]

@sajarac 

First, if your TodayMD column is a calculated column, it won't get updated unless you edit and save each individual item.

There are a number of different solutions to your issue.  Can you tell me how many items are in your list so that I might suggest the appropriate solution?

[sajarac]

Thank you very much for your prompt response, in the list I have 50 items and it won't increase more than that

[sajarac]

Also I am implementing this but flow is complaining also:

formatDateTime(outputs('Compose'),'MM/dd')

addDays(utcNow(),-3,'MM/dd')

[sajarac]

Hi @ScottShearer , I have changed the structure a little bit, and I am getting an email reminder but only in the day of the event. 

So, could you please advise how can I add a condition to check 3 days in advance?

DOBMD = 12-22

formatDateTime(outputs('Compose'),'MM-dd')

formatDateTime(utcNow(), 'MM-dd')

Thank you kindly

[bottekott]

DAYOFYEAR Function | Microsoft Docs

Returns an integer, 1 to 366, that represents the sequential day of the year in datetime or expression. The DAYOFYEAR function uses the Gregorian calendar.

So, to check if DOB is 3 days ahead do this:

• Compose (remove 3 days from DOB): addDays(DOB,-3)
• OR use the "subtract from time" action, subtract 3 days

• Condition;
  • dayOfYear(output from previous step)
  • is equal to
  • dayOfYear(utcNow())

This will probably be 1 day off in leap years, but you can adjust your flow those years if you want to :).

[KoenM]

@sajarac 

the way I implement to sent a birthday reminder is the following:

* I have a sharepoint list where the birthdate is kept in text format

* every day I run a flow that takes this birthdate minus the year, so I only keep mm/dd

* this is evaluated with today() + 5 (send a reminder 5 days in advance)

*if this is true I add the first and last name to a variable

* later on I check if this variable contains any characters, if not it's nobody's birthday in the next 5 days and I terminate the flow. If yes, an email is send to the rest of the team informing them of a birthday coming up

[sajarac]

Thank you both @bottekott and @KoenM. I still getting mental with this. I have 3 flows and only one is working so far:

Flow 1 = Send an email reminder for Birthday \ Not working!

Flow 2= Send an email reminder for Contract Renewal \ Not working!

Flow 3= Send a Birthday Card. | Working !!!!

The send an email reminder for a birthday and contract renewal I would like to happens  3 days before the day of the event, 2 days and the day of the event.

And also I have changed my SPL to get both values DOB and HireDate with this format MM-dd. don't know if this helps but having the format MM/dd flow was complaining.

Now I am stuck again don't know how to add that condition for the birthday reminder and the contract renewal.

Thanks in advance for any help.

Regards,

[Anonymous]

Hello @KoenM ,

Related to your message in this topic. Could you please be so kind and describe a little more specific the last point "send to the rest of the team...". I have very simillar situation but i can't handle how to exclude the birthday celebrant from recipient list. 

[KoenM]

Hi,

I use a double condition to check if the member of my list has a birthday, else he's on the "others" list.

condition birthdayboy: if today() = DOB

condition others: if today() != DOB

you can change today() to today()+5 if you want a 5 day advance notice

if condition birthdayboy = true --> append email address to varBirthdayboy

if condition others = true --> append email address to varOthers

ultimately check if varBirthdayboy != empty, then send the email to varOthers

Loop this every day for every member and you will have to variables when someone's birthday is coming up. Only send a warning to the others list, the birthdayboy will probably know his birthday is coming up 😉

[KoenM]

Hi, 

I think you are overcomplicating things here. I don't see the need to split the dates into days and months separately.

I start my flow with setting variables for Today

---

@sajarac wrote:

Hi there,

I have a share point list "Employees" and I would like to send an email reminder 3 days before and event occur like the birthday or a contract renewal. Both dates are in the past so that is why is getting tricky to figure this thing. I mean for the birthday we are now interested in the year just to match mm/dd with today's mm/dd, and the same for the contract renewal.

So I have created this complicated SPL where basically I am extracting the MM/dd from the birthday and Hire date and the MM/dd from today's and comparing values making an IF Statement. The problem is Sharepoint doesn't update a calculate field so my Current date keeps in the date that was saved or updated. I have tried many ways and none of them works. 

So, to summarize contract flow and BirthdayFlow will appear 4 days "Yes" until both days are exactly i.e 12/21 eq 12/21 that condition plus if active2 = Yes, will trigger the flow. 

I can't get this to work for the SPL side, I have tested and I can get the email reminder but if I update the SPL.

Someone could point me in the right direction?

Thanks in advance,

---

(formatDateTime(utcNow(),'dd-MM'
) and Today+5 (

formatDateTime(addDays(utcNow(),5),'dd-MM')

Then I start a condition to see if a birthday occurs or not by getting the Sharepoint DOB in the same format. I save it like dd-mm-yyyy so I just cut off the last 5 characters from the string with leaves me a dd-mm format, exactly the same as my variables

condition = if output from today+5 is equal to output of get sharepointDOB then you know that in 5 days someone will have a birthday

If TRUE, add the first and last name from that record to a variable, you can use this variable later on in your email

after this, add another condition which checks if this variable is empty or not. If not, you can send an email to whoever you want to know that someone is having a birthday

That's all the logic that you need, you can enhance it a bit further with sending a custom image if you store this also on your sharepoint. I'll leave the rest to your imagination