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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,922)