_Lookup

Last updated 27/11/2024

Overview

This documentation will cover three topics:

• How to add a lookup when a foreign relationship is established at the table-level.
• How to use a dependent field with a lookup.
• How to add a lookup without a database field and no foreign relationship created in the database.

When a foreign relationship is established at the table-level, the foreign key field can then have a display type of _Lookup on the associated form. Five will take the values in the first field included in the list from the referenced form with the most logical displayable display type and list these values in the lookup.

Add a _Lookup

A foreign relationship needs to be established in the database. For this example, the Ingredient table has a foreign relationship with the Category table as shown in the image below.

Figure 1 - Database model

Add the Categories Form

Two forms will need to be created to establish the lookup field. To have the ability to create the lookup completely in the wizard, add the form that references the table without the foreign key first. For this scenario, it will be the Categories form that will be added first.

1. Select Visual in the menu.

2. Select Form Wizard in the sub-menu.

Figure 2 - Form Wizard menu item

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

Figure 3 - Main Data Source field

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

Figure 4 - Next button

info

You can see here there is only one field called Name with a display type of _Text as Five automatically knocks out the primary key field, the Name field is flagged to be included in the list. The values entered in this field will be the values provided to the lookup on the Ingredients form.

Figure 5 - Name field

5. Click the Save button.

Figure 6 - Save button

Add the Ingredients Form

The Ingredients form will have the Category field and this field will reference the Categories form to get the values for the lookup.

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

Figure 7 - Main Data Source field

2. Click the Next button.

Figure 8 - 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 9 - Form fields

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

Figure 10 - Save button

info

To add a lookup in the Forms view, navigate to the field records. Select the field, include it in the list, select _Lookup in the Display Type field and select the form to reference in the Reference Form field. Shown in the image below.

Figure 11 - Add a _Lookup display type

_Lookup in an Application

Categories can be added on the Categories form.

Figure 12 - Categories form

A category that the ingredient will belong to can then be selected in the lookup on the Ingredients form.

Figure 13 - Ingredients form

Add a Dependent Field with a _Lookup

A field on a form can be dependent on the value selected in another field. When a value is selected in the dependent field, the field that relies on the dependent field will only display the values in the lookup that relate to the selected value.

Let's take a look at an example. In the Soul Food application, some ingredients have been added. The cheese and milk ingredients belong to the dairy category, and the apples, bananas, and oranges ingredients belong to the fruit category.

Figure 14 - Ingredients list

When we come to the Recipes form to add a new recipe and select the recipe ingredients for it, we first need to select a category. Once a category is selected, we can add an ingredient, however, our ingredient lookup is showing us all the ingredients, not just the ingredients for the selected category.

Figure 15 - Unfiltered ingredient lookup

In Five, we can make the Ingredient field dependent on the Category field, the list will be filtered to only display the ingredient values that belong to the selected category as shown in the image below.

Figure 16 - Filtered ingredient lookup

Back in Five, the Recipe and RecipeIngredient tables are now added into the database. For this example, the RecipeIngredient table has foreign relationships with the Recipe, Ingredient, and Category tables and the Ingredient table has a foreign relationship with the Category table.

Figure 17 - Database model

Add the Recipes Form

For this scenario, two forms need to be created to establish the lookups and add a dependent field. The first form we will add is the Recipes form.

1. Select Visual in the menu.

2. Select Form Wizard in the sub-menu.

Figure 18 - Form Wizard menu item

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

Figure 19 - Main Data Source field

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

Figure 20 - Save button

Add the Recipe Ingredients Form

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

2. Click the Add Menu Item switch.

info

The Add Menu Item switch is turned off as the Recipe Ingredients form will become a list page on the Recipes form.

Figure 21 - Main Data Source field and Add Menu Item switch

3. Click the Next button.

Figure 22 - Next button

4. Click the lookup icon in the Dependent field for the IngredientKey field and select CategoryKey.

info

The Ingredient field will now depend on the value selected in the CategoryKey field.

5. Click the List checkboxes for any extra fields you want to include in the list.

Figure 23 - Dependent field

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

Figure 24 - Save button

info

To add a dependent field in the Forms view, navigate to the field records. Select the field, include it in the list, select _Lookup in the Display Type field, select the form to reference in the Reference Form field, and select the field in the Dependent Field field. Shown in the image below.

Figure 25 - Add a dependent field

Edit the Recipes Form

The Recipes Form needs to have the Recipe Ingredients page added as we did not add a menu item for it.

1. Select the Recipes record in the list.

2. Click the Pages tab.

Figure 26 - Pages tab

3. Click the Add Pages button.

Figure 27 - Add Pages button

4. Type Recipe Ingredients in the Caption Field.

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

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

Figure 28 - Add the Recipe Ingredients page

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

Figure 29 - Save button

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

Figure 30 - Save button

Dependent Field in an Application

Once a category is selected only the ingredients belonging to that category will be available in the ingredient lookup.

Figure 31 - Filtered ingredient lookup

Add a _Lookup Without a Database Field

You can reference another form without a foreign relationship in the database. You may want to do this to filter data or calculate fields. This is done by referencing the form at the form-level and not selecting a field in the Field field. This enables you to use the lookup to filter data, however, not have a foreign relationship established in the database.

The ShoppingList and ShoppingItem tables are now added in the database. For this example, the ShoppingItem table has foreign relationships with the ShoppingList and Ingredient tables. The RecipeIngredient table has foreign relationships with the Recipe, Ingredient, and Category tables and the Ingredient table has a foreign relationship with the Category table.

The Category table has no relationship with the ShoppingItem table, however, we will put a Category field on the Shopping Items form to filter the ingredients that belong to the selected category. This is done through referencing the Categories form and not referencing a field in the databae.

Figure 32 - Database model

Form Setup

The Shopping Lists form has two pages. The second page is the Shopping Items list that references the Shopping Items form.

Figure 33 - Shopping Lists form

The Shopping Items form has six fields, including the CategoryKey field and the IngredientKey field.

Figure 34 - Shopping Items fields

The CategoryKey field does not reference a field in the database, ie, no field is selected in the Field field. It has a display type of _Lookup and references the Categories form, this is how we get the values for the category lookup. Whatever value is selected in Category field on the Shopping Items form will not get saved to the database as it does not reference a database field, however, it is being used to filter the data.

Figure 35 - CategoryKey field

The IngredientKey field is dependent on the CategoryKey, so dependent on what value is selected in the CategoryKey field is what will be displayed in the ingredient lookup.

Figure 36 - IngredientKey field

Lookup Without a Database Field in an Application

In the Soul Food application a category can be selected to filter the ingredient list. The value selected in the Category field will not be saved in the database, it is purely used to filter the ingredients that belong to the category.

Figure 37 - Filtered ingredient lookup