YOUSEFUL / DELPHI / SAMPLE PROJECTS

Filter console

Console program Self-checking Product: Atlas Info · Components: TAtlasDataFilter, TAtlasFilterEdit, TAtlasDBGrid, TAtlasDBLookupCombo

A console program that goes deeper into TAtlasDataFilter, TAtlasFilterEdit, TAtlasDBGrid.ExportCSV and TAtlasDBLookupCombo, using a six-row FireDAC TFDMemTable of customers and a three-row table of categories. It checks, in order:

contains, prefix and exact matching, case-insensitive, with the record count after each Apply;
that the text match composes with the dataset's own Filter expression and with an existing OnFilterRecord handler, and that Clear restores both;
that a second filter cannot take over the dataset, that Apply refuses to run during an edit, and that an unknown field name keeps the previous filter;
that typing into a TAtlasFilterEdit applies the filter and clearing it removes the filter;
CSV export of the filtered, visible columns, of the selected rows only, to customers.csv, and the refusal to export while an edit is pending;
lookup selection by display text and by key, with the master record left in edit state and the lookup cursor left where it was.

The controls are created on a form that is never shown; the data-aware controls need a parent window, not a visible one. Every step checks its own result. The program exits with code 0 when all checks pass, or prints the failed check and exits with code 1. It needs no input, database server or network.

Build and run

Open FilterConsole.dpr in Delphi 13; the IDE creates the project file. Add the Atlas Info src folder and the Atlas VCL src folder to the project's search path, then build and run for Win32 or Win64. No packages need to be installed. The program needs no input. It checks its own results and stops with an error if any of them differs from the output below.

It writes customers.csv to the current directory.

Expected output

1. Text matching
  ok  contains "ar" matches 4 of 6 names, case-insensitive
  ok  Filter.Text holds the lower-cased text
  ok  the filter is installed on the dataset
  ok  prefix "a" matches Acorn Design and Anchor Marine
  ok  exact match finds one record
  ok  Clear shows every record again
2. Composing with an existing filter
  ok  the native expression alone keeps 4 records
  ok  text match AND native expression keeps 2 records
  ok  Clear restores the native expression
  ok  text match runs after the existing OnFilterRecord handler
  ok  Clear restores the existing handler
3. Ownership and editing rules
  ok  a second controller cannot take over the dataset
  ok  Apply refuses to run while a record is being edited
  ok  an unknown field keeps the previous filter
4. TAtlasFilterEdit drives the controller
  ok  typing "ce" leaves Cedar Systems
  ok  clearing the edit clears the filter
5. CSV export of the visible rows
  ok  header plus the two filtered records
  ok  hidden columns are left out of the header
  ok  numbers and booleans use invariant text
  ok  export restores the current record
  ok  SelectedOnly exports the selected rows
  wrote ...\customers.csv
  ok  export refuses pending edits instead of posting them
6. Lookup selection
  ok  SelectText finds "Priority" from a prefix
  ok  the master field changed, nothing was posted
  ok  the lookup cursor did not move
  ok  an unknown text returns False
  ok  an unknown key raises and leaves the record alone
  ok  SelectKey followed by Post stores the key
PASS: 28 checks

customers.csv holds the two customers whose names start with "a", without the hidden CategoryID column:

ID,Name,Balance,Active
1,Acorn Design,1200.5,true
6,Anchor Marine,300,true

Adapting it

Point Filter.DataSet at your own dataset and change the field names passed to Apply. To filter as the user types, keep the TAtlasFilterEdit part and drop the direct Apply calls. To export something other than the filtered rows, change which grid columns are visible before calling ExportCSV.

Project files

Download all 34 projects

The ZIP contains every sample project with its README. See getting started for the search path to add.

Source

FilterConsole.dpr

program FilterConsole;

{$APPTYPE CONSOLE}

// Atlas Info: filter a FireDAC memory table with TAtlasDataFilter and
// TAtlasFilterEdit, export the visible rows with TAtlasDBGrid.ExportCSV and
// pick a lookup value with TAtlasDBLookupCombo. Every step verifies its own
// result and the program exits with code 0 when all checks pass.

uses
  System.SysUtils, System.Classes, System.Variants, System.IOUtils, Data.DB,
  FireDAC.Comp.Client, Vcl.Forms, Vcl.Controls,
  Atlas.DBControls, Atlas.DBFilter;

type
  TActiveOnly = class
    procedure FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  end;

var
  Checks: Integer = 0;

procedure TActiveOnly.FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  Accept := Accept and DataSet.FieldByName('Active').AsBoolean;
end;

procedure Check(Condition: Boolean; const Description: string);
begin
  if not Condition then
    raise Exception.Create('FAILED: ' + Description);
  Inc(Checks);
  Writeln('  ok  ', Description);
end;

function CreateCustomers(Owner: TComponent): TFDMemTable;
begin
  Result := TFDMemTable.Create(Owner);
  Result.FieldDefs.Add('ID', ftInteger);
  Result.FieldDefs.Add('Name', ftWideString, 80);
  Result.FieldDefs.Add('CategoryID', ftInteger);
  Result.FieldDefs.Add('Balance', ftFloat);
  Result.FieldDefs.Add('Active', ftBoolean);
  Result.CreateDataSet;
  Result.AppendRecord([1, 'Acorn Design', 2, 1200.5, True]);
  Result.AppendRecord([2, 'Harbor Services', 1, 850, True]);
  Result.AppendRecord([3, 'Northwind Workshop', 3, 2400, False]);
  Result.AppendRecord([4, 'Cedar Systems', 2, 450, True]);
  Result.AppendRecord([5, 'Orchard Foods', 1, 1600, False]);
  Result.AppendRecord([6, 'Anchor Marine', 3, 300, True]);
  Result.First;
end;

function CreateCategories(Owner: TComponent): TFDMemTable;
begin
  Result := TFDMemTable.Create(Owner);
  Result.FieldDefs.Add('ID', ftInteger);
  Result.FieldDefs.Add('Name', ftWideString, 40);
  Result.CreateDataSet;
  Result.AppendRecord([1, 'Standard']);
  Result.AppendRecord([2, 'Priority']);
  Result.AppendRecord([3, 'Partner']);
  Result.First;
end;

procedure Run;
var
  Form: TForm;
  Customers, Categories: TFDMemTable;
  Source, ListSource: TDataSource;
  Filter, Second: TAtlasDataFilter;
  Edit: TAtlasFilterEdit;
  Grid: TAtlasDBGrid;
  Lookup: TAtlasDBLookupCombo;
  ActiveOnly: TActiveOnly;
  CSV, FileName: string;
  Lines: TArray<string>;
  Rejected: Boolean;
begin
  // A hidden form owns the controls; the data-aware controls need a parent
  // window but the program never shows it.
  Form := TForm.CreateNew(nil);
  ActiveOnly := TActiveOnly.Create;
  try
    Customers := CreateCustomers(Form);
    Categories := CreateCategories(Form);
    Source := TDataSource.Create(Form);
    Source.DataSet := Customers;
    ListSource := TDataSource.Create(Form);
    ListSource.DataSet := Categories;

    Writeln('1. Text matching');
    Filter := TAtlasDataFilter.Create(Form);
    Filter.DataSet := Customers;
    Filter.Apply('Name', 'AR');
    Check(Customers.RecordCount = 4, 'contains "ar" matches 4 of 6 names, case-insensitive');
    Check(Filter.Text = 'ar', 'Filter.Text holds the lower-cased text');
    Check(Filter.Installed, 'the filter is installed on the dataset');
    Filter.Apply('Name', 'a', atmPrefix);
    Check(Customers.RecordCount = 2, 'prefix "a" matches Acorn Design and Anchor Marine');
    Filter.Apply('Name', 'northwind workshop', atmExact);
    Check(Customers.RecordCount = 1, 'exact match finds one record');
    Filter.Clear;
    Check((not Filter.Installed) and (Customers.RecordCount = 6), 'Clear shows every record again');

    Writeln('2. Composing with an existing filter');
    Customers.Filter := 'Balance >= 800';
    Customers.Filtered := True;
    Check(Customers.RecordCount = 4, 'the native expression alone keeps 4 records');
    Filter.Apply('Name', 'ar');
    Check(Customers.RecordCount = 2, 'text match AND native expression keeps 2 records');
    Filter.Clear;
    Check(Customers.Filtered and (Customers.RecordCount = 4), 'Clear restores the native expression');
    Customers.Filtered := False;
    Customers.Filter := '';
    Customers.OnFilterRecord := ActiveOnly.FilterRecord;
    Customers.Filtered := True;
    Filter.Apply('Name', 'ar');
    Check(Customers.RecordCount = 3, 'text match runs after the existing OnFilterRecord handler');
    Filter.Clear;
    Check(Customers.RecordCount = 4, 'Clear restores the existing handler');
    Customers.Filtered := False;
    Customers.OnFilterRecord := nil;

    Writeln('3. Ownership and editing rules');
    Filter.Apply('Name', 'ar');
    Second := TAtlasDataFilter.Create(Form);
    Second.DataSet := Customers;
    Rejected := False;
    try
      Second.Apply('Name', 'x');
    except
      on EAtlasDatabaseError do Rejected := True;
    end;
    Check(Rejected and (Customers.RecordCount = 4), 'a second controller cannot take over the dataset');
    Customers.Edit;
    Rejected := False;
    try
      Filter.Apply('Name', 'cedar');
    except
      on EAtlasDatabaseError do Rejected := True;
    end;
    Check(Rejected and (Customers.State = dsEdit), 'Apply refuses to run while a record is being edited');
    Customers.Cancel;
    Rejected := False;
    try
      Filter.Apply('Missing', 'x');
    except
      on EDatabaseError do Rejected := True;
    end;
    Check(Rejected and (Customers.RecordCount = 4), 'an unknown field keeps the previous filter');
    Filter.Clear;

    Writeln('4. TAtlasFilterEdit drives the controller');
    Edit := TAtlasFilterEdit.Create(Form);
    Edit.Parent := Form;
    Edit.Filter := Filter;
    Edit.FieldName := 'Name';
    Edit.Match := atmPrefix;
    Edit.Text := 'ce';
    Check(Customers.RecordCount = 1, 'typing "ce" leaves Cedar Systems');
    Edit.Text := '';
    Check((not Filter.Installed) and (Customers.RecordCount = 6), 'clearing the edit clears the filter');

    Writeln('5. CSV export of the visible rows');
    Grid := TAtlasDBGrid.Create(Form);
    Grid.Parent := Form;
    Grid.DataSource := Source;
    Grid.Columns[2].Visible := False; // CategoryID
    Filter.Apply('Name', 'a', atmPrefix);
    Customers.Last;
    CSV := Grid.ExportCSV;
    Lines := CSV.Split([sLineBreak], TStringSplitOptions.ExcludeEmpty);
    Check(Length(Lines) = 3, 'header plus the two filtered records');
    Check(Lines[0] = 'ID,Name,Balance,Active', 'hidden columns are left out of the header');
    Check(Lines[1] = '1,Acorn Design,1200.5,true', 'numbers and booleans use invariant text');
    Check(Customers.FieldByName('ID').AsInteger = 6, 'export restores the current record');
    Grid.SelectedRows.CurrentRowSelected := True;
    CSV := Grid.ExportCSV(True);
    Lines := CSV.Split([sLineBreak], TStringSplitOptions.ExcludeEmpty);
    Check((Length(Lines) = 2) and (Lines[1] = '6,Anchor Marine,300,true'), 'SelectedOnly exports the selected rows');
    FileName := TPath.Combine(GetCurrentDir, 'customers.csv');
    TFile.WriteAllText(FileName, Grid.ExportCSV, TEncoding.UTF8);
    Writeln('  wrote ', FileName);
    Grid.EditValue('Balance', '301.25');
    Rejected := False;
    try
      Grid.ExportCSV;
    except
      on EAtlasDatabaseError do Rejected := True;
    end;
    Check(Rejected and (Customers.State = dsEdit), 'export refuses pending edits instead of posting them');
    Customers.Cancel;
    Filter.Clear;

    Writeln('6. Lookup selection');
    Lookup := TAtlasDBLookupCombo.Create(Form);
    Lookup.Parent := Form;
    Lookup.ListSource := ListSource;
    Lookup.KeyField := 'ID';
    Lookup.ListField := 'Name';
    Lookup.DataSource := Source;
    Lookup.DataField := 'CategoryID';
    Customers.First;
    Categories.Last;
    Check(Lookup.SelectText('pri'), 'SelectText finds "Priority" from a prefix');
    Check((Customers.State = dsEdit) and (Customers.FieldByName('CategoryID').AsInteger = 2), 'the master field changed, nothing was posted');
    Check(Categories.FieldByName('ID').AsInteger = 3, 'the lookup cursor did not move');
    Customers.Cancel;
    Check(not Lookup.SelectText('absent'), 'an unknown text returns False');
    Rejected := False;
    try
      Lookup.SelectKey(999);
    except
      on EAtlasDatabaseError do Rejected := True;
    end;
    Check(Rejected and (Customers.State = dsBrowse), 'an unknown key raises and leaves the record alone');
    Lookup.SelectKey(3);
    Customers.Post;
    Check(Customers.FieldByName('CategoryID').AsInteger = 3, 'SelectKey followed by Post stores the key');
  finally
    Form.Free;
    ActiveOnly.Free;
  end;
end;

begin
  try
    Application.Initialize;
    Run;
    Writeln('PASS: ', Checks, ' checks');
  except
    on E: Exception do
    begin
      Writeln(E.ClassName, ': ', E.Message);
      Halt(1);
    end;
  end;
end.