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.
Figure 1 - Portfolio stage two model
Navigate to the Table Wizard
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.
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 the Buy table
2. Click the Add Fields button.
3. Type TransactionDate in the Name 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.
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.
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.
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.
Figure 9 - Add the 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 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.
Figure 12 - Add the 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
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.
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.
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
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.
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
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.
Figure 28 - Table Wizard menu item
Add the Allocation Table
1. Type Allocation in the Name field.
Figure 29 - Add the Allocation table
2. Click the Add Fields button.
3. Type Quantity in the Name field.
Figure 30 - Add the 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 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.
Figure 33 - Add the 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