1 - Build Database Tables

Last updated 23/05/2025

To take your knowledge of Five further, you will need to create three more tables for the Portfolio application. To reflect these changes, the Portfolio database model has been updated. The Buy, Sell, and Allocation tables are now required so a user can make transactions and allocate stock for a sale.

The Stock table has a one-to-many relationship with both the Buy and Sell tables. The Portfolio table has a one-to-many relationship with both the Buy and Sell tables. The Buy and Sell tables both have a one-to-many relationship with the Allocation table.

info

You need to be logged in with a Developer or Control role to continue creating the Portfolio application.

Figure 1 - Portfolio stage two model

Navigate to the Table Wizard

The new tables will be added via Five's Table Wizard.

1. Select Portfolio in the list.

2. Click the Manage button in the form app bar.

Figure 2 - Manage button

2. Click Data in the menu.

3. Click Table Wizard in the sub-menu.

Figure 3 - Table Wizard menu item

Add the Buy Table

1. Type Buy in the Name field.

Figure 4 - Add Buy table

2. Click the Add Fields button.

3. Type TransactionDate in the Name field.

Figure 5 - Add TransactionDate field

4. Click the Add Fields button.

5. Type Quantity in the Name field.

6. Click the Req. checkbox.

Figure 6 - Add Quantity field

7. Click the Add Fields button.

8. Type Price in the Name field.

9. Click the lookup icon in the Data Type field and select Float.

Figure 7 - Add Price field

10. Click the Add Fields button.

11. Type Fees in the Name field.

12. Click the lookup icon in the Data Type field and select Float.

Figure 8 - Add Fees field

13. Click the Add Fields button.

14. Type Total in the Name field.

15. Click the lookup icon in the Data Type field and select Float.

16. Click the Req. checkbox.

Figure 9 - Add Total field

17. Click the Next button.

Figure 10 - Next button

18. Click the Add Relationships button.

19. Click the lookup icon in the Table field and select Portfolio.

20. Click the Required checkbox.

Figure 11 - Add Portfolio relationship

21. Click the Add Relationships button.

22. Click the lookup icon in the Table field and select Stock.

23. Click the Required checkbox.

Figure 12 - Add Stock relationship

24. Click the Save button in the Table Wizard app bar.

Figure 13 - Save button

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

Figure 14 - Save button

Navigate to the Tables View

The Sell table, which we will add next, has the same fields and table relationships as the Buy table. This is a good time to introduce Five's copy feature to duplicate the Buy table record. To do this we need to go to the Tables view.

1. Click Data in the menu.

2. Click Tables in the sub-menu.

Figure 15 - Tables menu item

Add the Sell Table

When using the copy feature, Five exactly copies the record, so we will need to edit the data Sourse ID and the table name, and the primary field and index to reflect the Sell table.

1. Select the Buy record in the list.

2. Click the Copy button in the form app bar.

Figure 16 - Copy button

3. Click the Cancel button in the Data Source ID field and type Sell.

4. Click the Cancel button in the Table Name field and type Sell.

Figure 17 - Add the Sell table

5. Click the Fields tab.

Figure 18 - Fields tab

6. Select the BuyKey record.

Figure 19 - BuyKey field record

7. Either click the Edit button in the form app bar, or click directly in the Field ID field.

Figure 20 - Edit button

8. Click the Cancel button in the Field ID field and type SellKey.

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

Figure 21 - Save button

10. Click the Indices tab.

Figure 22 - Indices tab

11. Select the BuyKey record.

Figure 23 - BuyKey index record

12. Either click the Edit button in the form app bar, or click directly in the Index ID field.

Figure 24 - Edit button

13. Click the Cancel button in the Index ID field and type SellKey.

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

Figure 25 - Save button

15. Click the Save button in the form app bar above the list.

Figure 26 - Save button

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

Figure 27 - Save button

Navigate to the Table Wizard

One last table needs to be added into the Portfolio application, we'll go back to the Table Wizard to do this.

1. Click Data in the menu.

2. Click Table Wizard in the sub-menu.

Figure 28 - Table Wizard menu item

Add the Allocation Table

1. Type Allocation in the Name field.

Figure 29 - Add Allocation table

2. Click the Add Fields button.

3. Type Quantity in the Name field.

Figure 30 - Add Quantity field

4. Click the Next button.

Figure 31 - Next button

5. Click the Add Relationships button.

6. Click the lookup icon in the Table field and select Sell.

7. Click the Required checkbox.

Figure 32 - Add Sell relationship

8. Click the Add Relationships button.

9. Click the lookup icon in the Table field and select Buy.

10. Click the Required checkbox.

Figure 33 - Add Buy relationship

11. Click the Save button in the Table Wizard app bar.

Figure 34 - Save button

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

Figure 35 - Save button