Switch to: V9V8V7V6V5

Lesson 7 - Related Records in the Sub Form

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

The forms created in the lesson 2 show related records in the TableView control. In this lesson, you will display the payments of the customer using the Sub Form control.

Like with a TableView, there are three ways to define how the Sub Form 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 of them will be 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_subform_link”.
  • Double-click the new form to open it for editing.

Step 2: Add TabWidget

The TabWidget control will be used, the first tab will show the main details of the customer and the second tab will show his payments.

  • Drag TabWidget from the palette to the place above all other controls.

  • Drag the Form layout with customer details to the TabWidget area.

A new tab will appear on the tab bar:

  • Double-click the tab to rename it as “Details”.

Now you have the TabWidget with one tab.

Step 3: Add Sub Form

To show the related record a Sub Form control will be used.

  • Drag Sub Form from the palette to tab bar.

  • Select the “payment” table as the source for the sub form.

  • Rename a new tab as “Payments” and adjust the size of the form:

Step 4: Add Sub Form Navigation

If the Sub Form is intended to show only one record (for example, for 1:1 relation) the navigation controls are not necessary.

But in the current case, the Sub Form will show all payments of the current customer, it is 1:M relation, so it is necessary to add buttons to navigate through payments.

  • In the context menu of the payments form layout click Wrap to→BoxLayout Vertical

  • Drag horizontal layout to the place below the payments form.

  • Drag four ToolButton controls to the added layout.

  • Select the layout and align it to the left.

  • In the context menu of the layout select Margins→Remove.

  • Drag the vertical spacer to the place below the buttons layout.

  • In the context menu of the first tool button select Set Action→Table→First Record.

  • Set the “payment” table as a target for this action.

  • Set actions for the rest three buttons: Previous Record, Next Record, Last Record.

  • Modify the Update button script to update also the “payment” table:
link = this.dataSource.getChildObject( OBJECT_TYPE.LINK, 'fk_payment_customer' )
records = this.dataSource.getTable( 'payment' ).getTableCursor( this.uuid, link )
records.updateRecord
 
records = this.table.getTableCursor( this.uuid )
records.updateRecord()

Step 5: Execute Form

  • On the first tab, you can see the details of the customer.

  • Switch to the second tab to view his payments, move between payments using the navigation controls.

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_subform_link” form and in the context menu select Duplicate.
  • Using the context menu rename the duplicated form to “related_records_subform_map_fields”.
  • Double-click the new form to open it for editing.

Step 2: Map Fields

  • Select the Sub Form control on the Inspector tab

  • Edit the Map Fields property:

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

Step 3: Navigation Controls

The navigation controls of the subform still use the link reference in the scripts of the clicked and needUpdate slots.

So it is necessary to modify them.

Each form and subform has the records property to get its source records. This property can be used for navigation through the related records.

The name of the subform with related records is “SubForm”.

  • Edit the slots of the first record button.

clicked

this.SubForm.records.firstRecord()

needUpdate

sender.enabled = this.SubForm.records.position > 0
  • Edit the slots of the previous record button.

clicked

this.SubForm.records.prevRecord()

needUpdate

sender.enabled = this.SubForm.records.position > 0
  • Edit the slots of the next record button.

clicked

this.SubForm.records.nextRecord()

needUpdate

records = this.SubForm.records
sender.enabled = records.position < records.recordCount - 1
  • Edit the slots of the last record button.

clicked

this.SubForm.records.lastRecord()

needUpdate

records = this.SubForm.records
sender.enabled = records.position < records.recordCount - 1

Step 4: Execute Form

  • Click Execute, then press the Previous and Next buttons of the main form and the nested form.

The Sub Form 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 Sub Form 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.

A query “Query_PaymentsOfCustomer” created in the lesson 2 will be used as a source.

Step 1: Duplicate Form

  • On the project tab right-click the “related_records_subform_map_fields” item and in the context menu select Duplicate.
  • Using the context menu rename the duplicated form to “related_records_subform_map_parameters”.
  • Double-click the new form to open it for editing.

Step 2: Change Source

  • Select the Sub Form control in the Inspector and edit the Source property:

  • Switch to the Queries tab and select the query “Query_PaymentsOfCustomer”:

Step 3: 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 4: Execute Form

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

The subform 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.