Filtering, sorting, and viewing tables

You can filter data in a table to display only certain records. You can filter using the primary index created when you add a key to the table, or you can create and use secondary indexes to filter the data in different ways. You can also set conditions and ranges in table filters, to further narrow the range of values displayed.

You can also sort data in the table to change the way it is displayed, and you can view table data in an alternate format; for example, as a form.

In this section, you'll learn about

  applying and removing filters
  
  defining and deleting secondary indexes
  
  setting conditions in table filters
  
  specifying a filter range
  
  sorting tables
  
  viewing table data
  
For more information about filtering, sorting, and viewing tables, see "Reference: Filtering, sorting, and viewing tables."

Applying and removing filters

You can apply a filter to a table to display only the data you want to see. You can filter an entire table or a field. When you no longer want the filter, you can remove it.

Filters are similar to queries because most of the operators that work in a query can also be used in a filter. However, there are differences in the types of expressions you can use. For example, the ".." wildcard operator is not allowed in numeric or date fields in filters. Furthermore, this operator is allowed after the filter condition, but not before it. For example, you can type "view.." to filter for all values that contain the letters "view" (either upper or lowercase) followed by any other letters. This filter returns the values "View," "Viewing," "viewed," "viewable," and so on. Using the ".." wildcard is the only way to define a case-insensitive filter. For more information about operators, see "Setting operators and conditions in filters".

In addition, example elements are not used in filters; however, you can refer to one field from another. Calculated fields are not used in filters, neither are summary expressions, such as COUNT>5. Moreover, you cannot use memo, formatted memo, OLE, graphic, binary, and bytes, or BLOB fields in filters.

In filters, parentheses can be used to nest conditions. Checks are not used in filters, and records are displayed as if a CheckPlus is set on every field. In addition, filters cannot have multiple lines. The equivalent to multiple lines is the OR keyword.

The operator precedence is slightly different from queries. In addition, the comma functions as the AND operator for top-level conditions such as >200, <300. However, in a filter, the AND operator must be used instead of a comma to express more complicated conditions such as (>100 AND <200) OR (>300 AND <500). The general rule is that you must use AND when the condition is within parentheses. The LIKE, AS, and SET operators are not used in filters, but the TODAY and BLANK operators are allowed. The % operator, which is equivalent to mod, is supported in filters but not in queries. This operator only works on SQL tables.

To apply a filter to a table

1	Open a table.

2	Click Format  Filter.

3	Enable the Order by check box to order records according to the selected index.

4	Choose an index from the list.

If the table is keyed or has secondary indexes defined, you can choose which index Paradox uses to sort.

5	In the Filters on fields area, type the conditions for the records you want to see in the field's text box.

Notes

  When you view the indexes in the Index list, the primary index is identified with an asterisk (*).
  
  To specify a case-insensitive as opposed to a case-sensitive view order, you must first define the index with the specifications you want.
  
To filter a table field

1	Open a table.

2	Right-click a field, and click Filter.

3	Type a value in the Filter for box.

To remove a table filter

1	Open a table.

2	Click Format  Filter.

3	In the Filters on fields area, delete the filter condition text from the boxes.

To remove a table field filter

1	Right-click a field, and click Filter.

2	Delete the text in the Filter for box.

Defining and deleting secondary indexes

Paradox lets you define and delete secondary indexes. An index is a file that determines the order in which the records in a table are accessed and the way tables are linked. When you add a key to a table, you are creating a primary index. The records of the keyed table are then organized according to the values in the key field(s). Unlike primary indexes that are defined while creating the structure of a table, secondary indexes are optional; however, they can help speed up queries and display data with a different sort order.

A secondary index is a field or group of fields that you define as one or more sort orders for the table. It is also a field to which you can link tables. For example, you can link the sample Customer and Orders tables. The Orders table has a secondary index identified on its Customer number field. Adding a secondary index speeds up the process of searching for all the records with a given Customer number value.

A table can have more than one secondary index. In fact, you can identify each field of the table as a secondary index, which permits you to sort the table on any of its fields.

You can define a secondary index on any field or group of fields in a table except memo, formatted memo, binary, OLE, graphic, logical, or bytes fields.

When you use a secondary index, you only change the view order of the records. The physical location of the records in the table does not change, because the primary index or key is what determines the physical sort order for the table.

You can also create a composite secondary index by combining two or more fields. A composite secondary index is composed of a group of fields rather than a single field. When a composite secondary index is created, the records in the table are sorted based on the first field in the key and then on subsequent fields in the order in which they display in the table.

Composite secondary indexes can have up to 16 fields. If you are currently sorting on a secondary index and wish to change the sort order to a different field, you must change the secondary index. You may need to change the secondary index to establish new table links and speed up the search and replace operations. You can also delete a secondary index if you no longer need it.

To define a secondary index

1	Open a table.

2	Click Format  Restructure table.

3	Click the Secondary index tab.

Paradox displays any existing secondary indexes and each field you can use as a secondary index.

4	Click Add.

5	Type a name in the Index names list.

To define a composite secondary index, click Add and type more than one name to add additional fields.

6	Choose the field on which you want to define the secondary index from the Fields list.

7	Click the right arrow to move the field into the Selected index fields list.

If the default sort order is not appropriate, choose a sort order from the Sort list box.

8	Enable any of the following check boxes:

  Case-sensitive-specifies the use of capitalization, or case, as a criterion for sorting. Paradox sorts uppercase letters before lowercase letters.
  
  Maintained-updates the index automatically whenever you update the table. A table must have a key before you can create a maintained secondary index.
  
  Unique-determines whether records can have duplicate values in the secondary index field or fields. If two or more records have the same value in the secondary index field, the attempt to define the secondary index fails. You have to eliminate duplicate values before defining the secondary index.
  
Notes

  When choosing index options, you must first enable the Maintained check box before you can enable the Unique check box.
  
  When you create a secondary index based on a single field, the field name is used to identify the secondary index. Therefore, you cannot assign a composite secondary index the same name as any of the fields in the table. If you change the options of the secondary index, so it is not a Paradox Native Index, you can define a custom name.
  
Tip

  A non-maintained index is not automatically updated when you update the table, but you can open a non-maintained index for use on a table if the index is synchronized with the table. Click Format  Filter once you open the appropriate table to specify the index you want to use while working with the table. When you view a table with a non-maintained index, the table is temporarily locked and cannot be updated. The table is also locked if you use the ObjectPAL methods setIndex() or switchIndex().
  
To delete a secondary index

1	Open a table.

2	Click Format  Restructure table.

3	Click the Secondary index tab.

4	Choose the secondary index from the Index names list.

5	Click Remove.

6	Click Save.

Setting conditions in table filters

When filtering a table, you can expand your filtering capabilities by setting conditions. For example, suppose you have a customer table and you want to see customers that are either in Florida or the Bahamas. If you specify Bahamas as the country and Florida as the State/Prov., no records display in filtered view because you do not have any records that meet that condition. A customer cannot live in both Florida and the Bahamas simultaneously. However, you can search for customers that live in Florida or the Bahamas if you specify OR conditions across fields. You can do this by referring to one field from the text box of another field; for example, by specifying FL or Country=Bahamas as the State/Prov..

For a list of the operators and conditions you can apply to a filter, see "Setting operators and conditions in filters".

To set table filter conditions

1	Open a table.

2	Click Format  Filter.

3	In the Filters on fields area, type the either/or conditions in the field's box.

Notes

  When referring to a field name that contains a space or special characters, you must enclose the field name in brackets. For example, the condition City = Venice or [Customer No.] = 1560 is valid, but the condition City = Venice or Customer No. = 1560 is not. Furthermore, the condition [Sale%] < 20 is valid, but the condition Sale% < 20 is not. For more information about filtering using special characters, see "Filtering on special characters."
  
  It doesn't matter which text box you use to specify filter conditions. You could type Bahamas or [State/Prov.] = FL in the Country field. You could even type [State/Prov.] = FL or Country = Bahamas in the Name text box or any other text box.
  
Specifying a filter range

When filtering a table, you can specify a range of values based on the field(s) defined in the primary (key) or secondary index. For example, suppose you have a customer table that contains 500 customers and each customer has a unique customer identification number. In this case, the unique customer identification is also the primary key. When you set a filter range, you can filter the table using the customer identification and view a range of customers between 200 and 300.

You can also choose a filter range based on a composite index. If you choose a composite index, you can set ranges for multiple fields within the index. When setting ranges on a composite index, you must select a single continuous set of records on the chosen index. You do not have to specify a range for every field of the index, but you cannot skip over a field.

In this example, the filter range will display all records with Order No. 1000.00 to 1020.00:

To specify a range when filtering table data

1	Open a table.

2	Click Format  Filter.

3	Enable the Order by check box to order records according to the selected index.

4	Choose an index from the list.

If the table is keyed or has secondary indexes defined, you can choose which index Paradox uses to sort. An asterisk (*) precedes the primary index. If the table is keyed or has secondary indexes defined, you can choose which index Paradox uses to sort.

5	In the Filters on fields area, type the conditions for the records you want to see in the field's text box.

6	Click Range.

7	In the Set range for index dialog box, enable the Set range check box.

8	In the Field value box, type the starting value of the range you want to find.

9	Type ending values in the End of range box.

Note

  If you enable the Set range check box without first clicking in a text box, you automatically choose the last field for which you have specified a value as the field for which you want to define the range of values.
  
To specify a range on a composite index

1	Open a table.

2	Click Format  Filter.

3	Choose the composite index by which you want to filter the table from the Index list.

4	Click Range.

5	Type a value in the Field values box of each field.

If you need to set a range, enable the Set range check box. You can specify exact matches and range matches on the same composite index, but you can use a range match only on the last of the fields you define a match for.

You can also

Set an exact match on the first and second fields, and a range match on the third

Set an exact match on the first field, a range match on the second, and leave the third blank

Set a range match on the first field, and no range on the second or third

Set a range match on the first field and an exact match on the second or third

Note

  You cannot use a composite index on a dBASE table to set a range. You can, however, use an expression index. You can set an exact match, inexact match, or partial range on an expression index. For more information, see "Creating an index on a dBASE table.".
  
Sorting tables

With Paradox, you can sort tables in different ways to display data according to the criteria you specify.

You can sort on a table that is keyed or not keyed. When a table is keyed, the key determines the physical location of the records in the table. Paradox provides you with two ways to sort keyed tables. You can create a maintained secondary index, which displays records based on different sort criteria without changing the physical location of the records in the table. However, to change the physical location of the records in the table, you can sort the keyed table to a new unkeyed table; the original table remains unchanged. If a table is not keyed, records display in the table in the order in which you entered them. When you sort an unkeyed table, you change the actual location of the records in the table. You can sort an unkeyed table to itself, or create a new sorted table, leaving the original intact. For dBASE tables, the default order for records is chronological; for Paradox tables, it depends on the position in which they are entered.

You can also sort an answer table from a query. For information about sorting an answer table, see "To sort the Answer table" procedure.

When you sort tables in a multi-user environment, a lock is automatically placed on the table you are sorting and other users cannot modify its contents or structure. If another user has a lock on the table, you will not be able to begin sorting until that user finishes working with it. When you sort to a new table, a lock is automatically placed on that table and on the original table for the duration of the sort.

Paradox cannot sort on BLOB, BCD, logical, or bytes fields in Paradox tables and Memo, binary, OLE, or logical fields in dBASE tables.

To sort a table

1	Open a table.

2	Click Format  Sort.

3	In the New table box, type a name for the new table.

If the table is not keyed, enable the Use existing table check box to sort the table based on the new sort criteria, or type a name for the new table in the New table box. Paradox will create a new table based on the sort criteria without changing the original table.

4	Choose a field from the Fields list.

5	Click the Right arrow to place the field in the Sort order list.

6	Click Sort direction to define whether you want to sort in ascending (+) or descending (-) order.



You can also	

Change the sort order of the fields in the sort order list	Click the Change order Up or Down arrow.

Sort only on the fields listed in the Sort order box	Enable the Sort just selected fields check box.

Automatically display the sorted table	Enable the Display sorted table check box.

Notes

  You do not have to put all the fields from the Fields list in the Sort order list. Fields you do not explicitly put in the Sort order list are added to the end of that list before performing the sort, unless you have enabled the Sort just selected fields check box.
  
  If you enable the Sort just selected fields check box, you must place at least one field in the Sort order list. When you enable the Sort just selected fields check box, you are only sorting on the fields that display in the Sort order list. All the fields of the source table are included in the resulting sorted table, but they are not sorted beyond the listed fields. If two or more records have identical values in these fields, you cannot sort the records. They are placed in the table as a group, but unsorted within the group.
  
  You cannot sort SQL tables.
  
Viewing table data

You can view a table's data in a form, chart, crosstab, or report. When you view the table's data in an alternate format, such as a form or report, the property settings you have chosen for the table do not display.

To view table data in a quick form

  Click Tools  Quick design  Quick form.
  
To view table data in a quick chart

  Click Tools  Quick design  Quick chart.
  
If you have not yet defined a preferred chart, the Define chart dialog box opens.

To view table data in a quick crosstab

  Click Tools  Quick design  Quick crosstab.
  
If you have not yet defined a preferred crosstab, the Define crosstab dialog box opens.

To view table data in a quick report

  Click Tools  Quick design  Quick report.
  
Reference: Filtering, sorting, and viewing tables

This section contains additional information about filtering, sorting, and viewing tables.

You'll learn about:

  setting operators and conditions in filters
  
  filtering on special characters
  
Setting operators and conditions in filters

Operators are symbols that represent an operation to be performed on a value or values. You can also use either/or conditions to filter table data. Paradox evaluates operators in filters in a certain order. In expressions containing more than one operator, the operators are evaluated in the order of precedence shown in the following table.



Precedence	Operator

1	( ) [ ] " "

2	* / %

3	+ -

4	= <> < <= > >=

5	NOT

6	AND

7	OR

8	, (comma)

Notes

  Any expression contained in parentheses is evaluated first, and inner levels of parentheses are evaluated before outer levels. When two or more operators of equal precedence are in a single expression, they are evaluated from left to right.
  
  Filters cannot be applied to detail tables if the tables are linked in a single-value relationship.
  
Filtering on special characters

If you are filtering on special characters, you must precede the number or literal value that can be interpreted as an operator, such as ", /, \, -, +, =, with a backslash(\). In the OPAL method setgenfilter(), the filter criteria is put into a string and parsed to pick out numbers and operators for calculations. If the number or operator in the filter needs to be interpreted literally, it needs to be preceded by a backslash(\).

For example, to filter a table with the following records:

  1st Base
  
  2nd Base
  
  3rd Base
  
and retrieve only those that start with "1st," the filter would be this:

filter = "\1st.."

One backslash to indicate that number 1 is not a special character.

Note

  You can only specify a range of values based on the field(s) defined in the primary (key) or secondary index you selected. If you choose a composite index or key, you can set ranges for multiple fields within the index. For more information, see "To specify a range on a composite index."
  



