Flow failing due to 'Null' value

[Anonymous]

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?

[v-monli-msft]

Hi @Anonymous,

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

[Anonymous]

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

[v-monli-msft · Accepted solution]

Hi @Anonymous,

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/ta-p/31843

Regards,

Mona

[andrew_blubase]

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]

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

[Anonymous]

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]

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

[Anonymous]

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]

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.

[Anonymous]

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.

[Anonymous]

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]

Hello,

Thanks for your suggestion.

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

Regards,

Jigar

[bjdshp]

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

[seraph787]

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]

@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?

[Anonymous]

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

[vsolanon]

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

I applied your formula and it worked like a charm!

[Kaarthi]

Thanks Andrew, It works, Fantastic...

[TechIY]

Awesome! Thank you Andrew - evergreen solution