Skip to main content

Working with Parameters

Last updated 13/04/2022

Defining a Parameter

In Five, there are two types of parameters you can work with:


ParameterDescription
ConstantA constant is a fixed value and does not change over time.
ExpressionAn 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.


No Parameters Defined
Figure 1 - No parameters defined

Add a Constant Parameter

A constant parameter will return a specific data value.


info

Four tables will be selected to be joined to perform this query, this will be explained in more detail when adding an expression parameter.


  1. Select Queries in the menu.

  2. Click the Add Item button in the list app bar.

  3. Type a title in the Title field.

  4. Click in the Query field to open the Table and Fields Designer.


Add a query
Figure 2 - Add a query

  1. Click the Add Table button.

Add Table Button
Figure 3 - Add Table button

  1. Select the required table names to make the query.

Select Tables
Figure 4 - Select tables

  1. Click out of the Table Selector dialog box to close the window.

  2. Select each table and drag to where you would like it to be positioned on the Designer.


Position Tables
Figure 5 - Position tables

  1. For each table, double click the required field names to make the query.

  2. For the field you are setting the parameter, type =? in the Criteria field.

  3. Click the Save button in the Table and Fields Designer app bar.


Add Fields, Parameter Placeholder, and Save the Query
Figure 6 - Add fields, parameter placeholder, and save the query

  1. Click the Fields tab.

Fields Tab
Figure 7 - Fields tab

  1. Click the Parameters tab.

Parameters Tab
Figure 8 - Parameters tab

  1. Click the Add Parameters button.

Add Parameters Button
Figure 9 - Add Parameters button

  1. Type a parameter ID in the Parameter ID field.

  2. Click the lookup icon in the Parameter Type field and select Constant.

  3. Type the constant value in the Parameter field.

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


Add a Constant Parameter
Figure 10 - Add a constant parameter

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

Save the Parameter Form
Figure 11 - Save the form

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.


Covid-19 Record
Figure 12 - Covid-19 record


Query Results
Figure 13 - Query results

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.


Inherited Record
Figure 14 - Inherited record


Query Results
Figure 15 - Query results

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.

Database Model
Figure 16 - Database model

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

  1. Select Queries in the menu.

  2. Click the Add Item button in the list app bar.

  3. Type a title in the Title field.

  4. Click in the Query field to open the Table and Fields Designer.


Add a query
Figure 17 - Add a query

  1. Click the Add Table button.

Add Table Button
Figure 18 - Add Table button

  1. Select the required table names to make the query.

Select Tables
Figure 19 - Select tables

  1. Click out of the Table Selector dialog box to close the window.

  2. Select each table and drag to where you would like it to be positioned on the Designer.


Position Tables
Figure 20 - Position tables

  1. For each table, double click the required field names to make the query.

  2. For the field you are setting the parameter, type =? in the Criteria field.

  3. Click the Save button in the Table and Fields Designer app bar.


Add Fields, Parameter Placeholder, and Save the Query
Figure 21 - Add fields, parameter placeholder, and save the query

  1. Click the Fields tab.

Fields Tab
Figure 22 - Fields tab

  1. Click the Parameters tab.

Parameters Tab
Figure 23 - Parameters tab

  1. Click the Add Parameters button.

Add Parameters Button
Figure 24 - Add Parameters button

  1. Type a parameter ID in the Parameter ID field.

  2. Type an expression value in the Parameter field using {{five.stack.<tableId>.<primaryKey>}}.

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


Add an Expression Parameter
Figure 25 - Add an expression parameter

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

Save the Form
Figure 26 - Save the form

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.


Inherited Record
Figure 27- Inherited record


Save the Form
Figure 28 - Save the form