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.

  • 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

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

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.

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.

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.

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.

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.

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.

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

Reference: button

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

Reference: button

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

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.

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

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

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.

Related Topics Link IconRelated topics