HTML5 Data Bindings Support Product Page

How do you show records where a field is empty (null)?

Asked 28 Oct 2015 19:16:57
has this question
28 Oct 2015 19:16:57 Steve Skinner posted:
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?


Replied 29 Oct 2015 09:40:50
29 Oct 2015 09:40:50 Teodor Kuduschiev replied:
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:
Replied 29 Oct 2015 14:51:36
29 Oct 2015 14:51:36 Steve Skinner replied:
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?
Replied 29 Oct 2015 14:54:48
29 Oct 2015 14:54:48 Teodor Kuduschiev replied:
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.
Replied 29 Oct 2015 15:57:40
29 Oct 2015 15:57:40 Steve Skinner replied:
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?
Replied 29 Oct 2015 16:03:36
29 Oct 2015 16:03:36 Teodor Kuduschiev replied:
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.
Replied 29 Oct 2015 16:07:14
29 Oct 2015 16:07:14 Steve Skinner replied:
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.
Replied 29 Oct 2015 16:36:11
29 Oct 2015 16:36:11 Teodor Kuduschiev replied:
A simple question - did you check your members table fields or whatever the table is called if they are set to NOT NULL?
Replied 29 Oct 2015 16:40:11
29 Oct 2015 16:40:11 Steve Skinner replied:
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.
Replied 29 Oct 2015 16:43:16
29 Oct 2015 16:43:16 Teodor Kuduschiev replied:
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:
Replied 29 Oct 2015 17:11:39
29 Oct 2015 17:11:39 Steve Skinner replied:
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.
Replied 29 Oct 2015 18:09:51
29 Oct 2015 18:09:51 Teodor Kuduschiev replied:
I see - i will try to recreate this on our server and will then let you know how to fix this.

Reply to this topic