DBFILTER(database, table, filter, field_name, rec_num)

The DBFILTER function returns the contents of a field from a database table, after a specified named filter has been applied.

This requires that the filter has already been defined in Database. This contrasts with DBSQLFILTER (see DBSQLFILTER), which is similar in functionality but allows you to define simple filters "on-the-fly".

database is the name of a database and must include the full path if not in the same directory as the open document.

table is the name of the table within the database. table could also be the name of a relation.

filter is the named filter that has been applied to the table or relation.

field_name is optional and can be either the name of the field within a table or relation or the column number. If omitted, every row and column of the filtered table is returned.

rec_num is optional (and cannot be specified if field_name is omitted) and determines the record or row number of the returned value. If omitted, the entire column is returned (or table if field_name is also omitted).

The function arguments are case insensitive.

For example, a database called wineshop, located in a folder c:\ability\samples, contains a Product table of wines. A filter called Red has been defined that filters out all wines other than red. The table contains the name of each wine in a Title field and the price in a field called Price as follows:

Title

Price

Classification

Château Haut du Puy

17.00

red

Volnay-Santenots

27.00

red

Le Chambertin

53.00

red

Chambolle-Musigny

28.50

red

DBFILTER can be used to obtain the following results:

DBFILTER("c:\ability\samples\wineshop.adb", "products", "red")

returns "Château Haut du Puy" as this is the first column and row off the filtered table.

DBFILTER("c:\ability\samples\wineshop.adb", "products", "red", 1, 3)

returns "Le Chambertin", as this is the value from 3rd row and 1st column.

DBFILTER("c:\ability\samples\wineshop.adb", "products", "red", "price", 2)

returns 27, the 2nd record of the Price column.

AVG(DBFILTER("c:\ability\samples\wineshop.adb", "products", "red", "price"))

returns 31.375 - the average price of red wine.

ROWS(DBFILTER("c:\ability\samples\wineshop.adb", "products", "red"))

returns 12, the total number of records in the filtered table.

See also:

Other remote functions