PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US

  • Lower bound and upper bound on a Text or Composite Key key item
  • Filter and filtered browse
  • Selection Condition
  • Enabling/Disabling a filter
  • Filter on a composite key
  • Data files not in HFSQL format
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
Defines and enables a filter on a data file, view or query.
After its execution, the most common cases are as follows:
  • the filter cannot be created: HFilter returns an empty string (""). HError returns the error identifier.
  • the filter is created: HFilter returns the optimized search key that will be used to browse the data file, view or query.
Versions 15 and later
Android This function is now available for the Android applications.
New in version 15
Android This function is now available for the Android applications.
Android This function is now available for the Android applications.
Versions 17 and later
iPhone/iPad This function is now available for the iPhone/iPad applications.
New in version 17
iPhone/iPad This function is now available for the iPhone/iPad applications.
iPhone/iPad This function is now available for the iPhone/iPad applications.
Versions 18 and later
Android Widget This function is now available in Android Widget mode.
Universal Windows 10 App This function is now available in Windows Store apps mode.
New in version 18
Android Widget This function is now available in Android Widget mode.
Universal Windows 10 App This function is now available in Windows Store apps mode.
Android Widget This function is now available in Android Widget mode.
Universal Windows 10 App This function is now available in Windows Store apps mode.
Versions 21 and later
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Example
// Simple filter with condition:
// Find the customers of a city

SearchKey is string
mycity is string

mycity = "MONTPELLIER"
SearchKey = HFilter(Customer, "CITY='" + mycity + "'")
HReadFirst(Customer, SearchKey)
WHILE HOut() = False
// Process the record
...
HReadNext(Customer, SearchKey)
END
// Cancels the filter
HDeactivateFilter(Customer)
// Filter performed with a bounded search key and a condition:
// the search key is fixed

SearchKey is string
// Filters the invoices found between 1/1/2005 and
// 12/31/2005, whose total is greater than 1500 Euros IOT
// with a discount whose type is passed in parameter (text item)
SearchKey = HFilter(Invoice, InvoiceDate, "20050101", "20051231", ...
StringBuild("TotalIOT>1500 AND DiscountType='%1'" + , ...
Replace(sDiscountType, "", "\")))
// The Replace function is used to prefix the quotes
// contained in sDiscountType with a backslash
IF SearchKey <> "" THEN
HReadFirst(Invoice, SearchKey)
WHILE HOut() = False
// Process the record: send a thank you letter
Send_Letter()
HReadNext(Invoice, SearchKey)
END
END
...
// Cancels the filter
HDeactivateFilter(Invoice)
Syntax

Filter built with a bounded search key and a condition Hide the details

<Result> = HFilter(<File Name> , <Name of Search Key> , <Lower Bound> [, <Upper Bound> [, <Selection Condition>]])
<Result>: Character string
Search item. Corresponds to:
  • the search key of data file if the filter is enabled.
  • an empty string ("") if the filter cannot be implemented.
<File Name>: Character string (with or without quotes)
Name of data file, HFSQL view or query used.
<Name of Search Key>: Character string (with or without quotes)
Name of key item used to browse the data file, view or query. This item must be a search key of data file, view or query. The lower and upper bounds will be applied to this item.
<Lower Bound>: Type of search item
Minimum value (inclusive) of search item (if the search key is defined with an ascending browse in the analysis). The records corresponding to this minimum value will be included in the filter. For a descending browse (which means if the search key is defined with a descending browse in the analysis), it is the maximum value of the search item.
The type of this parameter must correspond to the type of search item. For example, if the search item is a string, the lower bound must also be a string.
<Upper Bound>: Type of search item, optional parameter
Maximum value (inclusive) for the search item (ascending browse). The records corresponding to this maximum value will be included in the filter. The type of this parameter must correspond to the type of search item. For example, if the search item is a string, the upper bound must also be a string.
For a descending browse, it is the minimum value of the search item.
If this parameter is not specified, the upper bound corresponds to the value of <Lower Bound>.
<Selection Condition>: Optional character string (with quotes)
Selection condition used to create the filter (see the Notes). This selection condition can be a character string in Ansi or Unicode format.

Filter built with a condition Hide the details

<Result> = HFilter(<File Name> , <Selection Condition>)
<Result>: Character string
Search item. Corresponds to:
  • the search key of data file if the filter is enabled
  • an empty string if the filter cannot be implemented
<File Name>: Character string (with or without quotes)
Name of data file, HFSQL view or query used.
<Selection Condition>: Character string (with quotes)
Selection condition used to create the filter (see the Notes). This selection condition can be a character string in Ansi or Unicode format.
Remarks

Lower bound and upper bound on a Text or Composite Key key item

If the lower bound and the upper bound are the same:
  • to implement an exact-match filter on a value, all you have to do is specify this value in the "Lower Bound" parameter.
    For example, to select the customers whose name corresponds to "Dupon":
    HFilter(Customer, Name, "Smith")

    The customer named "Smither" will not be selected.
  • to implement a generic filter on a value, you must:
    • fill the lower bound with the hMinVal constant to give it the minimum value.
    • fill the upper bound with the hMaxVal constant to give it the maximum value.
    For example, to select the customers whose last name starts with "Dupon":
    HFilter(Customer, Name, "Smith" + hMinVal, "Smith" + hMaxVal)

    The customers named "Smith" and "Smither" are selected.
Notes:
  • The hMinVal constant is equivalent to Charact(0).
  • The hMaxVal constant is equivalent to Charact(255).

Filter and filtered browse

After the call to HFilter, the data file must necessarily be browsed on the item returned by HFilter. The filter will be ignored if another item is used to browse the data file.
When a filter is defined and enabled on a data file (view or query), all records read correspond to the filter. If no other record corresponds to the filter during the browse:
  • HOut returns True.
  • the current record corresponds to the last record read with the filter.
For example:
On a filtered data file, after the function:the current record is:
HReadFirstthe first file record corresponding to the filter
HReadLastthe last file record corresponding to the filter
HReadNext (or HForward)the next record (or the next nth record) corresponding to the filter
HReadPrevious (or HBackward)the previous record (or the previous nth record) corresponding to the filter

Selection Condition

The general syntax of a condition has the following format:
"CustomerName>'Smith' and (ZipCode=34 or ZipCode=32)"
The supported operators depend on the type of items used in the condition:
<>DifferentValid for all types
>Greater thanValid for all types
>=Greater than or equal toValid for all types
<Less thanValid for all types
<=Less than or equal toValid for all types
 =Strictly equal toValid for all types
~=Almost equal to: ignores the space characters on the right, the accented characters, the lowercase and uppercase charactersValid for string types only
~~Very flexible equality: does not distinguish between lowercase and uppercase characters, ignores the space characters found before and after the string to test, ignores the lowercase accented characters, ignores the space characters and the punctuation characters inside the strings.Valid for string types only
]Contains: takes the lowercase and uppercase characters into accountValid for string types only
~]Contains: ignores the lowercase and uppercase charactersValid for string types only
]=Starts with: takes the lowercase and uppercase characters into accountValid for string types only
AndroidAndroid Widget JavaPHP The ~~ and ~= operators must not be used. These operators are not allowed.
Notes about the selection condition:
  • The constant strings must be enclosed in simple quotes.
  • If <Item Name> contains simple quotes, they must be doubled.
    For example: ‘Item‘‘with‘‘quotes‘
  • The comparisons between strings are performed according to the parameters specified for the indexes.
  • The binary memos and the composite keys cannot be part of a selection condition.
    For the composite keys, we recommend that you use the first syntax. The following syntax cannot be used:
    HFilter(FILENAME, "COMPKEY~]'AAA' AND COMPKEY]='" + ...
    HBuildKeyValue(FILENAME, COMPKEY, 0) + "'")
  • If a character string (constant or variable) contains a simple quote, this simple quote must be preceded by a backslash character ( \ ).
  • To optimize the browse operations, apply HStatCalculate to the data file before creating the filter. Indeed, the HFSQL engine analyzes the selection condition and it uses these statistics to define the most discriminating items that will be used to optimize the browse performed on the data file.
  • This selection condition can be a character string in Ansi or Unicode format.

Enabling/Disabling a filter

HDeactivateFilter is used to disable a filter.
HActivateFilter is used to re-enable a filter.
The filter is deleted when the data file (query or view) is closed (HClose for example).
A single filter can exist at a given time on a data file (query or view). If HFilter is used several times, only the last filter will be taken into account: the previous filters will be deleted.
WINDEVWEBDEV - Server codeiPhone/iPadWindows MobileUniversal Windows 10 AppJavaPHPAjaxUser code (UMC)HFSQLHFSQL Client/ServerStored procedures

Filter on a composite key

Several methods can be used to implement a filter on a composite key:
  • Method 1: Using a list of values in HFilter.
  • Method 2: Using the FOR EACH statement.
  • Method 3: Using HBuildKeyValue.
  • Method 4: Using HConvertX (method kept for backward compatibility with WINDEV 5.5)
See Composite keys and filters for more details.
Example for using the method 1: Using a list of values
The value of the composite key for which the filter must be implemented can be directly built in HFilter with the following syntax:
HFilter(<File Name>, <Name of Composite Key>,
[<List of Values for the Lower Bound of Composite Key>],
[<List of Values for the Upper Bound of Composite Key>])
Example: Find all the records in the CUSTOMER file whose last name is found between "AA" and "Barnaby" and whose first name is found between "Philomene" and "Tartuffe".
HFilter(Customer, LastNameFirstName, ["AA", "Philomene"], ["Barnaby", "Tartuffe"])
In this example, "AA","Zorro" is returned by the filter while "Philomene","Zorro" is not returned.
Example: Find all the records in the Tasks file whose tasks are included between 15/03/2011 00:00 and 25/03/2011 00:00.
HFilter(Tasks, TaskStarDateTaskStartTime, [20110315,0000], [20110325,0000])
WINDEVWEBDEV - Server codeReports and QueriesAndroidAndroid Widget Windows MobileJavaPHPAjaxUser code (UMC)Stored proceduresOLE DBNative Accesses

Data files not in HFSQL format

Components
WINDEVWEBDEV - Server codeReports and Queries wd230hf.dll
Windows Mobile wp230hf.dll
Java wd230java.jar
Linux wd230hf.so
Android wd230android.jar
Minimum required version
  • Version 9
This page is also available for…
Comments
 CADASTRO DE USUARIOS
VAMOS CONTINUAR CURSO ERP, COM CADASTRO DE USUARIOS


HFilter(usuario,Usuario_grupoID,usuario_grupo.usuario_grupoID)

// COM HFILTER ESTAMOS FILTRANDO OS USUARIOS SOMENTE DE 1 GRUPO
// WITH HFILTER WE ARE FILTERING USERS ONLY FROM 1 GROUP
// CON HFILTER ESTAMOS FILTRANDO LOS USUARIOS SOLAMENTE DE 1 GRUPO


TableDisplay(TABLE_usuario,taInit)

// ESTAMOS ATUALIZANDO A TABELA
// WE ARE UPDATING THE TABLE
// ESTAMOS ACTUALIZANDO LA TABLA


// BLOG COM VIDE E EXEMPLO

http://windevdesenvolvimento.blogspot.com.br/2017/07/aula-1200-windev-curso-erp-040-usuarios.html

https://www.youtube.com/watch?v=0-5SQfPfhXk

De matos
Jul. 08 2017
Exemplo hfilter
hfilter(cliente,cliente.id_empresa_cliente,edT_cliente_inicial + hMinVal,edt_cliente_final + hMaxVal)
tabledisplay(table_cliente,taStart)
HdeactivaterFilter(cliente)

// Blog Com video e exemplo

http://windevdesenvolvimento.blogspot.com.br/2016/11/aula-968-windev-tabela-035-hfilter.html

https://www.youtube.com/watch?v=ED9un1QMK-U

De matos
Dec. 04 2016