Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

(0) Share

Report

Posted on by Chris_W

120

Hello,

Hoping to get some assistance/guidance on how to complete this Power Automate Flow.

We've got a custom field, Lease End Date, in our Opportunity table. I want to have a Flow that takes that date and sends an email to the Opportunity owner (contact owner could work as well if easier) 90 days before that lease end date, letting them know to follow up with the client. I'm aiming to have it run every morning and pick out the Opportunities that have hit the 90-day window and then email the owner.

So far I've been able to set it up so that it pulls every Opportunity within a 90-day window. The issue is that if it runs daily, then it's going to be sending the same email every day to the owner. I'm having trouble filtering the results down to opportunities at 90 days and not just within the 90-day window.

Here is where I'm at so far. In the screenshots, I'm using the old Dataverse connector since I was hitting a wall with the new one. On the new one I used FetchXML to pull all the Opportunities within the 90 day window and got stuck not being able to narrow it down to ones that were 90 days on the run date.

I'd really appreciate some guidance on this one.

Thank you,

Chris

Screenshot-2022_2D00_11_2D00_16-at-2.28.57-PM.png

I have the same question (0)

All responses (9)

Answers (1)

Chris_W 120 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Thinking more about this, I'm wondering if there is a way to take the UTC time that is returned when I add the 90 days, and then convert it so that it keeps the +90 days but changes the time to midnight so it aligns with the dynamics records.

So if I'm running the flow right now.

Current time - 2022-12-12T13:43:53Z

Add 90 Days - 2023-3-13T13:43:53Z

Any function to convert that 90 days time to - 2023-3-13T5:00:00Z ??? Midnight seems to align with all of the lease end dates in dynamics as it's a calculated field. I'm thinking if I filter the query by that date with a midnight time it should work correctly.

Thanks,

Chris

Was this reply helpful? Yes No
Chris_W 120 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Steve Zhao2,

Thank you for the added input. I got pulled onto something else and am just now getting a chance to work on finishing this Flow.

I took your ideas together and added some additional actions using 3 instances of the Date/Time function steps to do the following:

1. Get Current Time

2. Take the current time and add 90 days to it

3. Convert the +90 days time to a short date pattern ie 12/12/2022

I was getting an error because I didn't take into account that Dynamics doesn't output the date format in a short date pattern when listing rows.

So I removed step three from above and changed my filter query (the one above in my first post) in the list rows function to this:

leaseenddate eq (the calculated time from step 2) - I switched the 90 days around in step 2 so that I can be sure that a result is returned as it matches a date in our system.

Running this didn't produce an error, but also didn't return a result.

I'm thinking the issue is that Dynamics has the lease end date field as a date 2023-03-05 but the time seems to be a static number. Whereas when I take the time in Power Automate, add 90 days to it, the result is 2023-03-05T22:16:07.2950081Z.

So when I'm running a filter query, my date/time will never equal the time in Dynamics.

Do you know of a way around this issue? The date is working, but I'm sure the time is the problem when trying to run a query using equals.

Really appreciate the help!

Best regards,

Chris

Was this reply helpful? Yes No
Suggested answer

Steve Zhao2 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Chris_W,

This definitely could work. As you say, your lease end date is 11/16/2023 and assume that the opportunity owner should receive email on 8/16/2023(-90 days).

Since your flow runs every day, you could get the run time of the flow. Assume that the run date is 8/16/2023, then you can get the date by utcnow function date and then add 90 days to run date is 8/16/2023.

So, when you list your rows, you need filter the lease end date equals the run date + 90. Then you can only get the opportunities whose lease end date is 11/16/2023.

Finally, loop these opportunities and send email to their owner is ok.

You can have a try to do this. If you stuck somewhere, feel free to ask.

Was this reply helpful? Yes No
Chris_W 120 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Steve Zhao2,

Correct, the lease end date is a date-only field, and the plan is to have it run daily.

The confusion I'm having is that the lease end date is a date into the future. So, if the lease end date in dynamics was 11/16/2023, would I need to filter to the lease end date field minus 90 days (lease end day - 90 days).

If the lease end date is 11/16/2023, the opportunity owner will receive the email on 8/16/2023 (give or take a day since I didn't check the calendar). :)

Do you believe that would work? I'm not opposed to using the other suggestion, though I like your process if it can work like that since it's a cleaner way of getting to the goal without needing additional rows created.

Best regards,

Chris

Was this reply helpful? Yes No
Suggested answer

Steve Zhao2 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Chris_W,

Ok, it seems that your lease end date is date only. So, you need create a scheduled flow which runs every day. And then you could get today's date.

Adding 90 days to today's day equals 11/16/2023 is the date you need to send email to the owner, right?

So, you just need to filter the lease end date equals (today + 90 days) is ok. Then loop the list and then send email to the owner.

Was this reply helpful? Yes No
Chris_W 120 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Steve Zhao2,

Looking through the process above. Could I apply this and switch the dates to make it work?

For example, if a client signed today, 11/17/2022, and signed a 1-year contract, the lease end date field auto-calculates to 11/16/2023.

So I'd want the user to get an email 90 days before 11/16/2023.

Given your example, how would I adjust the days so that when the flow runs each day that the owner of the opportunity in my example would get an email 90 days before 11/16/2023?

I'm trying to work through it based on what you laid out, though I'm haveing an issue getting my head wrapped around it for some reason.

Thank you!

Best regards,

Chris

Was this reply helpful? Yes No
Chris_W 120 on at

Like (1)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Bipin Kumar,

That is a good option, and I've used something similar in a different process.

Thanks for the response! I've gotten great tips from your blog in the past, so thank you for the content!

Best regards,

Chris

Was this reply helpful? Yes No
Suggested answer

Steve Zhao2 on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi Chris_W,

It seems that you only want to get the 90 days on the run date's opportunities.

For the better understanding, I would try to get 21 days (10/27/2022) on the run date's person.

The follow is like this:

First, we need to get 21 days before date, so I create a variable and its expression is:

addDays(addHours(startOfDay(utcNow()),-8),-21)

The reason why -8 hours is that the value of createon date in People table has converted Timezone (My personal setting Timezone is UTC+8). In the dataverse, they are still stored as UTC date.

Note: only User Local need to convert Timezone. If your Lease End Date is Date only, there is no need to convert it.

Behavior and format of the Date and Time column in Microsoft Dataverse - Power Apps | Microsoft Learn

Then create another variable which is used to get 20 days before date. Its expression is

addDays(variables('21 day before'),1)

Finally, we can filter the records between those days is ok.

Was this reply helpful? Yes No
Verified answer

Bipin D365 28,981 Moderator on at

Like (0)

Report

RE: Using Power Automate to send reminder emails as contract end dates approach. Need some assistance

Hi,

How about Creating two option field Is Followup Email Sent?

Then set this field value to yes once you send an email. Add filter condition in your list rows to only fetch where this field is No

Was this reply helpful? Yes No