The Query Menu Bar contains all options associated with a query object.

These include:

Remove

Removes the current query from the current dataModule.

View Fields

Opens the List Fields page which displays a list of fields names and their data type.

View Indexes (Keys)

Opens the List of Indexes (Keys) page which displays a list of index names and their associated fields. The expression is displayed for dBASE Plus expression indexes.

Edit

Opens the Edit Row page, where a primary key field and primary key value will be specified to locate the row to edit.

Append

Opens the Append Row page, where a primary key field will be specified to ensure that the primary key value of the appended row is unique.

Delete

Opens the Delete Row page, where a primary key field and primary key value will be specified to locate the row to delete.

About Editing, Appending, and Deleting Rows

The primary key of a table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). To append, edit, or delete a row of a table, you must specify the field that serves as primary key for the table, and the value of the primary key that uniquely identifies that row. A field will be pre-selected as primary key if so indicated by the table's meta-data, or if you have already specified the primary key in this session.

To edit or append a row, complete the edit/append form, then click 'Submit'. Enter dates in 'MM/DD/YYYY' format. Enter logical values as 'true' or 'false'. Enter DateTime values in 'MM/DD/YYYY HH:MM:SS' format. The primary key field (highlighted in bold) may not be changed when in edit mode.

Query Options

The buttons in the query object under the Query Menu Bar.

SQL

The SQL button opens a page providing two ways to edit the SQL statement on which the current query object is based.

Option 1allows you to edit the SQL statement directly.

Option 2 is used to build an SQL statement using the SQL Statement Builder.

Step 1 of the SQL Statement Builder is used to build the fields list portion of the SQL statement. Move individual fields to the "Selected" listbox, or check "All Fields" to include all the table’s fields.

Step 2 is used to build the WHERE portion of the select statement. This is the optimal way to filter client-server data since only records matching the WHERE condition are returned by the server. Filtering in this manner will work for any table type.

The Expression Builder is used to build individual filter conditions by selecting values from several dropdown menus. Once a condition has been built, add it to the condition list by clicking the Add button on the right. The other dropdowns will update to show only those options valid for the data type of the currently selected field. For example, a date field will list choices for "Today", "This Week", etc.

Once one or more expressions are listed in the conditions list, you can set the expressions scope by choosing to meet "Any of the Conditions", "All of the Conditions", or "None of the Conditions".

Click "Build SQL Select Statement" to build the new expression.

Index (Key)

The Index button opens the "Select Index (Key)" page, which displays a listbox containing all index keys created for the current table. After selecting an index (select "Natural Order" when no index is to be used), apply it by clicking "Submit". The next time the data is viewed, it will appear in the order of the newly applied index. This is also the first step in creating an index based parent-child relationship between two tables (for dBASE/Paradox tables), since the active index of a child table is the index used when a parent-child relationship is created.

Child Of / Clear Link

The "Child Of" button opens the "Set Parent of" page, which allows you to create a relationship between the current query object and another query object. An indexed relationship will be created if your child table’s index is active, and the queries are based on local tables. If no index is active, or the tables are non-local, a parameterized query is created.

Parent/child relationships can be either one-to-one (lookups) or one-to-many. You can have any number of relationships between tables in the dataModule. The "Clear Link" button clears the relationship between the current query object and an existing parent query object.

Filter / Clear Filters

Since the WHERE clause of an SQL statement may return only a portion of the entire dataset, subsequent filters will be applied on only those rows returned. The Filter button opens the "Filter Rowset of Query Object" page, which provides three different options for applying filters on rows returned by the query’s SQL statement. These three filtering options are based on the different ways dBASE Plus can filter data. All three filtering options can be created manually using either SQL or dBL code.

Option 1: Set a SQL-Style Rowset Filter

This option uses the rowset’s filter property and accepts basic SQL filter statements. It is the easiest type of filter to set, but only supports basic expressions like field = value. The drop-downs in the expression builder show the types of statements valid for this type of filter.

Option 2: Set a dBASE Plus Indexed Rowset Filter

This option makes use of an active index, and is the fastest, albeit least flexible, way to filter local tables. This method allows for partial string matches that start from the beginning of the indexed field. Exact string matches are also supported as long as the complete search string is entered, and then the search string is padded with enough spaces to make up the entire field length. Ranges can also be set with this method, by entering starting and ending values separated with a comma. Some example filter expressions are provided.

Option 3: Set a dBASE Plus Non-Indexed

This is the most flexible of the three search methods, however it can be a bit slow on large datasets. It uses it he rowset’s canGetRow method to create the filter and is valid for any table type. It accepts dBL expressions and all dBL operators and functions. It references fields the same way a query object is referenced in dBL:

   this.fields["MyField"].value

Using the expression builder is the best way to learn the dBL syntax required for this type of filter.

The Clear Filter button will clear all filters that have been applied to the current query.