DBFILTERSORT(database, table, filter, sort, field_name, rec_num)

The DBFILTERSORT function returns the contents of a field from a database table after a specified named filter and sort order have been applied.

This requires that the filter and sort order have already been defined in Database. This contrasts with DBSQLFILTERSORT (see DBSQLFILTERSORT), which is similar in functionality but allows you to define simple filters and sort orders "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.

sort is the named sort order 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

A sort order called ByPrice has been created that sorts in ascending order the price field.

DBFILTERSORT can be used to obtain the following results:

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

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

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

returns " Chambolle-Musigny", as this is the 3rd cheapest wine.

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

returns the price of the second cheapest wine, 27.

See also:

Other remote functions