Sending an email with multiple conditions on specific date

[ovis2018]

Hello - just joined the community to get assistance with a Flow I've been working with for several days and getting hung up on one last element. My goal is to create a flow that ultimately sends an email on a specific date using two additional conditions. 

While I've successfully created a flow using "Recurrence > Get Items > Apply > Condition > Condition > Delay Until > Send Email", I found that the flows sit running for extended period of times until the "Delay Until" date is met and I'm concerned that the flows will (a)time out if they run for too long or (b)consume too much bandwidth if there are too many flows running at one time.

So I'm trying to use a third condition that checks if the specific date is equal to today, instead of "Delay Until" so that the flow doesn't sit running for an extended period of time.  I'm getting stuck on creating the statement for the 3rd condition and not sure what the issue is.   I'm thinking maybe the formatting of the SP list item data point is not matching with the format of uctNow but really not sure. The date of the specified field I'm trying to use displays as: 

2019-01-30T08:00:00Z

The formula for the 3rd condition testing the date=today that I'm using is: 

@equals(formatDateTime(items('Apply_to_each')?['Reconciliation_x0020_Request_x00'],'yyyy-MM-dd'),formatDatetime(utcNow()),'yyyy-MM-dd')

Thank you! 

[ScottShearer]

@ovis2018:

First, I don't see anything obviously wrong with your Flow.  The condition looks right to me.

Is your Flow generating an error or just not producing the results that you expect?  If it is generating an error, can you post a screen shot of that error?  If there is no error, is it possible that you have some type of logic error?

Also, you are almost always better off filtering items coming from SharePoint using an OData filter.  Your Flow will run faster and you will avoid limitations in the number of items that can be returned by Get Items as well as the maximum number of loops.  I would consider moving at least your first two conditions to an OData filter.

Scott

[ovis2018]

Hi Scott, 

Thanks for the reply.  No error is being generated but I am not receiving the expected result (i.e. email delivery) despite several setup scenarios that should trigger the condition to result in an email.  

Appreciate the tip for using OData! I'll do some research into that.  That would be awesome to maximize the run time! 

Thank you!

-K

[ScottShearer]

@ovis2018:

I just noticed a couple of things that you may want to check.

First, you have a condition that checks for a value of Yes.  If this is a yes/no column, you should be checking for true.  To set it to true, enter true in the expression window and click on OK.

Also, you are checking Approval Status,  If this is a choice column, you should be checking the Approval Status Value column from Dynamic Properties rather than the Approval Status column.

Please check and let me know if this helps.

Scott

[ovis2018]

Hello again, 

The condition checking Approval Status is checking a value as it's a choice field, and the Costs Reconciled condition is looking for "Yes" as the Costs Reconciled column is a calculated column with a resulting "Yes" or "No" text.  Both of these conditions do not seem to cause the issue and when the 3rd condition of the date is removed, they trigger the expected result.  

The 3rd condition looks as though it's working properly as when tested it runs without failing and there's the green check (as seen in image below) but again, no emails are developed despite 100% assurance there are two SharePoint list items that match the logic. = /

[v-bacao-msft]

Hi @ovis2018,

Perhaps because of the time zone.

There may be a time difference between the time recorded in SharePoint and the time acquired in Flow.

You could create a new Flow for testing, and use the Compose output "Reconciliation_x0020_Request_x00" to compare the difference with the current time.

Best Regards,

[ovis2018]

Hi @v-bacao-msft,

Interesting point to investigate! It seems the two times of UTC-now and reconciliation date are not aligned by the time versus the date.  When I composed with the two data points, the following is what I get for a reconciliation date of today and UTC-now.  FYI - time of running was 9:52am.

2019-02-01T08:00:00Z  recon date  

2019-02-01T16:52:11.0662117Z current date

What needs to change to make the two sync up?

Thanks for the help! 

[v-bacao-msft]

Hi @ovis2018,

You could try converting the recon date to local time using ConvertTimeZone() function.

Please take a check of the function here:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#c...

Then these two times are then converted into a format similar to ‘yyyy-MM-dd’ for comparison.

Please take a try.

Best Regards,

[ovis2018]

@v-bacao-msft - thanks for the tips.  You would think with that addition, things would be working but still getting a "false" result for the condition checking the date matchup.  

**site is not lettting me upload an image so below is a typed out version of the results I'm getting**

-Convert time zone- (green check)

INPUTS

{

"baseTime": "2019-02-04T08:00:00Z",

"formatString": "u",

"sourceTimeZone": "Pacific Standard Time",

"destinationTimeZone": "UTC"

}

OUTPUTS

{

"body": "2019-02-04 08:00:00Z"

}

---->

-Condition 3- (green check)

INPUTS

Expression Result

false

---------------------------------------

A few thoughts:

(1) did the conversion of time zone work as it's showing the output in Pacific Time with the 08:00:00Z?

(2) because Condition 3 is set as the below, the formatting should be adjusted so both UTCnow and the field in question (Reconiliation Date) are the same - correct?

  Condition 3: @equals(formatDateTime(items('Apply_to_each')?['Reconciliation_x0020_Request_x00'],'yyyy-MM-dd'),formatDatetime(utcNow()),'yyyy-MM-dd')