trigger a flow based on a date

[DaleenB]

I have a document library for contracts that includes a "Months to Expiration" column which is calculated off the "Contract End Date" column.  I would like to trigger an email to the creator when the "Months to Expiration" equals 1.  I tried using the "Complete a custom action for the selected item" flow but have been unsuccessful in making it run.

I am brand new to Flow so I may be way off base here... if anyone has suggestions I would appreciate it!

 

[v-yamao-msft]

Hi DaleenB,

Please try to use the formatDateTime function and adddays function in your flow.

Then you could using the following trigger and actions and configure your flow likes below.

The code for the Condition is:

@equals(formatDateTime(items('Apply_to_each')?['EndDate'],'yyyy-MM-dd'),formatDateTime(adddays(utcnow(),-1),'yyyy-MM-dd'))

Then when the EndDate is equal to today’s date minus 1 day, an email will be sent to creator.

Please take this for a reference.

More details about workflow definition language, please check it at here:
https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

Please feel free reply if you need more help.

Best regards,
Mabel Mao

[DaleenB]

Thank you for the quick reply.  I must be missing something in the setup because the workflow fails when I run it.

[efialttes]

Hi

Can you share the expression from the Condition block? Please click first "Show in advanced mode" and you will get it in text format

Also, can you inspect the flow execution to identify the action block (or the loop iteration) where your flow in failing? You can go to "My Flows", select the flow you are testing, then go to "Run history" identify the last iteration that failed and click on it. This way you will be able to inspect both inputs and outputs from the flow execution.

Hope this helps

[DaleenB]

Hi!

The expression is:  @equals(formatDateTime(items('Apply_to_each')?['Contract End'], 'yyyy-MM-dd'), formatDateTime(adddays(utcnow(), -30), 'yyyy-MM-dd'))

So, I have shifted gears a bit from my first post.  I want this flow to run if the "Contract End" date is 30 days away.  So, if a user inputs 12/30/2017 as the end date, I want an email to be sent on 11/30/2017.

I have not had much training in writing expressions.  I copied the expression provided from an earlier reply and put "Contract End" because that is the name of the column with the information that I need for this flow.

[efialttes]

Hi again

I guess the problem is that the following dynamic data:

items('Apply_to_each')?['Contract End']

is sometimes NULL. I guess this means the Contract End value of some SP list items is empty. If so, I would recommend to handle NULL scenarios by means of coalesce() function.

This is a copy paste of a coalesce-based workaround described at https://powerusers.microsoft.com/t5/Flow-Ideas/If-item-equals-Null/idi-p/23308:

I had exact same issue.  I was able to work around using the coalesce function listed in Azure's Logic Apps documentation.  

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

Instead of checking for null, I checked if the date was greater than a date far in the past.  I wrapped the basic condition generated automatically by flow inside the coalesce function (you have to switch to advanced mode).  The coalesce function will not return a null.  If your date field is null it returns a "fallback" value that you set in the function.  Just set the fallback date less than the date in your condition.   In the example below I check to see if my ReceiveDate field is greater than 1/1/1991.  If yes then I send an email:

Basic condition: 

@greater(triggerBody()?['ReceiveDate'], '01/01/1991')

Advanced condition with coalesce function:

@greater(coalesce(triggerBody()?['ReceiveDate'],'01/01/1990'), '01/01/1991')

[DaleenB · Accepted solution]

Hi!  My data didn't contain any NULL items so this didn't solve my problem. However I did find this on a TechCommunity conversation.  https://techcommunity.microsoft.com/t5/PowerApps-Flow/Issue-with-creating-DateTime-Condition-in-Micr....

Converting the date to a string did the trick!  @less(string(item()['Contract_x0020_End']), adddays(utcnow(), 30)).

Thank you everyone for the help and additional information.  As I mentioned before I am brand new to expression writing and loved getting detailed on functions that I might be able to use in the future!

[v-yamao-msft]

Hi @DaleenB,

Glad to hear that you have figured out a solution and thanks for sharing.

Best regards,
Mabel Mao

[Ommuthu]

i have an issue can any one help

[Ommuthu]

I need a flow for StartDate And EndDate form sharepoint list for renewal purpose using a condition that is insame sharepoint list another column if that filed gets entry then when  flow runs mail need to come regarding EndDate to be RFenewal