Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
 View Only
  • 1.  Issues with Birthday Alerts using a SharePoint list

    Posted May 17, 2022 02:40 PM

    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?)

    list1.PNG
    list22.PNG

    - 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

    flow.PNG
    It returns the error below.

    flowwwfail.PNG

    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'.

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

    filter.PNG

    Thank you

     

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




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


  • 2.  RE: Issues with Birthday Alerts using a SharePoint list

    Top Contributor
    Posted May 18, 2022 07:29 AM
    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
    ------------------------------



  • 3.  RE: Issues with Birthday Alerts using a SharePoint list

    Silver Contributor
    Posted May 19, 2022 05:39 AM
    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
    ------------------------------