Tornado Class Library

Special Topic - Filter

For a Web DB application, search filter is a mandatory feature. Search filter comes in many flavors - Text, Date, Numeric, Boolean checkbox, Radio box, Select DropDown Box Text contains, text starts with, hidden fields, Date field with calendar assist, ReadOnly, Validation etc.. You'll find Tornado's search filter implementation is the most powerful among it's class.

 

Simple Search
<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim Mydb As New Tornado.Z()
   Mydb.dbQP = "U=2| M=ty=dh!sys=t| PS=11| S=plain| D=Nwind.Mdb| Q=Orders| Gdf=0,1| Fmhf=0,1| TH=Title=Search Filter| navigationitem=b5,search| filterflds=0,1"
   Mydb.ASPdbNET()
End Sub
</script>

After defining a Dual display and the fields to display in the grid and fields to hide in the form, add the FilterFields and the Search navigation button parameters and the entire Search Filter implementation is done. Execute the live example and click the 'Search' button and you'll notice that all the search setup is ready to go. Note that OrderID is numeric and CustomerID is Text. Try the following actions -

Now, you noticed that this Filter Search implementation is not really 'simple'. The 'simple' means simple to program. A quick question will be - How about search for the Oregon state like State = AZ or State = OR or State = TX? Go ahead and test that by just entering A or OR or B in the CustomerID field and check out the Filter criteria in the status line to see whether it is correct. What about the datetime field wrapper in MS-SQL as they are different than Access? When you specify dbType, these differences will be resolved internally. How about that? These are the questions that has been asked by our customers for the pass 5 years and we honored majority of them. So, what is the chance that you can re-use many of these customer driven features.

 

Advanced Search - Field Control

The following example is packed with advanced features. Just image how much work it'll take you to product an application equivalent to this 17 lines of code or you can do it al all.

<script runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
  Dim ct As String = "ind=EMPID,FULLNAME|Q=SELECT DISTINCT Employees.EmployeeID,"
  ct &= "FirstName & ' ' & LastName AS FullName FROM Employees;"
  ct &= "ind=SHIPID,SHIPNAME|sql=SELECT DISTINCT ShipperID,CompanyName FROM Shippers;"
  ct &= "ind=CUID,CMPNAME|sql=SELECT DISTINCT CustomerID,CompanyName FROM Customers"
  Dim Fadv1 As New Tornado.z
  With Fadv1
    Dim qp As String = "U=1| D=Nwind| S=Air| M=ty=dh!sys=t| Gdf=0,1| Fmhf=0,1| Ps=12|"
    qp &= "Q=Orders| Th=tit=Advanced Search| bof=filterdroptoggle=t| ni=b5,filter"
    .dbQP = qp
    .dbCalendar = "Excludedates(0)=12/26/2001|12/27/2001| Weeknumbers=true| buttonimage=/tornado/images-net/calendar_new1.gif"
    Dim ff As String = "field=CustomerID|type=SelectBox+BN|value=CUID|text=CMPNAME| note=Pick a Customer, "
    ff &= "field=EmployeeID|type=ROFilter| def=4| key=EMPID| lookup=FULLNAME| note=Pick an Employee, "
    ff &= "field=OrderDate| type=TEXTCALENDAR| tag=SIZE=30| note=Select an Order Date"
    .dbFilterFlds = ff
    .dbCommonTables = ct
    .dbLookUpFlds = "fie=1| key=CUID| look=CMPNAME,fie=2| key=EMPID| look=FULLNAME,field=6| keyindex=SHIPIDLookupindex=SHIPNAME"
    .dbNameMap = "fie=1|alias=Customer Name, fie=2|alias=Employee Name"
    .dbFilterSQL = "Select * From Orders where [[Filter]]"
    .ASPdbNET()
  End With
End Sub
</script>

What is going on in this example?

 

Compound Filtering

ASPdb.Net filter has a compound entries feature. The following are examples of various format -

Entry Means
O'Neal LIKE '%O''Neal%'  
=O'Neal = '%O''Neal%'  
123 = 123
>10 >10
01/02/2001 = #01/02/2001# (Access)
>07/04/01 >'07/04/01' (non Access)
BETWEEN 1 and 10 >=1 and <=10
A or B LIKE '%A%' OR LIKE '%B%'
ABC% LIKE 'ABC%'
OR ABC ..... OR LIKE '%ABC%'
'Barnes and Nobles' Enclosed search phrase in single quotes to search for the entire phrase w/o any compound filtering

 

 

FilterAssistant - Connector and Operator

The following is the default system FilterAssistant which provides quick choices of the filtering connector and operator in the filter screen. The checked values are default values with or without the Assistant. 

Text Comparison:
Contains  Starts With   Exactly   
Connect all fields using:
AND  OR

To change the appearance of the Assistant, use -

To set the operator and connector value manually, use -

 

Filter Notes

Both Filter and Edit has a 'Notes' options such that if the keyword is specified then the notes will be placed next to the Input box. Our experience shows that a simple note to tell user how to make a selection or set an edit value often saves a lot of code. See the following example -

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim x As New Tornado.z
    x.dbQP = "u=1|q=orders|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2"
    x.dbFilterFlds = "fi=0|Notes=This is Order,fi=1|No=This is customer ID,fi=2|No=Some more notes"
    x.ASPdbNET()
End Sub
</script> 

 

FilterOnEntry

How can you offer a selected records set for the user running the application based upon a criteria like a userid? If you use program.aspx?Userid=123 then there is no security at all. dbFilterOnEntry allows you to run the application with the dbSQL criteria set according to the property which is not accessible by the user.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim x As New Tornado.z
  x.dbQP = "u=1|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2"
  x.dbFilterOnEntry = "OrderId > 10260"
  x.dbSQL = "Select * from Orders"
  x.dbDebug = "ActiveSQL"
  x.ASPdbNET()
End Sub
</script> 

 

User controlled FilterSQL

You can have total control of the filtering criteria by defining your own FilterSQL and allow user to fill in just the [[Filter]] tag. See the following example.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim x As New Tornado.z
  x.dbQP = "u=1|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2"
  x.dbFilterSQL = "Select * from Orders where OrderId > 10260 AND [[Filter]]"
  x.dbSQL = "Select * from Orders"
  x.dbDebug = "ActiveSQL"
  x.ASPdbNET()
End Sub
</script> 

 

Ucase in Filter

Have you ever worked in some DB (Oracle?) that is all uppercase and you find out your filter mixed case input is not working.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim x As New Tornado.z
  x.dbQP = "u=1|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2"
  x.dbSQL = "Select * from Orders"
  x.dbFilterUCaseFunction = "UCase([[field]])"
  x.ASPdbNET()
End Sub
</script> 

 

JavaScript Validation in Filter

You can make a quick JavaScript function for validation as follows and remember that JS name is case sensitive. Input an EmployeeId of > 10 will trigger a false return from the JS() function.

<HEAD>
<SCRIPT LANGUAGE="JavaScript">
function MyJS() {
var id = parseInt(document.forms[0].elements[0].value,10)
if (id > 10 | id < 1 )
{
alert('\nSorry, you can only input employeeID 1-10.\n\n Please try again.');
return false; // go back to the form
} else {
return true; // good answer, proceed
}
}
</SCRIPT>
</HEAD>

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
  Dim JS As New Tornado.z
  With JS
    .dbQP = "u=36| D=Nwind| S=8| Gdf=0,1,2,3| Q=Employees| Ni=B5,Filter| Ff=0,1,2,3| Th=ti=Filter Javascript"
    .dbFilterValidateName = "MyJS()" '...Remember MyJS() is case sensitive!!
    .ASPdbNET()
  End With
End Sub
</script>

 

Select multiple fields in Filter

This is a new feature (Nov 2007) to enable user to select multiple fields to build a search filter with a criteria of A AND B AND C or A OR B OR C. See the following example code. Filter fields 0 and 1 with 1 is a drop down select box. Tag=Multiple creates the drop down effect. dbFilterConnection = OR condition is set to create A and B and C criteria. Submit the search query and watch the Criteria displayed at th estatus bar.

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim x As New Tornado.z
   x.dbQP = "u=1|q=orders|D=Nwind|Ni=b5,Filter,ResetFilter|Gdf=0,1,2,3|TH=tit=Filter Test|fc=OR"
   x.dbCommonTables = "ind=OID|Q=SELECT DISTINCT OrderID from orders"
   x.dbFilterFlds = "Fi=0|ty=SB|Val=OID|tag=multiple, 1"
   x.ASPdbNET()
End Sub
</script>

Send comments on this topic.


Copyright 1998-2006 ASP-db