Lesson 2 - Related Records in the TableView

In this lesson, you will learn how to create a form to display records related to the current record within a grid.

A form created in the previous lesson shows one record from the “customer” table at a time. In this lesson, you will display the payments of the customer using the TableView control.

There are three ways to define how the TableView control can find the related records:

• Using the database link (Foreign Key, ObjectPtr, Binary Link)
• Using the map of fields
• Using the map of parameters

All three of these methods are covered in this tutorial.

• Sakila - Here you can find archives of 'sakila' databases for Valentina DB.

• Project from the previous lesson, download and uncompress it.

Open an existing local project.

• On the Start Page click Open Project → Local… and select the downloaded project on disk.

In this part, we will use an existing database link (foreign key) between two tables to find the set of related records.

• On the project tab right-click the “simple_form” item and in the context menu select Duplicate.

• Using the context menu rename the duplicated form to “related_records_tableview_link”.

• Double-click the new form to open it for editing.

To show the related records a TableView control will be used.

• Drag TableView from the palette to the place above the navigation controls.

• In the shown dialog select the “payment” table as a source

As you can see the Link property now contains the name of the used link:

• In the context menu of the TableView click Select Fields… item

• Uncheck unnecessary field, it's “customer_id” that we already have in the form

• Adjust the size of the form to fit all columns

• Click Execute, then press the Previous and Next buttons.

The grid displays the records related to the current record of the form.

The map of fields defines the correspondence between fields of the form and fields of the TableView control. Only those records are shown in the TableView for which the values of the mapped fields match.

The map of fields is useful if there are no links in the database or if the records source is a view or a query.

• Switch to the project tab, right-click the “related_records_tableview_link” form and in the context menu select Duplicate.

• Using the context menu rename the duplicated form to “related_records_tableview_map_fields”.

• Double-click the new form to open it for editing.

• Select the TableView control

The map of fields will be used instead of the link, so it is necessary to clear the Link property.

• Click the Clear button in the Property Inspector for the Link property:

• Edit the Map Fields property:

• Set the correspondence between the fields named “customer_id” from the “payment” and “customer” tables:

A new value for the map now is shown in the Property Inspector:

• Click Execute, then press the Previous and Next buttons.

The grid displays the records related to the current record of the form, just like in the previous part.

The map of parameters can be used when the source of the TableView is a query with parameters. This way is more flexible than the previous because allows the user to define an exact query that will be executed.

Such query contains the special parameter placeholder, $p(param_name), which is replaced with the actual values during the form execution.

The map of parameters defines the correspondence between the parameter and the field in the form.

At first we must create a query which will be the source for the TableView.

• On the project tab click Create→Query

• Input the name “Query_PaymentsOfCustomer”

• Switch to parameters tab and create an integer parameter pCustomerId, the default value is 1:

• Switch to the query text tab and input the query:

• Click Insert Parameter and in the list select created parameter:

The query is completed with the parameter now:

SELECT * FROM payment WHERE customer_id = $P(pCustomerId)

• Switch to the Test tab and check that it is executed correctly:

• Click Create

You can see a new query on the project tab

• On the project tab right-click the “related_records_tableview_link” item and in the context menu select Duplicate.

• Using the context menu rename the duplicated form to “related_records_tableview_map_parameters”.

• Double-click the new form to open it for editing.

• Select the TableView control and edit the Source property:

• Switch to the Queries tab and select the query created in the first step:

• Edit the Map Parameters property:

• Type “customer_id” to map field of the form to the “pCustomerId” parameter of the query:

Now the map of parameters is defined:

• Click Execute, then press the Previous and Next buttons.

The grid displays the records related to the current record of the form, just like in the previous parts.

Not sure you did it right? You can find a completed version of the project from this lesson here.