Lookup Combo Boxes in Delphi: Choose a Customer or Item From a List

In a well-designed database, an invoice doesn’t store the customer’s name. It stores the CustomerID, a number that points to the Customers table. That’s what keeps the data clean: rename a customer once and every invoice shows the new name.

But users don’t want to type 17. They want to pick “Al Noor Trading” from a list. The bridge between the two is a lookup: show the name, save the ID.

Delphi has this built in, and it’s one of the reasons ERP screens are so quick to build. This is part 4 of the Delphi for ERP series. It continues from the customer screen in part 3.

Diagram: TDBLookupComboBox shows customer names from the Customers table and saves the CustomerID into the Invoices table

The Tables

Add an Items table and an Invoices header table to the database from part 3. Part 5 will add the invoice lines.

CREATE TABLE dbo.Items (
    ItemID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ItemCode  VARCHAR(20)   NOT NULL UNIQUE,
    ItemName  NVARCHAR(100) NOT NULL,
    UnitPrice DECIMAL(12,3) NOT NULL DEFAULT 0 CHECK (UnitPrice >= 0),
    IsActive  BIT           NOT NULL DEFAULT 1
);

CREATE TABLE dbo.Invoices (
    InvoiceID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    InvoiceNo   VARCHAR(20)   NOT NULL UNIQUE,
    InvoiceDate DATE          NOT NULL DEFAULT CAST(GETDATE() AS DATE),
    CustomerID  INT           NOT NULL REFERENCES dbo.Customers (CustomerID),
    Notes       NVARCHAR(200) NULL,
    TotalAmount DECIMAL(14,3) NOT NULL DEFAULT 0
);

INSERT INTO dbo.Items (ItemCode, ItemName, UnitPrice)
VALUES ('P-100', N'A4 paper (box)', 6.500),
       ('P-200', N'Laser toner',    28.000),
       ('P-300', N'Office chair',   45.750);

The REFERENCES part is a foreign key: SQL Server won’t accept an invoice for a customer that doesn’t exist. (See Primary Keys vs Foreign Keys.)

Method 1: TDBLookupComboBox on a Form

This is the classic way to pick one value on an entry form.

On a new form frmInvoice, drop:

  • qryInvoices (TFDQuery): SELECT InvoiceID, InvoiceNo, InvoiceDate, CustomerID, Notes, TotalAmount FROM dbo.Invoices ORDER BY InvoiceID DESC, plus dsInvoices (TDataSource) pointing to it.
  • qryCustomerList (TFDQuery): the list the user picks from, plus dsCustomerList pointing to it:
SELECT CustomerID, CustomerName, CustomerCode, City, IsActive
FROM dbo.Customers
ORDER BY CustomerName

Now drop a TDBLookupComboBox and set five properties:

PropertyValueMeaning
DataSourcedsInvoicesThe record being edited
DataFieldCustomerIDThe field that gets saved
ListSourcedsCustomerListWhere the choices come from
KeyFieldCustomerIDThe value to save from the chosen row
ListFieldCustomerName;CustomerCodeWhat the user sees (two columns)

Open both queries in FormCreate (open the list first):

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

Run it. The combo shows “Al Noor Trading C-001”, and when the user picks it, Invoices.CustomerID gets the right number. The user can also type the first letters of the name to jump to it.

Method 2: A Lookup Field (Best for Grids)

A combo box works for one value on a form. For lines in a grid (like items on an invoice) you want a dropdown inside the grid column. For that, create a lookup field on the dataset:

  1. Double-click qryInvoices → Fields Editor → right-click → Add all fields.
  2. Right-click → New field. Name: CustomerName, Type: String, Size: 100, Field type: Lookup.
  3. Set: Key Fields = CustomerID, Dataset = qryCustomerList, Lookup Keys = CustomerID, Result Field = CustomerName.

Now CustomerName behaves like a real column of qryInvoices, but it’s calculated from the list. In a TDBGrid it appears as a column with a dropdown button. When the user picks a name, Delphi writes the matching CustomerID into the invoice row.

You’ll use exactly this in part 5 for the item column on invoice lines.

TDBLookupComboBoxLookup field
Best forOne choice on a formA column in a grid
SetupProperties on the controlA new field in the Fields Editor
ShowsOne or more list columnsOne result column
Also usable in–Reports, exports, TDBText labels

Method 3: A JOIN, for Read-Only Lists

For a list of invoices that users only view (not edit), the simplest and fastest way is plain SQL:

SELECT i.InvoiceID, i.InvoiceNo, i.InvoiceDate, c.CustomerName, i.TotalAmount
FROM dbo.Invoices AS i
JOIN dbo.Customers AS c ON c.CustomerID = i.CustomerID
ORDER BY i.InvoiceDate DESC;

SQL Server does the lookup, and the grid simply shows the result. If you ever make a joined query editable, tell FireDAC which table to update with UpdateOptions.UpdateTableName := 'dbo.Invoices', and remove pfInUpdate from the ProviderFlags of the joined CustomerName field so FireDAC doesn’t try to save it. (A refresher on joins: SQL JOINs Explained Simply.)

When the List Is Too Long: a Search Dialog

A dropdown with 50 customers is great. With 50,000 it’s slow to load and useless to scroll. For big lists, use a search dialog instead: a small form with a search box and a grid that returns the chosen ID.

// In the search form unit
class function TfrmCustomerSearch.Execute(out ACustomerID: Integer): Boolean;
var
  F: TfrmCustomerSearch;
begin
  F := TfrmCustomerSearch.Create(nil);
  try
    Result := F.ShowModal = mrOk;
    if Result then
      ACustomerID := F.qrySearch.FieldByName('CustomerID').AsInteger;
  finally
    F.Free;
  end;
end;
// On the invoice form: a "..." button next to the customer field
procedure TfrmInvoice.btnFindCustomerClick(Sender: TObject);
var
  ID: Integer;
begin
  if TfrmCustomerSearch.Execute(ID) then
  begin
    if not (qryInvoices.State in [dsEdit, dsInsert]) then
      qryInvoices.Edit;
    qryInvoices.FieldByName('CustomerID').AsInteger := ID;
  end;
end;

Inside the search form, qrySearch runs a parameterised WHERE CustomerName LIKE :Pattern query as the user types, and the OK button (or Enter, or a double-click on the grid) sets ModalResult := mrOk. Many ERPs open this dialog with F4 or F9 in any ID field, which data entry users love.

Keeping the List Fresh

The list query is loaded once when the form opens. If someone adds a customer while the invoice form is open, it won’t be in the list. Refresh it when needed:

qryCustomerList.Refresh;

A good moment is right after the user returns from the customer screen, or when the invoice form gets focus again.

Common Mistakes

  • Opening the data query before the list query. The combo has nothing to look up yet and shows blanks. Open the list first.
  • Filtering inactive rows out of the list. Old invoices for an inactive customer then show an empty name. Keep them in the list, and block choosing them for new invoices (exercise 3).
  • Storing the name instead of the ID. Renaming a customer then breaks history. Store the ID; show the name.
  • A dropdown for huge tables. Use a search dialog over a few thousand rows.
  • Forgetting the foreign key. Without it, a bug or script can save a CustomerID that doesn’t exist.

Practice Exercises

  1. Show the selected customer’s city next to the combo box, read-only.
  2. Add an ItemID lookup combo that shows ItemCode and ItemName (you’ll need it in part 5).
  3. Stop users from saving an invoice for an inactive customer.

Answers

1. Create a second lookup field CustomerCity (Result Field = City, same keys as CustomerName), then drop a TDBText with DataSource = dsInvoices and DataField = CustomerCity. It updates as soon as the user picks a customer.

2. Add qryItemList (SELECT ItemID, ItemCode, ItemName, UnitPrice FROM dbo.Items ORDER BY ItemCode) and dsItemList, then set the combo’s ListSource = dsItemList, KeyField = ItemID, ListField = ItemCode;ItemName.

3. Use the list dataset’s Lookup method in BeforePost:

procedure TfrmInvoice.qryInvoicesBeforePost(DataSet: TDataSet);
var
  Active: Variant;
begin
  Active := qryCustomerList.Lookup('CustomerID',
    DataSet.FieldByName('CustomerID').AsInteger, 'IsActive');
  if VarIsNull(Active) then
    raise Exception.Create('Please choose a customer.');
  if Active = False then
    raise Exception.Create('This customer is inactive. Choose another customer.');
end;

Lookup searches the rows already loaded in qryCustomerList and returns Null if nothing matches. Add System.Variants to the uses clause for VarIsNull.

Summary

  • Store IDs in your tables; show names to users.
  • TDBLookupComboBox picks one value on a form: set DataSource/DataField and ListSource/KeyField/ListField.
  • A lookup field gives a dropdown column inside a TDBGrid, ideal for invoice lines.
  • For read-only lists, let SQL Server do the lookup with a JOIN.
  • For very large lists, use a search dialog instead of a dropdown.

Next in the series: master-detail forms, an invoice header with its invoice lines on one screen.

Related articles

Scroll to Top