The Grain Lock
Why a per-document total can never sit beside a per-line column in a table report — and how the builder stops you before the figures go wrong.
This is the one idea worth understanding before you build a table report. It is the reason some columns grey out, and it is the reason the columns that stay available can be trusted.
One Invoice Is Two Different Things
An invoice for three products is one document and three lines. Ask for a list and you have to say which you meant, because they are different lists with different row counts.
Every column in a Sales or Purchases table report is therefore declared at one of two grains:
| Grain | A row is | Columns |
|---|---|---|
| Document | One document | Total amount |
| Line | One line item | Item · Quantity · Rate · Discount · Tax · Line amount · Sales account (or Purchase account) |
| Both | Either — the value is the same on every line of the document | Date · Document number · Document type · Customer (or Vendor) · User · Location · Status · Payment method · Account collected to (or Account paid from) |
A report's grain is the intersection of the grains of the columns you ticked. Tick only both-grain columns and it stays at document grain — the cheaper, more natural "list of my invoices". Tick one line column and the whole report becomes one row per line.
What Goes Wrong Without the Lock
Take a three-line invoice for 4,500.00 and put Total amount beside Item:
| Document number | Item | Total amount |
|---|---|---|
| INV-001284 | Consulting — discovery workshop | 4,500.00 |
| INV-001284 | Training day (on site) | 4,500.00 |
| INV-001284 | Implementation support | 4,500.00 |
| Total | 13,500.00 |
Nothing in that table is a lie, row by row. The total is 300% wrong. Nobody reading it would notice, because 13,500.00 is a plausible number in a column of plausible numbers — and it would be carried into a PDF, an email and a decision.
A wrong figure that looks wrong gets found. A wrong figure that looks right gets used. The grain lock refuses the combination outright rather than producing a number that has to be caught by a reader.
How the Builder Stops You
Tick a line column and Total amount greys out immediately. The reason is attached to the checkbox itself:
"Total amount" is per document — it cannot share a row with the columns already selected, because the totals would be multiplied.
And the card states the state the report is in:
Some columns are unavailable because this report is currently one row per line item. A per-document total cannot sit beside a per-line column — the total would be counted once for every line.
The Columns header always names the current grain — 7 selected · one row per line item — so you never have to work it out from the ticks.
Untick every line column and Total amount becomes available again.
Two Places, One Rule
The greying-out is a courtesy: it tells you before you hit an error. The refusal itself lives on the server.
| Where | What it does |
|---|---|
| The builder | Mirrors the rule so a column can be explained as unavailable rather than failing after the fact. |
| The server, on every run | Resolves the grain independently and refuses an impossible combination with the reason. A saved definition that somehow held one — hand-edited, or built before a field changed — is refused when it runs, not silently run at the wrong grain. |
| The server, on an unknown column | Refused, never skipped. A column key it does not recognise fails the whole request rather than quietly dropping a column you asked for. |
What the Lock Makes Possible
The lock is not only a restriction — it is what makes the genuinely useful row legal:
| Document number | Item | Quantity | Line amount | Account collected to |
|---|---|---|---|---|
| INV-001284 | Consulting — discovery workshop | 2 | 2,640.00 | 1010 · Main operating account |
| INV-001284 | Training day (on site) | 3 | 2,145.00 | 1010 · Main operating account |
| SR-000917 | Cold-chain packaging (case) | 4 | 1,120.00 | 1030 · Petty cash |
Account collected to is one-to-one with the document, so repeating it on each line adds nothing and inflates nothing — which is exactly why it is declared at both grains and stays selectable. Line amount sums correctly because every row is a distinct line.
Two Consequences of Total Amount
Total amount is not read from a column — a document has no stored total; it is what its lines add up to, less any header discount. It is computed after the page of documents has been chosen, using the same formula the document itself uses, so a table report and the invoice can never disagree.
Because the value does not exist until the page is picked:
| Consequence | Why |
|---|---|
| Total amount cannot be filtered on | Filtering it could only filter the page you are holding, which would present a partial answer as a whole one. |
| Total amount cannot be sorted on | Sorting it would mean aggregating every document in the period before choosing a page — a query heavy enough to affect everyone else using the system. |
A unit price is not additive. Rate carries no aggregate, so its footer stays blank — rather than printing the sum of three unit prices, which is a number with no meaning.
Producer Modules Have One Grain
Inventory, Employees, Payroll, Customers, Vendors, Banking and Financial are not compiled from the document tables — they call the engine that owns those figures and keep its rows. One engine publishes one row shape, so there is nothing to intersect and nothing that can fan out. Every column stays selectable. See Table report modules.
Related
- Building one: Building a table report.
- The nine modules and their columns: Table report modules.
- Combining whole reports instead of columns: Composed reports.