Equal function for SharePoint List YesNo field always returns false

[ScoutingOutdoor]

I've searched high and low on the web for an answer. I've seen similar posts from years ago on this topic with screenshots from the older UI and the solutions presented don't seem to work now.

I have a PowerFlow taking a SharePoint List, using the Select action to prepare columns for the Create HTML table action. During the Select action, I am trying to format a YesNo field from the list first. When I output the field directly, it displays the actual value - True or False. I'm trying to format it instead as 'Yes' or 'No' but the formula always results in No.

I've tried... the following and variations of 'True'/True/true/bool(true)

if(equals(item()?['CrazyFieldName'],true),'Yes','No')

 but this results in No even when the actual value is True

converting the field to a string (just in case?)... 

string(item()?['CrazyFieldName'])

but this results in an empty return

converting the field to a boolean

bool(item()?['CrazyFieldName'])

but this causes the flow to fail

Any suggestions?

[ekarim2020]

Hi @ScoutingOutdoor ,

Try the following expression:

if(item()?['Business_x0020_Critical'],'Yes','No')

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

 

[ScoutingOutdoor]

Thanks @ekarim2020, I've tried this but I get an error when the flow tries to run saying CrazyFieldName is Null and the if statement doesn't support that. I then tried to wrap it in an empty check but this doesn't work either...

if(if(empty(item()?['CrazyFieldName']),false,item()?['CrazyFieldName']),'Yes','No')

[ScoutingOutdoor]

Here is a copy/paste of what I've got happening with a new list/automate... in the list, Option 1 & 2 are both yes/no fields.

Input

{
"from": [
{
"@odata.etag": "\"1\"",
"ItemInternalId": "1",
"ID": 1,
"Title": "test",
"Option1": true,
"Option2": false,

Formula

{
"inputs": {
"from": "@outputs('Get_items')?['body/value']",
"select": {
"Title": "@item()?['Title']",
"O1": "@{item()?['Option1']} _/_ @{if(if(empty(item()?['Option1']),false,item()?['Option1']),'yes','no')}",​

Output

{
"body": [
{
"Title": "test",
"O1": "True _/_ no",
"O2": false
},

[ekarim2020]

HI @ScoutingOutdoor ,

[Updated] Can you please provide a screenshot of the error along with the expression you are using, and some sample data I could use:

If the data is missing some property values, you could try check for this, as it will return a NULL value:

if(equals(item()?['NewRequestNotify'],null),'No','Yes')

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[ScoutingOutdoor]

The forum seems to have turned my formatted post previously into a weird display of the html. I was trying to show you the input, formula, and output. I wrote the nested if to handle the supposed empty condition of Option 1. In any case, do you see anything wrong with my formula? I am at a loss here, logically, this seems like it should work. It does not produce any errors as written.

When I tried the formula you suggested with the item()?[] it fails during the flow's run as it says the if statement only accepts boolean values and the item()?[] was null. Hence I added the nested if for empty and used that as a means to return false.

I appreciate your suggestion on the second formula but the item()?[] appears to have three states null, false, true so I would be worried about getting a false positive when the value is false instead of null.

[ekarim2020 · Accepted solution]

In one of my old SharePoint Lists, I have a Yes/No column called NewRequestNotify and I need to handle three values true, false and null:

Here is the sample data:

 

Compare with what read using a Get Items and Select action:

 

The JSON expression item()?['NewRequestNotify'] returns a null value if the NewRequestNotify property is missing from the data. For ID 3 the NewRequestNotify is missing so the expression returns null:

This is probably the result of the Yes/No field being added to the list at a later date or when no default value was set.

In this case you could try:

true = Yes

null, false = No

if(equals(item()?['NewRequestNotify'],null),'No',if(item()?['NewRequestNotify'],'Yes', 'No'))

 

Ellis

[ScoutingOutdoor]

Thank you Ellis. The formula you suggested works - I'm a but confused at the logic behind why it worked but nevertheless, I appreciate the solution. It is odd, my input under the select never shows null but always false/true despite the value being null causing the if statement originally to break.