Skip to main content

1 - Build Database Tables

Last updated 13/12/2023

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.


Portfolio Base Model Stage Two
Figure 1 - Portfolio stage two model
info
When working in the Cloud, you need to be logged in with a Developer or Control account to continue creating the Portfolio application.

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

info
You may need to select the Portfolio record first if you have more than one application, or are working in Local.

Manage Button
Figure 2 - Manage button

2. Click Data in the menu.

3. Click Table Wizard in the sub-menu.


Table Wizard Menu Item
Figure 3 - Table Wizard menu item

Add the Buy Table

1. Type Buy in the Name field.


Add the Buy Table
Figure 4 - Add the Buy table

2. Click the Add Fields button.

3. Type TransactionDate in the Name field.


Add the TransactionDate Field
Figure 5 - Add the TransactionDate field

4. Click the Add Fields button.

5. Type Quantity in the Name field.

6. Click the Req. checkbox.


Add the Quantity Field
Figure 6 - Add the 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.


Add the Price Field
Figure 7 - Add the 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.


Add the Fees Field
Figure 8 - Add the 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.


Add the Total Field
Figure 9 - Add the Total field

17. Click the Next button.


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.


Add the Portfolio Relationship
Figure 11 - Add the 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.


Add the Stock Relationship
Figure 12 - Add the Stock relationship

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


Save Button
Figure 13 - Save button

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


Save Button
Figure 14 - Save button
info
The Sell table 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.


Tables menu item
Figure 15 - Tables menu item

Add the Sell Table

1. Select the Buy record in the list.

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


Copy button
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.


Add the Sell Table
Figure 17 - Add the Sell table

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

5. Click the Fields tab.


Fields Tab
Figure 18 - Fields tab

6. Select the BuyKey record.


BuyKey Field 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.


Edit button
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.


Save button
Figure 21 - Save button

10. Click the Indices tab.


Indices Tab
Figure 22 - Indices tab

11. Select the BuyKey record.


BuyKey Index 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.


Edit button
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.


Save button
Figure 25 - Save button

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


Save button
Figure 26 - Save button

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


Save Button
Figure 27 - Save button
info
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.


Table Wizard Menu Item
Figure 28 - Table Wizard menu item

Add the Allocation Table

1. Type Allocation in the Name field.


Add the Allocation Table
Figure 29 - Add the Allocation table

2. Click the Add Fields button.

3. Type Quantity in the Name field.


Add the Quantity Field
Figure 30 - Add the Quantity field

4. Click the Next button.


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.


Add the Sell Relationship
Figure 32 - Add the 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.


Add the Buy Relationship
Figure 33 - Add the Buy relationship

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


Save Button
Figure 34 - Save button

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


Save Button
Figure 35 - Save button