# Search query designer

The Search query designer is the way in which you specify which data is extracted from the database for a Search and the columns into which the data is formatted. The Search specification is written in SQL format using either a section-based approach or a raw query.

Features

- Option to enter queries as either free text or in sections for ease of use
- Complete list of available tables and field names can be displayed
- Zoom in and out to increase or decrease the size of the font in the query designer window
  - Press and hold Ctrl and use the mouse wheel to select the size you want to use when viewing the code
- Certain keywords and comments will default to highlight in colour:
  - Green = Comments
  - Blue = Reserved words (e.g. SELECT, Function, If)
  - Violet/Pink = Functions (e.g. BeginQuery, Anchor, CBool)
  - Grey = Quoted sections (e.g. "grey")
  - Red = Selection Criteria (e.g. {pCurrent} )

Last edit 29/01/20

Search query designer form

Module: Database

Category: Search query designer

Activation: Search manager form > Search manager

Form style:

Special actions available for users with Administrator permissions:

- None

Database rules:

- None

Field definitions
Main
Don't show highlights

Reference: Yes/no

When ticked it will disable the colour highlighting. This can aid with scrolling issues on large queries.

Last edit 29/10/20

Display table wizard

Reference: button

This button displays the entire list of tables and their respective fields from the database in a separate form. When the form is displayed, select either the table name or the field name that is required and either double-click on the item or click on the Insert button to insert the text at the current cursor position.

Construct query as parts

Construct query as parts option

Reference: Yes/no

This option allows the user to enter the query in sections, eliminating the need to enter certain SQL commands such as the SELECT and FROM keywords. It has not only been designed to allow users with little SQL experience to create simply queries, but it has also been designed to allow seamless integration of parameter fields from the search dialog designer GUI.

Fields to SELECT

Reference: memo, expandable

For basic queries, this should contain the fields that need to be returned in the final query. SQL syntax demands the fields entered here fit the format FIELD1, FIELD2,… FIELDn. The SQL keyword SELECT is inserted automatically before the contents of this field.

Tables to select FROM

Reference: memo, expandable

For basic queries, this should contain the name of the tables from which data is required. SQL syntax demands the tables entered here fit the format TABLE1, TABLE2,… TABLEn. The SQL keyword FROM is inserted automatically before the contents of this field.

Selection criteria

Reference: memo, expandable

This should contain an expression that limits the data returned for the final query. SQL syntax demands the fields entered here fit the format FIELD1 = 1 AND FIELD2 = 2 etc.

The SQL keyword WHERE is inserted automatically before the contents of this field.

Records to ORDER BY

Reference: memo, expandable

This should contain an SQL ORDER BY expression. The SQL keyword ORDER BY is inserted automatically before the contents of this field.

Enter as whole query

Enter as whole query

Reference: Yes/no

This option allows the user to enter queries as free text. This is the more advanced option as queries are only limited by the user’s knowledge of the SQL language. The user is presented with one field and can enter a query as they please.

Build your own query

Reference: memo, expandable

This field can be used to enter a specific SQL expression in the required format. When using this field, the full query field below it will mirror whatever is entered in this field.

The following syntax can also be included in this field before the main SQL expression as many times as required:

    BeginQuery NameX

    Query

    EndQuery

This syntax allows multiple SQL expressions, as determined by Query in the above format, to be utilised by the main SQL expression. This is achieved using the format "Select fields from NameX".

For example, the following could be used in this field as a substitute for the SQL expression "Select * from MyTable".

    BeginQuery MyTableContents

    Select * from MyTable

    EndQuery

    Select * from MyTableContents

Comment out

Reference: button

Information is under review for a new version and will be updated soon.

Uncomment

Reference: button

Information is under review for a new version and will be updated soon.

RGB colour picker

Reference: Button

This button will open a colour picker. Selecting a colour will print the corresponding RGB code to the cursor position. This will overwrite any selected text. Selecting an RGB code before opening the colour picker will pre-select that colour, allowing for fine adjustments to the that colour.

Run to Cursor Test

Button

This button will run a test of the Search/Command up to the point where you have placed the cursor in the field (flashing text bar cursor). For example, to test the entire query, scroll to the very bottom and click to place the cursor after all the text entries.

When using the "run to cursor" button on commands, it will not have lasting effects. (i.e. it will mostly behave as normal, but any changes to data will be reverted once you close the command window)

The test button on the Search manager (not the query design "run to cursor" button, the original Test button) appears on the command manager as well. This button will affect data when testing commands, but only in IDLE mode, not in ADD or EDIT.

Last edit 10/12/19

Related topics