Details from the TwwFilter Help File Description InfoPower’s TwwFilterDialog is one of the most powerful InfoPower components as it gives your end-users the ability to visually filter a table or query, or modify the where clause of an existing SQL statement. Even though the dialog is capable of sophisticated SQL generation, the dialog is simple to use as it completely hides the filtering and SQL details from the end-user. All they need to know is what they are looking for. The dialog does the rest for them! InfoPower includes AND/OR/NULL support on a single field basis, can show nonmatching records, and has some filter optimizations that allow the filterdialog to take greater advantage of indexes while performing the filter. In addition, the user can search on calculated linked fields and lookupfields in the filterdialog. InfoPower 2000 adds support for picture masks and custom combo boxes. When you execute this dialog box, your end-users can specify a search value, or a range, for any number of fields contained within the table or query referred to by the DataSource property. They can also select a specific type of data match to be performed within the field, such as "From beginning of field", "Anywhere within the field", and "Exact match". The user can also check the Case Sensitive box to have TwwFilterDialog perform a case-sensitive match. The following describes the controls on the dialog: Fields Displays the list of fields that the user can search on All | Searched If the All Fields tab page is selected, then the Fields listbox displays all fields. When set to Searched, the Fields listbox displays only fields where the user has assigned a search value. Field Order If Alphabetic Field Order is selected then the Fields listbox is ordered alphabetically. If Logical is selected then the fields are displayed in their natural order. If you wish to disable the Field Order Radio Group then set the Options | fdShowFieldOrder property for the TwwFilterDialog to False. View Summary Button Click this button to view a summary of the current search criteria. New Search Button Click this button to start a brand new search. By Value Search criteria for currently selected field to be specified by a Field Value. You can clear the Field Value by clicking on the adjacent Clear button. Specify a search type of Exact Match to find exact matches of the Field Value. Specify Partial Match at Beginning and Partial Match Anywhere to find partial matches of the Field Value. Enable the Case Sensitive checkbox to consider case when searching for the Field Value. By Range Search Criteria for currently selected field to be specified by a starting and ending range. You can clear the starting and ending ranges by clicking on the adjacent Clear buttons. Filtering mechanisms used by the TwwFilterDialog The TwwFilterDialog’s allows you to filter the dataset using a wide variety of mechanisms. The filterdialog either uses the dataset’s built-in filtering when its FilterMethod is set to fdByFilter. When the FilterMethod is set to fdByQueryModify, it will modify the dataset’s SQL where clause. For more information on this property see the TwwFilterDialog FilterMethod property. DataSet’s Native Filtering The default filter method is fdByFilter. This means the filtering uses the dataset’s Filter property or its OnFilterRecord event to perform the filter, depending upon the FilterPropertyOptions | DataSetFilterType property. The following summarizes the filtering mechanisms when using fdByFilter. These mechanisms are controlled by the FilterPropertyOptions | DataSetFilterType property fdUseFilterProp Uses the dataset’s Filter property. The Filter property, although more limited in functionality, can offer significant performance benefits when filtering larger datasets. In general this is the fastest type of filter when using FilterMethod=fdByFilter. When set to this value, filtering is not supported on lookupfields, memofields, and calculated fields. In addition it will not support wildcard searches unless the back-end supports the ‘like’ operator (such as ADO to Microsoft Access which supports the Like keyword in filters). fdUseOnFilter Uses the OnFilterRecord event of the dataset. Includes support for lookupfields and memofields. When using ADO or Delphi 5’s InterBase objects, it additionally supports filters on calculated fields in your dataset. fdUseBothFilterTypes Uses both the dataset’s filter property and its OnFilterRecord events. If your back-end supports both filter types, then this should give you the best performance and most capabilities. Special considerations when filtering on datasets in data modules. The following warning only applies if you are using FilterMethod=fdByFilter, and either FilterPropertyOptions | DataSetFilterType of fdUseOnFilter or fdUseBothFilterTypes. Warning – If the dataset you are filtering is in a data module, you must place the TwwFilterDialog also in the data module, as use of dialog causes the dataset to call a method defined in the filterdialog.  Otherwise your filterdialog can be destroyed while your dataset still has an active callback filter. If the filterdialog is destroyed before the dataset, the dataset will be calling random memory and result in runtime access violations. Special considerations when filtering with Delphi’s ADO datasets. The ADO callback filtering currently has a bug in Delphi when encountering EOF and BOF during the filter. This can manifest itself as a run-time error. In addition, there are also problems when posting a dataset that has been filtered using callback filtering, as the system can enter an infinite loop. The FilterDialog uses callback filtering under the following property configuration. FilterMethod = fdByFilter FilterOptions.DataSetFilterType = fdUseOnFilter or fdUseBothFilterTypes We recommend that you instead use FilterOptions.DataSetFilterType of fdUseFilterProp when using TADOTable or TADOQuery to avoid these problems. Borland/Inprise has been notified of these problems, so we expect that you will be able to use callback filtering in the future once these issues are resolved them. Remote filtering by modifying the dataset’s SQL property When the FilterMethod is set to fdByQueryModify, the TwwFilterDialog parses the SQL of the dataset, and then replaces its where clause based on the user entered criteria. The revised query is then re-executed so that remote filtering is performed. Though functionally very similar to local filtering, the actual mechanism of filtering is not performed locally, but instead at the back-end. The back-end can then efficiently perform the search by utilizing available indexes. This filter method also has the advantage of reducing network traffic since the filtering is performed on the same machine that contains the data. InfoPower’s FilterDialog is designed to recognize the new ADO and Interbase dataset objects. However if you are using a 3rd party engine which has different property names or property types for its dataset, then you may need to use the OnInitTempDataSet event to initialize any additional properties for your dataset type. Use the SQLTables property to pre-define which tables the filterdialog should extract its list of field names from. If this property is uninitialized then the filterdialog will attempt to parse the SQL to retrieve this information. However the filterdialog’s parsing only handles simple SQL statements so it may not be useful for your specified SQL. In general you should always set the SQLTables property so that you will not be tied to the filterdialog’s limited parsing abilities. Use the FieldsFetchMethod to determine the logic InfoPower uses to retrieve the field information. You may wish to set this property to fmUseTFields if you want the FilterDialog to simply gather its field definitions from the dataset’s field properties. By doing so, the filterdialog will not need to parse the SQL for its list of tables, nor will it need to query the database for its list of fields for each table. As a result it may improve the speed of the initial display of the dialog. Note that the SQLTables property is ignored when FieldsFetchMethod is set to fmUseTFields. Caution Make sure that your field display labels are unique. InfoPower’s FilterDialog uses the field titles to fill the fields listbox. If there are duplicates, the user’s filtering specification becomes ambiguous and may not function as expected.