Answered

Dealing with NULL's in Power Automate using coalesce function

(0) Share

Report

Posted on by GavinF69

Been struggling with this for a while now, where a forms output potentially has a "null" in the output data, this can be because the form has a branching option to bypass certain selections resulting in "null" output or the user just plain didn't select any option from the choice available...

My entire flow...

My aim is to create a word document / report with the collected data from the form response and store in SharePoint, I followed this tutorial by I used @DamoBird365 Microsoft Forms to PDF – No Premium Actions - DamoBird365 to create the word document and populate SharePoint columns. The columns on SharePoint are based upon defined content types. The word template then pulls the content type field definitions (using Quick Parts in Word app) and should be updated based upon user input. All was working fine until I had to deal with unwanted characters i.e., "["/"] etc. that were coming through with certain data. I then used the suggestions to rid myself of the unwanted characters by again following instructions from Easily address escaped characters in Forms Response - DamoBird365.

Here is where the "fun" began...

The instructions work great when an option is selected and there is data in the field, but... when nothing is selected or the option to select is bypassed because of a forms branch then you get all kinds of strange behaviour...

Dealing with an empty date field was simple enough just declare a compose statement as follows:

Dealing with the unwanted characters was going ok until I hit the NULL input data

definition:

test result:

and when this input NULL is for a multi-line column type it is a whole different ball game... So, I started experimenting with initialising variables to store the "empty" data and then try and work with the empty data field, but I keep running into different error messages...

'The template language function 'json' expects its parameter to be a string or an XML. The provided value is of type 'Object'. or

'The template language function 'join' expects its first parameter to be an array. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#join for usage details.'.

So, I decided to try the coalesce function to default any NULL field received and then use this ' ' empty data to process in my string formats or list formats

but still flow complains...

definition:

test result:

Should I be changing this coalesce statement to the following, to hopefully take care of any type of NULL data i.e., coming through as a string, array or object?

coalesce(null, null, null)

any guidance or assistance will be appreciated, as can be seen by my other post here I've been struggling for a while trying to get around this problem....

Query regarding Apply to each loop to remove unwan... - Power Platform Community (microsoft.com)

Thanks.

I have the same question (0)

All responses (6)

Answers (1)

GavinF69 91 on at

Like (0)

Report
Re: Dealing with NULL's in Power Automate using coalesce function

Is there anyone out there in the universe that can assist me with this? It seems that many folks have creative ways of dealing with the NULL issue, but surely there should be a standardised way of doing this, i.e., for nulls on "strings", "arrays", etc. for
SharePoint integers/number columns do ...
SharePoint single line columns do ...
SharePoint multi-line columns do ...
SharePoint date columns do ... etc.
Maybe one of the many MVP folks or Snr developers in the community could do a sample with the relevant code to help us novices here... Seems like there are many ways for solving this issue, mostly with positive input patterns, but not negative input patterns (i.e., data exists in the field, and you manipulate it, but when the data is "blank" or "null" then the error messages are sometimes cryptic telling you where it is but then throws you to generic ways to solve) and you can spend your life rewriting flows because the insert you made to deal the error causes something else to break...

Here's hoping that someone picks up the torch or says look at .... where this has been explained. Have a great day everyone!

Was this reply helpful? Yes No
Ajinder31 530 on at

Like (0)

Report

Re: Dealing with NULL's in Power Automate using coalesce function

Hello @GavinF69, I'm not 100% sure if I understood the issue correctly but the error seems to suggest the you're trying to parse 'Object' while the method expects string or XML.
May I know what we're trying to achieve at steps JSON get - OB internal fancing options and input passed to the step?

Was this reply helpful? Yes No
GavinF69 91 on at

Like (0)

Report

Re: Dealing with NULL's in Power Automate using coalesce function

Hi Ajinder31, the "JSON get" is purely a simple Compose data operation to display what the field contains before deciding how to format it. Everything works perfect when there is data present, but as soon as the field contains NULL (either because forms had a branch which bypassed the question or user just didn't select any option available) then power automate just fails flow. If I remove the check and manipulate the strange characters ("[/n etc.) then the flow works, it is just I get all those escape characters in my document at the end...

Was this reply helpful? Yes No
Ajinder31 530 on at

Like (0)

Report

Re: Dealing with NULL's in Power Automate using coalesce function

Hi @GavinF69 , Sorry for delay in response. I didn't get notification about it.

Okay, If the problem is with null values, then we can check first if values are null or not.
For eg. if(empty(FORM_VALUE_TO_CHECK), 'WHAT_TO_DO_IF_NULL', 'IF_NOT_NULL')
OR We can always use the Condition Action to check for null values.

Was this reply helpful? Yes No
GavinF69 91 on at

Like (0)

Report

Re: Dealing with NULL's in Power Automate using coalesce function

Hi @Ajinder31 ,
The if empty expression does the pretty much the same as a coalesce expression. We know that the value coming through is NULL, so it will passes the first check.

The problem still comes at the JSON join expression, the join expects a value so it will not proceed and fails the flow. The join is really there to perform the action when there is valid data coming in that field. So what I did was add a condition to say if the output of the "if Empty" is NULL do nothing and if there is something there then perform the formatting, it seems to work see below...
Define null
check for null do nothing or if value is there then format values..
test result of condition statement:
I will now try for the other null fields by doing a similar exercise and hopefully that does the trick. Thanks for pointing me in the right direction

Was this reply helpful? Yes No
Verified answer

GavinF69 91 on at

Like (0)

Report

Re: Dealing with NULL's in Power Automate using coalesce function

Finally solved see explanation here: Solved: Re: Help just can't understand these Unable to fet... - Power Platform Community (microsoft.com)

Was this reply helpful? Yes No