Build the Book Club Tables

Last updated 16/04/2025

Once you have created your application, the next logical thing to do is to add your tables. This documentation will teach you how to add the tables for the Book Club application.

There are only three tables that need to be added to the database. The Book Club database model shows the tables and the relationships between the tables.

info

The iUser table was automatically created when you turned the Book Club application into a multiuser application.

Figure 1 - Book Club database model

While building the Book Club tables, you will learn the following:

• How to add tables with Five's Table Wizard
• How to edit tables with Five's Table form
• How to interact with the iUser table automatically created by Five; and
• How to upgrade a table using Five's Table Upgrade functionality

Navigate to the Table Wizard

1. 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 Classification Table

tip

You do not need to create a primary key field and index when you create a table via Five's Table Wizard, Five does this automatically for you!

1. Type Classification in the Name field.

2. Click the Add Fields button.

3. Type Name in the Name field.

Figure 4 - Add Classification table

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

Figure 5 - Save button

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

Figure 6 - Save button

Add the Book Table

1. Type Book in the Name field.

2. Click the Add Fields button.

3. Type BorrowMemberKey in the Name field.

info

Five will automatically configure some field definitions dependent on the name of the field. Because the name of the field has Key in it, Five has given it a data type of GUID, this is what we want here as you will see when we edit this table in the Tables view.

Figure 7 - Add Book table

4. Click the Add Fields button.

5. Type Image in the Name field.

6. Click the lookup icon in the Data Type field and select Binary.

7. Type 16777215 in the Size field.

8. Click the lookup icon in the Default Display Field and select _Picture.

Figure 8 - Add Image field

9. Click the Add Fields button.

10. Type Title in the Name field.

11. Click the Req. (Required) checkbox.

Figure 9 - Add Title field

12. Click the Add Fields button.

13. Type Author in the Name field.

14. Click the Req. checkbox.

Figure 10 - Add Author field

15. Click the Add Fields button.

16. Type ISBN in the Name field.

17. Click the Req. checkbox.

18. Type 15 in the Size field.

Figure 11 - Add ISBN field

19. Click the Next button.

Figure 12 - Next button

20. Click the Add Relationships button.

21. Click the lookup icon in the Table field and select Classification.

22. Click the Required checkbox

tip

You do not need to create the foreign key field and index when you create a relationship via Five's Table Wizard, Five does this automatically for you!

Figure 13 - Add Classification relationship

23. Click the Add Relationships button.

24. Click the lookup icon in the Table field and select iUser.

25. Click the Required checkbox.

Figure 14 - Add iUser relationship

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

Figure 15 - Save button

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

Figure 16 - Save button

Edit the Book Table

After saving a table created in the Table Wizard, the record will be listed in the Tables view and after selecting the record, you can edit the record using the Table form.

When a relationship is created in the Table Wizard, Five automatically creates the foreign field and index with the same name as the primary key in the foreign table. For example, when we established the relationship with the iUser table in the Book table, Five created the iUserKey field and the iUserkey foreign index in the Book table.

We are going to edit the book table so the iUserKey field and index are called MemberKey, this way we can easily see who is a member that owns a book and who is a member who is borrowing a book.

The documentation below will guide you through the steps to edit a field and an index record after saving a table. You will also see the Key fields and indices the Table Wizard automatically created for us.

tip

All table records are located in the Tables view!

1. Click Data in the menu.

2. Click Tables in the sub-menu.

Figure 17 - Tables menu item

3. Select the Book record in the list.

4. Click the Fields tab.

Figure 18 - Fields tab

5. Select the iUserKey record.

Figure 19 - iUserKey field record

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

Figure 20 - Edit button

7. Click the Cancel button in the Field ID field and type MemberKey.

8. Click the lookup icon in the Default Display Type field and select _Lookup.

Figure 21 - Edit iUserKey field

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

Figure 22 - Save button

10. Click the Indices tab.

Figure 23 - Indices tab

11. Select the iUserKey record.

Figure 24 - iUserKey index record

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

Figure 25 - Edit button

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

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

Figure 26 - Save button

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

Figure 27 - Save button

Add the BorrowHistory Table

info

The BorrowHistory table will be used to keep an audit of all borrowed and overdue books.

1. Click Data in the menu.

2. Click Table Wizard in the sub-menu.

Figure 28 - Table Wizard menu item

3. Type BorrowHistory in the Name field.

4. Click the Add Fields button.

5. Type Date in the Name field.

Figure 29 - Add BorrowHistory table

6. Click the Add Fields button.

7. Type DateReturned in the Name field.

Figure 30 - Add DateReturned field

8. Click the Add Fields button.

9. Type OverDueNotice in the Name field.

10. Click the lookup icon in the Data Type field and select TimeStamp.

Figure 31 - Add OverDueNotice field

11. Click the Next button.

Figure 32 - Next button

12. Click the Add Relationships button.

13. Click the lookup icon in the Table field and select iUser.

14. Click the Required checkbox.

Figure 33 - Add iUser relationship

15. Click the Add Relationships button.

16. Click the lookup icon in the Table field and select Book.

17. Click the Required checkbox.

Figure 34 - Add Book relationship

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

Figure 35 - Save button

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

Figure 36 - Save button

Edit the BorrowHistory Table

The BorrowHistory table needs to be updated to change the name of the iUserKey field and index to keep with the same naming conventions we have been using in the Book table.

1. Click Data in the menu.

2. Click Tables in the sub-menu.

Figure 37 - Tables menu item

3. Select the BorrowHistory record in the list.

4. Click the Fields tab.

Figure 38 - Fields tab

5. Select the iUserKey record.

Figure 39 - iUserKey field record

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

Figure 40 - Edit button

7. Click the Cancel button in the Field ID field and type MemberKey.

8. Click the lookup icon in the Default Display Type field and select _Lookup.

Figure 41 - Edit iUserKey field

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

Figure 42 - Save button

10. Click the Indices tab.

Figure 43 - Indices tab

11. Select the iUserKey record in the list.

Figure 44 - iUserKey index record

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

Figure 45 - Edit button

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

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

Figure 46 - Save-button

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

Figure 47 - Save button

Working with Five's iUser Table

The iUser table that Five adds into your multiuser application can be edited to suit your requirements.

We are going to add some fields to the iUser table. These fields will be used for the Parcel Locker locations to know where a book is available to pick up.

warning

The iUser table definition is defined by Five and you should not delete any of the fields configured by Five. You can add additional fields, and if you would like to add additional data related to the user, you can associate a record from another table by using the Roles security table configuration.

At the form-level, you can select to hide fields, this is the recommended way to not show any fields that you do not want to display.

1. Select the iUser record in the list.

2. Click the Fields tab.

Figure 48 - Fields tab

3. Click the Add Fields button.

Figure 49 - Add Fields button

4. Type AddressLine1 in the Field ID field.

5. Click the lookup icon in the Data Type field and select Text

6. Click the Required switch.

7. Click the lookup icon in the Default Display Type field and select _Text.

Figure 50 - Add AddressLine1 field

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

Figure 51 - Save button

9. Click the Add Fields button.

Figure 52 - Add Fields button

10. Type AddressLine2 in the Field ID field.

11. Click the lookup icon in the Data Type field and select Text.

12. Click the lookup icon in the Default Display Type field and select _Text.

Figure 53 - Add AddressLine2 field

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

Figure 54 - Save button

14. Click the Add Fields button.

Figure 55 - Add Fields button

15. Type Suburb in the Field ID field.

16. Click the lookup icon in the Data Type field and select Text.

17. Click the Required switch.

18. Click the lookup icon in the Default Display Type field and select _Text.

Figure 56 - Add Suburb field

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

Figure 57 - Save button

20. Click the Add Fields button.

Figure 58 - Add Fields button

21. Type State in the Field ID field.

22. Click the lookup icon in the Data Type field and select Text.

23. Click the Required switch.

24. Click the lookup icon in the Default Display Type field and select _Text.

Figure 59 - Add State field

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

Figure 60 - Save button

26. Click the Add Fields button.

Figure 61 - Add Fields button

27. Type PostCode in the Field ID field.

28. Click the lookup icon in the Data Type field and select Integer.

29. Click the Required switch.

30. Click the lookup icon in the Default Display Type field and select _Integer.

Figure 62 - Add PostCode field

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

Figure 63 - Save button

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

Figure 64 - Save button

info

At this point, you will receive the Table Upgrade window. This is to let Five know how to populate the new fields for any iUser records already saved in the database.

33. Click the Value checkbox for each of the fields.

34. Type 0 in the field for the fields: AddressLine1, AddressLine2, Suburb, State, and PostCode.

35. Click the Save button.

Figure 65 - Save table upgrade