1 - Build Database Tables
Last updated 27/03/2023
To take your knowledge of Five further, you will need to create three more tables to 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 Tables
1. Select the Portfolio record in the application list.2. Click the Manage button in the form app bar.
Figure 2 - Manage button
3. Select Data in the menu.
4. Select Tables in the sub-menu.
Figure 3 - Tables menu item
Add the Buy Table
1. Click the New Table Wizard button.2. Type Buy in the Name field.
Figure 4 - Add the Buy table
3. Click the Add Fields button.
4. Type TransactionDate in the Name field.
Figure 5 - Add the TransactionDate field
5. Click the Add Fields button.
6. Type Quantity in the Name field.
7. Click the Req. checkbox.
Figure 6 - Add the Quantity field
8. Click the Add Fields button.
9. Type Price in the Name field.
10. Click the lookup icon in the Data Type field and select Float.
Figure 7 - Add the Price field
11. Click the Add Fields button.
12. Type Fees in the Name field.
13. Click the lookup icon in the Data Type field and select Float.
Figure 8 - Add the Fees field
14. Click the Add Fields button.
15. Type Total in the Name field.
16. Click the lookup icon in the Data Type field and select Float.
17. Click the Req. checkbox.
Figure 9 - Add the Total field
18. Click the Next button.
Figure 10 - Next button
19. Click the Add Relationships button.
20. Click the lookup icon in the Table field and select Portfolio.
21. Click the Required checkbox.
Figure 11 - Add the Portfolio relationship
22. Click the Add Relationships button.
23. Click the lookup icon in the Table field and select Stock.
24. Click the Required checkbox.
Figure 12 - Add the Stock relationship
25. Click the Save button in the Table Wizard app bar.
Figure 13 - Save the Buy table
Add the Sell Table
1. Click the New Table Wizard button.2. Type Sell in the Name field.
Figure 14 - Add the Sell table
3. Click the Add Fields button.
4. Type TransactionDate in the Name field.
Figure 15 - Add the TransactionDate field
5. Click the Add Fields button.
6. Type Quantity in the Name field.
7. Click the Req. checkbox.
Figure 16 - Add the Quantity field
8. Click the Add Fields button.
9. Type Price in the Name field.
10. Click the lookup icon in the Data Type field and select Float.
Figure 17 - Add the Price field
11. Click the Add Fields button.
12. Type Fees in the Name field.
13. Click the lookup icon in the Data Type field and select Float.
Figure 18 - Add the Fees field
14. Click the Add Fields button.
15. Type Total in the Name field.
16. Click the lookup icon in the Data Type field and select Float.
17. Click the Req. checkbox.
Figure 19 - Add the Total field
18. Click the Next button.
Figure 20 - Next button
19. Click the Add Relationships button.
20. Click the lookup icon in the Table field and select Portfolio.
21. Click the Required checkbox.
Figure 21 - Add the Portfolio relationship
22. Click the Add Relationships button.
23. Click the lookup icon in the Table field and select Stock.
24. Click the Required checkbox.
Figure 22 - Add the Stock relationship
25. Click the Save button in the Table Wizard app bar.
Figure 23 - Save the Sell table
Add the Allocation Table
1. Click the New Table Wizard button.2. Type Allocation in the Name field.
Figure 24 - Add the Allocation table
3. Click the Add Fields button.
4. Type Quantity in the Name field.
Figure 25 - Add the Quantity field
5. Click the Next button.
Figure 26 - Next button
6. Click the Add Relationships button.
7. Click the lookup icon in the Table field and select Sell.
8. Click the Required checkbox.
Figure 27 - Add the Sell relationship
9. Click the Add Relationships button.
10. Click the lookup icon in the Table field and select Buy.
11. Click the Required checkbox.
Figure 28 - Add the Buy relationship
12. Click the Save button in the Table Wizard app bar.
Figure 29 - Save the Allocation table