Main view & sub view (Notion)

In Microsoft Access, there is something like a main form and a sub form. E.g., concerning orders, the main form contains the order heading and order totals and the sub form would contain the order lines. I guess that in Notion, this would rather be called main view and sub view, or main database and sub database.

But how would you build such a thing?

How this worked in Microsoft Access

Around the turn of the century, I have had extensive experience with building these kinds of forms on Microsoft Access, hence why I use it as reference. If I remember correctly, there were basically two ways of doing this:

Linked subform data source?

If I remember correctly, when you insert a subform (a form is just a windows) on a mainform, and you specified the source for this subform, I though you could directly link it to the source of the main form.

Filter on the content of a control

The other approach, that I'm more sure about: The data source for the subform had a filter. The input for that filter, was a value in a control on the main form. A control is a field on that form.

The subform query would be something like this:

select
   *
from
   order_lines
where
   order_id = MyMainForm.textbox_order_id.value

This is actually a whole different approach from what I have seen so far in Notion (or in MySQL): It incorporates the stuff that is actually being displayed at any given moment as valid objects to work with. This is quite a monumental stap from a pure database point of view: The user interface gets factored in. In MySQL, I would never come across something like this, for MySQL is concerned with the storage layer in a three-tier architecture. This would be something that would play a role in the top two tiers: Business logic and interface. It would be more something to do in PHP or Python.

Does Notion cross this gap? Not that I know of (a cursory check on formulas didn't look promising). But I love to be proven wrong here.

Peek view with a form?

This seems to be the solution, with the order header at the top and the order lines below it. But no. The problem is here, that the content in the board view at the bottom, is hard-coded: It filters records that correspond with the header. And that's the problem: It would mean that for every order, a separate form would have to be created, with this hard-coded filter

Board view & grouping?

• Grouping (columns) would be order-ID
• Subgrouping (rows) would be order lines

Problem: Where to put the order header (including orde totals)?

Peek view & backlinks?

• Have an order header peek view, including backlings
• Click on a backling to see the orderline

That doesn't sound very good: I think I would like order lines and orders on the same screen