Master Detail

Master-detail forms are used to display one-to-many and many-to-many relationships between entities. In a one-to-many relationship, one record can have multiple related records - for example, an order has many line items, or a patient has many medications. In a many-to-many relationship, records on both sides can have multiple connections - for example, a student can enroll in many courses, and each course can have many enrolled students.

A one-to-many relationship is implemented using two database tables, with a foreign key in the “many” table referencing the primary key of the “one” table. For example:

In the Orders/LineItems relationship, each record in the LineItems table has an OrderId field that references the Id field in the Orders table
In the Patient/Medications relationship, each record in the PatientMedications table has a PatientId field that references the Id field in the Patients table

A many-to-many relationship requires three database tables. In addition to the two tables that store the main entities, a junction table holds connections between the entities. For example:

For Students/Courses, a StudentCourses table would have both a StudentId and a CourseId
Each record in StudentCourses represents one student enrolled in one course
A student enrolled in three courses would have three records in the StudentCourses table
A course with twenty students would have twenty records in the StudentCourses table

Note

Relationships between tables must be set up in the database in order for Master Detail forms to work. You can use the Database tab of Visual DB to add relationships between tables.

How Master-Detail Forms Work

In a master-detail form, fields from the master record (the “one” side in a one-to-many relationship) are laid out like a regular form. Detail records (the “many” side) are displayed at the bottom of the form in a grid.

Visual DB uses two queries to display a master-detail form: one for the master records and one for the detail records. The details query must have exactly one query parameter. When executing this query, Visual DB passes the primary key of the master record as the parameter value, and the query retrieves only the detail records that correspond to the current master record.

You can use the details grid to insert, update, and delete detail records. Visual DB commits changes to the master record and detail records as a single transaction.

Note

A transaction is a sequence of related data changes to a database that are treated as a single atomic operation. All changes must either complete successfully together or none occur, which prevents data inconsistencies like a purchase order getting created without its order items.

Building a Master-Detail Form

The first step to build a master-detail form is to build the master part of the form. This step is identical to building a regular form. Start by creating a query that uses the master records’ database table as the main table. Additional related tables may be added to the query if needed. Then create a form using this query.

Next, build a query for the detail records. Create a query that uses the detail records’ database table as the main table. Additional related tables may be added to the query if needed. This query must have a parameter. To add one, click on ‘Add new condition’ in the query builder. In the condition, select the foreign key field that links to the master records. Leave the comparison as ‘is equal to’. Click the triangle to the left of the condition, select ‘Prompt user for value’. Then click OK to close the Query Builder dialog.

To add the details grid to the form, open the form and enter design mode. Click ‘Add line items table’ in the design panel. You’ll see a list of suitable queries - those that query records linked to the master record. (If your query isn’t listed, check that the relationship between the two tables is set up in the database tab.) Select your query, and a grid showing detail records will be inserted into the form.

Finally, configure the details grid. Exit design mode to resize columns as needed. To save the new column widths, return to design mode and select the grid. Use the design panel to rearrrange columns or hide them. Individual columns can be configured using the gear button.

One-to-Many Example

Let’s look at a master-detail form example using patients and their medications. In the database, the patient table has patient_id as its primary key. The medication table has medication_id as its primary key and includes a patient_id column that references the patient table as a foreign key.

To create this master-detail form, first create two queries:

A Patient query that uses the patient table as its main table
A PatientMedications query that uses the medication table as its main table and includes a parameter to fetch medications for a specific patient. To set up this parameter:
- Add a condition to the query
- Select the foreign key column patient_id as the condition field
- Set the comparison to ‘is equal to’
- Click the triangle next to the condition and select ‘Prompt user for value’

Create the Patient form using the Patient query. Enter design mode and click ‘Add line items table’. Select the PatientMedications query, and a grid showing the patient’s medications will be inserted into the form. Use the design panel to configure the grid as needed.

Many-to-Many Example

In a many-to-many relationship, you can view and manage the relationship from either side. For example, in a student registration system, you might want to see all courses a student is taking, or alternatively, see all students enrolled in a course. Each perspective requires its own master-detail form.

Let’s look at both perspectives using students and courses as an example. In the database, the student table has student_id as its primary key, and the course table has course_id as its primary key. A student_course junction table connects these tables, containing both student_id and course_id columns as foreign keys.

Student Perspective (Students and Their Courses):

To create this master-detail form, first create two queries:

A Student query that uses the student table as its main table
A StudentCourses query for managing course enrollments. This query:
- Uses the student_course table as its main table
- Adds the course table as a related table to display course title
- Includes a parameter to fetch enrollments for a specific student
- To set up this parameter:
  - Add a condition to the query
  - Select student_id from the student_course table as the condition field
  - Set the comparison to ‘is equal to’
  - Click the triangle next to the condition and select ‘Prompt user for value’

Create the Student form using the Student query. Enter design mode and click ‘Add line items table’. Select the StudentCourses query, and a grid showing the student’s course enrollments will be inserted into the form.

Course Perspective (Courses and Their Students):

To create this master-detail form, first create two queries:

A Course query that uses the course table as its main table
A CourseStudents query for managing student enrollments. This query:
- Uses the student_course table as its main table
- Adds the student as a related table to display student name
- Includes a parameter to fetch enrollments for a specific course
- To set up this parameter:
  - Add a condition to the query
  - Select course_id from the student_course table as the condition field
  - Set the comparison to ‘is equal to’
  - Click the triangle next to the condition and select ‘Prompt user for value’

Create the Course form using the Course query. Enter design mode and click ‘Add line items table’. Select the CourseStudents query, and a grid showing the course’s student enrollments will be inserted into the form.

Use the design panel to configure either grid as needed.

Joining Additional Tables

When building master-detail forms, you can enhance both master and detail queries by joining to additional tables. This allows you to display relevant information from related tables directly in your forms, making them more useful and informative. Here are some practical scenarios where adding joins is valuable:

In the master query:

When showing a patient list (master), join to an insurance_providers table to display each patient’s insurance company
When showing a course list (master), join to a faculty table to display the instructor’s name and department
When showing an order list (master), join to customers table to show customer contact information

In the detail query:

For patient medications (details), join to a medications_catalog table to show drug information like standard dosage and warnings
For course enrollments (details), join to academic_records to show each student’s major or academic standing
For order items (details), join to a products table to show product descriptions, categories, or current stock levels

The key benefit is that users see relevant related information without having to open separate forms or reports. This is particularly useful when the additional information helps with data entry - like seeing product prices while entering orders, or checking student information while managing course enrollments.