cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
risingson
Level: Powered On

Flow failing due to 'Null' value

I have a flow setup to create a Trello card upon creation of a Sharepoint list item. One of the items I'm putting into the card's description is a date and time value from the Sharepoint list item. I am using a utcnow() function inside a compose step before the card creation step so I can inser the time and date value as a formatted string. See below:

 

 

if(equals(triggerBody()?['Due_x0020_Date'],null),'',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))

 

 

This works fine if someone enters a value for the date when submitting a new Sharepoint list item. However, if they don't the flow will fail with the following error:

 

Unable to process template language expressions in action 'Due_Date' inputs at line '1' and column '2112': 'The template language function 'utcNow' expects its parameter to be of type string. The provided value is of type 'Null'.

 

So, I've tried adding an if() statement to deal with possible null values but can't seem to get it working. I've tried the following unsuccessfully (all provide the exact same error if no value is entered in the 'Due_Date' field when the new items is submitted):

 

 

if(equals(triggerBody()?['Due_x0020_Date'],null),'',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))
if(equals(triggerBody()?['Due_x0020_Date'],Null),'',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))
if(equals(triggerBody()?['Due_x0020_Date'],'Null'),'',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))
if(empty(triggerBody()?['Due_x0020_Date']),'',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))
if(empty(triggerBody()?['Due_x0020_Date'],'text',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))

 

if(equals(triggerBody()?['Due_x0020_Date'],Null),'text',formatDateTime(addHours(utcNow(triggerBody()?['Due_x0020_Date']),8),'yyyy-MM-dd | HH:mm'))

 

 

 

What am I doing wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Flow failing due to 'Null' value

Hi @risingson,

 

Perhaps you can add an condition to check if the dynamic content is null or not. Try with the condition like this:

@empty(triggerBody()?['']).

 

For your reference:

https://powerusers.microsoft.com/t5/Microsoft-Flow-Knowledge-Base/How-to-check-if-a-field-is-blank/t...

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

16 REPLIES 16
Community Support Team
Community Support Team

Re: Flow failing due to 'Null' value

Hi @risingson,

 

As long as the date and time data is null, the formula won't work and will return error. This will not so far in Compose. 

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
risingson
Level: Powered On

Re: Flow failing due to 'Null' value

So, is there a way around this? Perhaps a condition step?

Community Support Team
Community Support Team

Re: Flow failing due to 'Null' value

Hi @risingson,

 

Perhaps you can add an condition to check if the dynamic content is null or not. Try with the condition like this:

@empty(triggerBody()?['']).

 

For your reference:

https://powerusers.microsoft.com/t5/Microsoft-Flow-Knowledge-Base/How-to-check-if-a-field-is-blank/t...

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

andrew_blubase
Level: Powered On

Re: Flow failing due to 'Null' value

Hi,

 

a colleague and I were having a similar issue and were getting very frustrated. In the end we used a pretty long winded combination of expressions, but felt that it was better than using a lot of condition blocks as we needed to use this format date on three date fields!

if(equals(coalesce(triggerBody()?['DateFormat'],'NULL'),'NULL'),'',formatdatetime(coalesce(triggerBody()?['DateFormat'],'01/01/1901'),'dd/MM/yyyy'))

where DateFormat is the field name required.

The expression evaulates if the field date is null and if true returns blank, otherwise if false, the date field is formatted as required.

The 01/01/1901 just satisfies the formatdatetime functions requirement for a non blank string, but as the field should not be blank by this point, it won't be used in the end result.

Give it a go, hope it helps! Thanks to Rafael Garcia for preventing further hair loss.

gunjan
Level: Powered On

Re: Flow failing due to 'Null' value

Hopefully this is the problem with the Flow [Compose], it handles it easily using condition. i tried it using condition only.
used a bool function to check if the text [data of type DATETIME] is not empty, its empty do nothing otherwise use FormatDateTime Function...

 

issue date.jpg

NilsVB
Level: Powered On

Re: Flow failing due to 'Null' value

I've had the exact same problem!

Very frustrating indeed, because it questions everything you know about scripting/programming, by which I mean: it appears that the expression gets completely evaluated although one condition is false and one is true: both get calculated, why?!

This seems to me like a bug in the expression evaluator?

 

Can some of the devs confirm if this behaviour is intended or a bug?

gunjan
Level: Powered On

Re: Flow failing due to 'Null' value

I have reported for the bug but still not getting any response from Microsoft Support ...

 

https://powerusers.microsoft.com/t5/I-Found-A-Bug/Not-able-to-save-null-value-into-date-field-CRM-DY...

 

 

NVBC
Level: Powered On

Re: Flow failing due to 'Null' value

We are not alone: https://blog.kloud.com.au/2017/10/17/azure-logic-app-evaluating-if-condition-with-the-help-of-json-e...

 


 Some of us come from years of development experience, and at times we like to skill up ourselves to various other technologies, which leaves us with a mindset based on our past development experience and programming habits, which we gained over the years. When clients requirements are approached based on these backgrounds, we expect the code to work with the certain flow and these are where rules are broken while using IF condition in Azure Logic Apps.

 

StevenWade
Level: Powered On

Re: Flow failing due to 'Null' value

Piling on here, but this turns every data transformation or error handling task into an agonizing hack. It at least doubles the number of Compose actions you need to write to handle a potential null value or retype an attribute.

 

While it probably makes sense to push complex transformations out of Flow (writing an Azure Function app or wrapping a Logic app around a Liquid transformation), this is still annoying as heck.

risingson
Level: Powered On

Re: Flow failing due to 'Null' value

I have marked this issue as solved, since @v-monli-msft did technically provide a solution to my problem, despite it not being what we all consider "standard" practice. However, it does seem that this is how all microsoft apps work when evaluating if() statements. I waited to see if we could find a better way but it seems we're NULL for now. It's not ideal but it gets the job done. Thanks for all your input.

risingson
Level: Powered On

Re: Flow failing due to 'Null' value

Thanks for you reply, as this is currently the best way to handle this situation. However, as many people have mentioned it's not ideal to us. But it seems that is how Microsoft apps operations intentionally. Do you see any chance this behavior will be changed in the future?

jbhatt
Level: Power Up

Re: Flow failing due to 'Null' value

Hello,

 

Thanks for your suggestion.

 

@empty(triggerBody()?['']) Expression has resolved problem to checck Null or String Value in MS Flow.

 

Regards,

Jigar

bjdshp
Level: Powered On

Re: Flow failing due to 'Null' value

this one is the best answer.  Quick fix, thanks.

Highlighted
seraph787
Level: Powered On

Re: Flow failing due to 'Null' value

To me, the solution presented by @andrew_blubase is better at getting to the point without creating so many different flow actions.  In my case, this is 5 less flow actions I had to create just to get around this bug.

 

Thank you @andrew_blubase.

dhock
Level: Powered On

Re: Flow failing due to 'Null' value

@v-monli-msft , the @empty formula worked great with the old condition control where we could go into advanced mode, but with the new one that does not allow typing in a formula, how do we accomplish the same?

IronFelix
Level: Powered On

Re: Flow failing due to 'Null' value

Hi @v-monli-msft  may i ask in case you know, why i cannot access  (i am getting an access denied)

https://powerusers.microsoft.com/t5/Microsoft-Flow-Knowledge-Base/How-to-check-if-a-field-is-blank/t... ?

Thanks in advance

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,687)