cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seadude
Memorable Member
Memorable Member

SQL Boolean Not Recognized

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

2018-10-23-18-34-43.png

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

1 ACCEPTED SOLUTION

Accepted Solutions

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")

View solution in original post

3 REPLIES 3
CarlosFigueira
Power Apps
Power Apps

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:

ForumPost001.PNG

 

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?

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

2018-10-24-07-14-07.png

 

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!

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")

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (92,013)