4 Queries

This chapter contains the following topics:

4.1 Understanding the Query Control

In addition to using QBE and wildcards to search for records, you can define additional search criteria by creating a query. The Query control appears on Find Browse, Search/Select, and Power Browse forms that have a Find button, unless the form appears in a popup window (such as when using the visual assist). Additionally, the Query control appears in the Data Browser, which enables you to view the data in tables and business views and save the search criteria as a query.

See Viewing the Data in Tables and Business Views in the JD Edwards EnterpriseOne Tools Foundation Guide guide.

Note:

The queries created using the Query control are Enhanced Queries, which differ from the Saved Queries that users might have created in previous releases. You can convert the format of the old Saved Queries to Enhanced Queries by using a conversion process.

See "Converting Saved Queries to Enhanced Queries" in the JD Edwards EnterpriseOne Tools Runtime Administration Guide

The query feature enables you to select additional fields from a form and add conditions to narrow the search results. Queries enable you to create searches that are more specific than the search results from wildcards and QBE columns alone. The system combines the conditions defined in the form filter fields, the QBE line, and the query to retrieve records from the database.

Security options are available to prevent users from performing searches if they have not entered search criteria in the form filters or QBE columns. If application query security has been implemented, you receive an error or warning message that informs you that your search has been suppressed.

See "Managing Application Query Security" in the JD Edwards EnterpriseOne Tools Security Administration Guide

You can design and save queries for search criteria that you use often to find records. Queries can be designated to run automatically by selecting the "Run query when selected" option, which enables the query to search without having to click the Find button.

Additionally, you can designate a saved query as the default query for a form. A default query with the "Run query when selected" option will run whenever you enter the form, as well as any time you select the query from the drop-down query list. If a default query has not been defined, the Query field displays "All Records" when the form appears.

Note:

A default query will not run automatically if values are passed into the form by a form interconnection, or if the "Run query when selected" option is not selected.

If you want to search for records without using a query, set the Query field to All Records.

Any queries that you save are listed alphabetically under the My Queries label in the query drop-down list. The My Queries subgroup also includes an All Records selection, which enables you to search without queries. Any queries that you save are available to you only; no other user can access them unless they are copied and designated as public queries. If public queries are available, they are listed alphabetically under the Public Query subgroup in the query drop-down list.

See "Making Private Queries Public" in the JD Edwards EnterpriseOne Tools Runtime Administration Guide

4.2 Managing Queries

You use Query Manager to create, modify, and save queries. Click the icon to the right of the Query field to enter Query Manager. You can also use the Ctrl+E hot key combination to toggle between normal mode and Query Manager mode. Depending on the value in the Query field when you enter Query Manager, the options for a new query or an existing query appear in the Query Manager side panel. You can change the width of the Query Manager side panel, which causes a corresponding adjustment in the JD Edwards EnterpriseOne form until the minimal width is reached.

The icons in the Query Manager side panel are available for all User Defined Objects (UDOs) and are discussed in the "UDO Life Cycle and Statuses" of the JD Edwards EnterpriseOne Tools Foundation Guide.

When you enter Query Manager, a plus sign on the form designates each field and QBE column that you can select for a query. When you click a field or QBE column, the item appears in the Query Manager side panel with the appropriate comparison selections for the item's data type. Any items that you select display an X in the Query Manager side panel, which indicates that they have been selected. You can remove an item from the Query Manager side panel by clicking it.

When you edit the criteria in a query, both the query item and its corresponding field or QBE column on the form are highlighted.

The Populate Query icon enables you to enter values in fields and QBE columns on the form, and then click the Populate Query button to automatically create the query for you. The system populates the comparison selections for each item according to its data type.

The following table describes the fields in the Query Manager side panel:

Query Manager Options

Field Description
Name Select either (add new query) or an existing query from the drop-down list. The default value for the Query field is (add new query), unless an existing query was selected before entering the Query Manager. The (add new query) selection enables you to create a new query. Select an existing query to run it or modify it.
Set As Default Select this option to designate a query as the default. Only one query can be the default query for a form. The default query is used whenever the form opens, until the user selects another query or the All Records selection.
Run When Selected By selecting this option, you do not have to click the Find button to run the query. The query will run automatically whenever you select it. If you select this option for a default query, the query will run automatically when the form opens, unless it opens in a pop-up window (such as when using the visual assist) or when values are passed into the form by a form interconnection.

Note: When creating a query used by Watchlists, ensure that the "Run When Selected" option is selected. When creating a query to be used by a an analytical component on a Composed Page, it is recommended that this option be turned off for better performance.

Clear Form When Selected Select this option to clear any header field values that are specified in the application version and automatically populated when the application version is launched.
Match All Select this option if you want the query results to include all of the criteria specified in the query.
Match Any Select this option if you want the query results to include any (not all) of the criteria specified in the query.
Fields and QBE Columns Form fields and QBE columns that display a plus sign can be included in a query. Click the plus sign of the field or QBE column to include it in a query.
Comparison List When you select a field or QBE column to include in a query, fields display to the right of the item in the Query Manager side panel that enable you to customize the search criteria for the item. The comparison types that display for each data item (such as equal, not equal, starts with, between, is blank, etc.) depend on the data type.
Set Special Value Some conditions enable you to set a value for comparison. For example, if you select a date field and the "between" comparison type, the Set Special Value option enables you to specify the dates to be compared.

Comparison Types

The following table describes the comparison types that are supported for each data type when it is included in the search criteria.

Data /Type Example Comparison Types
String Order Type (DCTO) Equal, not equal, starts with, ends with, contains, between, in list, is blank, is not blank
Character Type Code (TYC) =, !=, <, <=, >, >=, between, in list
Numeric Address Number (AN8) =, !=, <, <=, >, >=, between, in list
Date Order Date (TRDJ) =, !=, <, <=, >, >=, between, in list
JDEUtime Start UTime (TASUTIME) =, !=, <, <=, >, >=, between, in list

The "in list" comparison type enables you to create a list of values for a data item. When you select "in list" from the comparison drop-down list, the Add More Value icon appears. Click the icon to display another field in which to enter the next value. To remove a value from the list, delete the value in the value field. The field is removed unless it is the only value field in the list.

Special Values

Fields or QBE columns that are based on string or date data types enable you to specify additional search criteria by using the Set Special Value option. Click the down arrow to the right of the value field to display the Set Special Value pop-up window. The Set Special Value window provides additional search options for the field or QBE column. For example, you could specify search criteria for orders that are due 5 days from the current date.

To set special values, make the appropriate selections in the Set Special Value window and then click OK. To clear special values, click the Reset button. The following table describes the special value options that are available for each data type.

Data /Type Special Value Options Description
String Login User ID Some forms enable you to customize a query to return records with data related only to the user ID of the person who is logged in.
Date Today + or Today -

Value

Days, Months, or Years

Use special values to search for dates that are a specified number of days, months or years before or after the current date.

In the Set Special Value pop-up window, select + or -, enter a number, and then select days, months, or years from the drop-down list.


4.3 Creating and Saving Queries to Search for Data

After you create a new query, click Save on the Query Manager side panel. If you modified an existing personal query, you can click either the Save or Save As icon. Clicking Save updates the original personal query with the changes. Clicking Save As requires you to enter a new query name, so that you have the original query and a new personal query with the updates you made.

Access a form with the Query control.

  1. To create a new query, select All Records from the Query drop-down list if it does not appear as the query selection.

  2. Click the Manage Queries icon.

  3. In the Query Manager side panel, verify that (create) is the value in the Name field. If another value appears, select (create) from the drop-down list.

  4. If you want the new query to be the default query for the form, click the Set As Default option.

  5. If you want the query to run automatically whenever it is selected, click the "Run query when selected" option.

  6. Select either the Match Any or Match All option.

  7. Click the form fields or QBE columns that you want use as search criteria.

    Note:

    You can select only the form fields and QBE columns that have a plus sign.
  8. Select a comparison type for each field or QBE column.

  9. Specify the comparison value for each field or QBE column in the query.

    Note:

    The system displays an error if the value entered is not valid. Close the error dialog box and enter a valid value.
  10. For date or string data items, enter a value for comparison or select the down arrow to specify values in the Set Special Value pop-up window.

  11. Click the Find button in the Query Manager side panel or on the form to run the query.

  12. To save the query, click the Save Query icon in the Query Manager side panel and enter a name for the query. The new query name appears in the Query field.

  13. Close the Query Manager side panel.

    The new query name appears in the Query field on the form. To find records without using a query, you must reset the Query field to All Records.

4.4 Using Existing Queries to Search for Data

Access a form with the Query control.

  1. From the Query drop-down list, select the query that contains the search criteria that you want to apply.

  2. Click the Find button to display the search results.

    If the query includes the "Run query when selected" option, the Find happens as soon as the query is selected.

4.5 Copying Queries

Access a form with the Query control.

  1. From the Query field, select the query that you want to copy.

  2. Click the Manage Queries icon to display the search criteria.

  3. If desired, change the search criteria.

  4. In the Query Manager side panel, click the Save As icon.

  5. Enter a name for the query.

    The new query name appears in the Query field.

  6. Close the Query Manager side panel.

    The new query name appears in the Query field on the form. To find records without using a query, you must reset the Query field to All Records.

4.6 Modifying, Publishing, and Deleting Queries

For more information on these common UDO tasks, see the following topics:

4.7 Selecting Additional Fields

See Creating and Saving Queries to Search for Data.

You can select a comparison type in the Query Manager side panel for each field or query-by-example (QBE) column and specify the comparison value in the query. To do so, click the form fields or QBE columns that you want use as search criteria. You can select only the form fields and QBE columns that have a plus sign.

Alternatively, you can use the Additional Fields Selection section to select the form fields or QBE columns. This section displays only the business view (BSVW) columns that are included on the form including the hidden columns. For example, if BSVW includes the columns A, B and C; A is displayed on the form, B is hidden and C is not included, then the Additional Fields Selection section displays A and B columns.

4.8 Clearing the Form

You can clear the values that you have entered on the form. For example, the values entered in the header or QBE column.

To clear the values on the form, select Clear Form When Selected option in the Query Manager side panel to clear the values on the form automatically when the query is selected.