Deleting a Sort Order at Runtime

rbuser

Is it possible to to delete a sort order at runtime? Is it possible to add
a sort order at runtime?

Thanks,

Scott Rowat

digitalmetaphors

You can manually add/delete the ORDER BY from your query at runtime. If you
are using Delphi Query components you can directly edit the SQL and resubmit
the query. If you are using DADE then see the example below for extracting
the SQL object from a data view. You can use the ClearOrderByFields method
of the SQL object.

-------------------------------------------------
Tech Tip: How to access the SQL object associated
with a Report created using DADE
-------------------------------------------------

TdaSQL is a class defined in daSQL.pas.
TdaSQL has a run-time interface for adding search Criteria,
etc. For an example see the EndUser\Custom DataViews example.

OR you can assign its SQLText property. Please note that
once you directly assign the SQLText property the Query tool
buttons such as Sort, Search can no longer be used.



uses
daDataModule;


function GetSQLObject(aReport: TppReport; var aSQL: TdaSQL): Boolean;
var
lDataModule: TdaDataModule;
lDataView: TdaDataView;
begin

aSQL := TdaSQL.Create(nil);

{get the datamodule}
lDataModule := daGetDataModule(aReport);

if (lDataModule <> nil) then
begin
lDataView := lDataModule.DataViews[0];

if (lDataView <> nil) and (lDataView is TdaQueryDataView) then
aSQL.Assign(TdaQueryDataView(lDataView).SQL);

end;

Result := (aSQL <> nil);

end;


Once you've retrieved the SQL object, you can do something useful to it,
like adding criteria values:

procedure ppReport1BeforePrint(Sender: TObject);
var
lSQL: TdaSQL;
lFields: TStringList;
lCriteria: TdaCriteria;
liIndex: Integer;
begin

{get SQL object}
GetSQLObject(ppReport1, lSQL);

SQL.InsertCriteria(liItem, dacrOR);

{if criteria have not been created, then add them}
if (lSQL.CriteriaCount = 0) then
begin
{get all available criteria fields}
lFields := TStringList.Create;

lSQL.AvailableCriteriaList(lFields);

{set string list entries to field names}
for liIndex := 0 to lFields.Count - 1 do
lFields[liIndex] := TdaField(lFields.Objects[liIndex]).FieldName;

{create order no criteria}
liIndex := lFields.IndexOf('OrderNo');

if (liIndex <> -1) then
begin
lCriteria := lSQL.SelectCriteria(liIndex);
lCriteria.Operator := dacoEqual;
end;

{create art code criteria}
liIndex := lFields.IndexOf('ArtCode');

if (liIndex <> -1) then
begin
lCriteria := lSQL.SelectCriteria(liIndex);
lCriteria.Operator := dacoEqual;
end;

{create begin/end date criteria}
liIndex := lFields.IndexOf('OrderDate');

if (liIndex <> -1) then
begin
lCriteria := lSQL.SelectCriteria(liIndex);
lCriteria.Operator := dacoBetween;
end;

lFields.Free;
end;

{set order no. search value}
lSQL.Criteria[0].Value := '1020';

{set art code search value}
lSQL.Criteria[1].Value := 'C';

{set begin/end date search value}
lSQL.Criteria[2].Value := '31/12/90,31/12/99';

end;

--
Cheers,

Alexander Kramnik
Digital Metaphors