Fields

Last updated 19/03/2024

When adding a table, you must add fields. Field names are the names you give to the columns in your table. The name should indicate what data is contained in the field and the fields should be relatable. For example, if you had a Customer table, all fields in the table would have related data for the customer such as, Name, Address, Mobile, Email, etc. Fields are also used to maintain relationships between tables, this is done by creating matching fields in two or more tables and then adding the foreign index.

The Fields Form

The table below is to give you an understanding of the fields on the Fields form.

Field	Description
Field ID	Add an ID for your field, this will be the name of the field in the database.
Data Type	Select the data type the field can hold.
Data Format	This field becomes available when a date/time data type has been selected. Add a date format that the web service is configured for.
Generated	Generate a GUID for the field.
Required	Make the field required.
Size	Add the data size the field can hold.
Default Caption	Add a default caption that will be available at the form-level.
Default Display Type	Add a default display type that will be available at the form-level. The display type is how you want the data represented on a form.
Default Value	Add a default value that will be available in the form field.

Primary Key Field

Each table in your database must have a primary key. The primary key constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, and in the table, this primary key can consist of a single and multiple fields.

If you have never added a table in Five, please refer to the chapter Add a Table.

Add a Primary Key Field

1. Click the Fields tab.

Figure 1 - Fields tab

2. Click the Add Fields button.

Figure 2 - Add Fields button

3. Type an ID in the Field ID field.

tip

It is good naming convention, to name your primary key field after your table. For example, if your table is called Customer, you would name the primary key field something like CustomerKey or CustomerID!

info

The Field ID field is an identifier, this field cannot contain whitespace.

4. Click the lookup icon in the Data Type field and select GUID.

info

Five uses GUIDs (globally unique identifiers) to uniquely identify each row in your table. A GUID is a 128-bit text string.

5. Click the Generated switch.

info

If you have selected the data type to be GUID for the primary key field, you must turn the Generated switch on to have a GUID automatically generated for each new record saved into the table.

caution

If you do not have the Generated switch on for a primary key field with a data type of GUID, you will receive an error in your application.

6. Click the Required switch.

info

The Size field is populated from the selected data type. The Default Caption field is populated from the Field ID.

7. Click the lookup icon in the Default Display Type field and select _Lookup.

Figure 3 - Add the primary key field

8. Click the Save button in the form app bar.

Figure 4 - Save the primary key field

info

You will need to add the primary index for the table.

Add a Field

1. Click the Add Fields button.

Figure 5 - Add Fields button

2. Click the lookup icon in the Data Type field and select a type.

3. Optional: Click the Required switch.

tip

You can edit the default values Five populates in the Size and Default Caption fields!

4. Click the lookup icon in the Default Display Type field and select a type.

Figure 6 - Add a field

5. Click the Save button in the form app bar.

Figure 7 - Save button

Foreign Key Field

The foreign key is a field (or a collection of fields) in one table, that refers to the primary key in another table.

Add a Foreign Key Field

1. Click the Add Fields button.

Figure 8 - Add Fields button

2. Type an ID in the Field ID field.

3. Click the lookup icon in the Data Type field and select GUID.

4. Optional: Click the Required switch.

5. Click the lookup icon in the Default Display Type Field and select _Lookup.

info

For a foreign key field, _Lookup needs to be selected as the display type for Five to be able to get the values from the first field in the related table that makes common sense and list these values in the field at the form-level.

Figure 9 - Add a foreign key field

6. Click the Save button in the form app bar.

Figure 10 - Save button

info

You will need to add the foreign index for the table.

7. Once you have added all your fields and indices to your table, click the Save button in the form app bar above the list.

Figure 11 - Save button

Data Types

Five has the following data types.

Data Type	Description
Binary	Data stored in its raw binary format.
Boolean	A data type with two possible values: true(1) or false(0)
Date	Stores the date in the format YYYY-MM-DD, for your MySQL database created by Five.
DateTime	Stores the date and time in the format YYYY-MM-DD HH:MI:SS, for your MySQL database created by Five.
Float	Stores positive and negative numbers with a decimal point.
GUID	Stores a 128-bit text string which is a globally unique identification (ID).
Identifier	Stores a text string of characters and numbers, excluding whitespace and symbols.
Integer	Stores whole numbers.
Memo	Stores larger amounts of data as either text or binary.
Options	Stores a JSON string containing JavaScript, mapping is controlled by Five.
Password	Stores a password in an encrypted format.
Text	Stores any kind of text data.
Time	Stores the time in the format HH:MI:SS, for your MySQL database created by Five.
TimeStamp	Stores the date and time in UTC in the format YYYY-MM-DD HH:MI:SS:ssss, for your MySQL database created by Five.