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 in Notion? → Using 'self-referential filters

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 quite a monumental stap from a 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. Not in SQL.

Ideas

I've found the real solution. Nevertheless, I like to keep these ideas, just to remind myself to be creative.

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

Filter on the value of a control?

This is possible in Notion and it is the actual solution. See separate chapter below for details.

Use a self-referential filter

The solution in Notion is a bit of a hybrid between the two approaches, mentioned for Microsoft Access:

• Create a main view and a sub view (e.g., as peek view templates) that are linked to each other
• In the subform, add a filter through this relation that refers to the main view.

This is the filter on the sub view. It refers to the relation field on the main view. In this case, a value has been given, but that's only for this record. In the template, it refers to Order with Orderlines, which is the name of the template for the main view

More formally: Filters can refer to templates. So, Notion broke through the barrier between the storage layer and business layer of the three-tier application architecture

Lastly: This seems to be indeed called self-reference [1]

See also

• Read a control (Notion) - A related problem that I haven't solved yet: How to read within a main view the value of a rollup-function that resides in a sub view?

Sources

• https://www.reddit.com/r/Notion/comments/eunbsy/use_the_property_of_a_page_to_filter_the_table_in/
• https://www.youtube.com/watch?v=dLbBd-2W3so - Solution!
• https://duckduckgo.com/?q=notion+self-referential+filter&ia=web