Skip to main content

GEN-07 Using Query by Example (QBE) Feature

Purpose

The purpose of this Quick Reference Guide (QRG) is to provide guidance on how to use the following North Carolina Financial System (NCFS) features: 

  1. Query By Example (QBE), including available operators and wildcards.
  2. Add or remove columns from table data.
  3. Export table data to Microsoft Excel.

Introduction and Overview

This QRG covers the various operators and wildcards used in the NCFS QBE feature.

They are used to select or filter data values whose content satisfies or matches the combination of operators, wildcards, number values, alphanumeric text, or dates that are entered in the QBE fields.

The QBE feature is frequently available within the Search Results section of a page (for example, the Manage Journals page).

The QBE icon graphic (on the tool bar) is represented as a funnel or filter.

Perform these steps to use the QBE feature and export the matched records to Microsoft Excel:

  1. Ensure the QBE feature is enabled.
  2. Enter the desired filtering operators, wildcards, alphanumeric values, or dates into the corresponding QBE fields, then press the Enter key.
  3. Add or remove columns from the matched records in the Search Results table, as desired.
  4. Sort records in the Search Results table, as desired (optional).
  5. Export the table of records to Microsoft Excel, if desired.

Enabling or Disabling QBE Feature

Perform either of the following to enable the QBE feature. There are 2 steps to complete this process.

Step 1.    If the row of QBE fields isn't displayed, click the QBE icon on the table toolbar.

Step 2.    Use the drop-down menu View > Query by Example to have empty (QBE) fields appear above each column name of the Search Results table.
 

Perform either of the following actions to disable the QBE feature. There are 2 steps to complete this process.

Step 1.    Click the QBE icon on the toolbar to remove the QBE fields.

Step 2.    Use the drop-down menu View > Query by Example to disable (uncheck) the QBE feature if the QBE fields are currently present.

Basic Use of QBE Feature

The following screenshot shows the QBE feature enabled. There are 2 steps to complete this process.

Step 1.    Enter values and any desired operators or wildcards in any of the QBE fields.

Step 2.    Press the Enter key on your keyboard to apply the filter criteria.

QBE Operators Listing – with Examples

The useable operators for QBE fields are the following. There are 2 steps to complete this process.

Step 1.    If only a value is entered, then the operator is interpreted as follows. 

If only a value is entered in an alphanumeric QBE field, then QBE will return the results for “starts with (value)”.

If only a number or a date are entered in a number or date field, then QBE will return the results for “equal to (the number or date)”.

Note: QBE fields are NOT case-sensitive. For example, “APPLE”, “Apple”, and “apple” would all function the same in an alphanumeric QBE field.

Step 2.    > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to).

Note: There is an additional consideration when using (>, <, >=, <=). A space character “ “ is considered part of the filter value. For example, if you enter >= def (with a space before the “d”) then “def” and “deg” must be preceded by a space character for this text to match the QBE filter.

Step 3.    The “and” operator can only be used in combination with other operators, for example: >0 and <=100.

Step 4.    The “or” operator is also used in combination with other operators.

Step 5.    QBE wildcards can be used anywhere within the entered value - at the beginning, middle, or end. QBE will ignore ALL other symbols, such as $#@&()+). The wildcard characters useable in QBE fields are the following:

a.    * or % (Multiple characters, including zero additional characters).

For example, if “buy*” or “buy%” is entered in a QBE alphanumeric field, the match results will be the word “buy” and any words beginning with “buy”, such as “buyer”.

b.    _ (Single character) Underscore.

For example, if “r_ck” is entered in a QBE character field, the match results will be words like “rock”, “rack”, “ruck”, “reck”, and “rick”.

Using QBE on Alphanumeric Fields Versus Date Fields

The data type that a QBE field filters determines what operators and wildcards can be used in that field. There are 2 steps to complete this process.

Step 1.    Operators and wildcards only work in QBE alphanumeric fields. They do not work in QBE date fields.

Step 2.    QBE alphanumeric fields filter based on alphanumeric sort. For example, if you enter >=def, then “def”, “deg”, “defa”, “dfe”, and “edf” are matches, but not “de1” or “de”.

Refreshing QBE Results

If you want to refresh your QBE filtered results, perform any of the following actions. There are 3 steps to complete this process.

Step 1.    With your cursor in any of the QBE fields, press the Enter key again.

Step 2.    If available, click the Search or Refresh buttons on the toolbar.

Step 3.    If available, using the drop-down menu, select View> Refresh.

Note: Any QBE-filtered column, even if hidden, will still apply to the table data.

Clearing QBE Results

To clear the QBE filter, perform either of the following actions. There are 2 steps to complete this process.

Step 1.    If available, click the Clear All icon at the beginning of the QBE row.

Step 2.    If not, then follow these steps:

  1. Delete any entered characters present in each QBE field.
  2. Refresh the table.

Adding and Removing Columns

Use the drop-down menu View > Columns to add or remove columns shown in the table.

  • Checkmark = include
  • No checkmark = exclude

Note: Please exercise caution when selecting the “Show All” choice in the drop-down menu View > Columns > Show All. If you select the “Show All” choice, you will then need to de-select each unwanted column, one by one.

Exporting to Excel

To limit the table data being exported to Microsoft Excel, consider the following:

  1.  Apply search criteria or QBE filter values.
  2. Hide table columns.
  3. If the table is limited to a certain number of rows per page, only the data on that page will be exported. Please go to any additional pages to export the additional rows to Excel.

When you are satisfied with table data, you may export the table data to Microsoft Excel using the Export to Excel icon on tool bar.

The data included when table data is exported to Microsoft Excel is:

1.    Any rows selected (highlighted) in the table data.

2.    If no rows are selected or row selection is disabled, all rows will be exported, including the rows currently not visible on the NCFS page.

Wrap-Up

The above content provides guidance around how to use the following NCFS features: 

  1.  Query By Example (QBE), including all usable operators and wildcards.
  2. Adding or removing columns from a table.
  3. Exporting table data from NCFS to Microsoft Excel.

Additional Resources

First Published

Last Updated