| Subject | Re: filter |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Mon, 2 Aug 2021 15:41:25 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | filter_test1.wfm |
On 2021/08/02 07:39, Milind Nighojkar wrote: > use this syntax to set filter to the records having the first number in the field to be "1" > > set filt to at('1',fieldname) = 1 As Akshat has pointed out, there's a problem here. Apart from that, this syntax is used with a table opened in a work area by the command USE or SELECT. In other words, this is for the original XDML commands in dBASE. Akshat's solution works where the field is left justified. Where the field is right justified one would use set filter to left(ltrim(data_c2),1) = '1' This doesn't work with OODML i.e where one uses a query object to access the table. Here one needs to set the rowset's filter property. Something like form.query1.rowset.filter = "fieldname = '1'". For this to work properly the rowset's filterOptions property must be set to 1 or 3 i.e Match partial length. As we are dealing with numerals the case doesn't affect things. So far, so good but where numbers, stored in a character field, are used to identify items they are usually padded with leading spaces so that the values are right justified. This is usually done so that items can be viewed in numerical order if necessary. With a right justified character field form.query1.rowset.filter = "fieldname = '1'" will only display those items where the number completely fills the field. The rowset's filter property needs a localSQL expression but it's abilities are so limited that it is not possible to filter for values starting with '1' where the data is right justified. Solving the problem with a parameter driven query is, however, quite simple. A little example showing both ways is attached. Mervyn. | |
if file('filter_test.dbf') // drop table filter_test endif if not file('filter_test.dbf') create table filter_test (id autoinc,data_n integer,data_c1 character(6),data_c2 character(6)) use filter_test generate 100 delete all for data_n <= 0 delete all for data_n> 999999 replace all data_c1 with ltrim(str(data_n)) replace all data_c2 with str(data_n,6,0,' ') use endif ** END HEADER -- do not remove this line // // Generated on 2021-08-02 // parameter bModal local f f = new filter_test1Form() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class filter_test1Form of FORM with (this) height = 16.0 left = 29.7143 top = 0.6818 width = 132.5714 text = "" endwith this.FILTER_TEST2 = new QUERY(this) with (this.FILTER_TEST2) left = 120.0 width = 9.0 height = 1.0 sql = 'select * from "filter_test.DBF" where trim(data_c2) like :filter order by data_c2' params["filter"] = "%" active = true endwith this.FILTER_TEST1 = new QUERY(this) with (this.FILTER_TEST1) left = 3.0 width = 9.0 height = 1.0 sql = 'select * from "filter_test.DBF"order by data_c2' active = true endwith this.GRID1 = new GRID(this) with (this.GRID1) dataLink = form.filter_test1.rowset columns["COLUMN1"] = new GRIDCOLUMN(form.GRID1) with (columns["COLUMN1"]) dataLink = form.filter_test1.rowset.fields["data_n"] editorType = 3 // SpinBox width = 18.5714 endwith columns["COLUMN2"] = new GRIDCOLUMN(form.GRID1) with (columns["COLUMN2"]) dataLink = form.filter_test1.rowset.fields["id"] editorType = 1 // EntryField width = 15.7143 endwith columns["COLUMN3"] = new GRIDCOLUMN(form.GRID1) with (columns["COLUMN3"]) dataLink = form.filter_test1.rowset.fields["data_c1"] editorType = 1 // EntryField width = 10.0 endwith columns["COLUMN4"] = new GRIDCOLUMN(form.GRID1) with (columns["COLUMN4"]) dataLink = form.filter_test1.rowset.fields["data_c2"] editorType = 1 // EntryField width = 10.0 endwith with (columns["COLUMN1"].editorControl) rangeMax = 100 rangeMin = 1 endwith with (columns["COLUMN1"].headingControl) value = "data_n" endwith with (columns["COLUMN2"].headingControl) value = "id" endwith with (columns["COLUMN3"].headingControl) value = "data_c1" endwith with (columns["COLUMN4"].editorControl) picture = "999999" fontName = "Consolas" fontSize = 10.0 fontBold = false fontItalic = false fontStrikeout = false fontUnderline = false endwith with (columns["COLUMN4"].headingControl) value = "data_c2" endwith height = 8.7727 left = 1.7143 top = 2.2727 width = 62.5714 endwith this.PUSHBUTTON1 = new PUSHBUTTON(this) with (this.PUSHBUTTON1) onClick = class::PUSHBUTTON1_ONCLICK height = 1.0909 left = 7.7143 top = 12.0455 width = 15.2857 text = "Filter data_c1" endwith this.PUSHBUTTON2 = new PUSHBUTTON(this) with (this.PUSHBUTTON2) onClick = class::PUSHBUTTON2_ONCLICK height = 1.0909 left = 25.5714 top = 12.0455 width = 15.2857 text = "Clear filter" endwith this.PUSHBUTTON3 = new PUSHBUTTON(this) with (this.PUSHBUTTON3) onClick = class::PUSHBUTTON3_ONCLICK height = 1.0909 left = 43.4286 top = 12.0455 width = 15.2857 text = "Filter data_c2" endwith this.GRID2 = new GRID(this) with (this.GRID2) dataLink = form.filter_test2.rowset columns["COLUMN1"] = new GRIDCOLUMN(form.GRID2) with (columns["COLUMN1"]) dataLink = form.filter_test2.rowset.fields["id"] editorType = 1 // EntryField width = 15.7143 endwith columns["COLUMN2"] = new GRIDCOLUMN(form.GRID2) with (columns["COLUMN2"]) dataLink = form.filter_test2.rowset.fields["data_n"] editorType = 3 // SpinBox width = 18.5714 endwith columns["COLUMN3"] = new GRIDCOLUMN(form.GRID2) with (columns["COLUMN3"]) dataLink = form.filter_test2.rowset.fields["data_c1"] editorType = 1 // EntryField width = 10.0 endwith columns["COLUMN4"] = new GRIDCOLUMN(form.GRID2) with (columns["COLUMN4"]) dataLink = form.filter_test2.rowset.fields["data_c2"] editorType = 1 // EntryField width = 10.0 endwith with (columns["COLUMN1"].headingControl) value = "id" endwith with (columns["COLUMN2"].editorControl) rangeMax = 100 rangeMin = 1 endwith with (columns["COLUMN2"].headingControl) value = "data_n" endwith with (columns["COLUMN3"].headingControl) value = "data_c1" endwith with (columns["COLUMN4"].editorControl) picture = "999999" fontName = "Consolas" fontSize = 10.0 fontBold = false fontItalic = false fontStrikeout = false fontUnderline = false endwith with (columns["COLUMN4"].headingControl) value = "data_c2" endwith height = 8.7727 left = 67.0 top = 2.1818 width = 62.5714 endwith this.PUSHBUTTON5 = new PUSHBUTTON(this) with (this.PUSHBUTTON5) onClick = class::PUSHBUTTON5_ONCLICK height = 1.0909 left = 89.0 top = 11.8636 width = 15.2857 text = "Clear filter" endwith this.PUSHBUTTON6 = new PUSHBUTTON(this) with (this.PUSHBUTTON6) onClick = class::PUSHBUTTON6_ONCLICK height = 1.0909 left = 108.0 top = 11.8182 width = 15.2857 text = "Filter data_c2" endwith this.TEXTLABEL1 = new TEXTLABEL(this) with (this.TEXTLABEL1) height = 1.0 left = 38.4286 top = 14.0909 width = 56.5714 text = "Setting the filter displays data in the field that starts with '1'" endwith this.TEXTLABEL2 = new TEXTLABEL(this) with (this.TEXTLABEL2) height = 1.0 left = 18.8571 top = 0.8182 width = 12.0 text = "Filtered" endwith this.TEXTLABEL3 = new TEXTLABEL(this) with (this.TEXTLABEL3) height = 1.0 left = 80.4286 top = 0.7727 width = 25.2857 text = "Parameter driven query" endwith this.rowset = this.filter_test1.rowset function PUSHBUTTON1_onClick() form.rowset.filteroptions :=3 form.rowset.filter = "data_c1 ='1'" return function PUSHBUTTON2_onClick() form.rowset.filter = "" form.rowset.first() return function PUSHBUTTON3_onClick() form.rowset.filteroptions :=3 form.rowset.filter = "data_c2 ='1'" return function PUSHBUTTON5_onClick() form.filter_test2.params['filter'] = [%] form.filter_test2.requery() return function PUSHBUTTON6_onClick() form.filter_test2.params['filter'] = [1%] form.filter_test2.requery() return endclass | |