Filter on null / non null

Talk about Filter (search) Settings

Moderator: alexandrleonenko

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Filter on null / non null

Postby dynsight » May 21st, '12, 02:00

I have a self-referential link on a table, and wish to create a filter for the following circumstances:

Parents: field is null
Children: field is not null
All: ignore field...

I didn't see anything in the forum about this. I guess I can write a custom filter, but was hoping there is an easy way to do this.

administrator
Site Admin
Site Admin
Posts: 6037
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filter on null / non null

Postby administrator » May 21st, '12, 07:45

Here the instructions of HOW TO...
viewtopic.php?f=33&t=184

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Filter on null / non null

Postby dynsight » May 23rd, '12, 02:13

This was actually much easier than you listed out... but your suggestion was helpful.

I track the variable with:

Code: Select all

// custom filter field
   $childFilter = isset($_REQUEST['ccc__ff__isChild']) ? $_REQUEST['ccc__ff__isChild'] : "%";


and my SQL is a bit simpler than what you suggested:

Code: Select all

 $sql = "SELECT crashcartid,crashcartname,address,city,state,zip,email,phone,password,location,country,left(crashcartname,1) as firstLetter, 'View Locations' as link_to_location, 'View Cart Contents' as action_view_link, if(isnull(parentOrg),'N','Y') as isChild from tbl_crashcart where left(crashcartname,1) like '$fl' and

[b]if(isnull(parentOrg),'N','Y') like '$childFilter'";[/b]


Note: I do have some code outside of your filtering, a pre-filter if you will... it is a listing of all of the first letters of the client names... listed across the top. This is an interface the client users were used to, and was a firm requirement.

Finally, I did the filter fields and array

Code: Select all

$fill_isChild = array('N'=>'Parents Only', 'Y'=>'Children Only','%'=>'Both');
  $filtering_fields = array(
      ...
     "Parent/Child"=>array("type"=>"enum","source"=>$fill_isChild ,"table"=>"", "field"=>"isChild", "show_operator"=>"false", "default_operator"=>"=", "case_sensitive"=>"false", "comparison_type"=>"string", "width"=>"", "on_js_event"=>"onchange='document.getElementById(\"ccc_ff_onSUBMIT_FILTER\").click();'"),


and it all works as expected. However, I was confused about the on_JS event for the filtering field...and I took it out. It make no difference... it worked perfectly fine without it. I will leave it in, but can I take it out safely?

administrator
Site Admin
Site Admin
Posts: 6037
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filter on null / non null

Postby administrator » May 23rd, '12, 07:52

Yes, but you have to check this variable and clean it:
$childFilter = isset($_REQUEST['ccc__ff__isChild']) ? $_REQUEST['ccc__ff__isChild'] : "%";

I mean check if it includes only allowed values.

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Filter on null / non null

Postby dynsight » May 24th, '12, 02:01

This is working, sort of. However, I am filtering by 3 fields

Code: Select all

$fill_isChild = array('N'=>'Parents Only', 'Y'=>'Children Only','%'=>'Both');
$filtering_fields = array(
      'Customer Name'=>array('type'=>'textbox',  'table'=>'tbl_crashcart', 'field'=>'crashcartname', 'filter_condition'=>'', 'show_operator'=>'false', 'default_operator'=>'%like%', 'case_sensitive'=>'false', 'comparison_type'=>'string|numeric|binary', 'width'=>'', "on_js_event"=>"onchange='document.getElementById(\"ccc_ff_onSUBMIT_FILTER\").click();'", 'default'=>''),
    
     'State'=>array('type'=>'dropdownlist',  'table'=>'tbl_crashcart', 'field'=>'state', 'filter_condition'=>'', 'show_operator'=>'false', 'default_operator'=>'=', 'case_sensitive'=>'false', 'comparison_type'=>'string|numeric|binary', 'width'=>'', 'on_js_event'=>'', 'default'=>'', 'source'=>'self'),
    
     "Parent/Child"=>array("type"=>"enum","source"=>$fill_isChild ,"table"=>"", "field"=>"isChild", "show_operator"=>"false", "default_operator"=>"=", "case_sensitive"=>"false", "comparison_type"=>"string", "width"=>"", "on_js_event"=>"onchange='document.getElementById(\"ccc_ff_onSUBMIT_FILTER\").click();'"),


If I change the Parent/Child filter it works fine... but if I type anything in the Customer name block, and click submit, I lose the setting for the Parent/Child... it goes away.

Is there way of set the ANY to % as well, or set the default value of the filter field to "%"?

administrator
Site Admin
Site Admin
Posts: 6037
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filter on null / non null

Postby administrator » May 24th, '12, 08:16

Can you please show us this issue online?
You may define any 'default_operator' you want: =, >= like% etc.

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Filter on null / non null

Postby dynsight » May 24th, '12, 09:51

Sorry, the page is secure, but I can create an account for you...just can't post it here.

I can send you the page of course... but you won't have the data.

However, you misunderstood my issue. I am not looking for the default OPERATOR (like, =, >) but setting the default VALUE of the filter field itself
(YES, NO, ...)

administrator
Site Admin
Site Admin
Posts: 6037
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filter on null / non null

Postby administrator » May 27th, '12, 09:28

I am not looking for the default OPERATOR (like, =, >) but setting the default VALUE of the filter field itself
(YES, NO, ...)


viewtopic.php?f=33&t=1934

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Filter on null / non null

Postby dynsight » May 30th, '12, 03:31

Almost does it... thanks. You really need to get the search feature on this board working...

In order to get this to work ,the user has to click the search button.

Is there any way to automatically run the search when the grid loads? That is, implement the search as entered (using default values) automatically, so the user does not have to click the search button.

administrator
Site Admin
Site Admin
Posts: 6037
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filter on null / non null

Postby administrator » May 30th, '12, 06:32

Yes. it's possible:
viewtopic.php?f=33&t=1934


Return to “Settings: Filtering (search) / Autocomplete”