Send emails based on calculated column date equal to today

[markikav]

Hi 

I have a scheduled flow to run daily for a process whereby they need to send auto emails to users for follow up actions after 3,5 or 7 days.

So if Strike1Issued date is not blank and DateCompleted is blank then it is an item in scope.

Then I need to see if the Follow up Day for 3 business days, 5 or 7 business days is today , if it is today send a reminder mail.

These are the SharePoint fields, the FollowUPxDay columns are auto calculated date only fields.

In my Flow I use an ODATA Filter Query just to get the items in scope, this list will have thousands of records in Production and items in scope daily will be 100-200.

Then I assumed from this filtered data I can put in condition(s) to compare the FollowUP dates with today and then if true send the appropriate email.

In the example here I've just tried to do it for one condition - FollowUp3Day.

Sometimes it works but I found that for my first record today the FollowUp3Day entry is 27/05/2022.

However when I look at the output in Flow it has - 

"FollowUp3Day":"2022-05-26T23:00:00Z"

Even though I used the recommended expressions from other posts to filter out the time and just use date as follows 

1 - formatDateTime(items('Apply_to_each')?['FollowUp3Day'],'yyyy-MM-dd')

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

So for some reason it's putting the calculated date at 11pm the night before?! and then doesnt execute cause the dates dont match?

2 Questions then - 

1. What is wrong with the dates for the calculated fields - it is set as date only, not date and time in SharePoint so it's something in Power Automate?

2. Is this the best way to achieve the goal ?  The different FollowUPDays have slightly different actions to be done also e..g the FollowUp7Day when executes also closes the item and sends a different mail to the other days. So that's why I thought it needs conditions or maybe a Switch statement but not sure how to get that to work.

Thanks!

Mark 

[eliotcole]

I would wager that this is to do with time zones and daylight savings, you might need to look at which time zone your environment is set at, and what they're being input as.

This isn't something I have a complete rundown to hand on, but I reckon there's likely a fair few posts about it here, and a little bit of stuff available in the Microsoft Docs to maybe help. Although I'd bet you can probably sort it all out by finding out which timezone your environment is in, and adjusting accordingly.

[markikav]

Hi @eliotcole 

That's interesting, thanks for the steer.

SHarePoint site regional settings are UTC - Dublin etc 

and the Recurrence card in Flow is set to same

So is the setting somewhere else in Power Automate that needs changing?

[eliotcole]

I *think* so, but don't quote me on that.

Check the PA settings, but each SharePoint site (hub, site, or traditional sites/subsites, etc) can have its own locale.

There may be a simple way to check this, which I'll look in to, but when you consider that I *think* that you can also maybe set a locale for a list ... then you're getting in to levels of diminishing anguish! 😅

This said, one of the many date formats out there is the one that Microsoft designate as 'r' in their docs. You could check to see if any date value has a region at the end.

---

EDIT - OK, so this HTTP GET request to SharePoint will provide the site Timezone:

_api/Web/RegionalSettings/TimeZone

The body response is formatted thusly:

{
  "d": {
    "__metadata": {
      "id": "https://YOUR_DOMAIN.sharepoint.com/sites/YOUR_SITE/_api/Web/RegionalSettings/TimeZone",
      "uri": "https://YOUR_DOMAIN.sharepoint.com/sites/YOUR_SITE/_api/Web/RegionalSettings/TimeZone",
      "type": "SP.TimeZone"
    },
    "Description": "(UTC-08:00) Pacific Time (US and Canada)",
    "Id": 13,
    "Information": {
      "__metadata": {
        "type": "SP.TimeZoneInformation"
      },
      "Bias": 480,
      "DaylightBias": -60,
      "StandardBias": 0
    }
  }
}

[googleadmirer]

Hi,

i now work on the same scenario to send reminder on 6,10, and 12 days before due dates  using calculated column. however, i cannot achieve it due to filter query condition that disable the use of calculated column. maybe you can elaborate more on the workaround to send reminder by filtering the (6,10, and 12 days) date  eq to todays date

[eliotcole]

Hi, @googleadmirer, I reckon you might best raise a new ticket for this.

When you do, try to make sure that you've done these few things below, as they're greatly assist anyone that tries to help you out:

1. Search the forums, here ... there's been a lot of repeat questions lately, and solutions are often right around the corner (or even in the side box called 'Recommended' !)
2. Include some sample data (it can be faked) with correct column/field titles
3. Include screenshots of any flow attempts that you have made (obfuscate sensitive information).
4. A reasonably succinct end goal, so that you can avoid moving goalposts, etc, during the process of getting help. 🙂

The most important one is the second one, as it shows what you have tried so far, and often (even more than the question!) it can really help anyone assisting you gain context on what you're trying to do.