Master-Detail Forms in Delphi: Invoice Header and Invoice Lines (FireDAC)

Almost every important ERP document has the same shape: one header and many lines. A sales invoice has one customer and date, and many items. A purchase order, a goods receipt, a journal entry and a stock transfer all look the same.

In database terms this is a one-to-many relationship. In Delphi it’s called master-detail: when the user moves to another invoice (the master), the lines grid (the detail) shows that invoice’s lines automatically.

This is part 5 of the Delphi for ERP series. It builds on the tables and the item lookup from part 4.

Diagram: Delphi master-detail invoice form, the Invoices header dataset driving the InvoiceLines dataset through MasterSource and the InvoiceID parameter

Step 1: The Invoice Lines Table

You already have Customers, Items and Invoices. Add the lines:

CREATE TABLE dbo.InvoiceLines (
    LineID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    InvoiceID INT           NOT NULL REFERENCES dbo.Invoices (InvoiceID) ON DELETE CASCADE,
    ItemID    INT           NOT NULL REFERENCES dbo.Items (ItemID),
    Qty       DECIMAL(12,3) NOT NULL CHECK (Qty > 0),
    UnitPrice DECIMAL(12,3) NOT NULL CHECK (UnitPrice >= 0)
);

CREATE INDEX IX_InvoiceLines_InvoiceID ON dbo.InvoiceLines (InvoiceID);

Three things to notice:

  • ON DELETE CASCADE: deleting an invoice deletes its lines too.
  • The index on InvoiceID: the lines grid reads lines by invoice every time the user moves, so this must be fast. (See SQL Indexes Explained Simply.)
  • No LineTotal column. It’s always Qty * UnitPrice, so we calculate it instead of storing it.

Step 2: The Master Dataset

On frmInvoice (from part 4) you have qryInvoices + dsInvoices and the customer lookup combo. Put the header fields in a top panel: InvoiceNo and Notes as TDBEdit, InvoiceDate as a TDBEdit (or a date picker), and the customer TDBLookupComboBox.

Also add a TDBNavigator for the header and a TDBGrid (or simply the navigator) for moving between invoices.

Step 3: The Detail Dataset, Linked to the Master

Drop a second query, qryLines, with a parameter named exactly like the master’s key field:

SELECT LineID, InvoiceID, ItemID, Qty, UnitPrice
FROM dbo.InvoiceLines
WHERE InvoiceID = :InvoiceID
ORDER BY LineID

Then set two properties:

PropertyValue
MasterSourcedsInvoices
MasterFieldsInvoiceID

That’s the whole link. FireDAC fills the :InvoiceID parameter from the current master row and re-runs the query every time the user moves to another invoice.

Add dsLines (DataSet = qryLines) and a TDBGrid for the lines with DataSource = dsLines. Open the master first, then the detail:

procedure TfrmInvoice.FormCreate(Sender: TObject);
begin
  qryCustomerList.Open;
  qryItemList.Open;
  qryInvoices.Open;
  qryLines.Open;
end;

With ADO: a TADOQuery supports the same DataSource + parameter link: set the detail query’s DataSource property to dsInvoices, and a parameter named InvoiceID is filled from the master field of the same name.

Step 4: An Item Dropdown in the Lines Grid

Users should pick an item by name, not type ItemID. Use a lookup field, as in part 4:

  1. Fields Editor on qryLines → Add all fields.
  2. New field: Name ItemName, Type String, Size 100, Field type Lookup. Key Fields ItemID, Dataset qryItemList, Lookup Keys ItemID, Result Field ItemName.
  3. Set LineID and InvoiceID to Visible = False.

The grid now has an ItemName column with a dropdown.

When the user picks an item, copy its current price into the line. Use the OnChange event of the ItemID field (select ItemID in the Fields Editor → Events):

procedure TfrmInvoice.qryLinesItemIDChange(Sender: TField);
var
  Price: Variant;
begin
  Price := qryItemList.Lookup('ItemID', Sender.AsInteger, 'UnitPrice');
  if not VarIsNull(Price) then
    qryLines.FieldByName('UnitPrice').AsCurrency := Price;
end;

The price is copied, not linked. That’s on purpose: if the item’s price changes next month, old invoices must keep the price they were sold at.

Step 5: Line Totals With a Calculated Field

In the Fields Editor, add another New field: Name LineTotal, Type Currency, Field type Calculated. Then fill it in OnCalcFields:

procedure TfrmInvoice.qryLinesCalcFields(DataSet: TDataSet);
begin
  DataSet.FieldByName('LineTotal').AsCurrency :=
    DataSet.FieldByName('Qty').AsCurrency * DataSet.FieldByName('UnitPrice').AsCurrency;
end;

Set DisplayFormat = #,##0.000 on LineTotal, UnitPrice and Qty. The grid now shows Item, Qty, Unit price and Line total, and the total updates as the user types.

Step 6: New Lines Need a Saved Header

There’s one trap. A new invoice gets its InvoiceID from SQL Server (it’s an identity column) only when it is saved. A line can’t point to an invoice that has no ID yet.

The simple fix: save the header automatically before the first line is added, and fill the line’s InvoiceID when it’s created.

procedure TfrmInvoice.qryLinesBeforeInsert(DataSet: TDataSet);
begin
  if qryInvoices.State in [dsInsert, dsEdit] then
    qryInvoices.Post;              // SQL Server assigns InvoiceID here
  if qryInvoices.IsEmpty then
    raise Exception.Create('Create the invoice header first.');
end;

procedure TfrmInvoice.qryLinesNewRecord(DataSet: TDataSet);
begin
  DataSet.FieldByName('InvoiceID').AsInteger :=
    qryInvoices.FieldByName('InvoiceID').AsInteger;
  DataSet.FieldByName('Qty').AsCurrency := 1;
end;

After Post, FireDAC reads the new identity value back into qryInvoices, so InvoiceID is ready for the line.

Step 7: Keep the Invoice Total Up to Date

The header has a TotalAmount column so lists and reports don’t have to add up lines every time. Recalculate it on the server whenever lines change. One UPDATE does it, with the parameter used only once:

procedure TfrmInvoice.UpdateInvoiceTotal;
begin
  DM.Conn.ExecSQL(
    'UPDATE i SET TotalAmount = COALESCE(t.Total, 0) ' +
    'FROM dbo.Invoices AS i ' +
    'OUTER APPLY (SELECT SUM(l.Qty * l.UnitPrice) AS Total ' +
    '             FROM dbo.InvoiceLines AS l WHERE l.InvoiceID = i.InvoiceID) AS t ' +
    'WHERE i.InvoiceID = :InvoiceID',
    [qryInvoices.FieldByName('InvoiceID').AsInteger]);
  qryInvoices.RefreshRecord;   // show the new total on screen
end;

procedure TfrmInvoice.qryLinesAfterPost(DataSet: TDataSet);
begin
  UpdateInvoiceTotal;
end;

procedure TfrmInvoice.qryLinesAfterDelete(DataSet: TDataSet);
begin
  UpdateInvoiceTotal;
end;

Add UpdateInvoiceTotal to the form’s private section. Show TotalAmount in a TDBText or read-only TDBEdit on the header.

Why calculate on the server and not by adding up the grid? Because the database is the source of truth. If two users edit lines, or a line is changed by another program, the server total is still correct.

A Note on Saving Everything at Once

In this design the header and each line are saved as the user goes. That’s simple and works well for most screens. Its weakness: if the user closes the screen halfway, you can have a header with only some of its lines.

For documents that must be all or nothing (like a journal entry, where debits must equal credits), you save the header and all lines inside one transaction. That’s exactly what part 6 builds.

Common Mistakes

  • No index on the detail’s foreign key. Every scroll of the master re-reads the lines. Without the index, each one is a table scan.
  • Linking the price to the item. Copy the price into the line when it’s chosen. History must not change when prices do.
  • Storing line totals that can disagree with Qty × Price. Calculate them.
  • Adding lines before the header has an ID. Post the header first (Step 6).
  • Opening the detail before the master. Open the lists, then the master, then the detail.

Practice Exercises

  1. Add a discount percentage to each line and include it in the line total and the invoice total.
  2. Show the number of lines under the grid (“3 lines”).
  3. Stop the same item from being added twice to one invoice, with a friendly message.

Answers

1. Add the column, include it in qryLines‘ SQL, then update the calculation and the total query:

ALTER TABLE dbo.InvoiceLines
ADD DiscountPct DECIMAL(5,2) NOT NULL DEFAULT 0 CHECK (DiscountPct BETWEEN 0 AND 100);
// OnCalcFields
DataSet.FieldByName('LineTotal').AsCurrency :=
  DataSet.FieldByName('Qty').AsCurrency * DataSet.FieldByName('UnitPrice').AsCurrency *
  (100 - DataSet.FieldByName('DiscountPct').AsCurrency) / 100;

In UpdateInvoiceTotal, change the sum to SUM(l.Qty * l.UnitPrice * (100 - l.DiscountPct) / 100).

2. In qryLines‘ AfterOpen, AfterPost and AfterDelete events, set lblLineCount.Caption := Format('%d lines', [qryLines.RecordCount]);. (Add System.SysUtils if Format isn’t found; VCL forms usually have it already.)

3. Add a unique constraint, then catch the error like in part 3:

ALTER TABLE dbo.InvoiceLines
ADD CONSTRAINT UQ_InvoiceLines_Item UNIQUE (InvoiceID, ItemID);
procedure TfrmInvoice.qryLinesPostError(DataSet: TDataSet;
  E: EDatabaseError; var Action: TDataAction);
begin
  if (E is EFDDBEngineException) and
     (EFDDBEngineException(E).Kind = ekUKViolated) then
  begin
    ShowMessage('This item is already on the invoice. Change the quantity instead.');
    Action := daAbort;
  end;
end;

Summary

  • Header + lines is the most common ERP document shape: one-to-many, or master-detail in Delphi.
  • Link the detail query with a :InvoiceID parameter, MasterSource and MasterFields.
  • Use a lookup field for the item column and copy the price into the line.
  • Calculate line totals with a calculated field; recalculate the invoice total on the server.
  • Save the header before adding lines, and index the detail’s foreign key.

Next in the series: transactions with FireDAC, saving a journal entry where debits must equal credits, all or nothing.

Related articles

Scroll to Top