SUBQUERY

Use Subquery to refine selection criteria when populating a table window.

The fields on this screen perform the same functions as their corresponding fields in the Query screen.

Use this feature whenever you receive the message that the number of rows for a table window exceeds the Maximum Table Rows setting in the System Settings screen in Costpoint System Administration. This feature is often automatically activated when such a situation arises.

Note: In inquiry functions, the Subquery performs a search of the database table using the data entered on the inquiry form's data fields in conjunction with the search criteria entered on the Subquery. To have the Subquery search limited to the search criteria entered in the Subquery, either select Subquery before entering data on the inquiry's form, or select New and then Subquery.

Selection Criteria

Field

The fields of the table window are listed in this drop-down box. Choose the field you wish to use as a selection criterion.

Relation

Use this drop-down box to define a relationship between the Field and Value fields. The relations available are:

Is Equal to

Is Greater than

Is Greater than or equal to

Is Less than

Is Less than or equal to

Is Not Equal to

Is Blank

Is Not Blank

Begins With

Ends With

Contains

Value

Value is used with Field and Relation to define the rows to be selected from the database. Enter the value to which you want to limit your search.

For example, to select all accounts less than 02000 in the Maintain Accounts screen, choose:

Field:                     Account

Relation:                Is Less than

Value:                    02000

Another example would be to select all active accounts.

Field:                     Active

Relation:                Is Equal to

Value:                    Y

Yet in another example, to retrieve all accounts beginning with 02, you would enter: 

Field:                     Account

Relation:                Begins With

Value:                    02

Case Sensitive

Select this checkbox if you want the search to look for exact upper- and lower-case matches for string fields. If you leave the checkbox unchecked, all occurrences of the search value will be selected, regardless of case.

Combine Method

Use these options to make more complex selection criteria statements. First, select the Field, Relation, and Value for the first part of the statement. Then select the Add pushbutton. You will see your new statement in the Current list box. Then, change Field, Relation, and Value to reflect the second portion of the statement. Select either the And or Or radio button.

AND

Select this radio button when both parts of the statement must be true for selection to take place.

OR

Select this radio button when either statement being true will cause a record to be selected.

Use the Add pushbutton to add the new statement to the Current list box.

Current

This box shows the statements as you create them. The highlighted row shows the statement that you will modify if you select the Replace pushbutton or that you will remove if you select the Delete pushbutton.

Add

Use this pushbutton to create more complex statements. For example, if you want to see all accounts greater than 02000 but less than 05000, you would first create the statement:

Account Is Greater than 02000

Select the And radio button in the Combine Method group box. Then change the Relation and Value fields to create the statement:

Account Is Less than 05000

Select the Add pushbutton. This will give you the following statement:

Account Is Greater than 02000

And Account Is Less than 05000

Replace

Once you have selected the desired values in Field, Relation, Value, and the Combine Method group box, select this pushbutton to enter the statement into the Current list box. The statement that you had already highlighted will be modified to reflect the new selection.

Delete

Use this pushbutton to remove the highlighted statement from the Current field.

Sort By

The columns of the table window are shown in the drop-down box. This field affects the order in which the rows of the result set are accessed when using the Previous, Next, First, and Last buttons on the toolbar.

Choose the one field on which the result set should be sorted. Then select either the Ascending or Descending radio button.

Number of Rows

Select the Count pushbutton and you will be told how many database rows satisfy the current selection criteria.

OK

Select this pushbutton when you are satisfied with your selection criteria. The Subquery screen will close and the previous screen will reappear, populated with the data from your Subquery selection.

Cancel

Select this pushbutton to cancel the Subquery selection criteria you have entered and close the Subquery screen. No data will be returned by the Subquery to you previous screen.

Hint for WAN Users:  If the Connects via WAN checkbox in the Maintain Users screen in Costpoint System Administration is selected, the normal Costpoint "segmented ID behavior" will be turned off for all segmented ID fields when using Subquery. This means that when you enter one of the segmented IDs, the segment separators (periods or dashes) will not automatically appear, the individual segment IDs will not be validated, and each segment will not be padded to the correct length for that segment ID. Instead, the entire field ID is treated as a single string.