Skip to main content

Table Upgrades

Last updated 12/12/2023

Saving a table in Five will make the Upgrade Table dialog window available, this enables you to select data from another table in your database to populate the fields in the current table.

Rules
  • Only SELECT statements can be written in the Upgrade Table window.
  • The number of columns being selected must equal the number of columns in the current table.
  • If data is coming from other tables they must be in the same database.

tip
If you do not need to use this feature, just simply click the Save button in the Upgrade Table window!

info
When adding a new table, the Table Upgrade window is available in the Table Wizard, the Table form, and the Database Modeler.

Table Upgrade With a Select Query​

Scenario
Lets say we have a Customer table with the fields CustomerKey, LastName, FirstName, PostCode, and Suburb, and we want to populate a PostCode table with some of the fields from the Customer table.

Database model
Figure 1 - Database model

Prerequisite
The Customer table is saved in Five.

Steps
1. Add the PostCode table.


Add a new table
Figure 2 - Add a new table

2. Add the fields PostCodeKey, PostCode, and Suburb.


Add the fields
Figure 3 - Add the fields

3. Add the primary index PostCodeKey.


Add the primary index
Figure 4 - Add the primary index

4. Click the Save button/s in the form app bar/s.

info
The amount of Save buttons to click may vary dependent on which page of the Table form you are on.

Save button
Figure 5 - Save button

5. Type your SELECT statement in the Table Upgrade window.

MySQL
SELECT Example
SELECT
  UUID(),
  PostCode,
  Suburb
FROM
  Customer


6. Click the Save button in the Table Upgrade window.


Save button
Figure 6 - Save button

tip
To test your data, go the Queries view and use the SQL Editor!

Testing Data​

1. Click Data in the menu.

2. Click Queries in the sub-menu.


Queries menu item
Figure 7 - Queries menu item

3. Click the Add Item button.


Add Item button
Figure 8 - Add Item button

4. Click in the Query field.


Query field
Figure 9 - Query field

5. Click the SQL tab.


SQL tab
Figure 10 - SQL tab

6. SELECT * FROM your new table (PostCode).


MySQL
SELECT Example
SELECT
  *
FROM
  PostCode


7. Click the Run button.


Run button
Figure 11 - Run button

Adding a New Field to a Table​

When you add a new field to a saved table in Five, the Table Upgrade window will be available again and will ask you how you want to fill this field for all existing records saved in the database. The documentation below, will explain how to use the table upgrade functionality when you add a new field to a saved table.

Add an Empty Value​

Empty is the default in the Table Upgrade window. If your field is not flagged as required, you can opt to save the table with an empty value in the field for all existing records.

caution
If your field is flagged as required, you will not be able to save the table with an empty value in the field.

1. Click the Save button.


Save button
Figure 12 - Save button

Add a Value​

The Value option enables you to add a value that will be saved into the field for all existing records in the table.

1. Click the Value checkbox.

2. Type a value in the field.


Value
Figure 13 - Value

3. Click the Save button.


Save button
Figure 14 - Save button

Add a Field​

The Field option enables you to select a field in the current table to populate the field for existing records.

1. Click the Field checkbox.

2. Click the lookup icon in the field and select a field from the current table.


Field
Figure 15 - Field

3. Click the Save button.


Save button
Figure 16 - Save button

Add a Query​

The Query option enables you to write a SELECT statement to populate the field for existing records.

1. Click the Query checkbox.

2. Type a SELECT statement in the field.


Query
Figure 17 - Query

3. Click the Save button.


Save button
Figure 18 - Save button

Editing a Field in a Table​

If you change the data type or reduce the size on a field, you will be asked how would you like to reduce the size of the field. This is because, the values in the field for existing records cannot be larger than the new size. The documentation below, will explain how to use the table upgrade functionality when you edit a field in a saved table.

Truncate​

Truncate will reduce the size of the data in the database by cutting it off so to speak to your new data size. By default, Five will apply your new data size in the field.

1. Click the Save button.

tip
You can change the size by typing in a new value, or using the up and down arrows in the field!

Save button
Figure 19 - Save button

Divide​

Divide will divide the current size in the database by the value in the field, this is used for the data types Float and Integer. By default, Five will apply the required number for the data to fit the field.

1. Click the Save button.


Save button
Figure 20 - Save button

Query​

Query enables you to write a SELECT statement to reduce the size.

1. Click the Query checkbox.

2. Type a SELECT statement in the field.

3. Click the Save button in the Table Upgrade window.


Save button
Figure 21 - Save button