Chapter 11. Master-Details

Chapter 11. Master-Details

Let's select the companies sheet and add form fields at cell E4.

Add fields for the companies table

Then let's move cell F4, the Company field, to C1 and remove column E. Now cell C1 contains the active company.

Move fields to the top line

 

Now, select cell E3 (outside of the active table) and connect to the dbo69.Payments table.

In the connection wizard, check at least the field used as a filter (Company) in the WHERE column:

Connect to the payments table and check WHERE for the company field

and insert the table at cell E3:

Insert a new connected table into Microsoft Excel

We see that the dbo69.Payments table has ribbon parameters including Company.

Also, we see the selected company in cell C1 that may be used as a parameter. Let's link them.

 

Select cell C1 and click the Define Name button in the Defined Names group on the Formulas tab:

Use the Define Name button

Specify the name as the parameter name, Company, and select the sheet name, companies, in the Scope field:

Create a named cell with the ribbon parameter name

Click OK. Then select a row in the Company table, and voilà!

Sample of master-details in Microsoft Excel

 

When you select the detail table, you may see its parameters at the ribbon:

The details table has a parameter that reads a value from the named cell

Both tables are editable.

 

Here is a list of events that implement master-details:

  1. A user selects another row in a master table.
  2. The add-in updates form fields (cell C1 with Company).
  3. The add-in changes parameters of the detail tables using named cell values (cell C1 as Company).
  4. The add-in reloads the details with new parameter values.

 

You may build more complex forms using the same technique.

For example, the Northwind example contains the following tables with master-detail relations:

  1. Customer first char index (A-Z)
  2. Customers
  3. Customer orders
  4. Orders
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK