Stacking tables into one view

Some questions need rows from several tables in one list. A statement of account reads invoices and payments side by side; a list of all money movements reads invoices, payments and adjustments. A stacked view puts the rows of two or more tables one under the other and pairs their fields into shared columns. Like every view, it reads its tables each time it opens.

Make a stacked view

1. In the sidebar, choose the database's New menu, then Dynamic table.
2. Under Tables, pick two or more tables. They stack in the order you pick them. Views and summaries can be stacked too: they are listed under Dynamic tables and summaries.
3. Give the view a Name and choose Next.
4. Check the Columns list, as below, and choose Create.

How the columns pair up

Each row of the list is a column of the stacked view, and under From a chip for each table that brings values into it.

• Fields with the same name are paired: every table's Amount lands in one Amount column.
• A field with another name can be moved by hand. Choose its chip and pick the column it lands in, so that Invoices' Issue Date and Payments' Payment Date share a Date column.
• In the same chip menu, Its own column gives a field a column of its own, named in Column name, and Leave it out drops it.
• Split by table, on a paired column, makes one column per table instead: Charged from invoices and Paid from payments.
• A column a table does not bring is empty on that table's rows. The list says so, and names the columns left out under it.

Two columns are added in front: Source, which says which table each row comes from, and its id in that table. Who created and changed each row, and when, is left out. Rows are never merged: two identical rows from two tables both stay.

Fields are paired by name only. The app never guesses that two differently named fields mean the same thing, so pair them yourself where they do.

After it is made

• To change it, choose its title, then Edit Dynamic Table definition. Remove a table with the × on its chip, add one with Add a table, and change where columns land in the same list.
• To add a formula, choose + (Add Field) at the end of the headers; only Calculation is offered. A running balance in date order is a formula with SUM(…) OVER (…): see Functions.
• To total it by month or by customer, build a summary on it: see Summaries.

A stacked view is read-only: records are added and changed in their own tables. Renaming a table or a field it reads keeps the view reading the same field, and the view's own column names stay as they were when it was saved.