Sending a birthday email from CDS fields using Flow

This WTF episode came about from fellow MVP Joel Lindstrom. The challenge was to have Flow send a birthday email to Contacts when the day and month of their birthday equals today's day and month. I gave it a go with CDS (Dynamics 365) and Flow.

I did hit a roadblock but I overcame it. Most of my Flows you have seen when I first launched my series was educating you around things you are familiar with today in Dynamics 365 workflows. I'm now going to step it up a notch by showing you more advanced Flows.

In this WTF episode I use a combination of actions and expressions which you haven't seen before in my previous posts. Sending a birthday email doesn't sound trivial but underneath the hood it can be because you need to think about

• How do I retrieve today's day and month
• How do I compare this with a Contact's birthday
• I only want to send birthday emails to Contacts whose day and month of their birthday equals today
• I need to schedule this on a daily basis

Buckle up cause you're about to Flow with me 🙂

Get Flowing

1. Trigger for the Flow to run daily

This logic needs to happen on a daily basis and the trigger you want to use is Recurrence. This allows you to set the parameters of how often the Flow should execute.

2. Compose action to identify the month and day of today's date

In order to identify today's date, we need to retrieve the current date. To do this we need to write an expression that will allow us to retrieve the current UTC time.  The other thing you need to remember is that date and time in Flow is treated as UTC, it will be in the format of yyyy-MM-ddThh:mm:ssZ

The expression needs to go one step further by converting the current UTC time to the local time zone and because we're only interested in the day and month, we only need to retrieve the day and month value.

For the string value of the time zone, I grabbed it from this Microsoft list. I was accidentally looking at an incorrect link but fellow MVP John Liu kindly pointed me to the right list. Thanks!

To do this we use the compose action.

The expression to use is:

convertFromUtc(utcNow(), 'AUS Eastern Standard Time','MM-dd')

3. Compose action to split the month and day

Now that we have retrieved the current day and month, we need to separate the two values as it's still in a UTC format of MM-dd. To do this we use the split function. The split function in PowerApps is the same in Flow.

The expression to use is: 

split(outputs('Convert_to_Time_Zone') ,'-')

4. Compose action to set the day value

Now that the previous action will provide outputs of month and day, we now need to set the day value in its own compose action so that we can refer to it in our criteria in a later step/action in the Flow.

The previous action provides the outputs in an array so in our expression, we need to reference the second value. 

The expression to use is:
outputs('Split_MM-dd')?[1]

5. Compose action to set the month value

The same applies in this action to set the month value.

The expression to use is:

outputs('Split_MM-dd')?[0]

6. List records to only retrieve Contacts whose birthday day and month equals today's day and month value

List records allows you to target only records that meet criteria. This is where Odata functions come into play. I decided to use the day and month function so that I can check that the birthdate (schema name) field's day and month matches my #4 and #5 compose action outputs.

The expression to use for the filter query field in the List records action is:

day(birthdate) eq '@{outputs('Set_Day')}' and month(birthdate eq '@{outputs('Set_Month')}'

7. Loop through all Contacts that meet the criteria

Use the Apply to Each to perform an action for all Contacts that meet the criteria.

8. Send email to the Contact

The action I demonstrated in my WTF vlog is the Send email (V2). The Flow team recently released an updated action that is a basic WYSIWYG editor. Enter in the email message content and you're good to go.

Spoke too soon

When your trigger your Flow, the Flow will fail. This was a true WTF moment for me as the error that spits out is:

The 'day' function isn't supported.

WHY?! 😭

Flow returns this error because of how the Odata does not support the day and most likely the month function. Even though Odata version 4.0 supports it:

Dynamics 365 does not.

I confirmed this by using fellow MVP Jason Lattimer's CRM REST builder. The message returned is:

So yeah, what now?

Get creative

In the Helper text of the filter query field in the List records Flow  action, it says it supports string and integer. I created two single line of text fields. I went with single line of text field so that I can use the Odata filter operation of "eq"

One field represents the day of the birthdate field, the other field represents the month of the birthdate field.

I then created another Flow that will trigger on create of a Contact to retrieve the month and day of the birthdate value, and populate the fields. Similar actions to my primary Flow is used which I explain my vlog.

Filter query expression take 2!

Alrighty, now we can update the filter query to use the custom fields to check that the day custom field value equals the compose action (#4) that sets the day value AND the month custom field equals the compose action (#5) that sets the month value.

The expression is:

ben_dayofbirthdate eq '@{outputs('Set_Day')}' and ben_monthofbirthdate eq '@{outputs('Set_Month')}'

Voila

Now when the Flow runs, no failures occur and the List records action successfully only retrieves Contacts whose day and month field values equal the day and month outputs from our compose actions.

YES!!!

My final comments

• Assumption is the contact is in the same time zone that’s used in the convert UTC to local time zone action
• I have not tried this with large volumes of data, 100,000+ records so am unsure what the performance would be like
• Schedule recurrence at an appropriate time

Summary

You can schedule sending a birthday email through Flow. Trick is to use a couple of custom fields and the a supported Odata operator in your query. I really enjoyed going through the challenge of getting this to work.

I'm curious to know if others would have approached this differently. Like Dynamics 365/CDS, there's more than one way to achieve a desired outcome. If you find another way, give it a go with Flow and post a tweet by referencing @benitezhere so that I can check it out.

Stay tuned as I have another cool post coming up.

Help me get to 500 YouTube subscribers

As mentioned in my vlog, subscribe to my YouTube channel if you haven't already done so as Will from TDG will supporting my milestone achievement by doing a sheperd's pie vlog. This was mentioned in the Power Apps podcast episode where I was a guest and chatted to Shawn, Chris and Will. It would be great to see in Will in the kitchen 😄

Jokes aside, it would be great if I reach 500+ subscribers. I started in 2014 and I'm going to keep going till I can. Thank you!