Lookups (Delphi)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\45.Lookups and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​VCL-​demos/​tree/​master/​Delphi/​Modules/​20.​Reports/​45.​Lookups

Overview

Sometimes you have a field with a value that is read from another table. On this example, the field Employees.ReportsTo is a number, which points to the EmployeeID of the supervisor. Using the <#lookup()> tag, you can write the name of the supervisor, not only his EmployeeID.

Concepts

• How to use the <#Lookup> tag to search for a field in another table.

• Normally you will make lookup on one field, as in the example: <#lookup(Employees;EmployeeID;<#Employees.ReportsTo>;LastName)>. (This code lookups the value of ReportsTo, on the table Employees, searches for the field EmployeeId and returns LastName. Now, sometimes you might want to lookup on more than on field. Imagine that you wanted to search on the table Employee, but the record that not only has EmployeeID = ReportsTo, but Country="Spain". You can use the Array> tag to search here: <#lookup(Employees;EmployeeID,Country;<#array(<#Employees.ReportsTo>;Spain)>;FirstName)>

• Unrelated to Lookups, we show two other Excel features you can use: We froze the top panel so when you scroll down, the header is always visible, and we added an AutoFilter to find Employees. We just added them on the template, and they continue to exist on the generated report. Note that you can't define them in an include.

Files

UMainForm.pas

unit UMainForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Report, FlexCel.Render,
  {$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
  ShellApi,
  Controls, Forms, Dialogs, StdCtrls, ExtCtrls;

type
  TMainForm = class(TForm)
    btnCancel: TButton;
    btnGo: TButton;
    SaveDialog: TSaveDialog;
    Label1: TLabel;
    procedure btnCancelClick(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
  private
    procedure RunReport;
    function GetDataPath: string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils, DemoData;

{$R *.dfm}

procedure TMainForm.btnCancelClick(Sender: TObject);
begin
  Close;
end;

procedure TMainForm.btnGoClick(Sender: TObject);
begin
  RunReport;

end;

function TMainForm.GetDataPath: string;
begin
  Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..');
end;


procedure TMainForm.RunReport;
var
  Report: TFlexCelReport;
begin
  if not SaveDialog.Execute then exit;

  Report := TFlexCelReport.Create(true);
  try
    Report.AddTable(DemoTables);
    Report.SetValue('Date', Now);
    Report.Run(
      TPath.Combine(GetDataPath, 'Lookups.template.xls'),
      SaveDialog.FileName);
  finally
    Report.Free;
  end;

  if MessageDlg('Do you want to open the generated file?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
  end;


end;

end.