cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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
Highlighted
Community Support
Community Support

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

17 REPLIES 17
Highlighted
Community Support
Community Support

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.
Highlighted
Advocate I
Advocate I

Re: Flow failing due to 'Null' value

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

Highlighted
Community Support
Community Support

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

Highlighted
Advocate II
Advocate II

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.

Highlighted
Advocate III
Advocate III

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

Highlighted
Advocate II
Advocate II

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?

Highlighted
Advocate III
Advocate III

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...

 

 

Highlighted
Advocate II
Advocate II

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.

 

Highlighted
Advocate II
Advocate II

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.

Highlighted
Advocate I
Advocate I

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.

Highlighted
Advocate I
Advocate I

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?

Highlighted
New Member

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

Highlighted
Frequent Visitor

Re: Flow failing due to 'Null' value

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

Highlighted
Advocate II
Advocate II

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.

Highlighted
Advocate V
Advocate V

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?

Highlighted
Frequent Visitor

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

Highlighted
Helper III
Helper III

Re: Flow failing due to 'Null' value

Thank you @andrew_blubase! This is what I was looking for 🙂

 

I applied your formula and it worked like a charm!

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Top Kudoed Authors
Users online (7,826)