_Lookup

Last updated 22/08/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 an indepth understanding of all of Five's lookup display types.

Add a Lookup Display Type

Prerequisites

• Your tables are built. For this example, the Category and Ingredient tables are used.
• A foreign relationship is established between your tables. For this example, a foreign relationship is created between the Ingredient table and the Category table.

Figure 1 - Ingredient and Category tables 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.

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

Figure 3 - Add the Categories form

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

Figure 4 - Save the Categories form

6. Click the New Form Wizard button.

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

Figure 5 - Add the Ingredients form

8. Click the Next button.

Figure 6 - 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.

Five sets the Display Type to _Lookup as the foreign relationship was set at the table-level.

Five can automatically make the reference to the Categories form as the form is already saved in Five and has a foreign relationship with the Ingredient table.

Figure 7 - Category field on the Ingredients form

9. Click the List checkbox for the Name and Price fields.

Figure 8 - List checkboxes

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

Figure 9 - Save the Ingredients form

Access the Reference Form Field

Once you have saved your form via the Form Wizard, you can access the Reference Form field by the steps provided below. This can be useful for editing purposes and also to know how you would reference a form not through the Form Wizard.

1. Select the Ingredients record in the list.

2. Click the Pages tab.

Figure 10 - Pages tab

3. Select the General record.

Figure 11 - General record

4. Click the Fields tab.

Figure 12 - Fields tab

5. Select the Category record.

Figure 13 - Category record

info

In the image below, you can see the Category field 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 14 - Category form field record

Add Records Using the Lookup Display Type

1. Click the Run button in the toolbar.

Figure 15 - Run button

info

If you are following along with the Soul Food application, you will land on the Categories menu item after running the application.

2. Click the Add Item button.

3. Type Poultry in the Name field.

Figure 16 - Add the category Poultry

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

Figure 17 - Save the category Poultry

5. Click the Add Item button.

6. Type Fruit in the Name field.

Figure 18 - Add the category Fruit

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

Figure 19 - Save the category Fruit

8. Click the Ingredients menu item.

Figure 20 - 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 21 - 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 22 - 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 23 - Add the ingredient Apples

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

Figure 24 - Save the ingredient Apples

Use a Dependent Field with a Lookup

You can filter the data in a lookup by giving the current 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 tables to create these forms can be found in the Introduction to Five's Lookups chapter.

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 Dependent Field

Prerequisites

• Your tables are built. For this example, the Category, Ingredient, Recipe, and RecipeIngredient tables are used.
• Foreign relationships are established. For this example, foreign relationships are created between the RecipeIngredient table and the Recipe, Ingredient, and Category tables.

Figure 25 - Table relationships in the Soul Food database

Steps

1. Click the New Form Wizard button.

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

Figure 26 - Add the Recipes form

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

Figure 27 - Save the Recipes form

4. Click the New Form Wizard button.

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

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

7. Click the Add Menu Item switch.

Figure 28 - Add the Recipe Ingredients form

8. Click the Next button.

Figure 29 - 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 30 - Dependent field

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

Figure 31 - List checkboxes

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

Figure 32 - 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 33 - Pages tab

14. Click the Add Pages button.

Figure 34 - 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 35 - Add the Recipe Ingredients page

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

Figure 36 - Save the Recipe Ingredients page

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

Figure 37 - Save the Recipes form

Access the Dependent Field

Once you have saved your form via the Form Wizard, you can access the Dependent field by the steps provided below. This can be useful for editing purposes and also to know how you can add a dependent field not through the Form Wizard.

1. Select the Recipe Ingredients record in the list.

2. Click the Pages tab.

Figure 38 - Pages tab

3. Select the General record.

Figure 39 - General record

4. Click the Fields tab.

Figure 40 - Fields tab

5. Select the Ingredient record.

Figure 41 - Ingredient record

info

In the image below, you can see the Ingredient field record is available and the lookup in the Dependent Field field allows you to select a dependent field.

Figure 42 - Dependent Field field

Add Records Using the Dependent Field

1. Click the Run button in the toolbar.

Figure 43 - 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 44 - Category records

Figure 45 - Ingredient records

2. Click Recipes in the menu.

3. Click the Add Item button.

Figure 46 - Add a recipe

4. Type Lemon Chicken in the Title field.

5. Click the Recipe Ingredients tab.

Figure 47 - Recipe Ingredients tab

6. Click the Add Recipe Ingredients button.

Figure 48 - 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 dependency, all the ingredients would be listed here, ie Apples and Thighs would be displayed irregardless of what category was selected.

Figure 49 - Ingredient field

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

Figure 50 - Save the Recipe Ingredient form

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

Figure 51 - Save the Recipe form