I have a list I'm filtering with a radio button group. It works for all of my radio button options except the one for null values.

The goal is to build a user management list tool for my client. They will use it to view users assigned to certain designations, and it also needs to show users without a designation.

In my data source, I am filtering on the field = form value (my radio button group). This works perfect for 3 of my 4 radio button options, which filter on values that are present in the field being filtered on, but how do I set up the data source and the 4th radio button option to show records where this field is empty?

Hi Steve,
the NULL values are not the best to be used in this case.
You could just set your column to "Not NULL" and leave the fields empty.

Then you can just use a value of: value="" in order to display the empty ones. And then using the IN filter, just filter your data source: prntscr.com/8wlfvk

I must be doing something wrong. I know I have the data source setup correctly as you've illustrated in your screen cap, but I still cannot get a list of records that have a null value in a certain field.

My data source is using the IN filter, and for the radio button value, I tried using value="". As before, the filtering works for the radio button values that have actual values, but not for the empty (null) ones.

Any other ideas?

But are you sure you changed your database table column to "Not null" and did empty the fields?
When using radio buttons or checkboxes you should use IN.

I don't really understand what you're saying.

I'm pretty sure I have my data source setup correctly, based on your previous instruction.


I just don't understand the other part of what you are saying:
"...are you sure you changed your database table column to "Not null" and did empty the fields?"

Do you mean that I need to modify the db table for that field and not allow null values in that field?

I am not referring to your database connection setup - i understood it is okay.

I am not sure if you make difference between an empty database field and a field set to default value null.
You should alter your database table and set the column/field to 'not null'. Then the default value will be jut empty field.
NULL is confusing as it actually means - undefined.

The thing is - there are no fields that have null values. There is a members table joined to a site assignments table which associates members with one or more site assignments. My datasource is based on an SQL View that grabs all members and their site assignments. There are many members that are not assigned to a site, so the view yields null values where this happens in the db View.

In this case, I think it would just be easier to create a second report that is designed to just list members with no assignments.

A simple question - did you check your members table fields or whatever the table is called if they are set to NOT NULL?

Yes, I did check that. There are no fields in the members table with null values.

The null values are a result of the output of an SQL View I created in the database which uses a left join to show all members and their site assignments stored in other tables. I used a left join because I wanted a db View that showed all members, even if they are not assigned.

Can you please send a link to your page, where i can inspect what exactly the datasource returns? As usual you can send it to:

Thank you for the offer to help further. However, I had to finish this task for the customer quickly this morning on a deadline, so I just created a second db view for members that weren't assigned to anything.

Filtering records with the data sources for null values still seems really awkward, compared to regular recordsets and sql queries. In this situation though, this example isn't a good test for this because the null values aren't coming from database fields - it's a result of data Views I created in the SQL db.

I see - i will try to recreate this on our server and will then let you know how to fix this.