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.