SOLVED

Reminder emails - Flow fails when date value is empty

%3CLINGO-SUB%20id%3D%22lingo-sub-2118862%22%20slang%3D%22en-US%22%3EReminder%20emails%20-%20Flow%20fails%20when%20date%20value%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118862%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20hope%20you%20can%20help.%26nbsp%3B%20I%20have%20a%20flow%20which%20sends%20emails%204%20days%20before%20a%20target%20date%20as%20reminders.%26nbsp%3B%20The%20datasource%20is%20a%20SharePoint%20Online%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20emails%20are%20sending%20correctly%20for%20list%20items%20which%20meet%20the%20criteria%20(eg%204%20days%20before%20the%20date%20field%20value)%2C%20however%20the%20Flow%20is%20failing.%26nbsp%3B%20This%20is%20for%20items%20on%20the%20SharePoint%20list%20where%20there%20are%20%3CSTRONG%3Eblank%20values%3C%2FSTRONG%3E%20for%20TargetCompletionDate.%26nbsp%3B%20Failing%20on%20the%20'Apply%20to%20each'%20step.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Condition%20is%20using%20the%20'addDays'%20function%20to%20check%20against%20the%20'TargetCompletionDate'%20value%20(%3CFONT%3Esee%20screenshot%20Reminder%20flow%203.png%3C%2FFONT%3E).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20SharePoint%20list%2C%20%3CEM%3ETargetCompletionDate%3C%2FEM%3E%20is%20a%20Date%20and%20Time%20column%20showing%20date%20only%20in%20a%20standard%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20error%20I%20see%20is%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CFONT%3EApply%20to%20each%3A%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3E%3CSTRONG%3EInvalidTemplate.%20Unable%20to%20process%20template%20language%20expressions%20for%20action%20'Condition'%20at%20line%20'1'%20and%20column%20'15107'%3A%20'The%20template%20language%20expression%20'formatDateTime(item()%5B'TargetCompletionDate'%5D%2C'yyyy-MM-dd')'%20cannot%20be%20evaluated%20because%20property%20'TargetCompletionDate'%20doesn't%20exist%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EI've%20attached%20the%20error%20screenshot%20and%20also%204%20screenshots%20to%20show%20the%20flow.%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EI%20feel%20the%20solution%20lies%20in%20additonal%20conditions%20on%20the%20flow%2C%20but%20my%20attempts%20aren't%20working.%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EAny%20help%20would%20be%20greatly%20welcomed.%26nbsp%3B%20Thank%20you!%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2118862%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowerApps%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120002%22%20slang%3D%22en-US%22%3ERe%3A%20Reminder%20emails%20-%20Flow%20fails%20when%20date%20value%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425314%22%20target%3D%22_blank%22%3E%40DogAndPonyShow%3C%2FA%3E%26nbsp%3Bdon't%20know%2C%20I'd%20have%20to%20see%20what%20your%20flow%20actions%20before%20it%20are.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119994%22%20slang%3D%22en-US%22%3ERe%3A%20Reminder%20emails%20-%20Flow%20fails%20when%20date%20value%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E-%20thank%20you%20so%20much!%26nbsp%3B%20That's%20worked%20a%20treat.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20add%20a%20filtered%20expression%26nbsp%3B%3CFONT%3E'%3CFONT%20color%3D%22%23000080%22%3ETargetCompletionDate%20ne%20null%3C%2FFONT%3E'%3C%2FFONT%3E%20-%20the%20workflow%20no%20longer%20errors.%26nbsp%3B%20Amazing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESide%20question%3A%20under%20Dynamic%20content%20I%20don't%20see%20any%20of%20the%20SharePoint%20list%20columns%20(screenshot%20attached)%20-%20why%20would%20this%20be%3F%26nbsp%3B%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119157%22%20slang%3D%22en-US%22%3ERe%3A%20Reminder%20emails%20-%20Flow%20fails%20when%20date%20value%20is%20empty%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425314%22%20target%3D%22_blank%22%3E%40DogAndPonyShow%3C%2FA%3E%26nbsp%3Bin%20your%20Get%20items%20action%20add%20a%20filter%20query%20of%20%3CSTRONG%3ETargetCompletionDate%20ne%20'%7Bnull%7D'%3C%2FSTRONG%3E%20where%20%7Bnull%7D%20is%20an%20expression%20from%20the%20Expression%20tab%20in%20the%20dynamic%20content%20box.%20ne%20stands%20for%20not%20equal%20to.%26nbsp%3B%20Make%20sure%20you%20include%20the%20single%20quotes%20around%20the%20expression.%20With%20this%20filter%20it%20will%20only%20bring%20back%20items%20from%20the%20list%20where%20the%20TargetCompletionDate%20actually%20has%20something%20in%20it%20so%20should%20avoid%20the%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi there,


I hope you can help.  I have a flow which sends emails 4 days before a target date as reminders.  The datasource is a SharePoint Online list.

 

The emails are sending correctly for list items which meet the criteria (eg 4 days before the date field value), however the Flow is failing.  This is for items on the SharePoint list where there are blank values for TargetCompletionDate.  Failing on the 'Apply to each' step.

 

The Condition is using the 'addDays' function to check against the 'TargetCompletionDate' value (see screenshot Reminder flow 3.png).

 

On the SharePoint list, TargetCompletionDate is a Date and Time column showing date only in a standard format.

 

The error I see is below.

 

Apply to each:
InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '15107': 'The template language expression 'formatDateTime(item()['TargetCompletionDate'],'yyyy-MM-dd')' cannot be evaluated because property 'TargetCompletionDate' doesn't exist
 
I've attached the error screenshot and also 4 screenshots to show the flow.
 
I feel the solution lies in additonal conditions on the flow, but my attempts aren't working.
 
Any help would be greatly welcomed.  Thank you!
 
3 Replies
best response confirmed by DogAndPonyShow (Occasional Contributor)
Solution

@DogAndPonyShow in your Get items action add a filter query of TargetCompletionDate ne '{null}' where {null} is an expression from the Expression tab in the dynamic content box. ne stands for not equal to.  Make sure you include the single quotes around the expression. With this filter it will only bring back items from the list where the TargetCompletionDate actually has something in it so should avoid the error.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott- thank you so much!  That's worked a treat.

 

If I add a filtered expression 'TargetCompletionDate ne null' - the workflow no longer errors.  Amazing.

 

Side question: under Dynamic content I don't see any of the SharePoint list columns (screenshot attached) - why would this be?  Thanks.

 

 

@DogAndPonyShow don't know, I'd have to see what your flow actions before it are.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User