_LookupQuery
Last updated 20/12/2024
Overview
This documentation will demonstrate how to use Five's _LookupQuery display type. _LookupQuery enables you to attach a query to a field. The results of this query can be used as the lookup values in the field. In its minimal form a _LookupQuery display type must have:
- A primary key field to have uniqueness
- A value field to display in the lookup
When you select _LookupQuery, a new field will become available called Lookup Data Source. This will require you to select a query that will be used to display the values you want in the field's lookup. The values will be generated from your query.
You can extend your query and provide metadata in a _LookupQuery to return results that can be used in conjunction with a function, or to filter the returned results.
Let's first take a look at an example of a _LookupQuery in its simplest form. In the Soul Food application, a contributor can be added to the application by adding their name.
When coming to the Recipes form, the person who added the recipe can be selected from the Contributor field, these values are coming from a query that is attached to the field. There is no foreign relationship between the Recipe and Contributor tables, hence, a _LookupQuery can be used to populate the values for the lookup in the Contributor field.
Back in Five, the Contributor table has no relationship with any other table in the database. A field has been added to the Recipe table called Contributor.
Form Setup
The Contributors form has been added which references the Contributor table and has the Name field.
The Recipes form has a field called Contributor, which is currently just a text field. Effectively, if we were to run the app now, there would be no lookup in the field and only text could be entered into the field.
Add a _LookupQuery Display Type
The query for the _LookupQuery needs to be added first and the field we need to edit is the Contributor field on the Recipes form. In the lookup we want the name of each the contributors to be listed.
Add the Query
1. Select Data in the menu.2. Select Queries in the sub-menu.
3. Click the Add Item button.
4. Type an ID in the Data Source ID field.
5. Click in the Query field to open Five's Query Builder.
6. Click the Add Table button.
7. Select a table.
8. Double click on the table's primary key field and the field with the value you want to display in the lookup.
9. Click the Save button in the Query Builder app bar.
10. Click the Save button in the form app bar.
Attach the Query to a Form Field
1. Select Visual in the menu.2. Select Forms in the sub-menu.
3. Select the record in the list.
4. Click the Pages tab.
5. Select the page record.
6. Click the Fields tab.
7. Select the field record.
8. Either click the Edit button in the form app bar or click directly in the Display Type field.
9. Click the lookup icon in the Display Type field and select _LookupQuery.
10. Click the lookup icon the Lookup Data Source field and select your query.
11. Click the Save button in the form app bar.
12. Click the Save button in the form app bar above the list.
Concatenating Fields
Two or more expressions can be concatenated to give the value you want in a lookup. Let's say in the Soul Food application, there are two fields to add a contributor's name, First Name and Last Name.
You can add a query that concatenates expressions together to be displayed in a field when using a _LookupQuery display type. Below is an example on how to do this.
SELECT
ContributorKey,
CONCAT('FirstName', ' ', 'LastName') AS Contributor
FROM
Contributor
With this attached as a _LookupQuery on the Recipe's Contributor field, the full name of the contributor will be available in the lookup.
_LookupQuery with Metadata
You can add metadata and attach the query to a field with Five's _LookupQuery display type. The metadata is then available on the form and can be available with Five's
Let's take a look at an example. In the Soul Food application, on the Ingredients form there is a Notes field that can be used to add information about the ingredient.
When adding shopping items to a shopping list, if additional notes are added for the selected ingredient, a popup will be displayed holding the information. This is done by the
metadata provided to the Ingredient field and adding a function with Five's
Back in Five, the Notes field has been added to the Ingredient table.
Form Setup
The Notes field has been added to the Ingredients form.
The Shopping Items form is a list page on the Shopping Lists form.
The Ingredient field on the Shopping Items form is currently a lookup and references the Ingredients form. This is the field that will be edited to add a _LookupQuery.
Add a _LookupQuery with Metadata
A function will use the metadata provided by the query, because of this the following steps need to be performed:
- Add the query
- Add the function
- Attach the _LookupQuery display type
- Attach the function to an event
Add the Query
1. Select Data in the menu.2. Select Queries in the sub-menu.
3. Click the Add Item button.
4. Type an ID in the Data Source ID field.
5. Click in the Query field to open Five's Query Builder.
6. Click the SQL tab.
7. Add your query.
SELECT
Ingredient.IngredientKey AS IngredientKey,
Ingredient.Name AS Name,
Ingredient.Notes AS Notes
FROM
Ingredient
WHERE
CategoryKey = ?
8. Click the Save button in the Query Builder app bar.
9. Click the Parameters tab.
10. Click the Add Parameters button.
11. Type an ID in the Parameter ID field.
12. Type the parameter in the Parameter field. Example
13. Click the Save button in the form app bar.
14. Click the Save button in the form app bar above the list.
Add the Function
1. Select Logic in the menu.2. Select Functions in the sub-menu.
3. Click the Add Item button.
4. Type an ID in the Function ID field.
5. Click in the Code field to open Five's Code Editor.
6. Type your code.
function DisplayNotes(five, context, result) {
const notes = five.getMetadata('IngredientKey', 'Notes');
if (notes) {
five.alert(notes);
}
return five.success(result);
}
7. Click the Save button in the Code Editor app bar.
8. Click the Save button in the form app bar.
Attach the _LookupQuery and the Function
1. Select Visual in the menu.2. Select Forms in the sub-menu.
3. Select the record in the list (Shopping Items).
4. Click the Pages tab.
5. Select the page record.
6. Click the Fields tab.
7. Select the field record (Ingredient).
8. Either click the Edit button in the form app bar or click directly in the Display Type field.
9. Click the lookup icon in the Display Type field and select _LookupQuery.
10. Click the lookup icon in the Lookup Data Source field and select your query (IngredientLookupQuery).
11. Click the Events tab.
12. Click the lookup icon in the On List Select field and select your function (DisplayNotes).
13. Click the Save button in the form app bar.
14. Click the Save button in the form app bar above the list.
_LookupQuery in the Form Wizard
If you know you want to use the display type _LookupQuery on creation of your form while using the Form Wizard, select _LookupQuery in the Display Type field. To add the query, you will need to edit the form record as documented above to add the data source ID for the query.
