filtering on range using two cols

In the table at http://proximityone.com/k12schools/azschools1.htm I have two cols LO (ranges PK,01 ... 12) and HI (same range). would like to have feature that filters based on a user setting for both/range; eg, show me all rows where the LO is 06 or higher and HI is 08 or lower.

How to do this? Thank you for any thoughts.

Warren,
Here is a sample valid for two ( or more ) filters, it's just for exact values , but it could easily be modified for higer/lower filters also.
i.e : replace;
.indexOf(filters[Xcol][1]) >-1
with either
>= (filters[Xcol][1])
or
<= (filters[Xcol][1])

note, it include filter undo by setting its value to "";
HTH

<script>
var names55 = ["", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" ];
    var combo = new AW.UI.Combo;
    combo.setControlText("combo");
    combo.setItemText(names55);
    combo.setItemCount(11);
 combo.onSelectedItemsChanged = function(index){
NestedFilter( combo.getItemText(index),0);
}   
        var combo2 = new AW.UI.Combo;
    combo2.setControlText("combo2");
    combo2.setItemText(names55);
    combo2.setItemCount(11);
combo2.onSelectedItemsChanged = function(index){
NestedFilter( combo2.getItemText(index),1);
}

    var grid1 = new AW.UI.Grid;
    grid1.setId("grid1");
    grid1.setHeaderTemplate(combo, 0);
        grid1.setHeaderTemplate(combo2, 1);
    grid1.setCellText(function(col, row){return col + "." + row});
    grid1.setColumnCount(2);
    grid1.setRowCount(85);
document.write(grid1); 


var max = grid1.getRowCount(); 
var filtersrunning = [];

function NestedFilter(searchcriteria,column){ 

searchcriteria = searchcriteria.toUpperCase();
var filters = filtersrunning ;
var i, rows = []; 
var ind = grid1.getRowIndices();

var samecolfound = false;

if(filtersrunning.length == 0 && searchcriteria !=''){ filters.push([column,searchcriteria] ) }

if(filtersrunning.length > 0 ){
for(var aa=0 ; aa<filtersrunning.length ; aa++){
if(filtersrunning[aa][0] == column){samecolfound = true }
}
for(var z=0 ; z<filtersrunning.length ; z++){
if(samecolfound && filtersrunning[z][0] == column && searchcriteria !=''){ filters[z]=[column,searchcriteria] }
if(!samecolfound && filtersrunning[z][0] != column && searchcriteria !=''){ filters.push([column,searchcriteria] ) }
 if(samecolfound && filtersrunning[z][0] == column && searchcriteria==''){ filters.splice(z,1) }
 }
}

if(filters.length == 0 ){
grid1.setRowCount(max); 
grid1.setRowIndices(''); 
Lab.setControlText("FOUND: " + max);
}

if(filters.length > 0 ){
 for (rw=0; rw<max; rw++){
 var found =0;
  for (Xcol=0; Xcol<filters.length; Xcol++){ 
   if ( grid1.getCellText(filters[Xcol][0], rw).toUpperCase().indexOf(filters[Xcol][1]) >-1 ){ 
   found++ ;
}
}        
if (found== filters.length) { rows.push(rw) }
} 

grid1.setRowCount(rows.length); 
grid1.setRowIndices(rows); 
Lab.setControlText("FOUND: " +rows.length);
}
filtersrunning = filters;
Lab.refresh();
grid1.getRowsTemplate().refresh();
}

var Lab = new AW.UI.Label;
Lab.setControlText("FOUND: " + max);
document.write(Lab);  
</script>

Uppsss!, Sorry
I should had tested with 3 filters before post.

please replace this block:

if(filtersrunning.length > 0 ){
for(var aa=0 ; aa<filtersrunning.length ; aa++){
//....

with this one:

if(filtersrunning.length > 0 ){
for(var aa=0 ; aa<filtersrunning.length ; aa++){
if(filtersrunning[aa][0] == column){samecolfound = true }
} 
for(var z=0 ; z<filtersrunning.length ; z++){
if(samecolfound ){
if(  searchcriteria !='' && filtersrunning[z][0] == column  ){ filters[z]=[column,searchcriteria] }
 if( searchcriteria=='' && filtersrunning[z][0] == column  ){ filters.splice(z,1) }
 }
  }
  if( !samecolfound ){
 if( searchcriteria !=''){ filters.push([column,searchcriteria] ) }
 }
}

Thanks

Also pls remove the line:
var ind = grid1.getRowIndices();

Thank you.
Have attempted to move this code into production.
See http://proximityone.com/k12schools/azschools2.htm.

"grid1" text changed to "obj" (as that grid already exists)

your "function nestedfilter" and subsequent code sits below "//333333333333333333333333" in azschools2.htm file.

I am unclear about where 0 & 1 should be changed in that code section (below "//333333333333333333333333"). The real columns for use in this app are 9 and 10.

Again, I very much appreciate your help.

Looks like we posted about the same time ... I still stand here ... have moved code into production .. http://proximityone.com/k12schools/azschools2.htm.

"grid1" text changed to "obj" (as that grid already exists)

your "function nestedfilter" and subsequent code sits below "//333333333333333333333333" in azschools2.htm file.

main issue now -->
I am unclear about where 0 & 1 should be changed in that code section (below "//333333333333333333333333"). The real columns for use in this app are 9 and 10 and they are attibuted as string.

Thank you.

Well, I could not get your sample to display the combos' popups ( at least in FF and Safary, will try in IE tomorrow)... but I see a few lines that need some changes to meet your needs.

replace:
var max = grid1.getRowCount();
with:
var max = grid1.table.getCount();

and also:
var names55 = ["", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" ];
with:
var names55 = ["", "0", "01", "02", "03", "04", "05", "06", "07", "08", "09","10","11","12" ];

but not sure if it needs more changes and/or how to implement the Higher/lower condition to be selectable on demand.
For a static test ( just the two values provided) you can try to replace the line:
if ( grid1.getCellText(filters[Xcol][0], rw).toUpperCase().indexOf(filters[Xcol][1]) >-1 ){ found++ }

with this two:
if(column==9 && grid1.getCellText(filters[Xcol][0], rw).toUpperCase()>=filters[Xcol][1] ){ found++ }
if(column==10 && grid1.getCellText(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] ){ found++ }

note that this test assumes to search only equal or higher values for column 9 , and equal or lower for column 10, so it is not possible to filter the opposite ( <= col9 or >= col10) as well as any different combination of it.
Hope this give you an idea

var max = table.getCount();

Thank you. I have made these mods.

I am seeing the table fine with FF & IE
http://proximityone.com/k12schools/azschools2.htm
but the dropdown shows no values in FF (it does in IE)

however when I click on dropdown values for cols 9 & 10 in IE, nothing happens. should the event trigger when either 9 or 10 is changed?

note also that the variable MAX has been used earlier for another operation ... is this getting in the way?

Could be , (you can rename max variable into another name).
But first try replacing this line (my fault):

var max = obj.table.getCount();
with this one:
var max = table.getCount();

if decided to rename max just this two lines contains it:
grid1.setRowCount(max); .....
for (rw=0; rw<max; rw++){ .....

And also replace

combo.setItemCount(11);
combo.setItemCount(14);

combo2.setItemCount(11);
combo2.setItemCount(14);

Thank you. cannot get to go ...

changed max to maxc (throughout relevant section):
//var maxc = obj.table.getCount();
var maxc = table.getCount();

since combo was used elsewhere, changed combo to combo1:
var names55 = ["", "0", "01", "02", "03", "04", "05", "06", "07", "08", "09","10","11","12" ];
var combo1 = new AW.UI.Combo;
combo1.setControlText("combo1");
combo1.setItemText(names55);
combo1.setItemCount(14);
combo1.onSelectedItemsChanged = function(index){
NestedFilter( combo1.getItemText(index),9);
}

can you check behavior/operation of the click on combo1 col & then combo2 col and see if you can get clues as to why not working?
http://proximityone.com/k12schools/azschools2.htm
trying 05 & 09 .. which is there, shows found=0

Strange, the only thing I discovered is:
( as your other filter functions are using getCellValue instead of getCellText)
[test-1] Try replacing it in those test lines : ... And/or...
[test-2] Use Number() instead of toUpperCase arround conditions :

[test-1]:
if(column==9 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()>=filters[Xcol][1] ){ found++ }

if(column==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] ){ found++ }

[test-2]:
if(column==9 && Number( obj.getCellValue(filters[Xcol][0], rw) ) >= Number( filters[Xcol][1]) ){ found++ }

if(column==10 && Number(obj.getCellValue(filters[Xcol][0], rw) ) <= Number(filters[Xcol][1] ) ) { found++ }

Sorry , I can't see anything else that could help.

Uff, almost...:)
I can now see my mistake.
this sentences are wrong:

if(column==9 && ....
if(column==10 && ....

and should be:
if( filters[Xcol][0]==9 && ....
if( filters[Xcol][0]==10 && ....

OK, as there are still issues , I decided to download your sample to see what's going on and... it seems that the nested filter I posted only works for arrays and needs a second var 'maxc_2' to work with CSV data models as described here.
http://www.activewidgets.com/javascript.forum.22570.11/need-help-with-filter-based.html

Here is the full code below '//33333..' :
note: I add two more lines for <9 and >10 for the case you want to use this single function for any filter ( instead of have multiple filter functions)
HTH

//333333333333333333333333
var maxc = table.getCount();
var maxc_2 = maxc;
var filtersrunning = []; 

function NestedFilter(searchcriteria,column){  

searchcriteria = searchcriteria.toUpperCase(); 
var filters = filtersrunning ; 
var i, rows = []; maxc = obj.getRowCount();  

var samecolfound = false; 

if(filtersrunning.length == 0 && searchcriteria !=''){ filters.push([column,searchcriteria] ) } 

if(filtersrunning.length > 0 ){ 
for(var aa=0 ; aa<filtersrunning.length ; aa++){ 
if(filtersrunning[aa][0] == column){samecolfound = true } 
}  
for(var z=0 ; z<filtersrunning.length ; z++){ 
if(samecolfound ){ 
if(  searchcriteria !='' && filtersrunning[z][0] == column  ){ filters[z]=[column,searchcriteria] } 
 if( searchcriteria=='' && filtersrunning[z][0] == column  ){ filters.splice(z,1) } 
 } 
  } 
  if( !samecolfound ){ 
 if( searchcriteria !=''){ filters.push([column,searchcriteria] ) } 
 } 
}  

if(filters.length == 0 ){ 
obj.setRowCount(maxc_2);  
obj.setRowIndices('');  
Lab.setControlText("FOUND: " + maxc); 
} 

if(filters.length > 0 ){ 
 for (rw=0; rw<maxc; rw++){ 
 var found =0; 
  for (Xcol=0; Xcol<filters.length; Xcol++){  
if ( filters[Xcol][0] < 9  && obj.getCellText(filters[Xcol][0], rw).toUpperCase().indexOf(filters[Xcol][1]) >-1 ){ found++ }
if ( filters[Xcol][0] > 10 && obj.getCellText(filters[Xcol][0], rw).toUpperCase().indexOf(filters[Xcol][1]) >-1 ){ found++ }
if(filters[Xcol][0]==9 && obj.getCellText(filters[Xcol][0], rw).toUpperCase()>=filters[Xcol][1] ){ found++ } 
if(filters[Xcol][0]==10 && obj.getCellText(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] ){ found++ }  
}         
if (found== filters.length) { rows.push(rw) } 
}  

obj.setRowCount(rows.length);  
obj.setRowIndices(rows);  
Lab.setControlText("FOUND: " +rows.length); 
} 
filtersrunning = filters; 
Lab.refresh(); 
obj.getRowsTemplate().refresh(); 
} 

var Lab = new AW.UI.Label; 
Lab.setControlText("FOUND: " + maxc); 
document.write(Lab);

Thank you.

I pasted your code replacing the code below //333333333333333333333333 and saved as
http://proximityone.com/k12schools/azschools3.htm.
(3 instead of 2).

it seems close, but not there yet.

if you click combo2 dropdown 1st and choose 06, that filter seems to work correctly. :) but if you start with combo1 dropdown, it does not work correctly. there seems to be an issue with combo1 logic handling. I cannot see what.

These are school grade ranges so, while the situation "<9 and >10" is good to have in general, it is not as critical.

when clicking ShowAll, it does not refresh. do I need to set combo1 & combo2 within that function -- replicating what is there for combo?

the line:
var i, rows = []; maxc = obj.getRowCount();
should say:
var i, rows = []; maxc = table.getCount();

there is a remaining issue with undo filter ( "" ) , that I'll try to review as soon as I can, but for now you can use the 0 to get the same result.

I mean select LO=0 and HI=12

The ShowAll issue is because combo var no longer exist so remove its lines:

function showAll(){
//combo.setControlText("State");
//combo.setControlImage("favorites");
obj.clearRowModel();
obj.clearSortModel();
obj.clearScrollModel();

obj.setRowCount(table.getCount());
obj.refresh();
}

Thank you.

This appears to be working when both col 9 and col 10 take on numeric values. col 9 has the possible values PK and KG.

I have modified http://proximityone.com/k12schools/azschools3.htm per your notes above plus added a revised set of values in names55 (adding PK & KG -- for combo1/col 9 -- only appear in col 9, not col 10) and and adding names56 (for combo2/col10 -- no PK or KG values).

since the PK/KG values are not numeric, the range test does not work when the dropdown is set as PK or KG. while I could change the values in the data to -1 & -2 for example, this would be meaningless/confusing to a user, so I seek to keep the values showing as KG & PK. How to accommodate keeping PK & KG and getting them to work with existing structure?

here is a possible workaround but I cannot see how to add the logic to the code.

1) if PK or KG are selected in combo1, set the col value test to 01 or greater (that is, make PK, KG and 01 equivalent).

2) if combo1 val of 02 or higher is selected automatically exclude rows with PK or KG.

how can this be added?

Thank you.

In this case you can do somethig like this:
( but then you need to remove PK & KC form the combo array and only use '01' instead). remember to set the setItemCount of this combo to 12 and not 14.

note: I also changed maxc_2 var into maxc in the line:
obj.setRowCount(maxc);
to make 'undo-filter' work properly ( in nested filter function that var does not make sense)
so you can remove it's declaration var line too;
var maxc_2 = maxc;

if(filters.length == 0 ){ 
obj.setRowCount(maxc);  
obj.setRowIndices('');  
Lab.setControlText("FOUND: " + maxc); 
} 

if(filters.length > 0 ){ 
 for (rw=0; rw<maxc; rw++){ 
 var found =0; 
  for (Xcol=0; Xcol<filters.length; Xcol++){ 
   var cellValue =  obj.getCellValue(filters[Xcol][0], rw);
if(filters[Xcol][0]==9 && cellValue >'99'){ // 'PK' or 'KG'
cellValue = '01';
}
if ( filters[Xcol][0] < 9  && obj.getCellValue(filters[Xcol][0], rw).toUpperCase().indexOf(filters[Xcol][1]) >-1 ){ found++ }
if ( filters[Xcol][0] > 10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase().indexOf(filters[Xcol][1]) >-1 ){ found++ }
if(filters[Xcol][0]==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] ){ found++ }
if(filters[Xcol][0]==9 && (cellValue).toUpperCase() >=filters[Xcol][1] ){ found++ }
 }        
if (found== filters.length) { rows.push(rw) } 
}

P.S. I found some rows in the CSV file containing 'PK' on column 10 = HI
and at least one in column 9 - LO containig the value 'UG'

HTH

Or you can add an array of values to combo1 and call the nested filter function via values.
this way you maintain visible 'PK' and 'KG' but send '01' to the function.

var names55 = ["", "0", "PK",  "KG",  "01", "02", "03", "04", "05", "06", "07", "08", "09","10","11","12" ]; 
var names55AA = ["", "0", "01",  "01",  "01", "02", "03", "04", "05", "06", "07", "08", "09","10","11","12" ];

var names56 = ["", "0", "01", "02", "03", "04", "05", "06", "07", "08", "09","10","11","12" ]; 
    var combo1 = new AW.UI.Combo; 
    combo1.setControlText("combo1"); 
    combo1.setItemText(names55); 
        combo1.setItemValue(names55AA); /// add Item Values
    combo1.setItemCount(16); 
 combo1.onSelectedItemsChanged = function(index){ 
NestedFilter( combo1.getItemValue(index),9); //// Changed getItemText to get ItemValue
}

Your other could be also implemented by simply:

var names55AA = ["", "0", "-1", "-2", "01", .........

And replacing the block:

if(filters[Xcol][0]==9 && cellValue >'99'){ // 'PK' or 'KG'
cellValue = '01';
}

with:

if(filters[Xcol][0]==9 && cellValue =='PK'){ cellValue = '-1'} // 'PK' 
if(filters[Xcol][0]==9 && cellValue =='KG'){ cellValue = '-2'} //  'KG'

Thank you. Working nicely now in IE.
In FF, the width of the dropdown is almost 0 - cannot select value - how to set wider? Tried style setting buit did not work.
http://proximityone.com/k12schools/azschools3.htm

Not really sure, as this sample is using 2.5.1 version , but could be some other css style declaration , the TABLE container itself or something else:
i.e.
http://www.activewidgets.com/javascript.forum.23245.7/combobox-popup-in-wrong-position.html

try to search for popup & FF to find some solutions.
or try to run it under 2.5.5 Trial to check if still hapening.
HTH

Cannot get to work with those variations; tried some other settings & no go. will start a new thread and maybe Alex will see/know.

Thank you.

tried this also?
combo1.getPopupTemplate().setStyle("height","350");

And try adding:
combo1.setId("combo1");
to make the css styles match the object:

you would also need it for combo2:

combo2.setId("combo2ID");

<style>
#combo2ID {width: 70px; }
#combo2ID-popup {width: 60px;height: 250px}
</style>

Thank you.
I have tried that. it is set that way now. The popup window is wider, but will not allow a selection to be made. will not work as it stands with FF.

There is another issue re using IE which works overall pretty well.

Chino Valley school has grade range 06-12. when you set lo at 6 and hi at 12, it shows. But, even with refresh, when you set lo at 9 and hi at 12 it does not appear -- it shoud, because it does offer those grades.

I cannot see what is stopping the logic.

if a row has a lo grade>=logradeselect and hi grade<=higradeselect it should be selected; but in my example, it is not being selected with 9-12 selection.

Thoughts?

Please disregard my last note. I am reviewing in more detail.
Will post an update in a bit.
Thank you.

1 -- your last suggestion makes it work with FF & IE. Thank you.

2 -- There is another issue .. .

Cactus Shadows school (appears 4th in display) has grade range 07-12. when you set lo at 7 and hi at 12, it shows. But, even with refresh, when you set lo at 9 and hi at 12 it does not appear -- it should, because it does offer those grades.

I cannot see what is stopping the logic.

if a row has a lo grade>=logradeselect and hi grade<=higradeselect it should be selected; but in my example, it is not being selected with 9-12 selection.

Thoughts?

Cactus Shadows school has grade range 07-12
if you set the LoGrade range to >= 9 then, 7 is out of range cause 7 < 9

But if you need to search those LoGrade grid-cell-values that contains a value ( less or equal than) the combo selected , then, just replace:

if(filters[Xcol][0]==9 && (cellValue).toUpperCase() >=filters[Xcol][1] ){ found++ }

with it's opposite:

if(filters[Xcol][0]==9 && (cellValue).toUpperCase() <=filters[Xcol][1] ){ found++ }

>= changed to <=

Re:
Cactus Shadows school has grade range 07-12
if you set the LoGrade range to >= 9 then, 7 is out of range cause 7 < 9
-->
The logic should be ... if the row lo and hi is >= lofilter & <= hifilter then select ... in this case, replace

if(filters[Xcol][0]==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] ){ found++ }
if(filters[Xcol][0]==9 && (cellValue).toUpperCase() >=filters[Xcol][1] ){ found++ }

with something like ...

if ( (filters[Xcol][0]==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] )
&&
(filters[Xcol][0]==9 && (cellValue).toUpperCase() >=filters[Xcol][1] ) ) {
found++ }

but there is a syntax error or something with above ... does not work

Please test it again with <= instead of >= for column 9

Could be that also needs a similar change for column 10 >= instead of <=

do you mean:
if ( (filters[Xcol][0]==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] )
&&
(filters[Xcol][0]==9 && (cellValue).toUpperCase() <=filters[Xcol][1] ) ) {
found++ }

that does not seem right ... but tried it as above anyway ... no match.

here is original --
if(filters[Xcol][0]==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()<=filters[Xcol][1] ){ found++ }
if(filters[Xcol][0]==9 && (cellValue).toUpperCase() >=filters[Xcol][1] ){ found++ }

if(filters[Xcol][0]==10 && obj.getCellValue(filters[Xcol][0], rw).toUpperCase()>=filters[Xcol][1] ){ found++ }
if(filters[Xcol][0]==9 && (cellValue).toUpperCase() <=filters[Xcol][1] ){ found++ }

does not work; no finds

has to be an "and" condition for the range

Cactus Shadows school has grade range 07-12 ... we wan this one under these types of filter settings
if LoGrade range to >= 9 and HiGrade<=12
if LoGrade range to >= 7 and HiGrade<=12
if LoGrade range to >= PK and HiGrade<=12

thank you.

No and needed, the nested filter function does acumulate other columns' filter by design ( into filtersrunning and filters arrays) and that's an eqivalent of & command by finding cells-values that match each array's element.
i.e.
if LoGrade range to >= 9 and HiGrade<=12
translated inside the function means filter array contains:
[['9', '9'],['10','12']] which looks for values in column 9 >= 9 and in column 10 <= 12 ( before last changes >= ,<= after) and do it on any filter ( combo) changes, comparing row by row so...
In my view, Cactus Shadows can meet 2nd and 3rd conditions , but not first because:

-------|7------------|12------- Cactus Shadows
-----------|9--------|12------- >= 9 and HiGrade<=12 ** not match
-------|7------------|12------- >= 7 and HiGrade<=12
-|PK-----------------|12------ >= PK and HiGrade<=12

I understand your description. thank you.

the existing op is great and useful as a variation; would like to keep.

but somehow we need rows selected whenever the lo and hi values in the row are a subrange of loselected and highselected --

lo>=loselected and hi<=hiselected

if this cannot be accomodated with the two dropdowns, what is the easiest alternative?

have added code below //444 in http://proximityone.com/k12schools/azschools3.htm attempting to do above ... there are two combos below table. this might work, but there remain some small errors plus the PK KG cell value issue.

Can you see tweaks to make this work?

Cactus Shadows school has grade range 07-12 should show with settings:
combolg=09 and combohg=12
combolg=07 and combohg=12
combolg=PK and combohg=12

Sorry, but I still couldn't see it.
Maths are obstinate in this :-)

table.getData(9, i).substr(0,2) >= slg.substr(0,2)

the key is '>=' ... must be higher or equal

Cactus Shadows
cell value in col 9 __ >= __ combo value
07--------------------------09 **** ( 07 is NOT higher or equal 9)
07--------------------------07 match
07--------------------------01 (PK) match