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.
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
- 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.
A foreign relationship is established between the Patient table and the Appointment table at the table-level.
Selecting these two tables will look like the following on the Designer. Five establishes the relationship line and the relationship type.
After you select a table it will appear on the Designer where you can drag and drop the tables to your desired positioning.
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.
Understanding the Query Builder
The following table explains the fields on the Query Builder. These will set the required conditions for the field.
|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.|
The Add Or button is for you to set an