This example shows how a foreign key type relationship can be configured between two reference data tables. A column in one data table will hold key values that refer to instances in the other data table.
- Additionally, an example is provided that shows how two reference data tables can be used to populate cascading drop-downs using a property to hold linked class keys to instances of another class.
Related Posts
- Example of Pega 7 Auto-Complete Control Backed by Data Table
- Source Dropdown Options from a Data Page and Group by a Property in Pega 7.1.9
- Using a Data Page for a Dropdown and Sorting the Options with a Data Transform in Pega 7.1.9
Summary
- Use Case Example with Two Cascading Drop-Down Controls in Pega 7
- Create Data Tables to Populate Drop-Down Options in Pega 7
- Configuring Cascading Drop-Downs in Pega 7
- A form shows 2 drop-downs, one that allows a user to select a car make such as
Honda
orAudi
(parent) and a dependent drop-down that allows to select a model for that car make (child), such asAccord
whenHonda
is selected. - Similarly, when
Audi
is selected in the Make drop-down,A4
,A5
,A6
and so on should be displayed in the child Model drop-down. - Every time the parent drop-down changes, the child drop-down should be updated automatically to show the models for the selected car make.
- The form with the 2 drop-downs that will be built in this example will look like the one in the screen shot below.
- In the screen shot,
Honda
has been selected in the Make drop-down and the Model drop-down shows all available models linked to the selected make.
- One data table will hold the options for the Make drop-down and another data table will hold the options for the Model drop-down. In this example, the two data tables are named as follows:
ABC-Insurance-Data-Make
for the Make drop-down (parent)ABC-Insurance-Data-Model
for the Model drop-down (child)- A data table can be created by navigating to DesignerStudio > Data Model > Data Tables > Data Tables.
- The Data Tables view lists all available data tables. A new data table can be added by clicking on the button Add new Data Table.
- The Data Table Wizard dialog for creating the
ABC-Insurance-Data-Make
data table is shown below. Note that it derives fromABC-Insurance-Data
and a lookup data page namedD_Make
and a list data page namedD_Makes
will be automatically generated. - In addition, a dedicated database table is created by checking CREATE DEDICATED DATABASE TABLE?.
- This will cause Pega to create and use a separate dedicated DB table for storing instances of
ABC-Insurance-Data-Make
and allows for migration of the data when exporting/importing.
- The
ABC-Insurance-Data-Make
class has two properties:
id
– unique key, of type Identifier
, has to start with letter and can contain hyphens and numbers: e.g. M-1
pyLabel
– of type Text
, inherited from ABC-Insurance-Data
, used to store the car makes: e.g. Honda
- Insert Make Records: The cog-wheel icon in the edit column of the
ABC-Insurance-Data-Make
data table can be used to create records.
- In this example, 5 car make records have been inserted into the table.
- The plus icon in the lower right hand corner can be used to add more instances.
- Since CREATE DEDICATED DATABASE TABLE was selected in the Data Table Wizard, a separate database table was created to store the instances of
ABC-Insurance-Data-Make
. - To get the table name, open the
ABC-Insurance-Data-Make
class and click on the Test Connection button.
- A new window will show the Pega database schema name and the table name. In this case, it is
pegadata.pr_ABC_Insurance_Data_Make
.
- The data can easily be viewed in the Pega database -in this case PostgreSQL is used- by using a tool such as pgAdmin.
- Refer to this post on using pgAdmin to view the Pega DB: Example of Connecting to Pega 7 PRPC PostgreSQL DB with pgAdmin.
- In the pgAdmin tool, navigate to the schema
pegadata
and locate the tablepr_abc_insurance_data_model
. - Right click on the table name and select View Data > Top 100 rows.
- For the second drop-down, a second data table called
ABC-Insurance-Data-Model
has been created with the Data Table Wizard. The summary is shown below.
- The
ABC-Insurance-Data-Model
class has three properties:
id
– the unique key, of type Identifier
, e.g. M-001
pyLabel
– inherited from ABC-Insurance-Data
, used for actual model value such as Civic
make
– a reference of type Identifier
to an instance of ABC-Insurance-Data-Make
- After creating the Model data table, the class
ABC-Insurance-Data-Model
needs to be configured further so that theMake
property will hold valid class key values, pointing toABC-Insurance-Data-Make
instances. - Using the App Explorer, the
make
propery of theABC-Insurance-Data-Model
class needs to be opened as edited.
- In order to link the values of the
make
property of theABC-Insurance-Data-Model
class toABC-Insurance-Data-Make
instances, the option Automatic reference to class instance (linked) needs to be selected. - In this example, the linked class is
ABC-Insurance-Data-Make
and the source property is.make
, which is linked to theid
property of theABC-Insurance-Data-Make
class. - In addition, under Display and Validation, select
pxDropdown
and Class Key Value for table type. - The Validation Class needs to be set too, in this case it needs to be
ABC-Insurance-Data-Make
, so that only valid IDs (keys) of that class can be set in.make
property of theABC-Insurance-Data-Model
class instances.
- Use the Designer Studio navigation to return to the Data Tables view.
- Insert Model Records: Click on the cog wheel icon for the car models class name (ABC-Insurance-Data-Model) to insert reference data.
- Insert references for car models and link them to
ABC-Insurance-Data-Make
instances by using valid class key values values such asM-3
to refer to the car makeAudi
. - PRPC will ensure that the make property can only hold valid
ABC-Insurance-Data-Make
class keys. - If a developer tries to insert a model instance where the make value does not refer to an existing
ABC-Insurance-Data-Make
instance, an error message will be displayed, i.e. referential integrity is enforced by PRPC. - In the below screen shot,
M-9
is not a valid class key in theABC-Insurance-Data-Make
data table, and a class key validation error is shown.
- The class key validation will be performed everytime the
make
property ofABC-Insurance-Data-Model
is set in the PRPC system (e.g.: data transfor, activity).
- In a Section rule, add a new drop-down control. In this case, the parent drop-down will be the reference for car makes.
- When a car make is selected, a second drop-down will be automatically updated to shows all models for the selected car make.
- Here, the Section rule applies to the
ABC-Insurance-Work-Auto
work object class.
- Click on the cog wheel icon to configure the drop-down control for car makes.
- Set the Property to
.Make
, a property of the work object class. It will hold the selected car make key. - On the General tab, configure the drop-down List Source as follows:
Type:
Data Page
Data page:
D_Makes
– The list data page that was created by the Data Table Wizard in step 1.Property for value:
.id
– the unique identifier of the ABC-Insurance-Data-Make
class.Property for display text:
.pyLabel
– the text to show in the drop-down list, e.g.: Honda
.- In this example, the load behavior is set to
At screen load (Default)
. - Click on OK and save the section rule. Then test it by clicking on Actions > Preview.
- The car make drop-down should be properly populated with the car make values as per the underlying data table as shown below.
- Before adding the second drop-down that will show the car model options based on the selected make, the Report Definition of the
ABC-Insurance-Data-Model
class needs to be modified in order to allow an input parameter for filtering the car models by make. - In the Application Explorer, open the
dataTableListReport
ofABC-Insurance-Data-Model
.
- Navigate to the Parameters tab of the Report Definition and add a text parameter called
MakeKey
as shown below. - This parameter will hold the value the user selects in the make drop-down field, which represents the unique key of a selected car make instance.
- Next, navigate to the Query tab of the Report Definition and add a filter condition.
- The filter condition will compare the input parameter
MakeKey
, which will hold a car make key to the linked class key.make
in theABC-Insurance-Data-Model
data table so that the report only returns instances where the make keys match. - Make sure to Save the changes to the Report Definition. A warning message will indicate that the
.make
and.id
properties are not optimized, resulting in poor performance. - They can be optimized using the Application Explorer. For details on how to optimize the 2 properties, refer to this post: Example of How to Create a Report Definition in Pega 7.
- It is a good idea to test the report before continuing. To do so, click on Actions > Run. By default, the report will run without any filters applied and all instances will be shown.
- The filter can be tested by clicking on the link next to the label Filters:. A new dialog allows to specify the value for the filter, in this case the key of a car make instance. When set to M-1 (Honda), the report will only show car model instances for that car make.
- Next, open the data page
D_Models
for the car models that was automatically generated by the Database Table Wizard and navigate to the Parameters tab. Add an input parameter, in this case calledMakeKey
, of data type String and set REQUIRED toYes
.
- On the Definition tab, make sure that Report Definition is selected for the data source and that the correct report name is used. Click on the Parameters link underneath the text fiel for the report name and tick the checkbox Pass current parameter page. This option will pass the input parameter
MakeKey
of the data page to the specified report definition, where it will be used for the filter condition.
- Save the changes to the data page. The data page can be tested by clicking on Actions > Run.
- The initial dialog shows the required
MakeKey
input parameter. Enter a valid value and click on Execute.
- An XML representation of the data page is shown and the matching car model instances can be seen in the
pxResults
structure.
- Finally, open the UI section rule and add a second drop-down for the car model options.
- Click on the cog wheel icon to configure the car model drop-down control.
- On the General tab, configure the List Source as follows:
Property:
.Model
– A propery of ABC-Insurance-Work-Auto
to store the selected car model key.Type:
Data page
Data page:
D_Models
– The list data page that was created by the Data Table Wizard in step 1.Parameter:
.Make
– Property of the work object class holding the make key, set when selecting an option from the first drop-down.Property for value:
.id
– The unique identifier of the ABC-Insurance-Data-Model
class.Property for display text:
.pyLabel
– The text to show in the drop-down list, e.g.: Civic
.- For Load behavior, select
After screen renders
, so that the options will be loaded when the make drop-down is rendered. - In addition, check the checkbox
Enable Caching of Options
to improve performance by preventing unnecessary loading of the data page and execution of the underlying report definition. - Finally, the UI section and the cascading drop-downs can be tested by clicking on Actions > Preview.
- Whenever the Make drop-down is changed, the Model drop-down will automatically load the linked car model options.
- Additional drop-downs can be added following the same steps. Here, a third drop-down could be added to show trim options such as
3.0T Quattro
whenever a model is selected.
helloteam,
Could you able to help me doing same usecase in pega 7 please ,
Hello,
this example was done in Pega 7.1.6. What problem do you have?
Regards