If Utcnow is greater than date in column - send an email (escalation)

[Krelz9210]

Hi, 

I have created an app via Sharepoint lists and powerapps that stores information about safety hazards

The individual safety hazards is assigned a due date, and the creater of the list item is the responsible.

I would like to Send an e-mail to the responsible if Today is later then the due date.

I have gone with a 1 week recurrence flow as my "trigger" 

Next i Get items of the list -> and now im unsure how to proceed 

I played around with the idea of creating a condition checking if Utcnow is greater than "due date"  -> If yes "send an e-mail" to the responsible (created by email) - i havent tested this, but im pretty sure it is not that easy 😛 - (unable to test atm) 

Please find attached the steps i've taken up till now - any help is highly appreciated 

Best regards and thanks in advance

[ekarim2020]

You can use an OData filter query to  select your items. Example:

Something like: Termin gt  ' today's date'

Note: the SharePoint column name used in the query (Termin??) does not need to be in single quotes but it must be the true name of the field (not its display name). To get the field name, view the column properties SharePoint, then look at the URL where it will show you the true column name (in the example below it's Project):

Ellis

[Pstork1]

If you have issues getting the Odata filter to work you can also use a Filter Array action.  The get Items will return all the records as an array.  You can then filter that array using a condition with the filter array statement.  Using Odata would be better since you don't retrieve records you don't need.  But sometimes its too complex to work with.  If that becomes a problem use the filter array.

[ScottShearer]

@Krelz9210 

First, be aware that the Get items action returns just 100 items by default.  If your list is or can be larger, go to the trigger settings, turn on pagination and set the threshold up to a maximum of 5000 (higher number possible if you have a premium license).  

You'll want to use a filter query in your Get items action rather than a condition.  That way, only items that meet your criteria will be returned.  All you need to do then is loop through the results and send your email.

Your filter query will look something like the screen shot below.  Please take note of the single quotes.  Also, DueDate may/may not work for you.  It is probably DueDate but the filter query requires the internal name of your column and internal names cannot have spaces.  The formatDateTime() expression puts the output of utcNow() in the same format as that used internally by SharePoint.

Please let me know if you have additional questions.

'

[Krelz9210]

Woah, thanks for all the great and elaborated answers!

[Krelz9210]

Hi Scott,

I will try and fiddle around with your suggestion later today - 

I missed an important detail when creating the post 

I have a Column of type Yes/no that we use to signal if the reported safety hazards are solved or not. 

I need a logic so that the flow will check that if the case is closed (I.E 'Yes') it will not Send an e-mail to the responsible 

How can i got about implementing this? 

Br

Kristian