Answered

Flow failing due to 'Null' value

(2) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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?

Categories:

Building flows

I have the same question (0)

All responses (25)

Answers (1)

v-monli-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

v-monli-msft Microsoft Employee on at

Like (7)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
andrew_blubase 73 on at

Like (19)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
gunjan 82 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
gunjan 82 on at

Like (1)

Report
Copy link

Link copied!

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-DYNAMICS/m-p/123197#M2329

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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

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.

Was this reply helpful? Yes No
StevenWade 129 on at

Like (4)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No