Labeled Invoice Totals in Quickbase Document Templates (Subtotal, Tax, Total)

A question came up in the Quickbase community about invoices built with Document Creation. The invoice template pulls in a report of the line items, and when you turn on totals or averages for that report, Quickbase adds one more row at the bottom with the numbers and no label. What the poster wanted was a proper totals area under the table: Subtotal, Tax, Total, each with a label, laid out the way an invoice normally looks.

Here's the original thread: Document Creation - Report Totals.

The short answer: leave totals off the report, calculate them on the invoice record itself with summary fields, and print those fields under the report with your own labels. This guide walks through the whole thing, including subtotals that only count some lines (like taxable vs non-taxable), a tax formula, currency formatting, and how to check the PDF.

Why the report's totals row isn't the right tool

The totals row on a report is built for reading data on screen. In a document it has a few problems:

• It has no label, or at best a generic one, so a reader just sees a stray row of numbers.
• It totals every numeric column. On an invoice you usually want Amount totaled, not Qty or Unit Price.
• It can only add things up. It can't apply tax, a discount or shipping, so there's no way to get from "sum of the lines" to "amount due".
• You can't move it. It's always glued to the bottom of the table, in the same columns.

The fix is to stop asking the report to do math. The invoice record can hold every number you need, and anything on the invoice record can be dropped anywhere in the template.

The example setup

I'll use a typical invoice app with two tables:

Invoices (the parent): Invoice #, Customer, Invoice Date, and a Tax Rate field (Numeric - Percent).

Invoice Lines (the child), related to Invoices:

Sample invoice #1042 for Sarah Lee has three lines:

With a tax rate of 8.25%, the numbers we want under the table are:

• Subtotal: 605.00
• Taxable subtotal: 320.00 (we may or may not print this one)
• Tax: 320.00 x 8.25% = 26.40
• Total: 631.40

If your lines already have a single Amount field, you're most of the way there. If they don't, add the Amount formula first, because the summary fields need one number per line to add up.

Step 1: Add a Subtotal summary field to the invoice

Summary fields live on the parent table and roll up values from the child records.

1. Open the Invoices table settings and go to Table relationships.
2. Open the relationship between Invoices and Invoice Lines.
3. In the summary fields section, choose to add a summary field.
4. Pick Total as the summary type and Amount as the field to total.
5. Leave it summarizing all related Invoice Lines for now.
6. Name it Subtotal and save.

Open a couple of invoices and check that Subtotal matches what you get adding up the lines by hand. For #1042 it should show 605.00.

A summary of a currency field comes out as currency, so it should already show a dollar sign. We'll tidy up formatting in Step 4.

Step 2: Add summary fields with criteria for partial subtotals

This is where summary fields really beat the report totals row. You can add more than one, and each one can only count the lines that match a filter.

1. Go back to the same relationship and add another summary field.
2. Again choose Total of Amount.
3. This time, choose the option to summarize only the records that match criteria, and set the criteria to Taxable is checked (equal to Yes).
4. Name it Taxable Subtotal and save.

For #1042 this shows 320.00.

If you want to show the non-taxable part separately, add a third one with Taxable is not checked, named Non-taxable Subtotal (285.00 here). You don't strictly need it, since it's always Subtotal minus Taxable Subtotal, but having it as its own field makes the template easier to read.

The same idea works for anything you can filter on: a Line Type of "Discount", a Category of "Shipping", lines marked "Billable", and so on. One summary field per bucket.

Step 3: Add formula fields for Tax and Total

Summary fields can only add up what's on the lines. For tax and the final total, use formula fields on the Invoices table.

Tax (Formula - Currency):

Round([Taxable Subtotal] * [Tax Rate], 0.01)

A Numeric - Percent field stores 8.25% as 0.0825, so you can multiply by it directly. Round(..., 0.01) rounds to the nearest cent, so the tax printed on the invoice is exactly the value that gets added to the total. Without it, the field might store 26.4000 and display 26.40 while the math behind it carries extra decimals, which is how you end up with totals that are off by a penny.

Total (Formula - Currency):

[Subtotal] + [Tax]

If you have other adjustments, add them here. For example, with a Shipping currency field on the invoice and a Discount summary field:

[Subtotal] - [Discount] + [Shipping] + [Tax]

If Tax Rate can be blank on some invoices, the Tax formula returns blank instead of 0, and that blank can carry through to Total. To be safe, either give Tax Rate a default value of 0, or wrap the formula:

If(IsNull([Tax Rate]), 0, Round([Taxable Subtotal] * [Tax Rate], 0.01))

Step 4: Format the fields as currency

The values you insert into a document template come out using the field's display settings, so set them up once on the field and every generated invoice follows them.

For Subtotal, Taxable Subtotal, Tax and Total, open each field's properties and check:

• Currency symbol is set (for example $), and placed before the number.
• Decimal places is 2, so 605 prints as 605.00.
• Separate thousands is on, so 12,480.00 doesn't print as 12480.00.

It's worth doing the same for Unit Price and Amount on the lines so the table and the totals block look like they belong together.

Step 5: Turn off totals on the report you use in the template

Next, stop the report from adding its own unlabeled row.

1. I'd make a report on Invoice Lines just for documents, for example Lines for Invoice PDF, rather than reusing a report people use on screen. That way someone tidying up a working report doesn't change every invoice you send.
2. Add the columns you want printed: Item, Qty, Unit Price, Amount.
3. In the report settings, make sure totals and averages are turned off for every column.
4. Save the report.

If you add it with Report as a customizable table, check the table it inserts too, and delete any total row you may have added by hand.

Step 6: Build the totals block in the template

Now open the template: Invoices table settings, Document templates, then open your invoice template (or select New template).

1. Insert the report if it isn't there yet: Add elements, then Report element or Report as a customizable table, and pick Lines for Invoice PDF. Use the report link setting so it only shows lines for the invoice being printed.
2. Click just after the report and add a new line below it. With a report element selected you can use the right arrow key and then Enter to get the cursor below it.
3. Insert a small table with two columns and three rows (more if you print shipping or discount).
4. In the left column, type your labels: Subtotal, Tax (8.25%) or just Tax, and Total.
5. In the right column, insert the matching field from the Invoices table into each row: Subtotal, Tax, Total.
6. Right-align the right column so the numbers line up with the Amount column above.
7. Make the Total row bold, and add a top border on it if you like.
8. Save.

If you'd rather show the actual tax rate in the label, insert the Tax Rate field into the label cell: type "Tax (", insert the field, then type ")".

If you're comfortable in source mode, the totals block can be a simple HTML table. The field markers look like <qb-field field-id="..."></qb-field>. Insert them with the builder rather than typing the IDs, because the IDs in your app will be different from mine. Here's roughly what the finished block looks like, with example IDs:

<table style="width: 45%; margin-left: auto; border-collapse: collapse; font-family: Arial;">
  <tr>
    <td style="padding: 4px 8px;">Subtotal</td>
    <td style="padding: 4px 8px; text-align: right;"><qb-field field-id="12"></qb-field></td>
  </tr>
  <tr>
    <td style="padding: 4px 8px;">Tax</td>
    <td style="padding: 4px 8px; text-align: right;"><qb-field field-id="14"></qb-field></td>
  </tr>
  <tr style="font-weight: bold; border-top: 2px solid #333;">
    <td style="padding: 6px 8px;">Total</td>
    <td style="padding: 6px 8px; text-align: right;"><qb-field field-id="15"></qb-field></td>
  </tr>
</table>

margin-left: auto pushes the block to the right side of the page, under the Amount column, which is where most people expect to find the total.

Step 7: Preview and test the PDF

Use Save and preview, pick an invoice, and select Download PDF. Check it against a few kinds of records:

• A normal invoice with taxable and non-taxable lines. Add up the Amount column yourself and compare it to Subtotal. For #1042: 150.00 + 170.00 + 285.00 = 605.00, tax 26.40, total 631.40.
• An invoice with no taxable lines. Tax should be $0.00, not blank. If it's blank, see the IsNull tip in Step 3.
• An invoice with no lines yet. Subtotal should be $0.00 and the report should be empty, not showing someone else's lines. If you see other invoices' lines, the report element isn't filtered by the report link.
• A long invoice that runs onto a second page. Make sure the totals block lands after the last line and doesn't get split awkwardly. If it does, add a page break or keep the block in a single table so it stays together.

The preview is close to the real thing but not perfect, so generate a real document from a record once (Mike Chen's test invoice, say) and open it the way a customer would.

Things to watch out for

The report and the summary fields have to agree. The summary fields count every related line that matches their own criteria. The report shows whatever its filter allows. If the report hides some lines, like ones with a Status of "Void", add the same condition to every summary field, or the totals won't match the table above them. This is the most common reason the numbers don't line up.

Report limits. A report in a document can only show so many rows (the customizable table tops out at 1,000). The summary fields don't have that limit, so on very large invoices the totals are still right even if the table is cut off. You'll want to know that before a customer does.

Rounding per line vs on the total. If Unit Price has more than two decimals, or Amount includes a line discount, round Amount on the line with Round([Qty] * [Unit Price], 0.01). Then the lines and the subtotal are added up from the same rounded numbers.

Tax per line. If different lines have different tax rates, put a Line Tax formula on the line (Round([Amount] * [Line Tax Rate], 0.01)) and use a summary field for Total of Line Tax instead of the Tax formula on the invoice.

Existing documents don't update. Documents that were already generated and saved keep the old layout. Only new ones pick up the totals block.

Use the fields elsewhere, too. Once Subtotal, Tax and Total are real fields on the invoice, you can show them on the form, filter reports on them ("invoices over $10,000"), and send them to accounting with a pipeline. That's a nice side effect of not leaving the math to the report.

Wrap-up

Keep the report for the lines, keep the math on the invoice. Summary fields add up the lines (with criteria when you only want some of them), formula fields handle tax and the final total, and the template just prints those fields next to labels you write yourself. You get to decide what the labels say, where the block sits and how the numbers look, and the totals always match the lines above them.