QueryByForm Manual

Documentation for components :

TFilterByForm Version 1.5

TQueryByForm Version 1.0

Created By :

Jayan Chandrasekhar,
Riyadh, Saudi Arabia,
Telephone 4772228 Ext 229

E-Mail : jayan_chandra@hotmail.com or jayan@zajil.net

Last Update :

Component : 1st February, 1999 ( Monday )
Documentation : 1st February, 1999 ( Monday )

Demo Data Alias :QBFDemoData

Unit File Name :QryByFrm.Pas ( Contains both the components )

Dedication : To All Delphi Programmers

Note : Written with Delphi-3. Should work in Delphi 1, 2 and 4 also.

Introduction

The aim of this Component is to provide the Delphi Programmers an easy way to implement QueryByForm sort of feature in their programs. That is, as the user works with a form designed by you a Delphi programmer, the user can on the fly during run-time see the data you present as per his requirements. For instance your form may provide employee-details . The user who is browsing through the data may want to see all the Male Employees from one particular Country. Usually you will have to provide for such Queries by providing some button and writing code. That too if the user has informed you while the program was being designed. If there are lets say 20 fields on the form the user should be able to Query any way he wants. The components TQueryByFormand TFilterByForm can help you do so without much effort just that by providing you 3 object methods. Now the user can query the employee data provided by you on the form in any way he pleases. Here are some examples. He may want to see the employee data in 1 of the following ways :

All the male employees.
All the employees having Salary Greater than 50,000.
All the employees who are from a particular country.
All the male employees whose Salary are Greater than 50,000 and are Americans.

You the programmer need not know in advance how the user is going to query. The user at run-time would do so by entering the criteria in the fields on your form. He can also use relational operators provided by clicking the right-mouse button and selecting from the relational operator PopUpMenu while in the field. On executing the query or setting the filter, the Table or Query will reflect the users selection in the result set. TQueryByForm provides these feature by using the SELECT statement of the TQuery and TFilterByForm provides this feature by using the Filter property of TTable / TQuery. Which should be used is your choice, depending on your requirements. Needless to say TQueryByForm is more powerful. Please read the documentation provided below. My components support the data aware controls TDBEdit, TDBLookUpComboBox, DBLookUpListBox, TDBCheckBox, TDBRadioGroup, TDBComboBox, TDBListBox and TDBValueComboBox ( found at some web-site which I find useful ). Following the way I have programmed you can modify the component to work with other DataAware Controls. Both the components work with TDBCtrlGrid. Please note that these components will not work with TDBGrid.

How To Use TQueryByForm Component ?

As usual you will build your form with Query, DBNavigator, DataAware controls. Now decide if you want to add the QueryByForm feature to your form. The Query in the TQuery component can be any SELECT statement with a FROM, WHERE, ORDER,GROUP BY etc. I have not tried Sub-Queries. All type of JOINS will work. Calculated fields with expression, SUM, COUNT etc. will work.
Double click on the TQuery Component to create the fields (TFields) using the Field-Editor. Please note that it is a must that the Origin property of each field should have the Table/Field related information. You do not have to fill this. Delphi will do it for you. When you create a form with a TQuery object in the Form Wizard and double-click on the TQuery component you will see that all the fields have been created. But some how I have noticed that the Origin property is blank. This does not serve our purpose. So delete all these fields and create those fields again. In this way you will see that the Origin property has the necessary information. With Visual Query Builder ( at least in Delphi 3 ) you don’t have to do so. It works fine. Please note that the value in the Origin property will look some what like this "Employee.DB".Employee Name. As long as there is value in the Origin property it is okay. Please note this has to be done for TQueryByForm component only ( It is not needed for TFilterByForm ). More details on Origin property below.
Drop the TQueryByForm component on to your form. Select the DataBaseType. See the Properties documentation below for more details.
Set the DataSource property to the Datasource of your TQuery and DataAware controls. Now add three Buttons to your form. The first button to call the method GetCriteria. The second button to call the method ExecQuery and the third button to call the method ReSetQuery . That’s all. You have given the QueryByForm feature to your user. Easy isn’t it ?
GetCriteria method clears all the fields related to the Datasource and waits for the user to enter his criteria. In the absence ( default ) of selecting any relational operator the Equal To ( = ) relational operator is in effect. If the user wants to use other relational operators ( <, >, <> etc. ) he can avail this by clicking the right-mouse-button while in the field. Once the user has entered / selected his criteria, the ExecQuery method has to be called. The ExecQuery method incorporates the user-criteria into the SQL SELECT statement and re-opens the Query. The ReSetQuery will reset the query to the original one.

How it works ?

When the user clicks the button calling the GetCriteria method all the fields on the form connected to the same DataSource as the QBF DataSource gets cleared. This is the place where the user enters the Criteria he wants the query to work on. The user can avail the relational operators by clicking on the right mouse button. FilterByForm Version 1 used to put the DataSet into an Insert state to clear all the fields and get user criteria. This was fine in case of Tables. But in case of querys the BDE does not always return a Live Query. The query becomes ReadOnly. Thus not allowing us to put the Query into an Insert state. To overcome this problem a temporary In-Memory table is created and the DataSource’s DataSet property is changed to the temporary table. Thus all the fields get cleared and user Criteria can be collected. This solves the Live-Query problem. I have taken some care so that the user does not ( play with the ) Delete or Insert while he is in qsGetCriteria state by blocking the Insert and Delete features. I would recommend that you Disable such Buttons or DBNavigator to be on the safe side. You can enable them as soon as the ExecQuery method is called. When user activates the ExecQuery method the DataSet is changed back to our Query. The ExecQuery parses the SELECT query and seperates the Query into 6 parts. The SELECT-Part, FROM-Part, WHERE-Part, GROUPBY-Part, HAVING-Part and ORDERBY-Part. Now the user criteria is translated and added to the WHERE-Part or the HAVING-Part as per the requirement. ReSetQuery brings the Query to its original form.

Notes on the Origin Property of TField object :

The Origin property of the field objects (TFields) connected to the TQuery has a very important role to play in the working of TQueryByForm component. So it is necessary that the Delphi programmer understands it properly. TQueryByForm gets its field related information from this origin property. Paradox, Dbase, Interbase field-names are automatically put in the origin property by the Field-Editor. Calculated-Fields and Aggregate ( SUM,COUNT ) fields info are not properly put in the origin property. Microsoft Access fields info are also not properly put in the origin fields. Where ever the origin property is not in the way we want we have to manually correct it. Here are some instances...

For the field Name in the Dbase table Animals.Dbf in the Alias DBDemos

the origin property should be "ANIMALS.DBF".NAME

For the field Name in the Paradox table Country.Db in the Alias DBDemos

the origin property should be "COUNTRY.DB".Name

For the field Species No in the Paradox table Biolife.Db in the Alias DBDemos

the origin property should be "BIOLIFE.DB".Species No

For the field CountryId in the Interbase table Employee.GDB in the Demos Directory

the origin property should be COUNTRY.CountryID

For the field Station Prefix in a MS Access table Air Freight

the origin poperty should be [Air Freight].Station Prefix

In case of calculated fields the origin field should have the expression as the value. That is the expression as in the SELECT query should be Copy/Pasted into the origin property. For Example : ( Employee.Salary*0.30 )

Similarly incase of GROUP Expressions like SUM, COUNT, AVG, MAX, MIN, the expression to be put into the Origin property .

For Example : SUM( REC."AMOUNT" )

It is important to bear in mind that in case of GROUP Expressions, the expression should start with SUM, COUNT, AVG, MAX, MIN with out any spaces or any other characters.

Another important point to be noted is that the FieldKind property of the TField object should be changed to fkInternalCalc in case of calculated fields. All non-calculated fields should be of FieldKind fkData.

Properties :

DataSource ( Published ) Here you have to select the DataSource that binds the TQuery Component and the DataAware controls on which the QueryByForm feature will work by changing the SELECT statement’s WHERE part to meet the users Criteria.

DataBaseType (Published) There are 3 database types that QueryByForm works with.

dtStandard - Paradox and Dbase ( default )

dtAccess - Microsoft Access Database ( MDB)

dtInterBase - Interbase

QBFState - (Run time, Read Only) There are 3 states

qsInActive - Is when GetCriteria method is not called or after the ReSetQuery is called.

qsGetCriteria - Is When GetCriteria method is called and before ExecQuery is called. It is in this state the user enters his criteria. He can also use the right-mouse button to select the relational operators and other functions while in a Data-Aware Control.

qsExecuted - Is after the ExecQuery method is called. ReSetQuery method will change the state to qsInActive .

QBFInEffect - ( Run Time , Read Only ) returns True if the query was successfully executed after the ExecQuery method is called. Some time it so happens that the user criteria does not effect any change on the query. For example when the user calls the GetCriteria method and then calls the ExecQuery method without filling in any criteria. After the method is called the query that will be active will have no change. By checking QBFInEffect we will be able to know and inform the user that no change has been made to the Query. If QBFInEffect does not return True you do not have to call ReSetQuery as it will be done automatically by the Component.

ShowExpAsHint – ( Default is True ) When in qsGetCriteria or qsExecute state the Hint property will show your selection. That is when the GetCriteria method is called the user will be presented with blank fields where he can enter the values and click the Right-mouse button to select the needed Relational Operator. It is possible that he would forget what relational operator that was selected. If he wants to check he can move the mouse cursor on the field. The hint will show him his selection. The same happens after the ExecQuery method is called. ShowHint property of the Data Aware control should be True. Please note that if the relational operator is not selected, the default relational operator in effect will be Equal To ( = ). This will not be shown as a tip in the qsGetCriteria state. However in qsExecuted state it will be shown.

WildCardEscape ( Default is '@' ) Here you can put the character which you want to be the Wild Card Escape character - Default is ‘@’ in Wild Card based LIKE operator. The Wild Card Escape character can be changed to any other character. For example :

WHERE Address LIKE "%York%"

Will search for all data in the Address field containing York in it.

Since % is a wild card operator we can check whether % is a part of the field value by using this escape character. If you want to check whether a ‘%’ is there in the field, the wild card escape character can be used. If you want to search in a Character field named Discount for 20%.

WHERE Discount LIKE " %20@%%"

For more information refer to SQL LIKE documentaion.

CaseSensitive ( Default is False ) All querys on text fields will be CaseSensitive if set to true and vice versa.

SelectPart, FromPart, WherePart, GroupPart, HavingPart, OrderPart ( RunTime, ReadOnly ) After the ExecQuery is called the parsed SQL statement is divided into 6 parts which is self-explanatory. ReSetQuery clears these properties.

Methods :

GetCriteria ( Returns True if Successful ) The first method to be called so that all the DataAware controls on the form will be cleared. The QBFState becomes qsGetCriteria. The user will type or select his Criteria in this state. He can also use the right-mouse button to select the relational operators and other functions from the PopUpMenu. Returns true if GetCriteria method was successful. GetCriteria saves the state of DataAware components in the Tag property of the Data-Aware-Control itself. Hence Tag property should not be changed or used while in the qsGetCriteria or qsExecuted state. ReSetQuery will clear the Tag property.

ExecQuery ( Returns True if Successful ) Once the user enters his Criteria the ExecQuery method should be called so that the user Criteria is translated by changing the WHERE and HAVING statements and the SELECT statement is Re-Opened with the new Criteria in effect. Please note that the property QBFInEffect will tell you whether the attempt to change the query was successful or note. ExecQuery also returns true if successful.

ReSetQuery This method has to be called if the effect of GetCriteria or ExecQuery has to be removed and the DataSet is re-opened with the original query in effect. Please note that it is very important to call this method as this method de-allocates the memory allocated by the GetCriteria method. Also note that if you call GetCriteria method it will Call the ReSetQuery method ( Implicitly ). If you don’t intend to call the GetCriteria method again ReSetQuery has to be called explicitly. Please remember this while destroying your form. You can put it in the Form Destroy Event.

Relational Operators available on clicking the Right-Mouse-Button while in qsGetCriteria state :

Relational Operators	Explanation
Equal To, Not Equal To, Less Than, Greater Than	No explanation required
Between & Not Between	The From part of the Between Range to be entered in the Field itself and when Between or Not Between is selected from the Relational Operator PopUpMenu the To part to be entered in the InputBox which pops up on selection.
Contains & Not Contains	If the string that is entered in the field is present in the field values of the table then it is shown. Actually here the LIKE reserved word is used with the Wild Card % automatically prefixed and suffixed. For example : If you want to see whether the field-value in the table contains a substring Rob, the user can enter Rob in the field and select *Contains* relational operator. It is automatically translated as %Rob% internally. So the user should not put the percentage sign. However the Wild Card underscore ( _ ) that is substitute for single character should be used whenever needed.
Like & Not Like	This is nothing but the LIKE operator of SQL SELECT statements and works exactly in the same way. Please note that the *WildCardEscape* property has the default ‘@’. This can be changed. Please refer to the SQL documentation for more details on Wild Card in LIKE operator. Even though the relational operator STARTING WITH is not provided – it can be easily emulated with the wild card %. For eg: if you want to all the names starting with 'Robert' you can enter Robert% in the field and select LIKE relational operator.
In & Not In	This is nothing but the SQL IN operator in the WHERE clause. You do not have to enter anything in the Field. When you select IN from the PopUpMenu an InputBox pops up where you can enter all the values seperated by commas. If string values are not entered in quotes a SQL error will be generated .For Example : In case of strings : "Jan","Feb","Mar" etc. In case of numbers : 10,20,30 For more details refer to SQL IN documentation.
Null & Is Not Null	No explanation is required

Note : In all the above cases NOT is the negative of the same operator

What is new in FilterByForm Ver 1.5 ( FilterByForm Ver 1.0 was published on 17Nov1998 at the www.undu.com site ) .

FilterByForm component is just like QueryByForm except that it works on Table / Query Filter property. If you have existing programs using FilterByForm Ver 1.0, remember to change the QBFTable property and select the DataSource property. And also note that it supports only the Relational operators EqualTo, NotEqualTo, LessThan, GreaterThan , Between, NotBetween , ( New ) Null, (New) NotNull.

Please remove the old FilterByForm component and Install the unit QueryByForm which contains both the components.

Please note that the methods –

GetQuery has been changed to GetCriteria
Execute has been changed to SetFilter
ClearQuery has been changed to ReSetFilter

All the properties in TQueryByForm as explained above except CaseSensitive, WildCardEscape, SelectPart, FromPart, WherePart, GroupPart, HavingPart, OrderPart, are the same for FilterByForm. Additionally FilterByForm has a property QBFObtions which is explained below.

QBFOptions There are two SubProperties which has to set to True or False.

CaseSensitive - Default is False. When False the filter set by the component will be case insensitive.

AsteriskAsWildCard - Default is True. If you do not know the exact way the field value is you can use Asterisk as a Wild Card. For example you are not sure Smith is Smith or Smythe. You can enter Sm*. Please note ‘*’ can be used as a suffix only.

Important Note :

The state of Data Aware Controls are saved in Tag property of the Data Aware controls itself. So please do not use it or assign it while in qsGetCriteria and qsExecuted state. Use it after you call ReSetQuery ( or ReSetFilter in case of FilterByForm ) method. Also note that if you have anything stored in the Tag property before calling the GetCriteria method, it will be restored to you in the Tag property itself , when you call ReSetQuery method.

Known Problems :

When the temporary In-mem table is created it is of type ttDefault which is Paradox. You may have to distribute Paradox Driver along with your programs if it is using some other type of DataBase.

Conclusion :

If you have other Data Aware controls which you want to make QueryByForm aware you can add them to the object code. There are 4 places ( and 4 Places in FilterByForm ) where I have marked *TEMPLATE*. The code has been commented {}. Do not use this. Leave it in comments itself. Instead copy this commented code and paste just above the commented area. Replace the object TDBYourDataAwareControl to your object name. The source comes in only 1 Unit name QueryByForm.Pas. Demos are also enclosed in a Zip file. An Standard Alias named QBFDemoData to be created with the right DataPath.

I have tried both the components on Dbase, Paradox, Access and Interbase (Local). They work fine. I have not worked with them on the network. But I think that should be no problem.

I know it does not follow the true spirit of Object Oriented Programming. New features should be added by inheriting. But in the logic that I have used there a lot of type-casting of Data Aware controls. So I could not generalise the logic for all Data Aware Controls. May be somebody can come out with improvements. Please note that these components are not free. It is e-mail ware. That is if you try this component you have an obligation to inform me what you think of it, your suggestions, In what way you found it useful etc. Feedback is important. In no way should you try to sell this component or its idea. It is intended to be free. This component is a result of hours of hard work and imagination. So please do not forget to give me some credit in the applications you use the component. Feel free to copy and change the component as you like and you feel is better. But informing me about the changes is a must. I am not responsible for any problems you face as a result of using this component. You use it at your own risk. The component is limited by my Knowledge of Delphi, my working experience and my imagination. I will keep on improving this component and you will get the future updates if you respond to me. I would also be happy if you can share your components with me. You can send e-mails to my address jayan_chandra@hotmail.com or jayan@zajil.net.

To the relational operator list I would like to Add a Sounds Like ( Soundex ) feature. But neither Query nor Filter accepts User-Defined-functions. In Interbase maybe stored-procedures would do the trick. I have not tried this. That’s all folks. Keep on building components and share your knowledge......Have lots of fun . Enjoy...............