Overview

Last updated 6/04/2022

Table and Fields Designer

Five's Table and Fields Designer allows you to select tables from your database to create queries on a graphical user interface. This enables you to link related information easily and create queries to retrieve data from your database without the need for writing SQL syntax.

info

Five's Table and Fields Designer only supports SELECT statements. It does not support INSERT, UPDATE, or DELETE.

There are three main objects to familiarize yourself with in Five's Table and Fields Designer:

• Table and Fields
• Designer
• Query Builder

Table and Fields

The tables store the data in the database. Selecting a table will make the table appear in the Designer with all the table fields. Relationships established at the table-level will automatically be linked on the Designer.

Example

A foreign relationship is established between the Patient table and the Appointment table at the table-level.

Figure 1 - Database model

Selecting these two tables will look like the following on the Designer. Five establishes the relationship line and the relationship type.

Figure 2 - Tables on the Designer

Designer

After you select a table it will appear on the Designer where you can drag and drop the tables to your desired positioning.

Figure 3 - Designer

Query Builder

You will use the Query Builder to build out your queries to extract data from your tables. Queries may combine data from multiple tables and the data can be manipulated by using the Query Builder fields.

Figure 4 - Query Builder

Understanding the Query Builder

The following table explains the fields on the Query Builder. These will set the required conditions for the field.

Field	Description
Table	Selecting a field from a table will populate the Table field with the name of the table that holds the field.
Field	Selecting a field from a table will populate the Field field with the name of the field. The field can be changed by clicking the lookup icon.
Alias	Assign an alias, or alternate name to the database field name. An alias lets you refer to the original field, however, give it another name.
Sort	Select to display the results in an order. Ascending will return lowest to highest for a numeric field or from A to Z in a text field. Descending will return highest to lowest for a numeric field or from Z to A in a text field.
Show	Select to have a field not show in the results.
Criteria	Add WHERE, AND, and OR clauses to filter records in the result set.

Figure 6 - Query Builder fields

The Add Or button is for you to set an OR condition.

Figure 6 - Add OR button