Power Automate Exchange

Hello,

 

My goal is making a flow that sends out a birthday alert to our staff via Microsoft Teams 1 day before their birthday or half birthday. I have tried multiple different methods, but they all come down to the same result (The flow was succesful, but it failed to send the message to teams because the branching condition is not satisfied.)

 

My most recent attempt is this:

 

- I added a column that automatically subtracts one day from their birthday (Column Name: Birthday Alert)

- I added another column that automatically compares the day before their birthday, to todays date. (Value of 1 or 0) (Column Name: Birthday Alert Today?)

- I set up "Reccurence" --> "Get items" --> "Apply to each" --> "value" -->  Condition: Birthday Alert Today? is equal to 1

- If yes: Send a teams message. If No: Do nothing


It returns the error below.

How can I fix this issue?

 

and if this issue is fixed, and the branching for the condition for the Teams message is still not satisfied, how do i fix that?

 

Also, could this be causing the issue?

 

As you can see, my sharepoint list items that have a birthday tomorrow are showing a value of '1'.


But when I filter that column by '1', it says there are no items that fit that criteria.

Thank you

 

I am very new to Power Automate, so please explain it to me like im 5 years old if possible 

------------------------------
Blake LeMay
Operations Supervisor
------------------------------

I have a very similar process I gather birthdays and anniversaries between 14 and 21 days from the run date, and send out calendar reminders to all staff for each day there is a notable event (multiple people on the same day are grouped into one invite). Sharepoint calculated columns are tricky to work with, they don't recalculate behind the scenes, only when the page loads when you actually open it do they refresh. I think you are on the right track as to the #value! throwing the flow off, that could be fixed by converting the value to an integer, but if you want to use the calculated values you have to use the "Send HTTP Request To Sharepoint" action to ​​trigger the refresh behind the scenes in order to use it.

The way I do it is I have my list of birthdays, and I created a calculated column to pull just the month and day of the birthday. So in sharepoint my column looks like Birthday: 5/18/2022 birthmonthday 0518. Back in power automate I have a get items from sharepoint to get all my birthday items, then two filter arrays to narrow my scope to the 7 days I need. In your case you could use your condition action and do where int(BirthMonthDay) -> this converts your value to a number,  equals outputs('Int_End_Day')
Where Int_End_Day is defined by the following sequence of thee individual steps preceding it:
EndDate: Get future time of 1 day

EndDay: skip(concat('00', string(formatDateTime(body('EndDate'), 'MM'))), sub(length(concat('00', string(formatDateTime(body('EndDate'), 'MM')))), 2)) skip(concat('00', string(formatDateTime(body('EndDate'), 'dd'))), sub(length(concat('00', string(formatDateTime(body('EndDate'), 'dd')))), 2))

Int_End_Day: int(outputs('EndDay'))


------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------

Original Message:
Sent: May 17, 2022 02:40 PM
From: Blake LeMay
Subject: Issues with Birthday Alerts using a SharePoint list

Hello,

 

My goal is making a flow that sends out a birthday alert to our staff via Microsoft Teams 1 day before their birthday or half birthday. I have tried multiple different methods, but they all come down to the same result (The flow was succesful, but it failed to send the message to teams because the branching condition is not satisfied.)

 

My most recent attempt is this:

 

- I added a column that automatically subtracts one day from their birthday (Column Name: Birthday Alert)

- I added another column that automatically compares the day before their birthday, to todays date. (Value of 1 or 0) (Column Name: Birthday Alert Today?)

- I set up "Reccurence" --> "Get items" --> "Apply to each" --> "value" -->  Condition: Birthday Alert Today? is equal to 1

- If yes: Send a teams message. If No: Do nothing


It returns the error below.

How can I fix this issue?

 

and if this issue is fixed, and the branching for the condition for the Teams message is still not satisfied, how do i fix that?

 

Also, could this be causing the issue?

 

As you can see, my sharepoint list items that have a birthday tomorrow are showing a value of '1'.


But when I filter that column by '1', it says there are no items that fit that criteria.

Thank you

 

I am very new to Power Automate, so please explain it to me like im 5 years old if possible 

------------------------------
Blake LeMay
Operations Supervisor
------------------------------

Hi,

I have a similar process but I do all the math in Powerautomate instead of Sharepoint. My sharepoint list only has the birthday listed for each member, but actually in a text format (not date/time) to make my life easier...

I actually have 2 flows running. One which will send an email to the entire list - except for the birthday boy/girl - 5 days up front and one which will post a message in teams on the day of the birthday. I also use a PictureURL stored in the SP list to brighten up the email and the post but that is optional off course. Printscreens can contain Dutch because I'm sending the emails in my own language. I will demonstrate the most difficult Flow, sending a message in teams on your birthday is quite simpel

send an email to the list 5 days up front:
The main problem here was to separate the birthdayboy from the rest of the list and only send an email to the rest of the team. It would be weird to receive a warning for your own birthday...
trigger is a recurrence on every day at 9am
Step 1: Get Items to get all the records from your sharepoint list
Step 2: calculate Today: formatDateTime(utcNow(),'dd-MM')
Step 3: calculate Today +5 :formatDateTime(addDays(utcNow(),5),'dd-MM')
Step 4: initialize 3 variable that we will use later on: varBirthdayboy, varOthersemail, varGetBirthDateFromSP
Step 5: condition check to see if a birthday will occur (flow runs every day at 9am)
Loop for every record in your sharepoint list: check if birthday occurs --> input: get the birthday in format dd-MM (substring of the listed birthday)
IF YES 'today+5' = output of previous substring --> we have a Condition birthdayboy
IF NO 'today+5' = output of previous substring --> we have a Condition othersEmail

Condition Birthdayboy (to be able to send a nicely formatted mail):
append to string variable varBirthdayboy: Firstname + Lastname from the SP list
append to string variable varGetBirthDateFromSP: output of previous substring

Condidion Othersemail:
append to array variable varOthersEmail: put all the email addresses from everyone in the list except the birthdayboy in an array so we can email them later

Decide on sending an email or not:
Condition: if the length of varBirthdayboy not is equal to 0
YES: join(variables('varOthersEmail'),';') --> join all email addresses from the previous array with a ; to compose your TO address
send an email with all your variables you created
TO: output of the JOIN
subject: varbirthdayboy will celebrate their birthday within 5 days
...




------------------------------
Koen Mathijs
------------------------------

Original Message:
Sent: May 17, 2022 02:40 PM
From: Blake LeMay
Subject: Issues with Birthday Alerts using a SharePoint list

Hello,

 

My goal is making a flow that sends out a birthday alert to our staff via Microsoft Teams 1 day before their birthday or half birthday. I have tried multiple different methods, but they all come down to the same result (The flow was succesful, but it failed to send the message to teams because the branching condition is not satisfied.)

 

My most recent attempt is this:

 

- I added a column that automatically subtracts one day from their birthday (Column Name: Birthday Alert)

- I added another column that automatically compares the day before their birthday, to todays date. (Value of 1 or 0) (Column Name: Birthday Alert Today?)

- I set up "Reccurence" --> "Get items" --> "Apply to each" --> "value" -->  Condition: Birthday Alert Today? is equal to 1

- If yes: Send a teams message. If No: Do nothing


It returns the error below.

How can I fix this issue?

 

and if this issue is fixed, and the branching for the condition for the Teams message is still not satisfied, how do i fix that?

 

Also, could this be causing the issue?

 

As you can see, my sharepoint list items that have a birthday tomorrow are showing a value of '1'.


But when I filter that column by '1', it says there are no items that fit that criteria.

Thank you

 

I am very new to Power Automate, so please explain it to me like im 5 years old if possible 

------------------------------
Blake LeMay
Operations Supervisor
------------------------------