_Lookup
Last updated 26/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.
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.
3. Click the lookup icon in the Main Data Source field and select Category.
4. Click the Next button in the form app bar.
5. Click the 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.2. Click the Next button.
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.
3. Click the Save button in the Form Wizard app bar.
_Lookup in an Application
Categories can be added on the Categories form.
A category that the ingredient will belong to can then be selected in the lookup on the 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.
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.
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.
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.
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.
3. Click the lookup icon in the Main Data Source field and select Recipe.
4. Click the Save button in the Form Wizard app bar.
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.
3. Click the Next button.
4. Click the lookup icon in the Dependent field for the IngredientKey field and select CategoryKey.
5. Click the List checkboxes for any extra fields you want to include in the list.
6. Click the Save button in the Form Wizard app bar.
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.
3. Click the 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.
7. Click the Save button in the form app bar.
8. Click the Save button in the form app bar above the list.
Dependent Field in an Application
Once a category is selected only the ingredients belonging to that category will be available in the 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.
Form Setup
The Shopping Lists form has two pages. The second page is the Shopping Items list that references the Shopping Items form.
The Shopping Items form has six fields, including the CategoryKey field and the IngredientKey field.
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.
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.
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 just to filter the ingredients that belong to the category.