Specifying Dataspy criteria

When you create, edit, or copy a Dataspy, you need to specify filter, sort, layout, and advanced criteria. When the Dataspy is applied to a list, only records that match the specified criteria are found, and they are displayed according to the specified sort order and layout.

To specify filter criteria:

  1. Open any list view page, select a Dataspy in the Dataspy drop-down list, and then click Edit .
  2. With Filter selected in the left pane, go to the first field and select a field on which to filter data.
  3. In the second field, select an operator.
  4. In the third field, specify a value for which to filter.
    Note:  Enter relative dates by specifying + or - with units in days. For example,"-1" is one day back and >="-7" is last week.
  5. To select a join operator, click the AND/OR hyperlink. Following are the available values:
    • Specify AND, to include records that contain all joined conditions
    • Specify OR, to include records that contain one or the other condition
  6. To add parentheses to a selected row, click the Left and Right parenthesis buttons. Parentheses are used to group statements together when mixing AND and OR statements.
  7. Specify additional filter criteria. To add additional lines to the filter, click Add Line.
    Note: To remove a filter line, choose the blank selection in the first drop-down list.

See the example below to specify the sort criteria:

  1. In the left pane, click Sort.
  2. In the first drop-down list, specify the field by which to sort the records and then click the sort ascending or sort descending button to set sort order.
  3. Repeat for additional fields as necessary.
Note: To remove sort criteria, choose the blank selection in the drop-down lists.

See the example below to specify layout criteria:

  1. In the left pane, click Layout.
  2. Move the fields from the Available Fields list to the Visible Fields list as described below:
    Function Procedure
    Add a field Select a field in Available Fields and then click the right arrow (>).
    Remove a field Select a field in Visible Fields and then click the left arrow (<).
    Add all fields Click the double right arrow (>>).
    Remove all fields Click the double left arrow (<<).
    Reorder fields Select a field in Visible Fields and then click the up or down arrow to move the field.
  3. Specify the number of rows to retrieve from the database before downloading another set of rows.

You can specify the number of records to retrieve before another set of records is retrieved from the database in a list view. This setting does not affect the height of a table (the number of rows shown without scrolling the table); however, this setting is important because it affects the download and display speed for a list containing a large number of records.

For example, if the value is set at 100 (the default), the system downloads only the first 100 records that match the criteria specified in the selected Dataspy. When you scroll to the end of the 100 records, the next 100 matching records are displayed. If the value is set to 300, then no records are shown until all 300 records are retrieved.

See the example below to specify advanced criteria:

  1. In the left pane, click Advanced.
  2. Specify an additional Where clause as necessary. The Where clause is appended to the Where clause generated by the Dataspy filter. The Where clause is appended with an AND statement if a filter has been specified.
  3. Click Test Where Clause. If the SQL statement compiles without error, a confirmation message is displayed. You can also click Display SQL Statement to view the entire SQL statement. The SQL Statement field is read-only.