Power Automate Opportunity Flows failing because Value is NULL

Carol Grant

I've had 3 flows that bring in Opportunities that have failed in the last few weeks. It's pulling in the Expected Amount and Expected Date which are Null or not filled in.

Unable to process template language expressions in action 'Append_to_array_variable' inputs at line '0' and column '0': 'The template language function 'formatNumber' expects its first parameter to be an integer or a decimal number. The provided value is of type 'Null'.

Annoying when you have to put in a “if not empty then format”… for an expression.

Also it's disappointing when you have to switch back to Classic Mode designer when you click Edit now in Power Automate. It automatically loads the Co-Pilot, forcing the AI.

Happy coding!

Carol

Alex Wong

@Carol Grant
yea.. the copilot is getting annoying…….

as for expression and null, well, you already got answer, you just have to condition. it would be nice if the expression handles “null” more gracefully.

@{if(empty(dynamic_content), ‘’, formatNumber(xxxx, ‘xxx’))}

Worst is the “box” to type expression is so short.. so i always have to do it in notepad

Carol Grant

@Alex Wong
Thanks Alex, I always have to do the expressions in notepad also. Thanks for posting what you use, there are many different ways to say if not equal to null or empty.

Alex Wong

@Carol Grant
yup, there's a few ways to do it, and since power automate treats null string and empty string the same, that's why I use empty(), as I only need to pass in 1 parameter. Generally I try to be lazy as long as doesn't affect the logic =D

Alex Wong

@Alex Wong
I think the copilot doesn't work on “switch” action in a flow, a few of my flow that uses “switch” is not loading in copliot mode. @Heather McLean @Erik Leaver Maybe a comment you can provide back to MS.

I think I will give copliot a chance and use it, at least if this is the direction that MS is going, I want to get ahead and provide feedback that tailor to user needs.

I will say.. ONE thing I like so far is the dynamic content box and expression box is better, have more room to type

Heather McLean

@Alex Wong - Yes, I’ve been wrestling with the new edit mode myself. Let us start a new post to gather feedback that we can take into the next Power Platform Community Call with Microsoft. CC: @Erik Leaver

Sounds like a good topic for user group.

Heather McLean

@Carol Grant

I‘ve been creating some new flows not for Opportunities but for Fundraisers using the null() expression and they have so far been working. May want to try going into the new editor and seeing if saving from there fixes things? That would be very telling!

Ben Regier

@Carol Grant
My personal favorite way to handle situations like these is the coalesce() function. As parameters, it takes any number of variables, outputs or values, and it basically just returns the first one that isn't null.

So if you have a situation where you need to check if your MyNumber variable is null and you want to return a 0 if it is, all you have to do is:

coalesce(variables('MyNumber'),0)

If MyNumber has a number in it, the expression returns that number. If not, it returns 0.

Carol Grant

@Ben Regier

I'm loving this discussion because I tried using empty but empty didn't like that my expected amount value as a float. I was debating if I should convert it to a string but I decided instead to use greater instead of empty

if(greater(body('Get_an_opportunity')?['expected_amount']?['value'],0),

formatNumber(body('Get_an_opportunity')?['expected_amount']?['value'],'C2','en-US'),0)

I'm wondering how using coalesce with the if would work here??

if(coalesce(body('Get_an_opportunity')?['expected_amount']?['value']),
formatNumber(body('Get_an_opportunity')?['expected_amount']?['value'],'C2','en-US'),0)

@Heather McLean

are you doing an expression with Null or a control? Would love to see an example that may help others too.

Glen Hutson

@Carol Grant
I've had better luck with coalesce rather than empty as it appears that empty doesn't necessarily mean null. Null means “does not exist”. Empty means “I'm here, but have nothing”.

Alex Wong

@Glen Hutson
I use coalesce a lot in SQL, particularly useful when handling multiple parameters and get the first that doesn't have null. Definitely a good option

Alex Wong

@Carol Grant:

@Ben Regier

I'm loving this discussion because I tried using empty but empty didn't like that my expected amount value as a float. I was debating if I should convert it to a string but I decided instead to use greater instead of empty

if(greater(body('Get_an_opportunity')?['expected_amount']?['value'],0),

formatNumber(body('Get_an_opportunity')?['expected_amount']?['value'],'C2','en-US'),0)

I'm wondering how using coalesce with the if would work here??

if(coalesce(body('Get_an_opportunity')?['expected_amount']?['value']),
formatNumber(body('Get_an_opportunity')?['expected_amount']?['value'],'C2','en-US'),0)

@Heather McLean

are you doing an expression with Null or a control? Would love to see an example that may help others too.

empty() expression only works on collection or string only. empty(string) will be true if null or empty string.

Ben Regier

@Carol Grant

I'm wondering how using coalesce with the if would work here??

if(coalesce(body('Get_an_opportunity')?['expected_amount']?['value']),
formatNumber(body('Get_an_opportunity')?['expected_amount']?['value'],'C2','en-US'),0)

If you use coalesce, you don't need the if(). Here's how I'd do it, with indentation for readability.

formatNumber(

coalesce(

body('Get_an_opportunity')?['expected_amount']?['value'],
0

),
'C2','en-US'

)

Or altogether:

formatNumber(coalesce(body('Get_an_opportunity')?['expected_amount']?['value'],0),'C2','en-US')

Alex Wong

@Ben Regier
I think the issue is if showing 0 when there's no expected amount is appropreiate

so instead, maybe:

coalesce(formatNumber(dynamic_content_amount, ‘C2’,'en-US'), ‘’)

Ben Regier

@Alex Wong
coalesce(formatNumber(dynamic_content_amount, ‘C2’,'en-US'), ‘’)

This will still fail if the dynamic content is null. But you have a point - @Carol Grant, what should the expression return if the expected amount is null? Should it return $0.00, or something else?

Alex Wong

@Ben Regier
Yup your right…formatNumber will fail first not return null

You will have to use if in there

Carol Grant

@Ben Regier- Hi Ben- I would like it to return null or empty. Not just zero. Thanks!

@Alex Wong Thanks to Alex also for your suggestions.

Alex Wong

@Carol Grant
I would just do this:

if(equals(outputs('Get_an_opportunity')?['body/ask_amount/value'],null), '', formatNumber(outputs('Get_an_opportunity')?['body/ask_amount/value'], 'C2', 'en-US'))

Heather McLean

@Carol Grant -

I love this topic of when to use Null v. Empty v. Coalesce. Seems like a great topic for User Group @Erik Leaver