PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US

  • Overview
  • Creating a filter
  • Characteristics
  • Handling the filters in WLanguage
  • WLanguage functions
  • WLanguage properties
  • Browse and search in a selection of filtered records
  • Optimizing the filters
  • Filters and statistics
  • Filters and composite keys
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Filtering a data file, a view or a query
ODBCNot available with this kind of connection
Overview
A filter is used to define by programming a selection of records corresponding to one or more criteria. This selection of records can be performed on a HFSQL data file, a HFSQL view or a query.
The records found in this selection can be read and browsed by the browse functions (HReadFirst, HReadNext, ...). This allows you, during a browse, to directly process all the selected records.
For example, to send a specific email to all the customers living in state 34, 35 and 36, you must:
  • Create a filter on the Customer data file to select the customers living in state 34, 35 and 36,
  • Browse the filtered data file and run for each customer a procedure used to send the email.
Filters or queries?
  • The filters are recommended when the selection condition applies to a single data file, a single view or a single query. The filters are used to create complex selection conditions on the records or to easily select the records found in an interval of values.
  • The queries are recommended when the selection of records must be performed on several data files.
Versions 17 and later
iPhone/iPad This feature is now available for the iPhone/iPad applications.
New in version 17
iPhone/iPad This feature is now available for the iPhone/iPad applications.
iPhone/iPad This feature is now available for the iPhone/iPad applications.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Creating a filter
A filter is created by HFilter. This filter is used to select:
  • the records found between two bounds: the bounds must be defined for a key item.
  • the records corresponding to a selection condition specific on an item.
In both cases, HFilter returns the best item that will be used to browse the filtered records. This item can be a key item of the data file or not. If the data file is not browsed according to this item, the filter will not be taken into account.

Characteristics

A single filter per data file (view or query) can be enabled at a given time.
Handling the filters in WLanguage

WLanguage functions

The following WLanguage functions are used to manage the filters:
Versions 09 and later
HActivateFilter
New in version 09
HActivateFilter
HActivateFilter
Enables the filter that was previously created for the specified data file (view or query).
Versions 09 and later
HDeactivateFilter
New in version 09
HDeactivateFilter
HDeactivateFilter
Temporarily disables the filter on a data file (view or query).
Versions 09 and later
HFilter
New in version 09
HFilter
HFilter
Defines and enables a filter on a data file, view or query.
Versions 17 and later
HFilterContains
New in version 17
HFilterContains
HFilterContains
Defines and enables a "Contains" filter on a data file, view or query.
Versions 09 and later
HFilterIdentical
New in version 09
HFilterIdentical
HFilterIdentical
Defines and enables a filter used to find the exact value of a string item.
Versions 09 and later
HFilterIncludedBetween
New in version 09
HFilterIncludedBetween
HFilterIncludedBetween
Defines and enables an "Included between" filter on a file, view or query.
Versions 09 and later
HFilterStartsWith
New in version 09
HFilterStartsWith
HFilterStartsWith
Defines and enables a "Start with" filter on a file, view or query.

WLanguage properties

The following WLanguage properties are used to manage the filters:
MaxValueRetrieves the upper bound of the current filter (defined by HFilter) on a data file, a HFSQL view or a query.
MinValueRetrieves the lower bound of the current filter (defined by HFilter) on a data file, a HFSQL view or a query.
FilterConditionReturns the selection condition implemented by HFilter on a data file, a HFSQL view or a query.
FilterWithBoundsUsed to find out whether bounds have been specified on the filter implemented by HFilter on a data file, a HFSQL view or a query.
FilteredItemUsed to find out the item on which a filter was implemented by HFilter on a data file, a HFSQL view or a query.
Browse and search in a selection of filtered records
  • To browse a set of filtered records, all you have to do is use the HFSQL browse functions.
    HReadFirstPositions on the first record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
    HReadNextPositions on the next record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
    HReadPreviousPositions on the previous record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
    HReadLastPositions on the last record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
  • To browse a set of filtered records, all you have to do is use the HFSQL search functions.
    HReadSeek/HReadSeekFirst
    • If the search applies to the search key, seeks and positions on the first record of the filter corresponding to the specified condition (if it exists). HOut returns True if no other record corresponds to the filter and to the search condition.
    • If the search applies to another item, seeks and positions on the first record of the file (not filtered) corresponding to the specified condition (if it exists). HOut returns True if no other record corresponds to the filter and to the search condition.
    HReadSeekLast
    • If the search applies to the search key, seeks and positions on the last record of the filter corresponding to the specified condition (if it exists). HFound returns True if no other record corresponds to the filter and to the search condition.
    • If the search applies to another item, seeks and positions on the last record of the data file (not filtered) corresponding to the specified condition (if it exists). HOut returns True if no other record corresponds to the filter and to the search condition.
Optimizing the filters
To optimize the browse of filtered records:
  • perform statistical calculations of your indexes on a regular basis,
  • define composite keys in your data files.

Filters and statistics

The filters are based on the HFSQL index statistics. To optimize the browse of filtered records, we recommend that you use recent statistics. These statistics are updated by HStatCalculate.
Therefore, we recommend that you regularly calculate the statistics of the filtered files (especially for the data files that are often modified, an order line for example). These statistics are automatically recalculated when reorganizing (or re-indexing) the data files.
Caution: Depending on the size of the data file, calculating the statistics can take quite a long time. Furthermore, this calculation locks the data file: the data file cannot be used during this period of time (HFSQL Classic only). We advise you to perform this calculation when the database is not used (night process for example)

Filters and composite keys

If the filter condition is applied to several items corresponding to a composite key of your data file, the search key automatically chosen for the filter will be this composite key.
For example:
  • A filter is defined on the CUSTOMER data file. This filter is applied to the LASTNAME and FIRSTNAME items.
  • A composite key LASTNAME+FIRSTNAME is found in CUSTOMER data file.
  • The composite key will be automatically chosen as search key for the filtered data file.
See Composite keys for more details.
See also
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment