SQL Boolean Not Recognized

[seadude]

Hello,

Scenario:

• SQL Data Source with Boolean data type in one column
• Gallery displaying SQL table data
• Using an IF statement to control the Text in a label

If(
    ThisItem.readingStatus = false,
    "Bad",
    ThisItem.readingStatus = true,
    "Good",
    "WTF"
)

• Here you can clearly see that this logic is not working. The label to the right is set to ThisItem.readingStatus

What am i missing? Are Boolean values not true/false when using them in a function?

[CarlosFigueira]

You probably have null values in your boolean column (I'm assuming it's a column of type BIT in SQL) - when the value of readingStatus is converted to text to be displayed in the label containing 'ThisItem.readingStatus', null / blank values are being interpreted as false ones.

To show this behavior, I created a table with a text (NVARCHAR) and a boolean (BIT) column in SQL, and displayed it in a gallery:

The first label (1, bold) shows the value of the text column (ThisItem.Title)

The second (2) shows ThisItem.readingStatus

The third one shows your IF expression

The fourth one shows IsBlank(ThisItem.readingStatus)

This is likely why you're seeing the WTF values in your app. Can you take a look at the values in your database to see if they have any value or if they're NULL?

[seadude]

Thank you @CarlosFigueira for the reply. I do not have access to this DB other than through PowerApps. When I create a data table, I can see the toggle icon. If I change this to "text" in the options, it reads "false".

How can I reliabely reference the state of the bit? When using...

If(
    IsBlank(ThisItem.readingStatus),
    "Incomplete",
    "Complete"
)

...things seem to work. But that might be because the values are all "null". What happens when they get populated? Will this function still work?

I'm having trouble understanding if a SQL bit column set to "false" = IsBlank, or if "null" = IsBlank. Please let me know what you think, otherwise I'll get the dba to add some values in here and I'll report back.

Take care!

[CarlosFigueira · Accepted solution]

The thing with boolean (BIT) values in SQL is that they don't have only two possible values - they have three (true, false, null).

If you really want to have only two possible values, and you have access to the database, then you must declare the column as NOT NULL (i.e., using something like 'ALTER TABLE <table name> ALTER COLUMN readingStatus BIT NOT NULL' - although you'll need to fill out the null values with true/1 or false/0 prior to that).

If you don't have access to the DB then you'll need to make a call of how you want to treat null values - as false (which is what PowerApps is doing), or as a third value. If you want to know the real value of a column, you can use an expression like this one:

If(
    IsBlank(ThisItem.readingStatus), "NULL",
    ThisItem.readingStatus, "true",
    "false")