The Query Builder dialog is available from many places and is
used to help you create data queries for Print Menu Designs and in
the Script Manager for evaluating data in the database.
Overview
The Query Builder dialog is designed to assist users create valid
SQL queries in the Report Designer, Action Menus and other areas
of this application.
Users who are skilled in creating SQL queries can simply enter them
into the Data Query fields and other text boxes where a query can be
used. However, if you have never worked with queries, it can be a
confusing task to get the syntax correct.
The Query Builder allows the user to simply point and click, choose
options from the drop-down lists to build a query that will read the
correct data.
Choose Action
Choose the type of query from the drop-down list. If you're reading
data from one or more tables in the database, this will be the
'SELECT' option.
Choosing Tables
The Table 1 and Table 2 drop-down lists will contain all of the
tables in the application's database. Choose the main table (or only
table) from the Table 1 list; all of the fields from that table will
then be displayed in the field list below it. By default, all fields
are selected for reading, which usually you should keep especially
if you're creating a query for the Report Designer.
Choose a second table, which you MUST link to the first table using
the 'Select JOIN' option on the upper right side of the dialog. The
first field, GuidID, will NOT be selected in the second list of
fields, as it will conflict with the same fieldname from the first
list. Do NOT check this field in the second list.
Typically if Table 1 has a related table, it will be named the same
as Table 1 with the '_Items' text added to the end of it. For
example, the Orders table in Sales stores the main Order information
and the Order_Items table stores all of the line items for the
Order.
See the Section called "Choose Search Criteria" for how to use the
checkboxes below each table to filter for Active, not Deleted and
not Archived data.
Joining Two Tables
The JOIN keyword is used in an SQL statement to query data from two
or more tables, based on a relationship between certain columns in
these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a
unique value for each row. In each table the primary key is called "GuidID".
Each primary key value must be unique within the table. (The purpose
is to bind data together, across tables, without repeating all of
the data in every table).
The matching key (from Table 2) is usually named the same name as
Table 1, with the '_GuidID' text on the end of it. For example, in
Sales Orders, the main table is called 'Orders', the related table
is called 'Order_Items' and the key field in Order_Items is called 'Order_GuidID'.
There are some variants to this rule, but generally that's how to
tell two tables are related.
Please see the System Administrator's guide for your application for
a list of specific table relations.
When you select Table 1 and Table 2, the JOIN drop-down lists will
be automatically linked by the correct keys.
Which JOIN to use?
The INNER JOIN keyword return rows when there is at least one match
in both tables and excludes records that have no match.
The LEFT JOIN keyword returns all rows from the left table (Table
1), even if there are no matches in the right table (Table 2). NOTE:
You must uncheck the Active, Delete Flag and Archive Flag checkboxes
under Table 2 to make this work.
The RIGHT JOIN keyword Return all rows from the right table (Table
2), even if there are no matches in the left table (Table 1). NOTE:
You must uncheck the Active, Delete Flag and Archive Flag checkboxes
under Table 2 to make this work.
Choose Search Criteria
Choose the fields from the Field Name drop-down lists to limit the
results of the query. In the 'Value' field, enter the value to find
or one of the prompts: @TEXT (for text fields); @NUMBER (for numeric
fields); and @DATE (for date fields).
Please note the reminder: "If this query is for a report and uses
the 'Print Current Record' option, ONLY check the 'Delete_Flag =
'No' option for Table 2." The reason for this is that if you are
viewing a record, you'll choose a "Print" option from the print menu
(not a Report), which means you want to print the record you're
viewing, whether or not it's Active or Archived. So these filters
will only trip the "No Data Found" message if they're checked and
your document is NOT Active or it IS Archived. However, even if it's
not Active and/or IS Archived, you probably don't want to print any
Deleted line items associated with it, so you should always check
the "Delete_Flag = 'No'" option to prevent that from happening. The
only issue with this is if you tried to print a deleted record, the
line items wouldn't print, as that choice will exclude them. Then
you could simply "undelete" it, then print it and delete it again if
you don't want to keep it.
Table 1/Table 2 Checkboxes
These checkboxes determine if the default 'Active = Yes, Delete Flag
= No, Archive Flag = 0' filters are added for each table. If you're
using LEFT or RIGHT JOINs on two tables, you should uncheck the
checkboxes under Table 2, as empty values in the second table will
prevent the parent value from Table 1 from displaying in the
results.
If you need to include any records that ARE Inactive, Deleted or
Archived, then leave these boxes unchecked.
Choose How To Order Results
Choose the fields to set the order of the results. By default all
sorts are ordered in ASCENDING order; check the 'DESCending'
checkbox after the field name to reverse the order.
Choose Group-By Fields
These options are not available for all queries; see the System
Administrator's Guide for how to use these fields.
