Switch to: V9V8V7V6V5

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:

All three of these methods are covered in this tutorial.

These example lessons make use of a version of the well known Sakila database, the successor to Microsoft's Northwind database. While in this lesson we use a version of Sakila adapted to Valentina DB, the skills are transferable to any other database.

Archives

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

This archive version of the 'sakila' database with Foreign Key links, download and un-compress it.

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

Preparation

Open an existing local project.

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

Case 1: Related records using the link

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

Step 1: Duplicate Form

  • 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.

Step 2: Add TableView

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

Step 3: Execute Form

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

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

Case 2: Related records using the map of fields

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.

Step 1: Duplicate Form

  • 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.

Step 2: Map Fields

  • 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:

Step 3: Execute Form

  • 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.

Case 3: Related records using the map of parameters

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.

Step 1: Create Query

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

Step 2: Duplicate Form

  • 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.

Step 3: Change Source

  • Select the TableView control and edit the Source property:

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

Step 4: Map Parameters

  • 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:

Step 5: Execute Form

  • 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.