_Lookup

Last updated 9/06/2023

Introduction

This documentation will cover two subjects:

• How a lookup is created when you have a foreign relationship set at the table-level.
• How a dependent field works with a lookup.

When you create a foreign relationship with Five's Table Wizard, Five will automatically create the Default Display Type field to be a _Lookup. When you come to the form-level and add the form, Five will display this in the Form Wizard. In your application, Five will take the values in the first field included in the list from the referenced form with the most logical displayable data type and list these in the lookup.

Before you begin this documentation, please refer to the Introduction to Five's Lookups chapter so you have an understanding of the database design to demonstrate the display type _Lookup. All the tables used to demonstrate Five's lookups are documented in the Introduction to Five's Lookups chapter so you can build them if you wish and use them to learn about our lookup display types. This documentation is designed for you to build and add data to get a thorough understanding.

Add a Lookup Display Type Using the Form Wizard

Prerequisites

• The Category and Ingredient tables are built.
• A foreign relationship established on the Ingredient table with the Category table.

Figure 1 - Ingredient and Category foreign relationship

Steps

1. Select Visual in the menu.

2. Select Forms in the sub-menu.

Figure 2 - Forms menu item

3. Click the New Form Wizard button.

Figure 3 - New Form Wizard button

4. Click the lookup icon in the Main Table field and select Category.

Figure 4 - Add the Categories form

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

Figure 5 - Save the Categories form

6. Click the New Form Wizard button.

7. Click the lookup icon in the Main Table field and select Ingredient.

Figure 6 - Add the Ingredients form

8. Click the Next button.

Figure 7 - Next button

info

In the image below, you can see Five has given the Category field a _Lookup display type and has automatically referenced the Categories form.

Figure 8 - Category field on the Ingredients form

9. Click the List checkbox for the Name field.

Figure 9 - List checkbox

10. Click the Save button in the Form Wizard app bar.

Figure 10 - Save the Ingredients form

Access the Reference Form Field

This documentation is to explain how to access the Reference Form field once you have saved the form in the Form Wizard. This can be useful for editing purposes and also to know how you would add a reference form not through the Form Wizard.

1. Select the Ingredients record in the list.

2. Click the Pages tab.

Figure 11 - Pages tab

3. Select the General record.

Figure 12 - General record

4. Click the Fields tab.

Figure 13 - Fields tab

5. Select the Category record.

Figure 14 - Category record

info

In the image below, you can see the Category field form record is available and the lookups in the Display Type and Reference Form fields allow you to select the display type and make reference to a form.

Figure 15 - Category field form record

Add Category and Ingredient Records in the Soul Food Application

1. Click the Run button in the toolbar.

Figure 16 - Run button

2. Click the Add Item button.

3. Type Poultry in the Name field.

Figure 17 - Add the category Poultry

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

Figure 18 - Save the category Poultry

5. Click the Add Item button.

6. Type Fruit in the Name field.

Figure 19 - Add the category Fruit

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

Figure 20 - Save the category Fruit

8. Click the Ingredients menu item.

Figure 21 - Ingredients menu Item

9. Click the Add Item button.

10. Click the lookup icon in the Category field.

info

Both of the categories that were just added are now available here in the Category lookup on the Ingredients form.

Figure 22 - Category lookup

11. Select Poultry in the lookup list.

12. Type Thighs in the Name field.

13. Type 12.00 in the Price field.

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

Figure 23 - Save the ingredient Thighs

15. Click the Add Item button.

16. Click the lookup icon in the Category field and select Fruit.

17. Type Apples in the Name field.

18. Type 5.00 in the Price field.

Figure 24 - Add the ingredient Apples

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

Figure 25 - Save the ingredient Apples

Use a Dependent Field with a Lookup

This documentation is to explain how you can filter the data in a lookup by giving a field on a form a dependent field. What will be displayed in the lookup is dependent on what value is selected in another field. For example, lets say in the Soul Food application that when you select a category you only want the ingredients to be displayed that belong to that category.

The following forms will be added into the Soul Food application to demonstrate this.

• Recipes form
• Recipe Ingredients form

The Recipes form will also need to be edited to add a Recipe Ingredients page.

Please Note If you are building the Soul Food application to learn all of Five's lookup display types, the video below only shows how to add a dependent field. Refer to the documentation below to continue building the Soul Food application.

Add a Lookup Display Type with a Dependent Field

Prerequisites

• The Category, Ingredient, Recipe, and RecipeIngredient tables are built.
• A foreign relationship established on the Ingredient table with the Category table.
• Foreign relationships established on the RecipeIngredient table with the Recipe, Category, and Ingredient tables.
• The Categories and Ingredients forms are created.

Figure 26 - Table relationships in the Soul Food database

Steps

1. Click the New Form Wizard button.

2. Click the lookup icon in the Main Table field and select Recipe.

Figure 27 - Add the Recipes form

3. Click the Save button in the Form Wizard app bar.

Figure 28 - Save the Recipes form

4. Click the New Form Wizard button.

5. Click the lookup icon in the Main Table field and select RecipeIngredient.

6. Add a space between Recipe and Ingredients in the Name field.

7. Click the Add Menu Item switch.

Figure 29 - Add the Recipe Ingredients form

8. Click the Next button.

Figure 30 - Next button

9. Click the lookup icon in the Dependent field for the Ingredient field and select CategoryKey.

info

By selecting the CategoryKey field here, the ingredients displayed in the lookup in the Ingredients field will depend on the selection made in the Category field.

Figure 31 - Dependent field

10. Click the List checkboxes for the Category and Ingredient fields.

Figure 32 - List checkboxes

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

Figure 33 - Save the Recipe Ingredients form

info

The Add Menu Item switch was turned off for the Recipe Ingredients form because it will be added as a page on the Recipes form.

12. Select the Recipes record in the list.

13. Click the Pages tab.

Figure 34 - Pages tab

14. Click the Add Pages button.

Figure 35 - Add Pages button

15. Type Recipe Ingredients in the Caption field.

16. Click the lookup icon in the Page Type field and select List.

17. Click the lookup icon in the Action field and select RecipeIngredients (Form).

Figure 36 - Add the Recipe Ingredients page

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

Figure 37 - Save the Recipe Ingredients page

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

Figure 38 - Save the Recipes form

Access the Dependent Field

This documentation is to explain how to access the Dependent Field field once you have saved the form in the Form Wizard. This can be useful for editing purposes and also to know how you would add a reference form not through the Form Wizard.

1. Select the Recipe Ingredients record in the list.

2. Click the Pages tab.

Figure 39 - Pages tab

3. Select the General record.

Figure 40 - General record

4. Click the Fields tab.

Figure 41 - Fields tab

5. Select the Ingredients record.

Figure 42 - Ingredients record

info

You now have access to the Dependent Field and can edit this field by either clicking the Edit button in the app bar or clicking directly in the Dependent Field field.

Figure 43 - Dependent field

Add a Recipe Record in the Soul Food Application

1. Click the Run button in the toolbar.

Figure 44 - Run button

info

There are currently two categories saved in the system: Fruit and Poultry, and two ingredients: Apples and Thighs, as shown in the two images below.

Figure 45 - Category records

Figure 46 - Ingredient records

2. Click Recipes in the menu.

3. Click the Add Item button.

Figure 47 - Add Item button

4. Type Lemon Chicken in the Title field.

5. Click the Recipe Ingredients tab.

Figure 48 - Recipe Ingredients tab

6. Click the Add Recipe Ingredients button.

Figure 49 - Add Recipe Ingredients button

7. Click the lookup in the Categories field and select Poultry.

8. Click the lookup in the Ingredient field.

info

The lookup will only display the ingredients that belong to the Poultry category. As we only have one ingredient belonging to the Poultry category, Five has populated the field automatically for us.

If you were to remove the CategoryKey field in the Ingredients Dependent Field field on the Recipe Ingredients form, all the ingredients would be listed here, ie Apples and Thighs would be displayed irregardless of what category was selected.

Figure 50 - Ingredient field

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

Figure 51 - Save the Recipe Ingredient form

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

Figure 52 - Save the Recipe form