Working with Parameters
Last updated 13/04/2022
Defining a Parameter
In Five, there are two types of parameters you can work with:
| Parameter | Description |
|---|---|
| Constant | A constant is a fixed value and does not change over time. |
| Expression | An expression is a value that is created by combining one or more five or form properties to return a specific set of data results. |
Parameterized Queries
A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. Five uses the symbol ? as the placeholder for a parameter.
We can hardcode a parameter or create an expression parameter using Five's API to increase the reuse of execution plans.
Example
The following example is to walk you through the result for having no parameters, having a constant parameter, and using an expression interacting with Five's API.
The layout we'll be working with is as follows:
A form called Classification, on this form the name of the classification can be entered.
From the Classification record, we can drill down to the Classification Types query to see what type of classification each patient has.
If you wanted to find all the classifications for each patient, you could start with a non-parameterized query that just pulls up that data.
Add a Constant Parameter
A constant parameter will return a specific data value.
Four tables will be selected to be joined to perform this query, this will be explained in more detail when adding an expression parameter.
Select Queries in the menu.
Click the Add Item button in the list app bar.
Type a title in the Title field.
Click in the Query field to open the Table and Fields Designer.
- Click the Add Table button.
- Select the required table names to make the query.
Click out of the Table Selector dialog box to close the window.
Select each table and drag to where you would like it to be positioned on the Designer.
For each table, double click the required field names to make the query.
For the field you are setting the parameter, type
=?in the Criteria field.Click the Save button in the Table and Fields Designer app bar.
- Click the Fields tab.
- Click the Parameters tab.
- Click the Add Parameters button.
Type a parameter ID in the Parameter ID field.
Click the lookup icon in the Parameter Type field and select Constant.
Type the constant value in the Parameter field.
Click the Save button in the form app bar.
- Click the Save button in the form app bar above the list.
With the example we are using, we drill down from the Classification form to the Classification Types query. Because we have hardcoded the parameter value this will never change.
For Example, if we select the Covid-19 record and drill down to the query results we would just see the patients with the classification of Covid-19.
However, if we select the Inherited record and drill down to the report we would also just see the patients with the classification of Covid-19.
By using an expression and interacting with Five's API we can dynamically change the results dependent of which classification record is selected.
Expression Parameter
If you want to use the above query repeatedly to get the data for different classifications, you can add an expression to dynamically return the results using the five.stack
property.
five.stack.<tableId>.<primaryKey> enables you to work at database-level to return the primary keys of the associated tables for the forms stacked above the current action. These keys can be used to filter the records that are associated with the parent form.
Example
The tables Classification, Diagnosis, Appointment, and Patient have the following relationships.
- A patient can have many appointments.
- An appointment can have many diagnosises.
- A classification can have many diagnosises.
The tables Patient and Classification do not directly have a relationship between each other, however, they can be joined together using the foreign relationships established between the Classification and Diagnosis tables, and the Diagnosis and Appointment tables, and the Appointment and Patient tables. When we select all four of these tables in the Designer, Five will automatically create the joins so we can have access to the patient details to get a patient's first and last names.
By using five.stack.Classification.ClassificationKey as the parameter on the current form (Classification) when we drill down from this form the query results will dynamically
populate the records for the current record.
Add an Expression Parameter - five.stack
Select Queries in the menu.
Click the Add Item button in the list app bar.
Type a title in the Title field.
Click in the Query field to open the Table and Fields Designer.
- Click the Add Table button.
- Select the required table names to make the query.
Click out of the Table Selector dialog box to close the window.
Select each table and drag to where you would like it to be positioned on the Designer.
For each table, double click the required field names to make the query.
For the field you are setting the parameter, type
=?in the Criteria field.Click the Save button in the Table and Fields Designer app bar.
- Click the Fields tab.
- Click the Parameters tab.
- Click the Add Parameters button.
Type a parameter ID in the Parameter ID field.
Type an expression value in the Parameter field using
{{five.stack.<tableId>.<primaryKey>}}.Click the Save button in the form app bar.
- Click the Save button in the form app bar above the list.
With the example we are using, we drill down from the Classification form to the Classification Types query. Because we have created an expression using five.stack the results
will dynamically change dependent on which classification record is selected.
For example, if we select the Covid-19 record and drill down to the report we will just see the patients with the classification of Covid-19.
And if we select the Inherited record and drill down to the report we will just see the patients with the classification of Inherited.
