How to: Extend Master - Detail to additional levels

[alastairwalker]

I have a situation where I need to extend the Master - Detail table scenario to additional levels e.g.

Parent -> Master -> Detail. (and potentially to -> sub-detail as well).

The challenge is with creating the joins between the tables, and the order in which these should happen.

Assuming I start with creating a new List, and then adding a table (say Parent), and then create the following joins: (also assuming that the following tables already exist:

Parent:
ID
date_time
Label

Master:
ID
date_time
ID_Parent
Label

Detail:

ID
date_time
ID_Parent
ID_Master
Label

The tables are then joined as follows:

a) Parent joined to Master (ID in Parent table, linked to ID_Parent in Master table); then
a) Master joined to Detail (ID in Master table, linked to ID_master in Detail table).

So far - so good.

When I add a Detail record, by virtue of a Related Data link to an Add Detail record, the field id_parent is correctly populated during the Add, but I cannot seem to get the id_master value to be populated as well (into the Detail record.)

To sum up: I want to be able to create a Detail record, with both ID_Parent and ID_Master correctly populated.

Should I be following a different strategy in setting up the list? Should the joins be constructed differently?
Should there be additional joins?

Is it, in fact, possible to achieve what I am trying to do?

Any comments will be really appreciated!

Alastair