DIY Script: Difference between revisions

From eStream Software
 
(132 intermediate revisions by 3 users not shown)
Line 7: Line 7:
==DIY Script==
==DIY Script==
Available Action/Event
Available Action/Event
::{| class="wikitable"
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
|-
! Action/Event !! Description
! Action/Event !! Description
Line 56: Line 56:
|}
|}


:::----------------------------------------------------------------------------------------------------------------------------------------------------
{| style="margin: 1em auto 1em auto;"
::::{|
|-
|
----------------------------------------------------------------------------------------------------------------------------------------------------
{|
|-
|-
| [[File:Template.Tips-01.jpg|60px]]||  
| [[File:Template.Tips-01.jpg|60px]]||  
: Commonly we only '''OnOpen''', '''OnBeforeSave''' & '''OnGridColumnValueChanged''' Event
: Commonly we only '''OnOpen''', '''OnBeforeSave''' & '''OnGridColumnValueChanged''' Event
|}
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
|}


:::----------------------------------------------------------------------------------------------------------------------------------------------------
{| style="margin: 1em auto 1em auto;"
::::{|
|-
|
----------------------------------------------------------------------------------------------------------------------------------------------------
{|
|-
|-
| [[File:Template.Warning-01.jpg|80px]]||  
| [[File:Template.Warning-01.jpg|80px]]||  
: Avoid use 2 same Event/Action in 1 Business Object (eg 2 OnOpen in Sales Invoice is '''NOT''' allow)
: Avoid use 2 same Event/Action in 1 Business Object (eg 2 OnOpen in Sales Invoice is '''NOT''' allow)
|}
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
===Example 1 - Get UDF & Do Calculation===
|}
 
===Get UDF & Do Calculation===
Below is Example are doing following actions
Below is Example are doing following actions
* Get '''UDF_Length''' & '''UDF_Width''' from '''Maintain Item''' UDF Fields
* Get '''UDF_Length''' & '''UDF_Width''' from '''Maintain Item''' UDF Fields
* Do Calculation Qty := UDF_Length * UDF_Width * UDF_Rate
* Do Calculation Qty := UDF_Length * UDF_Width * UDF_Rate


Last Script Update : 08 Aug 2016<br />
Last Script Update : 23 Oct 2017<br />
Level : Basic
Level : Basic


Line 108: Line 117:
   Result := FComServer;
   Result := FComServer;
end;
end;
 
function CheckAvailFld(const lcds : TClientDataset; lFld: String): Boolean;
function CheckAvailFld(const lcds : TClientDataset; lFld: String): Boolean;
var findField: TField;
var findField: TField;
Line 135: Line 144:
   end;
   end;
end;
end;
 
procedure AssignValue;
procedure AssignValue;
var i : integer;
var i : integer;
Line 142: Line 151:
   for i:=0 to DataSet.FieldDefs.Count-1 do begin
   for i:=0 to DataSet.FieldDefs.Count-1 do begin
     AFld := DataSet.FieldDefs.Items[i].Name;
     AFld := DataSet.FieldDefs.Items[i].Name;
     if Copy(AFld, 1, Length('UDF_')) = 'UDF_' then begin // Set UDF Value
     DataSet.Edit; // Make sure is in Edit Mode
if Copy(AFld, 1, Length('UDF_')) = 'UDF_' then begin // Set UDF Value
       if CheckAvailFld(cdsData, AFld) then
       if CheckAvailFld(cdsData, AFld) then
         DataSet.FindField(AFld).AsString := cdsData.FindField(AFld).AsString;
         DataSet.FindField(AFld).AsString := cdsData.FindField(AFld).AsString;
     end;
     end;
   end;
   end;
end;
procedure Recalc;
begin
  // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
  DataSet.Edit; // Make sure is in Edit Mode
  DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                            DataSet.FindField('UDF_Width').AsFloat *
                                            DataSet.FindField('UDF_Rate').AsFloat; 
end;
end;
   
   
begin
begin
   if SameText(EditingField, 'ItemCode') OR // when selecting or change itemcode field
   if SameText(EditingField, 'ItemCode') then begin // when selecting or change itemcode field
    SameText(EditingField, 'UDF_Rate') then begin // when change UDF_Rate field
     try
     try
       GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item
       GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item
 
       AssignValue; // Set the Invoice detial UDF Fields from Maintain Item UDF Fields
       AssignValue; // Set the Invoice detial UDF Fields from Maintain Item UDF Fields
   
   
       // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
       Recalc;
      DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                                DataSet.FindField('UDF_Width').AsFloat *
                                                DataSet.FindField('UDF_Rate').AsFloat;
     finally
     finally
       cdsData.Free; // Free the temporary table after used
       cdsData.Free; // Free the temporary table after used
     end;
     end;
   end;
   end;
 
  if SameText(EditingField, 'UDF_Rate') then // when change UDF_Rate field
    Recalc;
end.
end.
</syntaxhighlight>
</syntaxhighlight>
Line 184: Line 202:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 2 - Change Title Caption===
===Change Title Caption===
Below is Example are doing following actions
Below is Example are doing following actions
* Change the Sales Quotation Title to '''My Quotation'''
* Change the Sales Quotation Title to '''My Quotation'''
Line 220: Line 238:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 3 - Get UDF From Maintain Customer===
===Get UDF From Maintain Customer===
Below is Example are doing following actions
Below is Example are doing following actions
* At '''Sales Invoice''' On Select Customer Code prompt UDF_MSG from '''Maintain Customer'''  
* At '''Sales Invoice''' On Select Customer Code prompt UDF_MSG from '''Maintain Customer'''  
Line 292: Line 310:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 4 - Get Transfer Information===
===Get Transfer Information - QT to IV===
Below is Example are doing following action
Below is Example are doing following action
* Quotation direct Transfer to Invoice.
* Get the '''Invoice number, Date & Qty''' which had transferred to '''Invoice''' in the Quotation and shown it below the '''Browse''' button
* Get the '''Invoice number, Date & Qty''' which had transferred to '''Invoice''' in the Quotation and shown it below the '''Browse''' button


Last Script Update : 30 Oct 2015<br />
Last Script Update : 20 Sep 2024<br />
Level : Advance
Level : Advance


Line 320: Line 339:
     L1, L2, L3, L4, L5, L6 : TLabel;
     L1, L2, L3, L4, L5, L6 : TLabel;
     cdsTemp : TClientDataset;
     cdsTemp : TClientDataset;
      
     BtnBrowse : TcxButton;
 
function ComServer: Variant;
function ComServer: Variant;
begin
begin
   if FComServer = Null then  
   if FComServer = Null then
     FComServer := CreateOleObject('SQLAcc.BizApp');
     FComServer := CreateOleObject('SQLAcc.BizApp');
   Result := FComServer;
   Result := FComServer;
Line 343: Line 363:
begin
begin
   lDocKey := M.Dataset.FindField('Dockey').AsString;
   lDocKey := M.Dataset.FindField('Dockey').AsString;
   lDtlKey := D.Dataset.FindField('Dtlkey').AsString;
 
   if Trim(D.Dataset.FindField('Dtlkey').AsString) <> '' then
    lDtlKey := D.Dataset.FindField('Dtlkey').AsString else
    lDtlKey := '-1';
   FComServer := null;
   FComServer := null;
   cdsTemp := TClientDataset.Create(nil);
   cdsTemp := TClientDataset.Create(nil);
Line 372: Line 395:
       if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
       if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
         L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
         L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
       L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat);  
       L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat);
     finally
     finally
       cdsTemp.Free;
       cdsTemp.Free;
     end;
     end;
   end;
   end;
 
 
end;
end;


Line 383: Line 406:
   M    := TDataSource(Self.FindComponent('dsDocMaster'));
   M    := TDataSource(Self.FindComponent('dsDocMaster'));
   D    := TDataSource(Self.FindComponent('dsDocDetail'));
   D    := TDataSource(Self.FindComponent('dsDocDetail'));
     
 
   C := Self.FindComponent('frDataSetButton1');
   C := Self.FindComponent('frDataSetButton1');
   if Assigned(C) then begin
  BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
   if TWinControl(Self.FindComponent('DetailControl')).Visible then begin
     T.Enabled  := True;
     T.Enabled  := True;
     T.Interval := 1000; // = Update every 1 second
     T.Interval := 1000; // = Update every 1 second
     T.OnTimer  := @OnTimer;
     T.OnTimer  := @OnTimer;
 
 
     with L1 do begin
     with L1 do begin
       Parent    := TWinControl(C);
       Parent    := TWinControl(C);
       Width      := 66;
       Width      := 66;
       Left      := 6;
       Left      := 6;
       Top        := 200;
       Top        := BtnBrowse.Top + BtnBrowse.Height+5;
       Caption    := 'Invoice No';
       Caption    := 'Invoice No';
       Font.Color := clBlue;
       Font.Color := clBlue;
       Font.Style := [fsBold];
       Font.Style := [fsBold];
     end;  
     end;
     with L2 do begin
     with L2 do begin
       Parent    := TWinControl(C);
       Parent    := TWinControl(C);
       Width      := 66;
       Width      := 66;
       Left      := 6;
       Left      := 6;
       Top        := 215;
       Top        := L1.Top + L1.Height+5;
       Caption    := 'DocNo';
       Caption    := 'DocNo';
       Font.Color := clBlue;
       Font.Color := clBlue;
Line 413: Line 437:
       Width      := 66;
       Width      := 66;
       Left      := 6;
       Left      := 6;
       Top        := 240;
       Top        := L2.Top + L2.Height+5;
       Caption    := 'IV Date';
       Caption    := 'IV Date';
       Font.Color := clGreen;
       Font.Color := clGreen;
Line 422: Line 446:
       Width      := 66;
       Width      := 66;
       Left      := 6;
       Left      := 6;
       Top        := 255;
       Top        := L3.Top + L3.Height+5;
       Caption    := 'Date';
       Caption    := 'Date';
       Font.Color := clGreen;
       Font.Color := clGreen;
Line 432: Line 456:
       Width      := 66;
       Width      := 66;
       Left      := 6;
       Left      := 6;
       Top        := 280;
       Top        := L4.Top + L4.Height+5;
       Caption    := 'XF Qty';
       Caption    := 'XF Qty';
       Font.Color := clRed;
       Font.Color := clRed;
Line 441: Line 465:
       Width      := 66;
       Width      := 66;
       Left      := 6;
       Left      := 6;
       Top        := 295;
       Top        := L5.Top + L5.Height+5;
       Caption    := 'Qty';
       Caption    := 'Qty';
       Font.Color := clRed;
       Font.Color := clRed;
       Font.Style := [fsBold];
       Font.Style := [fsBold];
     end;    
     end;
   end;  
   end;
end.
end.
</syntaxhighlight>
</syntaxhighlight>
Line 454: Line 478:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 5 - Only Enable Some of the field on Click Edit by User===
===Only Enable Some of the field on Click Edit by User===
Below is Example are doing following action in Sales Cash Sales
Below is Example are doing following action in Sales Cash Sales
* Set all Field to Read Only at Header except Agent, DocNo, DocRef1, DocNoEx & UDF_Expired
* Set all Field to Read Only at Header except Agent, DocNo, DocRef1, DocNoEx & UDF_Expired
Line 460: Line 484:


Remember to Enable Back by Disable the GetAuthorised (see below) Script & Insert it to '''OnBeforeNew''' script<br />
Remember to Enable Back by Disable the GetAuthorised (see below) Script & Insert it to '''OnBeforeNew''' script<br />
Cons : It will be delay about 1 to 2 sec. after click Edit & New button
Cons : It will be delay about <span style="color:#0000ff">3 to 4 sec.</span> after click Edit & New button


Last Script Update : 03 Nov 2015<br />
Last Script Update : 03 Nov 2015<br />
Line 597: Line 621:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 6 - Calculate Age between 2 date===
===Calculate Age between 2 date===
Below is Example are doing following actions
Below is Example are doing following actions
* Do Calculation UDF_Age := UDF_DateTo - UDF_DateFrom
* Do Calculation UDF_Age := UDF_DateTo - UDF_DateFrom
Line 632: Line 656:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 7 - Simple DropDown Selection List===
===Simple DropDown Selection List===
Below is Example are doing following actions
Below is Example are doing following actions
* Create Label
* Create Label
* Create a drop down list at Maintain Customer for UDF_SearchKey
* Create a drop down list at Maintain Customer for UDF_SearchKey


Last Script Update : 24 Nov 2015<br />
Last Script Update : 26 Mar 2018<br />
Level : Advance
Level : Advance


Line 664: Line 688:
procedure Setup;
procedure Setup;
begin
begin
   cdsSearchKey := TClientDataset.Create(nil);  
  dsSearchKey  := TDataSource.Create(nil);
   cdsSearchKey := TClientDataset.Create(nil);
  grGrid1      := TcxGrid.Create(nil); 
end;
 
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
end;


procedure TearDown;
procedure TearDown;
begin
begin
   cdsSearchKey.Free;
   FreeCmpt(cdsSearchKey);
  FreeCmpt(dsSearchKey);
  FreeCmpt(gvSearchKey);
  FreeCmpt(grGrid1);
end;
end;


Line 680: Line 716:
   cdsSearchKey.CreateDataSet;
   cdsSearchKey.CreateDataSet;
      
      
  grGrid1    := TcxGrid.Create(nil);
   gvSearchKey := TcxGridDBTableView.Create(grGrid1);
   gvSearchKey := TcxGridDBTableView.Create(grGrid1);
  dsSearchKey := TDataSource.Create(nil);   
 
   with grGrid1 do begin
   with grGrid1 do begin
     Parent  := nil;
     Parent  := nil;
Line 700: Line 735:
   dsSearchKey.DataSet          := cdsSearchKey;
   dsSearchKey.DataSet          := cdsSearchKey;
   with gvSearchKey do begin
   with gvSearchKey do begin
    Name                            := 'Restricted_1';
     OptionsCustomize.ColumnFiltering := False;
     OptionsCustomize.ColumnFiltering := False;
     DataController.DataSource  := dsSearchKey;
     DataController.DataSource  := dsSearchKey;
Line 731: Line 767:
begin
begin
   M := TDataSource(Self.FindComponent('dsDocMaster'));
   M := TDataSource(Self.FindComponent('dsDocMaster'));
   edCompanyCategory := Self.FindComponent('edCompanyCategory') as TcxDBExtLookupComboBox;
   edCompanyCategory := Self.FindComponent('edControlAccount') as TcxDBExtLookupComboBox;
   edSearchKey := TcxDBExtLookupComboBox.Create(edCompanyCategory);
   edSearchKey := TcxDBExtLookupComboBox.Create(edCompanyCategory);
   lbSearchKey := TLabel.Create(edCompanyCategory);
   lbSearchKey := TLabel.Create(edCompanyCategory);
Line 742: Line 778:
   with lbSearchKey do begin // Create Label
   with lbSearchKey do begin // Create Label
     Parent  := edCompanyCategory.Parent;
     Parent  := edCompanyCategory.Parent;
     Left    := edCompanyCategory.Left + edCompanyCategory.Width + 35;
     Left    := edCompanyCategory.Left + edCompanyCategory.Width + 150;
     Top    := edCompanyCategory.Top +3;
     Top    := edCompanyCategory.Top +3;
     Caption := 'Search key :';     
     Caption := 'Search key :';     
Line 749: Line 785:
   with edSearchKey do begin // Create Drop List
   with edSearchKey do begin // Create Drop List
     Parent  := edCompanyCategory.Parent;
     Parent  := edCompanyCategory.Parent;
     Left    := edCompanyCategory.Left + edCompanyCategory.Width + 100;
     Left    := edCompanyCategory.Left + edCompanyCategory.Width + 250;
     Top    := edCompanyCategory.Top;
     Top    := edCompanyCategory.Top;
     Name    := 'edSearch';
     Name    := 'edSearch';
Line 767: Line 803:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 8 - Auto Add Service Charge===
===Auto Add Service Charge===
Below is Example are doing following actions
Below is Example are doing following actions
* Auto Append ItemCode '''SERVICE'''
* Auto Append ItemCode '''SERVICE'''
* Do Calculation for ItemCode '''SERVICE''' UnitPrice := Total Doc Amount (Excluding GST) * 0.1 & Round to 2 decimal point
* Do Calculation for ItemCode '''SERVICE''' UnitPrice := Total Doc Amount (Excluding GST) * 0.1 & Round to 2 decimal point


Last Script Update : 12 Mar 2016<br />
Last Script Update : 08 Nov 2022<br />
Level : Basic
Level : Basic


Line 795: Line 831:
procedure DelOldRecord;
procedure DelOldRecord;
begin
begin
   ODocAmt := 0.00;
   D.DataSet.Filter  := 'ItemCode=''SERVICE'' ';
  D.DataSet.DisableControls;
  D.DataSet.Filtered := True;
   try
  try
    if D.DataSet.RecordCount > 0 then begin
      D.DataSet.First;
      D.DataSet.Delete;
    end;
    D.DataSet.Filter   := '';
    D.DataSet.Filtered := False;
     D.DataSet.First;
     D.DataSet.First;
     while not D.DataSet.Eof do begin
     while not D.DataSet.Eof do begin
      if D.DataSet.FindField('ItemCode').AsString = 'SERVICE' then
        D.DataSet.Delete
      else
       if D.DataSet.FindField('ItemCode').AsString <> 'RTN5Cents' then // To Excluding 5 Cents rounding
       if D.DataSet.FindField('ItemCode').AsString <> 'RTN5Cents' then // To Excluding 5 Cents rounding
         ODocAmt := ODocAmt + D.DataSet.FindField('Amount').AsFloat; // To Get Total DocAmt before GST
         ODocAmt := ODocAmt + D.DataSet.FindField('Amount').AsFloat; // To Get Total DocAmt before GST
       D.DataSet.Next;
       D.DataSet.Next;
     end;  
     end;
   finally
   finally
     D.DataSet.EnableControls;
     D.DataSet.Filter  := '';
   end;
    D.DataSet.Filtered := False;
   end;
end;
end;


begin
begin
   D := TDataSource(Self.FindComponent('dsDocDetail'));
   D := TDataSource(Self.FindComponent('dsDocDetail'));
 
  ODocAmt := 0.00;
   DelOldRecord;
   DelOldRecord;


   with D.DataSet do begin      
   with D.DataSet do begin
     Append;
     Append;
     FindField('ItemCode').AsString    := 'SERVICE';
     FindField('ItemCode').AsString    := 'SERVICE';
     FindField('UnitPrice').Value      := SimpleRoundToEx(ODocAmt * 0.1, -2);  
     FindField('UnitPrice').Value      := SimpleRoundToEx(ODocAmt * 0.1, -2);
     FindField('Disc').AsString        := '';
     FindField('Disc').AsString        := '';
     FindField('Transferable').AsString := 'F';
     FindField('Transferable').AsString := 'F';
     Post;
     Post;
   end;  
   end;
end.
end.
</syntaxhighlight>
</syntaxhighlight>
Line 831: Line 871:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 9 - Auto Click Profit Estimator Button===
===Auto Click Profit Estimator Button===
Below is Example are doing following actions
Below is Example are doing following actions
* Auto Click the Profit Estimator button
* Auto Click the Profit Estimator button
Line 867: Line 907:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 10 - Simulate build-in Calculation using UDF Fields===
===Simulate build-in Calculation using UDF Fields===
Below is Example are doing following actions
Below is Example are doing following actions
* Get Discount Amount
* Get Discount Amount
Line 960: Line 1,000:
   lDisc := SimpleRoundToEx(GetDiscountedValue(lAmt, DataSet.FindField('UDF_Disc').AsString), -2); //lAmt - UDF_Disc
   lDisc := SimpleRoundToEx(GetDiscountedValue(lAmt, DataSet.FindField('UDF_Disc').AsString), -2); //lAmt - UDF_Disc
   lTax  := SimpleRoundToEx(GetTaxedValue(lDisc, DataSet.FindField('TaxRate').AsString), -2); //TaxAmt
   lTax  := SimpleRoundToEx(GetTaxedValue(lDisc, DataSet.FindField('TaxRate').AsString), -2); //TaxAmt
   DataSet.FindField('TaxAmt').AsFloat := lTax;
   DataSet.FindField('TaxAmt').AsFloat := lTax - lDisc;
   DataSet.FindField('Amount').AsFloat := lDisc;
   DataSet.FindField('Amount').AsFloat := lDisc;
end;
end;
Line 978: Line 1,018:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 11 - Auto Click Edit, Save & Browse Button===
===Auto Click Edit, Save & Browse Button===
Below is Example are doing following actions
Below is Example are doing following actions
* Select Record at Grid
* Select Record at Grid
Line 986: Line 1,026:
* Auto Click Browse button
* Auto Click Browse button


Last Script Update : 26 Aug 2016<br />
Last Script Update : 20 Apr 2019<br />
Level : Basic
Level : Basic


Line 1,021: Line 1,061:
       i := i + 1;
       i := i + 1;
       Self.Caption := Format('%s of %s', [IntToStr(i), IntToStr(G.DataSet.RecordCount)]);
       Self.Caption := Format('%s of %s', [IntToStr(i), IntToStr(G.DataSet.RecordCount)]);
       BtnBrowse.Click;
       Application.ProcessMessages;
  BtnBrowse.Click;
       BtnEdit.Click;
       BtnEdit.Click;
       BtnSave.Click;
       BtnSave.Click;
Line 1,058: Line 1,099:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 12 - Get Current User Name and Current Working Date===
===Get Current User Name and Current Working Date===
Below is Example are doing following actions
Below is Example are doing following actions
* Set Current Login User ID & Current Working Date to UDF
* Set Current Login User ID & Current Working Date to UDF
Line 1,097: Line 1,138:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 13 - Get Excel Data===
===Get Excel Data===
Below is Example are doing following actions
Below is Example are doing following actions
* Get Data from Excel & append to detail
* Get Data from Excel & append to detail
Line 1,201: Line 1,242:
       Top    := btnMoveDown.Top;
       Top    := btnMoveDown.Top;
       Height  := btnMoveDown.Height;
       Height  := btnMoveDown.Height;
       Left    := btnMoveDown.Left + btnMoveDown.Width + 95;
       Left    := btnMoveDown.Left + btnMoveDown.Width + 130;
       Width  := 120;
       Width  := 120;
       Caption := '&1. Get Excel Data';
       Caption := '&1. Get Excel Data';
Line 1,215: Line 1,256:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 14 - Copy To & Paste From Clipboard===
===Copy To & Paste From Clipboard===
Below is Example are doing following actions
Below is Example are doing following actions
* Copy string to Clipboard
* Copy string to Clipboard
Line 1,289: Line 1,330:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 15 - Get CSV/TXT Data===
===Get CSV/TXT Data===
Below is Example are doing following actions
Below is Example are doing following actions
* Get Data from CSV/TXT & append to detail
* Get Data from CSV/TXT & append to detail


Last Script Update : 14 Jan 2016<br />
Last Script Update : 03 Mar 2018<br />
Level : Advance
Level : Advance


Line 1,322: Line 1,363:
   S := TStringList.Create;
   S := TStringList.Create;
   try
   try
     S.Delimiter     := ',';
     S.Delimiter       := ',';
     S.DelimitedText := AStr;
S.StrictDelimiter := True;
     S.DelimitedText   := AStr;
     if AIndex < S.Count then Result := Trim(S[AIndex])
     if AIndex < S.Count then Result := Trim(S[AIndex])
     else Result := '';
     else Result := '';
Line 1,390: Line 1,432:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 16 - Set Default Filtering For Sales Report===
===Set Default Filtering For Sales Report===
Below is Example are doing following actions
Below is Example are doing following actions
* Filter by Agent in Sales Document Listing
* Filter by Agent in Sales Document Listing
Line 1,414: Line 1,456:
|  
|  
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
//GL
//- GL Document Listing - Panel2
//- GL Journal of Transaction Listing - Panel2
//- GL Transaction Summary - Panel2
//- GL Ledger - Panel2
//- GL Receipt and Payment - Panel1
//Customer
//Customer
//- Customer Document Listing - Panel1, Panel3
//- Customer Document Listing - Panel1, Panel3
Line 1,473: Line 1,522:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 17 - Copy RefCost to UDF At Maintain Item===
===Copy RefCost to UDF At Maintain Item===
Below is Example are doing following actions
Below is Example are doing following actions
* Set UDF_CommCost := RefCost * 1.1 (round to 2 decimal)
* Set UDF_CommCost := RefCost * 1.1 (round to 2 decimal)
Line 1,509: Line 1,558:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 18 - Recalculate Formula Done at OnGridColumnValueChanged Script for Transferred Document===
===Recalculate Formula Done at OnGridColumnValueChanged Script for Transferred Document===
Below is Example are doing following actions
Below is Example are doing following actions
* Recalculate the Formula Done at OnGridColumnValueChanged
* Recalculate the Formula Done at OnGridColumnValueChanged
Line 1,515: Line 1,564:
* Copy UDF fields (eg UDF_Qty) to Original fields for Non Transfer Items
* Copy UDF fields (eg UDF_Qty) to Original fields for Non Transfer Items


Last Script Update : 12 Mar 2016<br />
Last Script Update : 08 Oct 2024<br />
Level : Basic
Level : Basic


Line 1,587: Line 1,636:
         VD  := FindField('Disc').AsString;       
         VD  := FindField('Disc').AsString;       
         Edit;
         Edit;
         FindField('SEQ').Value := i; //To make sure the SEQ field is correct...
         FindField('SEQ').AsFloat := (i*1000); //To make sure the SEQ field is correct...
         if Trim(FindField('FromDocType').AsString) = '' then begin // Copy UDF to Original Fields
         if Trim(FindField('FromDocType').AsString) = '' then begin // Copy UDF to Original Fields
           FindField('Qty').AsFloat      := FindField('UDF_Qty').AsFloat;
           FindField('Qty').AsFloat      := FindField('UDF_Qty').AsFloat;
Line 1,617: Line 1,666:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 19 - Hide Stock Opening Grid In Maintain Item===
===Hide Stock Opening Grid In Maintain Item===
Below is Example are doing following actions
Below is Example are doing following actions
* Hide the Grid & Navigator Button for Opening Balance
* Hide the Grid & Navigator Button for Opening Balance
Line 1,662: Line 1,711:
:10. Click '''Save''' button
:10. Click '''Save''' button


===Example 20 - Export Data To Excel===
===Export Data To Excel===
Below is Example are doing following actions
Below is Example are doing following actions
* Export Maintain Agent List to Excel
* Export Maintain Agent List to Excel
Line 1,769: Line 1,818:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 21 - Change DocRef 1 Label===
===Change DocRef 1 Label===
Below is Example are doing following actions
Below is Example are doing following actions
* Change the Purchase Order DocRef1 Label to '''C.PO # :'''
* Change the Purchase Order DocRef1 Label to '''C.PO # :'''


Last Script Update : 03 Sep 2016<br />
Last Script Update : 21 Jul 2021<br />
Level : Basic
Level : Basic


Line 1,792: Line 1,841:
|  
|  
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
var C1, C2 : TComponent;
var L1, L2: TComponent;
Begin
    T : TTimer;
  C1 := Self.FindComponent('lbDocRef1');
    ACount : Integer;
   C2 := Self.FindComponent('lbTerms');
procedure OnTimer(Sender: TObject);
   if Assigned(C1) then begin
begin
     TLabel(C1).Caption := 'C.PO # :';
   T.Tag := T.Tag + 1;
     TLabel(C1).Left := TLabel(C2).Left;
   if Assigned(L1) then begin
     TLabel(L1).left := TLabel(L2).Left;//220;
     TLabel(L1).Caption  := 'C.PO # :'; // Change the label Caption
   end;
   end;
end.
  T.Enabled  := not (T.Tag = ACount);
end;
 
begin
  L1 := Self.FindComponent('lbDocRef1'); // Find the original label
  L2 := Self.FindComponent('lbTerms');
  ACount := 5;
  T          := TTimer.Create(Self);
  T.Enabled  := True;
  T.Interval := 1000; // = 1 sec
  T.OnTimer  := @OnTimer;
  T.Tag      := 0;
end;
</syntaxhighlight>
</syntaxhighlight>
|}
|}
Line 1,807: Line 1,870:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 22 - Add Click Button===
===Add Click Button===
Below is Example are doing following actions
Below is Example are doing following actions
* Create 2 buttons at Sales Quotation
* Create 2 buttons at Sales Quotation
:* 1 at Beside Profit Estimator
:* 1 at Beside Up Down Button
:* 1 at Below Browse Button
:* 1 at Below Browse Button


Last Script Update : 07 Sep 2016<br />
Last Script Update : 29 Mar 2022<br />
Level : Basic
Level : Basic


Line 1,833: Line 1,896:
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
var C : TComponent;
var C : TComponent;
     btnProfit, BtnBrowse,  
     //btnMoveDown : TSpeedButton;//for SQL Acc Version 5.2022.929.815 and below
    btn1, btn2  : TcxButton;
    btnMoveDown : TcxButton;//for SQL Acc Version 5.2022.930.816 and above
    BtnBrowse, btn1, btn2  : TcxButton;


procedure OnClick1(Sender: TObject);
procedure OnClick1(Sender: TObject);
Line 1,849: Line 1,913:


begin
begin
   C         := Self.FindComponent('frDataSetButton1');
   C           := Self.FindComponent('frDataSetButton1');
   BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
   BtnBrowse   := TcxButton(C.FindComponent('btnViewDetail'));
   btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
   //btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));//for SQL Acc Version 5.2022.929.815 and below
  btnMoveDown := TcxButton(Self.FindComponent('BtnMoveDown'));//for SQL Acc Version 5.2022.930.816 and above
    
    
   btn1    := TcxButton.Create(btnProfit);
   btn1    := TcxButton.Create(btnMoveDown);
   btn2    := TcxButton.Create(btnProfit);
   btn2    := TcxButton.Create(C);
    
    
   if Assigned(btnProfit) then begin     
   if Assigned(btnMoveDown) then begin     
     with btn1 do begin //Create Beside the Profit Estimator Button  
     with btn1 do begin //Create Beside the Up Down Button
       Parent  := btnProfit.Parent;
       Parent  := btnMoveDown.Parent;
       Top      := btnProfit.Top;
       Top      := btnMoveDown.Top;
       Left    := btnProfit.Left + btnProfit.Width + 3;
       Left    := btnMoveDown.Left + btnMoveDown.Width + 120;
       Height  := btnProfit.Height;
       Height  := btnMoveDown.Height;
       Width    := 100;
       Width    := 100;
       Caption  := '&1. Button';
       Caption  := '&1. Button';
Line 1,889: Line 1,954:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 23 - Using TxQuery===
===Using TxQuery===
Below is Example are doing following actions
Below is Example are doing following actions
* Create 1 button at Sales Quotation to Get Total Base Quantity
* Create 1 button at Sales Quotation to Get Total Base Quantity
Line 1,968: Line 2,033:
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


===Example 24 - How to find component in different Event?===
===How to find component in different Event?===
Below is Example are doing following actions
Below is Example are doing following actions
* Create 1 button at Sales Quotation
* Create 1 button at Sales Quotation
Line 2,002: Line 2,067:
begin
begin
   btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
   btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
   btnNew    := TcxButton.Create(self);
   btnNew    := TcxButton.Create(self); //Important it must be xx.Create(self)
   if Assigned(btnProfit) then begin
   if Assigned(btnProfit) then begin
     with btnNew do begin
     with btnNew do begin
Line 2,008: Line 2,073:
       Top    := btnProfit.Top;
       Top    := btnProfit.Top;
       Height  := btnProfit.Height;
       Height  := btnProfit.Height;
       Name    := 'Calc_1';
       Name    := 'Calc_1'; //Name of the Component
       Left    := btnProfit.Left + btnProfit.Width + 3;
       Left    := btnProfit.Left + btnProfit.Width + 3;
       Width  := 80;
       Width  := 80;
Line 2,021: Line 2,086:
:10. Click '''Save''' button
:10. Click '''Save''' button


'''Steps - OnBeforeSave'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Quotation'''
:03. Right Click the '''Sales Quotation'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnBeforeSave''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnBeforeSave Script 
|-
|
<syntaxhighlight lang="delphi">
var btnNew  : TcxButton;
begin
  btnNew := TcxButton(Self.FindComponent('Calc_1')); //Find the Newly created Component
  if Assigned(BtnNew) then
    btnNew.Click;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button


<div style="float: right;">  [[#top|[top]]]</div>
===Example 25 - ===
* More Coming Soon....
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>


==FAQ==
===Simple DropDown Selection List in Document Detail ===
01. Why when click '''Compile Script''' button prompt error '''[Error] (xx:xx):Unknown identifier 'Dataset''''
Below is Example are doing following actions
: This happen if you doing script on the event '''OnGridColumnValueChanged''' & can ignore the error if you confirm your script is correct.
* Create a drop down list at Sales Invoice Detail for UDF_DeliveryType
 
Last Script Update : 27 Dec 2016<br />
Level : Advance
 
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''  
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)


02. How do I know my script is correct when using the script on the event '''OnGridColumnValueChanged''' ?
{| class="mw-collapsible mw-collapsed wikitable"
: You can add below script at 1st line before you click '''Compile Script''' button but remember to remove it before you click '''Save''' button.
! OnOpen Script  
|-
|
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
var EditingField, Dataset :Variant;
var cdsDlvType : TClientDataSet;
</syntaxhighlight>
    gcDlvType  : TcxGridColumn;
    grGrid1    : TcxGrid;
    dsDocMaster, dsDlvType : TDataSource;
    gvDocDetail, gvDlvType : TcxGridDBTableView;


03. How many data source (TDatasource) it had for data entry (eg Sales Invoice) & what is each name?
procedure CreateXMLTable;
: Generally it had 3 data source & some had extra 1 (i.e. total 4).
begin
: Below is the list & detail
  with cdsDlvType.FieldDefs do
{| class="wikitable" style="margin: 1em auto 1em auto;"
      Add('Code', ftString,  50, False);
|-
  cdsDlvType.CreateDataSet;
! DataSource Name !! Description
end;
|-
| dsGrid || Data shown in Grid(Read Only mode) Before Click '''Detail''' Button
|-
| dsDocMaster || Header Data
|-
| dsDocDetail || Item Grid(Detail) Data
|-
| dsSerialNumber || Selected Item Code Serial Number in the Item Grid(Detail) Data
|}


04. How to check the Record in Edit Mode?
procedure AddDlvType(const s : String);
: You can use below script
<syntaxhighlight lang="delphi">
var AState : TDataSetState;
    M      : TDataSource;
begin
begin
   M := TDataSource(Self.FindComponent('dsDocMaster'));
   with cdsDlvType do begin
  AState := M.DataSet.State;
    Append;
  if (AState = dsInsert) or (AState = dsEdit)  then begin // Check is in Insert or Edit Mode
    FieldValues['Code'] := s;
     MessageDlg('Yes In Edit Mode', mtInformation, [mbOK], 0);
     Post;
   end;
   end;
end;
end;
procedure AddRecord;
begin
  cdsDlvType := TClientDataSet.Create(self);
  cdsDlvType.Name := 'cdsDlvType';
  CreateXMLTable;
  AddDlvType('By Mail');
  AddDlvType('By Air');
  AddDlvType('By E-Mail');
  AddDlvType('By Phone');
  AddDlvType('By Fax');
  AddDlvType('By Courier');
end;
procedure CreateTable;
begin
  grGrid1  := TcxGrid.Create(nil);
  gvDlvType := TcxGridDBTableView.Create(grGrid1);
  dsDlvType  := TDataSource.Create(gvDocDetail);
  AddRecord;
  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;
  with grGrid1.Levels.Add do begin
    Caption  := 'DlvTypeGrid';
    GridView := TcxCustomGridView(gvDlvType);
  end;
  with gvDlvType.CreateColumn do begin
    Caption              := 'Code';
    DataBinding.FieldName := 'Code';
    HeaderAlignmentHorz  := taCenter;
    Options.IncSearch    := False;
  end;
//  cdsDlvType.IndexFieldNames := 'Code'; //Sort by Code
  dsDlvType.DataSet          := cdsDlvType;
  with gvDlvType do begin
    OptionsCustomize.ColumnFiltering := False;
    DataController.DataSource  := dsDlvType;
    OptionsView.Footer          := True;
    OptionsView.ColumnAutoWidth := True;
    OptionsView.GroupByBox      := False;
    ApplyBestFit(nil, False, False);//Optional
  end;
  with gvDlvType.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column  := gvDlvType.GetColumnByFieldName('Code');
        Position := spFooter;
        Kind    := skCount;
        Format  := 'Count = #';
      end;
    finally
      EndUpdate;
    end;
  end;
end;
begin
  gvDocDetail  := TcxGridDBTableView(Self.FindComponent('gvDocDetail'));
  CreateTable;
  gcDlvType:= gvDocDetail.GetColumnByFieldName('UDF_DeliveryType');
  SetGridColumnPropertiesClass(gcDlvType,'TcxExtLookupComboBoxProperties');
  with TcxExtLookupComboBoxProperties(gcDlvType.Properties) do begin
    DropDownWidth      := 300;
    DropDownListStyle  := lsEditFixedList;
    ImmediatePost      := True;
    View              := gvDlvType;
    KeyFieldNames      := 'Code';
    ListFieldItem      := gvDlvType.GetColumnByFieldName('Code');
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Default Company Code & Payment Method In Sales Cash Sales===
Below is Example are doing following actions
* On Click New auto Set Default for
:- Customer Code  := 300-C0001
:- Payment Method  := 320-000
:- Payment Project := ----
Last Script Update : 28 Dec 2016<br />
Level : Basic
'''Steps - OnAfterNew'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Cash Sales'''
:03. Right Click the '''Sales Cash Sales'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnAfterNew''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnAfterNew Script 
|-
|
<syntaxhighlight lang="delphi">
var M : TDataSource;
    C : TComponent;
begin
  C := Self.FindComponent('actBarcode');
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  if Assigned(M) then begin
    with M.Dataset do begin
      FindField('Code').AsString            := '300-C0001';
      FindField('P_PAYMENTMETHOD').AsString  := '320-000';
      FindField('P_PAYMENTPROJECT').AsString := '----';
    end;
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Get Transfer Information - QT to DO to IV===
Below is Example are doing following action
* Quotation Transfer to Delivery Order to Invoice.
* Get the '''Quotation number, Date & Qty''' in the '''Invoice''' and shown it below the '''Browse''' button
Last Script Update : 03 Jun 2017<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var FComServer, lBizObj : Variant;
    C : TComponent;
    T : TTimer;
    M, D : TDataSource;
    L1, L2, L3, L4, L5, L6 : TLabel;
    cdsTemp : TClientDataset;
   
function ComServer: Variant;
begin
  if FComServer = Null then
    FComServer := CreateOleObject('SQLAcc.BizApp');
  Result := FComServer;
end;
procedure Setup;
begin
  T  := TTimer.Create(Self);
  L1 := TLabel.Create(self);
  L2 := TLabel.Create(self);
  L3 := TLabel.Create(self);
  L4 := TLabel.Create(self);
  L5 := TLabel.Create(self);
  L6 := TLabel.Create(self);
end;
procedure GetToDocInfo;
var lSQL, lDtlkey : String;
begin
  lDtlKey := D.Dataset.FindField('FromDtlkey').AsString;
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_QT A '+
                'INNER JOIN SL_QTDTL B ON (A.Dockey=B.Dockey) ' +
                'WHERE B.Dockey=(SELECT FROMDOCKEY FROM SL_DODTL ' +
                'WHERE Dtlkey=%s) '+
                'AND B.DtlKey=(SELECT FROMDTLKEY FROM SL_DODTL '+
                'WHERE Dtlkey=%s)',[lDtlkey, lDtlkey]);
 
  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
  finally
    FComServer := null;
  end;
end;
procedure OnTimer(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  L2.Caption := '';
  L4.Caption := '';
  L6.Caption := ''; 
  if (AState = dsBrowse) and (Trim(D.Dataset.FindField('FromDtlkey').AsString) <> '') then begin
    GetToDocInfo;
    try
      L2.Caption := cdsTemp.FindField('DocNo').AsString;
      if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
        L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
      L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat);
    finally
      cdsTemp.Free;
    end;
  end;
 
end;
begin
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  D    := TDataSource(Self.FindComponent('dsDocDetail'));
     
  C := Self.FindComponent('frDataSetButton1'); 
  if Assigned(C) then begin
    T.Enabled  := True;
    T.Interval := 1000; // = Update every 1 second
    T.OnTimer  := @OnTimer; 
 
    with L1 do begin
      Parent    := TWinControl(C);
      Width      := 66;
      Left      := 6;
      Top        := 200;
      Caption    := 'QT No';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;   
    with L2 do begin
      Parent    := TWinControl(C);
      Width      := 66;
      Left      := 6;
      Top        := 215;
      Caption    := 'DocNo';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;
    with L3 do begin
      Parent    := TWinControl(C);
      Width      := 66;
      Left      := 6;
      Top        := 240;
      Caption    := 'QT Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;
    with L4 do begin
      Parent    := TWinControl(C);
      Width      := 66;
      Left      := 6;
      Top        := 255;
      Caption    := 'Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;
    with L5 do begin
      Parent    := TWinControl(C);
      Width      := 66;
      Left      := 6;
      Top        := 280;
      Caption    := 'Orig. Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;
    with L6 do begin
      Parent    := TWinControl(C);
      Width      := 66;
      Left      := 6;
      Top        := 295;
      Caption    := 'Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;     
  end; 
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Approval by User if Document Amount Exceed Limit===
Below example is to prompt Authorised if the Document Amount Exceed the limit (eg 10000) in Sales Invoice
Last Script Update : 18 Sep 2023<br />
Level : Advance
'''Steps - OnBeforeSave'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnBeforeSave''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnBeforeSave Script 
|-
|
<syntaxhighlight lang="delphi">
var FComServer : Variant;
    M : TDataSource; 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
procedure Approved(const lUN, lUPW : string);
var lAPW, lSQL : String;
    cdsUsers  : TClientDataSet;
begin
  FComServer  := null;
  cdsUsers := TClientDataSet.Create(nil);
  lSQL    := Format('SELECT UDF_Password FROM SY_USER WHERE Code=%s AND UDF_AllowApproved = 1',[QuotedStr(lUN)]);
  try
    FComServer      := null;   
    cdsUsers.Data := ComServer.DBManager.Execute(lSQL);
    lAPW := cdsUsers.FindField('UDF_Password').AsString;
    if (lUPW <> lAPW) or (Trim(lAPW) = '') then begin
      MessageDlg('Incorrect Password', mtWarning, [mbOK], 0);
      Abort;
    end;
  finally
    FComServer := null;
    cdsUsers.Free;
  end;
end;
procedure InitPWForm;
var fmPassword : TForm;
    lbUserName, lbPassword : TcxLabel;
    edUserName, edPassword : TEdit;
    btnOK      : TButton;
begin
  fmPassword := TForm.Create(nil);
  lbUserName := TcxLabel.Create(fmPassword);
  lbPassword := TcxLabel.Create(fmPassword);
  edUserName := TEdit.Create(fmPassword);
  edPassword := TEdit.Create(fmPassword);
  btnOK      := TButton.Create(fmPassword);
  try
    with fmPassword do begin
      Parent      := nil;
      Height      := 150;
      Width      := 280;
      Caption    := 'Please Enter User Name and Password';
      Color      := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbUserName do begin
      Parent  := fmPassword;
      Left    := 10;
      Top    := 10;
      Caption := '&User Name :';
    end;
    with edUserName do begin
      Parent  := fmPassword;
      Left    := 100;
      Top      := 10;
      Width    := 150;
      CharCase := ecUpperCase;
    end;
    with lbPassword do begin
      Parent  := fmPassword;
      Left    := 10;
      Top    := 35;
      Caption := '&Password :';
    end;
    with edPassword do begin
      Parent := fmPassword;
      Left  := 100;
      Top    := 35;
      Width  := 150;
      PasswordChar := '*';
    end;
    with btnOK do begin
      Parent      := fmPassword;
      Top        := 70;
      Width      := 100;
      Left        := (fmPassword.Width/2) - 50;
      Caption    := '&OK';
      ModalResult := mrOk;
    end;
    fmPassword.ScaleBy(fmPassword.Monitor.PixelsPerInch, 96);
    if fmPassword.ShowModal = mrOK then
      Approved(edUserName.Text, edPassword.Text) else
      Approved('','');
    end;
  finally
    fmPassword.Free;
  end;
end;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  V := M.DataSet.FindField('DocAmt').AsFloat;
  if V >= 10000 then
    InitPWForm;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Add DB Check Box===
Below example is to create DBCheckBox at Header in Sales Order
Last Script Update : 07 Aug 2017<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Order'''
:03. Right Click the '''Sales Order'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var dsDocMaster  : TDataSource;
    edFreeDlvery : TcxDBCheckBox;
    btnMoveDown  : TSpeedButton;
begin
  dsDocMaster  := TDataSource(Self.FindComponent('dsDocMaster'));
  btnMoveDown  := TSpeedButton(Self.FindComponent('BtnMoveDown'));
  edFreeDlvery := TcxDBCheckBox.Create(self);
 
  if Assigned(btnMoveDown) then begin
    with edFreeDlvery do begin
      Parent := btnMoveDown .Parent;
      Top    := btnMoveDown .Top;
      Left  := btnMoveDown.Left + btnMoveDown.Width + 100;
      Width  := 100;
      Name  := 'FreeDlvry';
      Properties.Caption        := ' Free Delivery';
      Properties.NullStyle      := nssUnchecked;
      Properties.ValueChecked  := 'T';
      Properties.ValueUnChecked := 'F';
      DataBinding.DataSource    := dsDocMaster;
      DataBinding.DataField    := 'UDF_FreeDlvry';
    end;
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Set Default 14 days delivery date from DocDate===
Below example is to set Default 14 Days from Document Date in Sales Quotation
Last Script Update : 20 Sep 2017<br />
Level : Basic
'''Steps - OnGridAfterInsert'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Quotation'''
:03. Right Click the '''Sales Quotation'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnGridAfterInsert''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnGridAferInsert Script 
|-
|
<syntaxhighlight lang="delphi">
var M : TDataSet;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  Dataset.FindField('DeliveryDate').Value := M.FindField('DocDate').Value +14; 
end;
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Add TcxDBTextEdit ===
Below example is to Create new TcxDBTextEdit for UDF_Wastage in Maintain Item
Last Script Update : 21 Dec 2017<br />
Level : Basic
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Stock'''
:03. Right Click the '''Stock Item'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
Var edWast, edRmk : TcxDBTextEdit;
    pnlDock  : TWinControl;
    dsAccess : TDataSource;
    lblWast  : TcxLabel;
begin
  edRmk    := TcxDBTextEdit(Self.FindComponent('edBarcode'));
  dsAccess := TDataSource(Self.FindComponent('dsAccess'));
  edWast    := TcxDBTextEdit.Create(Self);
  lblWast  := TcxLabel.Create(Self);
  with lblWast do begin
    Parent  := edRmk.Parent;
    Left    := edRmk.Left - 92;
    Top    := edRmk.Top + edRmk.Height + 4;
    Width  := 38;
    Height  := 17;
    Caption := 'Wastage :';
  end;
  with edWast do begin
    Parent                := edRmk.Parent;
    Name                  := 'edWast';
    Left                  := edRmk.Left;
    Top                    := edRmk.Top + edRmk.Height + 2;
    Width                  := 300;
    Height                := edRmk.Height;
    TabOrder              := 20;
    DataBinding.DataSource := dsAccess;
    DataBinding.DataField  := 'UDF_Wastage';
    Properties.Alignment.Horz := taRightJustify; //Align to Right
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Call Report Object In Script ===
Below example is to Create new Button to calculate Margin for each Invoice & update to Header UDF_Margin field
Last Script Update : 23 Nov 2017<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var C : TComponent;
    B, btnOK, BtnBrowse : TcxButton;
    fmParameter    : TForm;
    lIVList        : TStringList;
    lWorkingDate  : TDateTime;
    lDF , lDT      : TcxDateEdit;
    P              : TDataProcessorProxy;
    D              : TDataSet;   
    FComServer, lBizObj, lDocKey, lDocNo, lMainDataSet, V: Variant;
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
  if not VarIsNull(V) then
    Result := V
  else
    Result := ADefault;
end;
function VarToStr(const V: Variant): string;
begin
  Result := VarToStrDef(V, '');
end;
function FormatJSonDate(D: TDateTime): String;
var AFormat: string;
begin
  AFormat := 'yyyy-mm-dd';
  Result := '"' + FormatDateTime(AFormat, D) + '"';
end;
procedure SpoolParameter;
begin
  P := TDataProcessorProxy.Create('Sales.ProfitLoss.DP');
  P.AsJSONParam := '{' +
                  '  "SelectIV"        : true ' +  // Boolean (true / false)
                  ',  "SelectDO"        : false ' +
                  ',  "SelectDN"        : false ' +
                  ',  "SelectCS"        : false ' +
                  ',  "SelectCN"        : false ' +
                  ',  "Agent"          : [] ' + // List / Array - this mean select ALL
                  ',  "Company"        : [] ' +
                  ',  "Location"        : [] ' + // List / Array - this mean select location ---- & Balakong
                  ',  "CompanyCategory" : [] ' +
                  ',  "HasCategory"    : false ' +
                  ',  "WithSalesTax"    : false ' +
                  ',  "StockGroup"      : [] ' +
                  ',  "DateFrom"        : ' + FormatJSonDate(lDF.Date) + // Date format is yyyy-mm-dd
                  ',  "DateTo"          : ' + FormatJSonDate(lDT.Date) +
                  ',  "Doc_IV"          : [] ' +
                  ',  "Doc_DO"          : [] ' +
                  ',  "Doc_DN"          : [] ' +
                  ',  "Doc_CS"          : [] ' +
                  ',  "Doc_CN"          : [] ' +
                  ',  "Category"        : [] ' +
                  ',  "ItemProject"    : [] ' +
                  ',  "DocProject"      : [] ' +
                  ',  "SelectDate"      : true ' +
                  ',  "CategoryTpl"    : "" ' + //String
                  ',  "Item"            : [] ' +
                  ',  "Area"            : [] ' +
                  ',  "UseRefCost"      : false ' +
                  ',  "Batch"          : [] ' +
                  '}';
  P.Execute;
end;
procedure GetIVList;
begin
  lIVList := TStringList.Create;
  lIVList.Duplicates := dupIgnore;
  lIVList.Sorted    := True;
  D.First;
  while not D.Eof do begin
    lIVList.Append(D.FindField('DocNo').AsString);
    D.Next;
  end;
end;
procedure OnClickApply(Sender: TObject);
var i, j : integer;
begin
  SpoolParameter;
  D := P.GetDataSetByName('Main');
  GetIVList;
 
  FComServer  := null;
  lBizObj      := null;
  lBizObj      := ComServer.BizObjects.Find('SL_IV');
  lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
  i := 0;
  for j := 0 to lIVList.Count -1 do begin
    i := i + 1;
    V := Format('%d of %d',[i, lIVList.Count]);
    btnOK.Caption := V;
    lDocNo  := lIVList[j];
    lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
    lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
    with D do begin
      Filter  := 'DocNo=' + QuotedStr(lIVList[j]);
      Filtered := True;
    end;
   
    if not VarIsNull(lDocKey) then begin
      lBizObj.Open;
      lBizObj.Edit;
      lMainDataSet.FindField('UDF_Margin').AsFloat := D.FindField('DocMargin').AsFloat;
      lBizObj.Save;
      D.Filtered := False;
    end;
  end;
  lIVList.Free;
  if Assigned(P) then
    P.Free; 
  FComServer  := null;
  lBizObj      := null;
  btnOK.Caption  := '&Apply';
  MessageDlg('Record(s) successfully Updated', mtInformation, [mbOk], 0);
  fmParameter.Close;
end;
procedure InitForm;
var lbDF, lbDT  : TcxLabel;
begin
  lWorkingDate := CurrentWorkingDate;
  fmParameter := TForm.Create(nil);
  lDF        := TcxDateEdit.Create(fmParameter);
  lDT        := TcxDateEdit.Create(fmParameter);
  btnOK      := TcxButton.Create(fmParameter);
  lbDF        := TcxLabel.Create(fmParameter);
  lbDT        := TcxLabel.Create(fmParameter);
 
  try
    with fmParameter do begin
      Parent      := nil;
      Height      := 100;
      Width      := 300;
      Caption    := 'Date range..';
      Color      := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbDF do begin
      Parent  := fmParameter;
      Left    := 5;
      Top    := 10;
      Caption := 'Date :'
    end;
    with lDF do begin
      Parent := fmParameter;
      Top    := 10;
      Left  := 40;
      Width  := 100;
      Date  := StartOfAMonth(YearOf(lWorkingDate), MonthOf(lWorkingDate));
    end;
   
    with lbDT do begin
      Parent  := fmParameter;
      Left    := 145;
      Top    := 10;
      Caption := ' To '
    end;
    with lDT do begin
      Parent := fmParameter;
      Top    := 10;
      Left  := 180;
      Width  := 100;
      Date  := Trunc(EndOfAMonth(YearOf(lWorkingDate), MonthOf(lWorkingDate)));
    end;
    with btnOK do begin
      Parent := fmParameter;
      Top    := 35;
      Width  := 100;
      Left  := (fmParameter.Width/2) - 50;
      Caption := '&Apply';
      OnClick := @OnClickApply;
    end;
    end;
    fmParameter.ScaleBy(fmParameter.Monitor.PixelsPerInch, 96);
    fmParameter.ShowModal;
  finally
    fmParameter.Free;
  end;
end;
procedure OnClickForm(Sender: TObject);
begin
  InitForm;
end;
begin
  C  := Self.FindComponent('frDataSetButton1');
  BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
  if Assigned(C) then
    B := TcxButton.Create(Self);
    B.Parent  := TwinControl(c);
    B.width  := BtnBrowse.Width;
    B.Top    := BtnBrowse.Top + BtnBrowse.Height + 5;
    B.Left    := BtnBrowse.Left;
    B.Height  := BtnBrowse.Height;
    B.Caption := 'Calc Margin';
    B.OnClick := @OnClickForm;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Set Discount to Empty for next line===
Below is Example will automatic remove the Discount field value on insert next item line.
Last Script Update : 27 Nov 2017<br />
Level : Basic
'''Steps - OnGridAfterInsert'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnGridAfterInsert''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnGridAfterInsert Script 
|-
|
<syntaxhighlight lang="delphi">
begin
  Dataset.FindField('Disc').AsString := '';
end;
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Prompt Dialog for Discount===
Below is Example are doing following actions
* Auto Append ItemCode '''DISC'''
* Do Calculation for ItemCode '''DISC''' UnitPrice := Total Doc Amount * Rate Enter
Last Script Update : 29 Nov 2017<br />
Level : Basic
'''Steps - OnBeforeSave'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnBeforeSave''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnBeforeSave Script 
|-
|
<syntaxhighlight lang="delphi">
var M, D : TDataSource;
function GetDiscountedValue(AValue: Variant; Discount: string): Variant;
var
  lStrLst: TStringList;
  S: string;
  I: Integer;
begin
  Result  := Null;
  lStrLst := TStringList.Create;
  try
    try
      lStrLst.Delimiter := '&';
      lStrLst.DelimitedText := Trim(StringReplace(Trim(Discount), '+', '&', [rfReplaceAll]));
      for I := 0 to lStrLst.Count - 1 do
      begin
        S := Trim(lStrLst.Strings[I]);
        if S[Length(S)] = '%' then
        begin
          S := Trim(Copy(S, 1, Length(S) - 1));
          AValue := AValue * ((StrToFloat(S)) / 100);
        end
        else
          AValue := StrToFloat(S);
      end;
      Result := AValue*-1;
    except
      RaiseException(ExceptionType, 'Discount formatting error ');
    end;
  finally
    lStrLst.Free;
  end;
end;
procedure CalcDisc(const lRate:String);
var lDocAmt : Variant;
begin
  lDocAmt := M.DataSet.FindField('DocAmt').AsFloat;
  with D.DataSet do begin
    Edit;
    Append;
    FindField('ItemCode').AsString    := 'DISC';
    FindField('Description').AsString := FindField('Description').AsString + ' -' + lRate+'%';
    FindField('Qty').AsFloat          := 1;
    FindField('UnitPrice').AsFloat    := GetDiscountedValue(lDocAmt, lRate+'%');
    Post;   
  end;
end;
procedure InitPWForm;
var fmPassword : TForm;
    lbRate : TcxLabel;
    edRate : TcxTextEdit;
    btnOK  : TButton;
begin
  fmPassword := TForm.Create(nil);
  lbRate    := TcxLabel.Create(fmPassword);
  edRate    := TcxTextEdit.Create(fmPassword);
  btnOK      := TButton.Create(fmPassword);
  try
    with fmPassword do begin
      Parent      := nil;
      Height      := 100;
      Width      := 280;
      Caption    := 'Enter the Discount Rate (%)';
      Color      := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbRate do begin
      Parent  := fmPassword;
      Left    := 10;
      Top    := 10;
      Caption := '&Rate :';
    end;
    with edRate do begin
      Parent := fmPassword;
      Left  := 100;
      Top    := 10;
      Width  := 150;
    end;
    with btnOK do begin
      Parent      := fmPassword;
      Top        := 38;
      Width      := 100;
      Left        := (fmPassword.Width/2) - 50;
      Caption    := '&OK';
      ModalResult := mrOk;
    end;
    fmPassword.ScaleBy(fmPassword.Monitor.PixelsPerInch, 96);
    if fmPassword.ShowModal = mrOK then
      CalcDisc(edRate.Text);
    end;
  finally
    fmPassword.Free;
  end;
end;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  D := TDataSource(Self.FindComponent('dsDocDetail'));
  InitPWForm;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Create New Grid Tab on  Existing Grid===
Below is Example are create new Tab in Existing Grid in Customer Due Listing<br />
Last Script Update : 03 Jan 2018<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Customer Due Document'''
:03. Right Click the '''Customer Due Document'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var btnCalc, btnApply : TcxButton;
    M, dsGrid : TDatasource;
    FcxGrid  : TcxGrid;
    gvGrid    : TcxGridDBTableView;
    cdsMain  : TClientDataSet;
    lDisplayFormat : String;
procedure Setup;
begin
  dsGrid  := TDataSource.Create(nil);
  cdsMain := TClientDataSet.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
procedure TearDown;
begin
  FreeCmpt(dsGrid);
  FreeCmpt(cdsMain);
end;
procedure SetNumericDisplayFormat(AClientDataSet: TClientDataSet);
var f  : TFMTBCDField;
    f1 : TFloatField;
    i  : Integer;
begin
  for i := 0 to AClientDataSet.FieldCount - 1 do begin
    if AClientDataSet.FieldDefs.Items[i].DataType = ftFMTBcd then begin
      f := TFMTBCDField(AClientDataSet.Fields.Fields[i]);
      if not (f = nil) then f.DisplayFormat := lDisplayFormat;
    end;
    if AClientDataSet.FieldDefs.Items[i].DataType = ftFloat then begin
      f1 := TFloatField(AClientDataSet.Fields.Fields[i]);
      if not (f1 = nil) then f1.DisplayFormat := lDisplayFormat;
    end;
  end;
end;
procedure PrepareNewTab;
var i : integer;
begin
  FcxGrid.RootLevelOptions.DetailTabsPosition := dtpTop;
  if FcxGrid.Levels.Count > 1 then begin
    for i := FcxGrid.Levels.Count - 1 downto 1 do begin
      FcxGrid.Levels[i].GridView.Free;
      FcxGrid.Levels[i].Free;
    end;
    Self.Refresh;
  end;
  gvGrid := CreateTcxGridDBTableView(FcxGrid);
  with FcxGrid.Levels.Add do begin
    Caption  := 'Calc';
    GridView := TcxCustomGridView(gvGrid);
    Active  := True;
  end;
  with gvGrid do begin
    Name                            := 'Restricted_1';
    OptionsCustomize.ColumnFiltering := False;
    OptionsView.ColumnAutoWidth      := True;
    OptionsView.Footer              := True;
    OptionsView.GroupByBox          := False;
    OptionsBehavior.IncSearch        := True;
    DataController.DataSource        := dsGrid;
    OptionsData.Editing              := False;
  end; 
end;
procedure CreateXMLTable;
begin
  FreeCmpt(cdsMain);
  cdsMain := TClientDataSet.Create(nil);
  cdsMain.FieldDefs.Assign(M.DataSet.FieldDefs);
  cdsMain.CreateDataSet;
  cdsMain.IndexFieldNames := 'Code';// Sort By Code
  dsGrid.DataSet := cdsMain;
  gvGrid.DataController.DataSource := dsGrid;
  gvGrid.DataController.CreateAllItems(False);// Create cxGridColumn 
end; 
procedure TuneGridColumn;
var B : Boolean;
begin
  B := False;
  with gvGrid do begin
    //Hide Columns
    //for i := 0 to ColumnCount -1 do
    //  Columns[i].Visible := False; // To 1 shot hide all fields
    GetColumnByFieldName('Dockey').Visible          := B;
    GetColumnByFieldName('DocNoEx').Visible          := B;
    GetColumnByFieldName('PostDate').Visible        := B;
    GetColumnByFieldName('Agent').Visible            := B;
    GetColumnByFieldName('Journal').Visible          := B;
    GetColumnByFieldName('CurrencyCode').Visible    := B;
    GetColumnByFieldName('CurrencyRate').Visible    := B;
    GetColumnByFieldName('LocalDocAmt').Visible      := B;
    GetColumnByFieldName('Cancelled').Visible        := B;
    GetColumnByFieldName('Project').Visible          := B;
    GetColumnByFieldName('Area').Visible            := B;
    GetColumnByFieldName('DocType').Visible          := B;
    GetColumnByFieldName('PaymentAmt').Visible      := B;
    GetColumnByFieldName('LocalPaymentAmt').Visible  := B;
    GetColumnByFieldName('CNAmt').Visible            := B;
    GetColumnByFieldName('LocalCNAmt').Visible      := B;
    GetColumnByFieldName('CTAmt').Visible            := B;
    GetColumnByFieldName('LocalCTAmt').Visible      := B;
    GetColumnByFieldName('GainLoss').Visible        := B;
    GetColumnByFieldName('CompanyCategory').Visible  := B;
    GetColumnByFieldName('Description').Visible      := B;
    GetColumnByFieldName('Code').Visible            := B;
    //Reposition Columns
    GetColumnByFieldName('DocNo').Index            := 0;
    GetColumnByFieldName('DocDate').Index          := 1; 
    GetColumnByFieldName('DueDate').Index          := 2;
    GetColumnByFieldName('CompanyName').Index      := 3;
    GetColumnByFieldName('Terms').Index            := 4;
    GetColumnByFieldName('DocAmt').Index          := 5;
    GetColumnByFieldName('Outstanding').Index      := 6;
    GetColumnByFieldName('LocalOutstanding').Index := 7;
    GetColumnByFieldName('Age').Index              := 8;
    //Rename Columns
    GetColumnByFieldName('DocNo').Caption          := 'Doc No';
    GetColumnByFieldName('DocDate').Caption        := 'Doc Date'; 
    GetColumnByFieldName('DueDate').Caption        := 'Due Date';
    GetColumnByFieldName('CompanyName').Caption    := 'Company Name';
  end;
  with gvGrid.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column  := gvGrid.GetColumnByFieldName('DocNo');
        Position := spFooter;
        Kind    := skCount;
        Format  := 'Count = #';
      end;
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column  := gvGrid.GetColumnByFieldName('DocAmt');
        Position := spFooter;
        Kind    := skSum;
        Format  := lDisplayFormat;
      end;
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column  := gvGrid.GetColumnByFieldName('Outstanding');
        Position := spFooter;
        Kind    := skSum;
        Format  := lDisplayFormat;
      end;
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column  := gvGrid.GetColumnByFieldName('LocalOutstanding');
        Position := spFooter;
        Kind    := skSum;
        Format  := lDisplayFormat;
      end;           
      finally
        EndUpdate;
    end;
  end;   
end;
procedure PrepareData;
var i : integer;
begin
  M.Dataset.DisableControls;
  cdsMain.DisableControls;
  try
  M.Dataset.First;
  while not M.Dataset.EOF do begin
    cdsMain.Append;
    for i:=0 to M.Dataset.FieldDefs.Count-1 do
      cdsMain.FindField(M.Dataset.FieldDefs.Items[i].Name).Value := M.Dataset.FindField(M.Dataset.FieldDefs.Items[i].Name).Value;
      cdsMain.Post;
      M.Dataset.Next;
    end;
  finally
    SetNumericDisplayFormat(cdsMain); //Set DisplayFormat
    M.Dataset.EnableControls;
    cdsMain.EnableControls;
    cdsMain.First;
    gvGrid.ApplyBestFit(nil, False, False);
  end;   
end;
procedure OnGenClick(Sender: TObject);
var s : string;
begin
  s := self.Caption;
  lDisplayFormat := '#,0.00;-#,0.00;-';
  try
    self.Caption := 'Get Raw Information...';
    btnApply.Click;
    self.Caption := 'Prepare Tab...';
    PrepareNewTab;
    self.Caption := 'Prepare XML Table...';
    CreateXMLTable;
    self.Caption := 'Grid Tuning...';
    TuneGridColumn;
    self.Caption := 'Preparing Data...';
    PrepareData;
  finally
    self.Caption := s;
  end;
end;
begin
  btnApply  := Tcxbutton(Self.FindComponent('btnApply'));
  FcxGrid  := TcxGrid(Self.FindComponent('FcxGrid'));
  M      := TDatasource(Self.FindComponent('dsMain'));
 
  btnCalc := TcxButton.Create(Self);
  with btnCalc do begin
    Parent  := btnApply.Parent;
    Top      := btnApply.Top;
    Left    := btnApply.Left + btnApply.Width + 5;
    Width    := btnApply.Width + 2;
    Caption  := '&1. Generate';
    ShowHint := True;
    Hint    := 'Generate Overdue List';
    OnClick  := @OnGenClick;
  end;   
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===How to Detect what is the Current Doc Type in Document Listing?===
Last Script Update : 25 Feb 2022<br />
Level : Basic
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Document Listing'''
:03. Right Click the '''Sales Document Listing'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var edDocumentType : TcxComboBox;
procedure edDocumentTypeChange(Sender: TObject);
var s : string;
begin
  s := 'Now selected Doc type is ' + edDocumentType.EditValue;
  MessageDlg(S, mtInformation, [mbOk], 0);
end;
begin
  edDocumentType := Self.FindComponent('edDocumentType') as TcxComboBox;
  edDocumentType.Properties.OnEditValueChanged := @edDocumentTypeChange;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Simple Copy From Dataset to New Doc ===
Below is Example to Copy Quotation Information to Sales Invoice<br />
Last Script Update : 08 Sep 2018<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Quotation'''
:03. Right Click the '''Sales Quotation'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var FComServer, lBizObj, lMainDataSet, lDocDetail : Variant;
    btnMoveDown : TSpeedButton;
    btnCopy  : TcxButton;
    M, D : TDataSet;
    cdsMaster, cdsDetail : TClientDataSet;   
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
procedure Setup;
begin
  cdsMaster  := TClientDataSet.Create(nil);
  cdsDetail  := TClientDataSet.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
procedure TearDown;
begin
  FreeCmpt(cdsMaster);
  FreeCmpt(cdsDetail);
end;
procedure CreateXMLTable;
begin
  with cdsMaster.FieldDefs do
    begin
      Add('FName', ftString,  100, False);
    end;
  with cdsDetail.FieldDefs do
    begin
      Add('FName', ftString,  100, False);
    end;
  cdsMaster.CreateDataSet;
  cdsDetail.CreateDataSet;
end;
procedure AddField(const lTbl, lFld:String);
begin
  if lTbl = 'M' then
    with cdsMaster do begin
      Append;
      FieldValues['FName'] := lFld
      Post;
    end else
    with cdsDetail do begin
      Append;
      FieldValues['FName'] := lFld;
      Post;
    end;
end;
procedure PostToAcc;
var lFName : string;
begin
  lBizObj.New;
  lMainDataSet.FindField('DOCKEY').Value  := -1;
  cdsMaster.First;
  while not cdsMaster.Eof do begin
    lFName := cdsMaster.FieldValues['FName'];
    lMainDataSet.FindField(lFName).Value := M.FindField(lFName).Value;
    cdsMaster.Next;
  end;
  lMainDataSet.Post;
  D.First;
  while not D.Eof do begin
    lDocDetail.Append;
    lDocDetail.FindField('DtlKey').Value    := -1;
    lDocDetail.FindField('DocKey').Value    := -1;
    cdsDetail.First;
    while not cdsDetail.Eof do begin
      lFName := cdsDetail.FieldValues['FName'];
      lDocDetail.FindField(lFName).Value := D.FindField(lFName).Value;
      cdsDetail.Next;
    end;
    lDocDetail.Post;
    D.Next;     
  end;   
  lBizObj.Save;
  lBizObj.Close;     
end;
procedure OnClick1(Sender: TObject);
begin
  FComServer  := null;
  lBizObj      := null;
  lBizObj      := ComServer.BizObjects.Find('SL_IV');
  lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
  lDocDetail  := lBizObj.DataSets.Find('cdsDocDetail');
  try
    PostToAcc; 
    MessageDlg('Posting Done', mtInformation, [mbOk], 0);
  finally
    lMainDataSet := null;
    lDocDetail  := null;
    lBizObj      := null;
    FComServer  := null;
  end;
end;
begin
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown')); 
  M          := TDataSource(Self.FindComponent('dsDocMaster')).Dataset; 
  D          := TDataSource(Self.FindComponent('dsDocDetail')).Dataset; 
  btnCopy  := TcxButton.Create(btnMoveDown);
 
  if Assigned(btnMoveDown) then begin   
    with btnCopy do begin //Create Beside the Up Down Button
      Parent  := btnMoveDown.Parent;
      Top      := btnMoveDown.Top;
      Left    := btnMoveDown.Left + btnMoveDown.Width + 120;
      Height  := btnMoveDown.Height;
      Width    := 100;
      Caption  := '&1. Copy To IV';
      OnClick  := @OnClick1;
      ShowHint := True;
      Hint    := 'Copy To IV';
    end;
  end;
 
  CreateXMLTable;
//Add Field Master Table
  AddField('M', 'Code');
  AddField('M', 'DocNoEx');
  //AddField('M', 'CompanyName');
  //AddField('M', 'Address1');
  //AddField('M', 'Address2');
  //AddField('M', 'Address3');
  //AddField('M', 'Address4');
  //AddField('M', 'Phone1');
  //AddField('M', 'Fax1');
  //AddField('M', 'Attention');
  //AddField('M', 'Area');
  //AddField('M', 'Agent');
  AddField('M', 'Project');
  //AddField('M', 'Terms');
  //AddField('M', 'CurrencyRate');
  AddField('M', 'Description');
  //AddField('M', 'D_Amount');
  AddField('M', 'Validity');
  AddField('M', 'DeliveryTerm');
  AddField('M', 'CC');
  AddField('M', 'DocRef1');
  AddField('M', 'DocRef2');
  AddField('M', 'DocRef3');
  AddField('M', 'DocRef4');
  AddField('M', 'BranchName');
  //AddField('M', 'DAddress1');
  //AddField('M', 'DAddress2');
  //AddField('M', 'DAddress3');
  //AddField('M', 'DAddress4');
  //AddField('M', 'DPhone1');
  //AddField('M', 'DFax1'); 
 
//Add Field Detail Table
  AddField('D', 'Number');
  AddField('D', 'ItemCode');
  AddField('D', 'Description');
  AddField('D', 'Description2');
  AddField('D', 'Description3');
  AddField('D', 'Remark1');
  AddField('D', 'Remark2');
  AddField('D', 'Location');
  AddField('D', 'Project');
  AddField('D', 'Qty');
  AddField('D', 'UOM');
  AddField('D', 'UnitPrice');
  AddField('D', 'Disc');
  AddField('D', 'Tax'); 
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Create New Column on Existing Grid ===
Below is Example to Add UDF_Margin in the Sales Document Listing<br />
This only work if in Report Designer in Main Pipeline/Table had the field(s)<br />
Last Script Update : 19 Feb 2019<br />
Level : Basic
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Document Listing'''
:03. Right Click the '''Sales Document Listing'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var C: TcxGridDBTableView;
begin
  C := TcxGridDBTableView(Self.FindComponent('gvMaster'));
  if C.GetColumnByFieldName('UDF_Margin') = nil then begin
    with C.CreateColumn do begin
      DataBinding.FieldName := 'UDF_Margin';
      Caption := 'Margin';
    end;
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Auto Click Apply button and Change the report Grid Data===
Below Example is to Change Header & Detail Description field in Sales Document Listing
Last Script Update : 21 Mar 2019<br />
Level : Basic
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Document Listing'''
:03. Right Click the '''Sales Document Listing'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var btnApply, btnCalc : Tcxbutton;
    M, D : TDataset;     
   
procedure OnGenerate(Sender: TObject);
var lMDesc, lDDesc, lDocNo : String;
begin
  btnApply.Click; //Auto Click Apply button
  M := TDataSource(Self.FindComponent('dsMasterTrans')).Dataset;
  D := TDataSource(Self.FindComponent('dsDetail')).Dataset;
 
  M.DisableControls;
  D.DisableControls;
  try                   
    M.First;
    while not M.Eof do begin
      lDocNo := M.FindField('DocNo').AsString;   
      lMDesc := M.FindField('Description').AsString +
              '- Hello Change Master Description';
      M.Edit;
      M.FindField('Description').AsString := lMDesc;
      M.Post;
      M.Next;
    end;
    D.First;
    while not D.Eof do begin
      lDDesc := D.FindField('Description').AsString +
                '- Hello Change Detail Description';
      D.Edit;
      D.FindField('Description').AsString := lDDesc;     
      D.Post;
      D.Next;
    end;
    MessageDlg('Done', mtInformation, [mbOk], 0);     
  finally   
    M.EnableControls;
    D.EnableControls;
  end;   
end;
begin
  btnApply  := Tcxbutton(Self.FindComponent('btnApply'));
  btnCalc  := TcxButton.Create(btnApply);
  with btnCalc do begin
    Parent  := btnApply.Parent;
    Top    := btnApply.Top;
    Left    := btnApply.Left + btnApply.Width + 2;
    Width  := 100;
    Caption := '&1. Generate';
    OnClick := @OnGenerate;
  end; 
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Update DocNo To other Document===
Below Example is to Update UDF_ORNo in Sales Invoice for Knock-off Payment from Customer Payment
Last Script Update : 03 Jul 2019<br />
Level : Advance
'''Steps - OnAfterSave'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Customer Payment'''
:03. Right Click the '''Customer Payment'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnAfterSave''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnAfterSave Script 
|-
|
<syntaxhighlight lang="delphi">
var M, D : TDataSet;
    FComServer, lBizObj : Variant;
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
  if not VarIsNull(V) then
    Result := V
  else
    Result := ADefault;
end;
function VarToStr(const V: Variant): string;
begin
  Result := VarToStrDef(V, '');
end;
procedure UpdateIV(const lDocNo:String);
var lMainDataSet, lDocKey : Variant;
begin
  FComServer  := null;
  lBizObj      := null; 
  try
    lBizObj      := ComServer.BizObjects.Find('SL_IV');
    lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
    lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
    lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);       
   
    if not VarIsNull(lDocKey) then begin
      lBizObj.Open;
      lBizObj.Edit;
      lMainDataSet.FindField('UDF_ORNo').Value  := M.FindField('DocNo').Value;
      lBizObj.Save;
      lBizObj.Close;
    end;
       
  finally
    lBizObj    := null;
    FComServer := null;
  end;
end;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  D := TDataSource(Self.FindComponent('dsaKnockOff')).Dataset;
  D.First;
  while not D.Eof do begin
    if (D.FindField('Outstanding').AsFloat = 0) and
      (D.FindField('KnockOff').AsString = 'T') then
        UpdateIV(D.FindField('DocNo').AsString);
    D.Next;
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===How to Reset the Serial Number When update Location Field?===
Below Example is to Set Back the Serial Number List when Location change/Updated
*Applicable for Sales Module Only as Purchase will not reset when location change
Last Script Update : 19 Oct 2020<br />
Level : Advance
'''Steps - OnBeforeSave'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnBeforeSave''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnBeforeSave Script 
|-
|
<syntaxhighlight lang="delphi">
var M, D, SN : TDataSet;
  lSN : TStringList;
 
procedure GetSN;
begin
  SN.First;
  while not SN.Eof do begin
    lSN.Append(SN.FindField('SerialNumber').AsString);
    SN.Next;
  end; 
end;
procedure SetSN;
var i : integer;
begin
  for i := 0 to lSN.Count -1 do begin
    SN.Append;
    SN.FindField('SerialNumber').AsString := lSN[i];
    SN.Post;
  end;
end;
 
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet;
  D := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
  SN := TDataSource(Self.FindComponent('dsSerialNumber')).Dataset;
 
  lSN := TStringList.Create;
  D.First; 
  try   
    while not D.EOF do begin         
      lSN.Clear;
      if D.FindField('Location').AsString <> 'KL' then begin
        GetSN;
        D.Edit; 
        D.FindField('Location').AsString := 'KL'; //Location Field is change/Updated
        SetSN;
      end;
    D.Next;
  end;
  finally
    lSN.Free;
  end;   
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===How to find component in Advance Form?===
Below Example is to change the label in Advance Form
Last Script Update : 11 Nov 2021<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
// Create Advance Form & Rename it to fmCalc (under Object Inspector)
// Drop 1 label & Rename it to lbBankCode (under Object Inspector)
// Load it OnOpen script
var C : TWinControl;
    edAccNo : TLabel;
    i : integer;
begin
  for i := 0 to Self.ComponentCount - 1 do begin
    if Pos('fmCalc', Self.Components[i].Name) > 0 then begin
      C := Self.Components[i] as TWinControl;
      edAccNo := TLabel(C.FindComponent('lbBankCode'));
      Break;
    end;
  end; 
  if Assigned(edAccNo) then
    edAccNo.Caption := 'Fairy';
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===DropDown List from Maintenance Agent List===
Below is Example are doing following actions
* Create Label
* Create a drop down list at Sales Invoice for UDF_Agent2
Last Script Update : 07 Apr 2020<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var FComServer : Variant;
    M  : TDataSource;
    lbAgent  : TLabel;
    cdsAgent : TClientDataSet;
    grGrid1    : TcxGrid;
    dsAgent : TDataSource;
    gvDocDetail, gvAgent : TcxGridDBTableView;   
    btnProfit  : TcxButton;
    cxAgent    : TcxDBExtLookupComboBox;
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
procedure Setup;
begin
  cdsAgent := TClientDataSet.Create(nil); 
end;
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
procedure TearDown;
begin
  FreeCmpt(cdsAgent);
end;
procedure PrepareXML;
var lSQL : String;
begin
  FComServer := null;
  try
    lSQL := 'SELECT Code, Description FROM Agent ' +
            'WHERE ISActive=''T'' ';
   
    cdsAgent.Data := ComServer.DBManager.Execute(lSQL);
  finally
    FComServer := null;
  end; 
end;
procedure CreateTable;
begin
  grGrid1 := TcxGrid.Create(nil); 
  gvAgent := TcxGridDBTableView.Create(grGrid1);
  dsAgent := TDataSource.Create(gvDocDetail);       
 
  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;
 
  with grGrid1.Levels.Add do begin
    Caption  := 'AgentView';
    GridView := TcxCustomGridView(gvAgent);
  end;
 
  dsAgent.DataSet := cdsAgent;
    with gvAgent.CreateColumn do begin
      Caption              := 'Code';
      DataBinding.FieldName := 'Code';
      HeaderAlignmentHorz  := taCenter;
      Width                := 50;
      Options.Editing      := False;
    end;
    with gvAgent.CreateColumn do begin
      Caption              := 'Description';
      DataBinding.FieldName := 'Description';
      HeaderAlignmentHorz  := taCenter;
      Width                := 100;
      Options.Editing      := False;
    end;   
   
    with gvAgent do begin
      Name                            := 'Restricted_1';
      OptionsCustomize.ColumnFiltering := False;
      DataController.DataSource  := dsAgent;
      OptionsView.Footer          := True;
      OptionsView.ColumnAutoWidth := True;
      OptionsView.GroupByBox      := False;
      OptionsData.Editing        := False;
    end;
    with gvAgent.DataController.Summary do begin
      BeginUpdate;
      try
        with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
          Column  := gvAgent.GetColumnByFieldName('Description');
          Position := spFooter;
          Kind    := skCount;
          Format  := 'Count = #';
        end;
      finally
        EndUpdate;
      end;
    end;     
end;
begin
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  btnProfit  := TcxButton(Self.FindComponent('BtnProfitEstimator'));
  gvDocDetail := TcxGridDBTableView(Self.FindComponent('gvDocDetail'));
  cxAgent    := TcxDBExtLookupComboBox.Create(btnProfit);
 
  PrepareXML;
  CreateTable;
 
  if Assigned(btnProfit) then begin
    lbAgent := TLabel.Create(btnProfit);
    with lbAgent do begin
      Parent    := btnProfit.Parent;
      Name      := 'lbAgent2';
      Width      := 100;
      Left      := btnProfit.Left + btnProfit.Width +6;
      Top        := btnProfit.Top;
      Caption    := 'Agent 2';
    end;
   
    with cxAgent do begin
      Parent  := btnProfit.Parent;
      Top      := btnProfit.Top;
      Left    := btnProfit.Left + btnProfit.Width + lbAgent.Width+12;
      Height  := btnProfit.Height;
      Width    := 200;
      DataBinding.DataSource      := M;
      DataBinding.DataField        := 'UDF_Agent2';     
      Properties.View              := gvAgent;
      Properties.KeyFieldNames    := 'Code';
      Properties.ListFieldItem    := gvAgent.GetColumnByFieldName('Description');
      Properties.DropDownListStyle := lsFixedList;
      Properties.ImmediatePost    := True;
      Properties.DropDownWidth    := 400;
      EditValue                    := 0;
    end;               
  end; 
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Disable Terms Field===
Below is Example is disable the Terms Field in Sales Invoice
Last Script Update : 07 Apr 2020<br />
Level : Basic
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var edTerms : TcxDBExtLookupComboBox;
begin
  edTerms := Self.FindComponent('edTerms') as TcxDBExtLookupComboBox;
  if Assigned(edTerms) then
    edTerms.Enabled := False;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Export CSV/TXT Data===
Below is Example are doing following actions
* Export Data to CSV/TXT from Invoice
Last Script Update : 14 May 2020<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var lSL : TStringList;
    btnMoveDown : TSpeedButton;
    btn1  : TcxButton;
    M, D  : TDataSet;
procedure GetData;
var s : string;
begin 
  D.First;
  while not D.Eof do begin
    s := M.FindField('DocNo').AsString + '|' +
        M.FindField('Code').AsString + '|' +
        D.FindField('ItemCode').AsString + '|' +
        D.FindField('Description').AsString + '|' +
        D.FindField('Qty').AsString + '|' +
        D.FindField('UnitPrice').AsString + '|' +
        D.FindField('Tax').AsString + '|' +
        D.FindField('TaxAmt').AsString + '|' +
        D.FindField('Disc').AsString + '|' +
        D.FindField('Amount').AsString;
    lSL.Append(s);
    D.Next;
  end;
end;
procedure OnClick1(Sender: TObject);
var lFileName, lFilter : string;
begin
  lFilter  := 'Texts Files (*.txt, *.csv)|*.txt;*.csv|All Files (*.*)|*.*';
  lFileName := 'SL_IV_' + FormatDateTime('YYYYMMDD', M.FindField('DocDate').Value);
  lSL := TStringList.Create;
  try
    if PromptForFileName(lFileName, lFilter, 'txt', 'Save As...', '.', True) then begin
      GetData;
      lSL.SaveToFile(lFileName);
      MessageDlg('Export Done', mtInformation, [mbOk], 0);     
    end;
  finally
    lSL.Free;
  end;
end;
begin
  M          := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  D          := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown')); 
  btn1    := TcxButton.Create(btnMoveDown);
 
  if Assigned(btnMoveDown) then begin   
    with btn1 do begin
      Parent  := btnMoveDown.Parent;
      Top      := btnMoveDown.Top;
      Left    := btnMoveDown.Left + btnMoveDown.Width + 120;
      Height  := btnMoveDown.Height;
      Width    := 100;
      Caption  := '&1. Button';
      OnClick  := @OnClick1;
      ShowHint := True;
      Hint    := 'Button Click 1';
    end;
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Run External Application===
Below is Example are doing following actions
* Auto Run SQL Payroll
Last Script Update : 19 Jun 2020<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Agent'''
:03. Right Click the '''Agent'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var FWshShell : Variant;
    C : TComponent;   
    BtnBrowse, btn2  : TcxButton;
function ComWshShell: Variant;
begin
  if FWshShell = Null then begin
    FWshShell := CreateOleObject('WScript.Shell');
  end;
  Result := FWshShell;
end;
procedure OnClick2(Sender: TObject);
begin
  FWshShell := Null;
  try
    ComWshShell.Exec('C:\estream\SQL Payroll\bin\SQLPay.exe');   
  finally
    FWshShell := Null;
  end; 
end;
begin
  C          := Self.FindComponent('frDataSetButton1');
  BtnBrowse  := TcxButton(C.FindComponent('btnViewDetail'));   
 
  btn2    := TcxButton.Create(C);
 
  if Assigned(C) then begin
    with btn2 do begin  //Create Below the Browse Button
      Parent  := TwinControl(c);
      Top      := BtnBrowse.Top + BtnBrowse.Height + 5;
      Height  := BtnBrowse.Height;
      Left    := BtnBrowse.Left
      Width    := BtnBrowse.Width;
      Caption  := '&2. Payroll';
      OnClick  := @OnClick2;
      ShowHint := True;
      Hint    := 'Open Payroll App.';
    end;
  end; 
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Update Same UDF in Sales Invoice in Customer Invoice===
Below is Example are doing following actions
* Auto update UDF_VehicleNo in Sales Invoice to UDF_VehicleNo in Customer Invoice
Last Script Update : 26 Nov 2024<br />
Level : Advance
'''Steps - OnAfterSave'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales'''
:03. Right Click the '''Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnAfterSave''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnAfterSave Script 
|-
|
<syntaxhighlight lang="delphi">
var FComServer : Variant;
    M : TDataSet;
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
  if not VarIsNull(V) then
    Result := V
  else
    Result := ADefault;
end;
function VarToStr(const V: Variant): string;
begin
  Result := VarToStrDef(V, '');
end;
procedure UpdateARIV;
var lDockey, lBizObj, lMain, V : Variant;
    lDocNo, lCode : String;
begin
  lDocNo := M.FindField('DocNo').AsString;
  lCode  := M.FindField('Code').AsString;
  try
    FComServer  := null;
    lBizObj      := ComServer.BizObjects.Find('AR_IV');
    lMain        := lBizObj.DataSets.Find('MainDataSet');
    V := FComServer.CreateOleVariantArray(1);
    V.SetItem(0, lDocNo);
    V.SetItem(1, lCode);
    lDocKey      := lBizObj.FindKeyByRef('DocNo;Code', V.AsOleVariant);
    lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
    if not VarIsNull(lDocKey) then begin
      lBizObj.Open;
      lBizObj.Edit;
      lMain.FindField('UDF_VehicleNo').AsString := M.FindField('UDF_VehicleNo').AsString;
      lBizObj.Save;
    end;
  finally
    lBizObj.Close;
    lBizObj    := null;
    FComServer := null;
  end;
end;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  UpdateARIV;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Create Dummy Table ===
Below is Example are doing following actions
* Create Button to Prompt out Dialog with grid
* Save Grid Data to UDF_GList1
Last Script Update : 05 Feb 2022<br />
Level : Advance
'''Steps - OnOpen'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Customer'''
:03. Right Click the '''Customer'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnOpen''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnOpen Script 
|-
|
<syntaxhighlight lang="delphi">
var btnRate : Tcxbutton;
    AState    : TDataSetState;
    M, dsGrid : TDatasource;
    cdsGrid : TClientDataset;
    edCode  : TcxDBTextEdit;
    grGrid1 : TcxGrid;
    fmForm  : TForm;
    gv1    : TcxGridDBTableView;
procedure Setup;
begin
  cdsGrid := TClientDataset.Create(nil);
  dsGrid  := TDatasource.Create(nil);
  grGrid1 := TcxGrid.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
procedure TearDown;
begin
  FreeCmpt(cdsGrid);
  FreeCmpt(dsGrid);
  FreeCmpt(grGrid1);
end;
procedure PrepareTable;
var gc1 : TcxGridColumn;
begin
  gv1 := TcxGridDBTableView.Create(grGrid1);
  with grGrid1.Levels.Add do begin
    Caption  := 'Grid';
    GridView := TcxCustomGridView(gv1);
  end;
  with grGrid1 do begin
    Parent  := nil;
    Visible := True;
  end;
  with gv1 do begin
    Name                            := 'Restricted_1';
    OptionsCustomize.ColumnFiltering := False;
    DataController.DataSource  := dsGrid;
    OptionsView.Footer          := True;
    OptionsView.ColumnAutoWidth := True;
    OptionsView.GroupByBox      := False;
    OptionsData.Editing        := True;
    OptionsBehavior.IncSearch  := False;
    DataController.CreateAllItems(False);
    ApplyBestFit(nil, False, False);
  end;
  with gv1.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column  := gv1.Columns[0];
        Position := spFooter;
        Kind    := skCount;
        Format  := 'Count = #';
      end;
    finally
      EndUpdate;
    end;
  end;
  gc1 := gv1.GetColumnByFieldName('IsActive');
  SetGridColumnPropertiesClass(gc1,'TcxCheckBoxProperties');
  with TcxCheckBoxProperties(gc1.Properties) do begin
    NullStyle      := nssUnchecked;
    ValueChecked  := 'T';
    ValueUnChecked := 'F';
    ImmediatePost  := True;
  end;
end;
procedure PrepareXML;
begin
  FreeCmpt(cdsGrid);
  cdsGrid := TClientDataset.Create(nil);
  with cdsGrid.FieldDefs do begin
    Add('Description', ftString,  50, False);
    Add('Rate',        ftFMTBCD,  4, False);
    Add('IsActive',    ftString,  1, False);
  end;
  cdsGrid.CreateDataSet;
  if Trim(M.DataSet.FindField('UDF_GList1').AsString) <> '' then
    cdsGrid.XMLData := M.DataSet.FindField('UDF_GList1').AsString;
  dsGrid.Dataset := cdsGrid;
end;
procedure nvgtClick(Sender: TObject; Button: TNavigateBtn);
begin
  if Button = nbInsert then
    TDBNavigator(Sender).DataSource.DataSet.AppendRecord(['',0,'T']);
end;
procedure PostData;
begin
  cdsGrid.MergeChangeLog;
  if cdsGrid.RecordCount <> 0 then
    M.DataSet.FindField('UDF_GList1').AsString := cdsGrid.XMLData else
    M.DataSet.FindField('UDF_GList1').AsString := '';
end;
procedure OnCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if (AState = dsInsert) or (AState = dsEdit) then
    PostData;
  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;
  gv1.Free;
  CanClose := True;
end;
procedure InitForm;
var nvgGrid : TDBNavigator;
    pnlTop, pnlBtm, pnlBtn  : TPanel;
    btnOk  : TButton;
begin
  AState  := M.DataSet.State;
  fmForm  := TForm.Create(nil);
  pnlTop  := TPanel.Create(fmForm);
  pnlBtm  := TPanel.Create(fmForm);
  pnlBtn  := TPanel.Create(fmForm);
  btnOk  := TButton.Create(fmForm);
  nvgGrid := TDBNavigator.Create(fmForm);
  if Assigned(grGrid1) then
    grGrid1.Free;
  grGrid1 := TcxGrid.Create(nil);
  PrepareXML;
  PrepareTable;
  try
    with fmForm do begin
      Parent      := nil;
      Height      := 480;
      Width        := 800;
      Caption      := Format('Disc List(s) - %s',[M.DataSet.FindField('Code').AsString]);
      Color        := clWhite;
      BorderStyle  := bsDialog;
      Font.Size    := 10; //Optional
      Font.Name    := 'Courier New'; //Optional
      Position    := poMainFormCenter;
      OnCloseQuery := @OnCloseQuery;
    end;
    with pnlTop do begin
      Parent      := fmForm;
      Align      := alTop;
      BevelInner  := bvNone;
      BevelOuter  := bvNone;
      ParentColor := True;
      Height      := 30;
      Width      := fmForm.Width;
    end;
    with nvgGrid do begin
      Parent        := pnlTop;
      Top            := 3;
      Left          := 5;
      VisibleButtons := [nbInsert,nbDelete];
      Width          := 48;
      Height        := 25;
      DataSource    := dsGrid;
      OnClick        := @nvgtClick;
      Enabled        := (AState = dsInsert) or (AState = dsEdit);
    end;
    with pnlBtm do begin
      Parent      := fmForm;
      Align      := alClient;
      BevelInner  := bvNone;
      BevelOuter  := bvNone;
      ParentColor := True;
      Width      := fmForm.Width;
    end;
    with grGrid1 do begin
      Parent := pnlBtm;
      align  := alClient;
    end;
    with pnlBtn do begin
      Parent      := fmForm;
      Align      := alBottom;
      BevelInner  := bvNone;
      BevelOuter  := bvNone;
      ParentColor := True;
      Height      := 30;
      Width      := fmForm.Width;
    end;
    with btnOK do begin
      Parent := pnlBtn;
      Top    := 3;
      Width  := 100;
      Left  := (fmForm.Width/2) - (btnOK.Width/2);
      Caption := '&Post';
      ModalResult := mrOk;
      Enabled := (AState = dsInsert) or (AState = dsEdit);
    end;
    fmForm.ShowModal;
  finally
    fmForm.Release;
  end;
end;
procedure OnClick(Sender: TObject);
begin
  InitForm;
end;
begin
  M      := TDatasource(self.FindComponent('dsDocMaster'));
  edCode  := TcxDBTextEdit(self.FindComponent('edCode'));
  btnRate := TcxButton.Create(edCode);
  with btnRate do begin
    Parent := edCode.Parent;
    Left  := edCode.Left + edCode.Width+10;
    Top    := edCode.Top;
    Width  := 130;
    Name  := 'edRate';
    Caption := '&1.Show Rate';
    OnClick := @OnClick;
  end;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Disable Prompt Dialog for Set Default DocNo Set===
Last Script Update : 04 May 2024<br />
Level : Advance
'''Steps - OnAfterNew'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales'''
:03. Right Click the '''Sales Invoice'''
:04. Select '''New Event'''
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:06. Select '''OnAfterNew''' for '''Event''' field
:07. Click OK
:08. Click the Calc (name create at Step 5 above) on the left panel
:09. Copy below script & paste to the Right Panel (Script Section)
{| class="mw-collapsible mw-collapsed wikitable"
! OnAfterNew Script 
|-
|
<syntaxhighlight lang="delphi">
var M : TDataset;
procedure OnNextDocNoChanged(Sender: TField);
begin
  //  Disable Build in Event
end;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet;
  M.FindField('DocNoSetKey').OnChange := @OnNextDocNoChanged;
end.
</syntaxhighlight>
|}
:10. Click '''Save''' button
<div style="float: right;">  [[#top|[top]]]</div>
===Example 50===
* More Coming Soon....
<div style="float: right;">  [[#top|[top]]]</div>
==FAQ==
===Why when click Compile Script button prompt error <code>[Error] (xx:xx):Unknown identifier 'Dataset'</code>===
: This happen if you doing script on the event '''OnGridColumnValueChanged''' & can ignore the error if you confirm your script is correct.
===How do I know my script is correct when using the script on the  event <code>OnGridColumnValueChanged</code> ?===
: You can add below script at 1st line before you click '''Compile Script''' button but remember to remove it before you click '''Save''' button.
<syntaxhighlight lang="delphi">
var EditingField, Dataset :Variant;
</syntaxhighlight>
===How many data source (TDatasource) it had for data entry (eg Sales Invoice) & what is each name?===
: Generally it had 3 data source & some had extra 1 (i.e. total 4).
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Name !! Description
|-
| dsDocMaster || Header fields
|-
| dsDocDetail || Detail or Item Fields
|-
| dsSerialNumber || Serial Number Fields
|}
===How to check the Record in Edit Mode?===
: You can use below script
<syntaxhighlight lang="delphi">
... //yr other code
var AState : TDataSetState;
    M      : TDataSource;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  AState := M.DataSet.State;
  if (AState = dsInsert) or (AState = dsEdit)  then
    MessageDlg('Yes In Edit Mode', mtInformation, [mbOK], 0);
end;
... //yr other code
</syntaxhighlight>
===Using Form Mode - Split Browse & Detail Windows Option Prompt <code>Null Pointer Exception Error</code>===
: You can use below script
<syntaxhighlight lang="delphi">
... //yr other code
begin
  if TWinControl(Self.FindComponent('DetailControl')).Visible then begin
  ...//yr other code
  end;     
end.
</syntaxhighlight>
===Message Dialog with Option===
<syntaxhighlight lang="delphi">
... //yr other code
begin
  if MessageDlg('Are you sure?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
    MessageDlg('Yes Click', mtInformation, [mbOK],0) else
    MessageDlg('No Click', mtInformation, [mbOK],0);
end.
</syntaxhighlight>
</syntaxhighlight>


05.
===After upgrade to Version 816 prompt error Message <code>Cannot cast an object</code>===
[[File:DIYScript-03.gif|center]]


==See also==
==See also==

Latest revision as of 06:53, 26 November 2024

Menu: Tools | DIY | SQL Control Center...

Introduction

This is Additional Module(DIY Script Module) which allow user to override or customise the SQL System.
The Language use is Pascal

DIY Script

Available Action/Event

Action/Event Description
OnOpen The script trigger On Open the Form (eg On Open the Sales Invoice Form)
OnClose The script trigger On Close/Exit the Form (eg on Exit Sales Invoice Form)
OnBeforeNew The script trigger before the build in New Button action execute.
OnAfterNew The script trigger after the build in New Button action Execute.
OnBeforeEdit The script trigger before the build in Edit Button action execute.
OnAfterEdit The script trigger after the build in Edit Button action Execute.
OnBeforeDelete The script trigger before the build in Delete Button action execute.
OnAfterDelete The script trigger after the build in Delete Button action Execute.
OnBeforeSave The script trigger before the build in Save Button action execute.
OnAfterSave The script trigger after the build in Save Button action Execute.
OnBeforeCancel The script trigger before the build in Cancel Button action execute.
OnAfterCancel The script trigger after the build in Cancel Button action Execute.
OnBeforePrint The script trigger before the build in Print or Preview Button action Execute.
OnGridBeforeInsert The script trigger before the build in + Button action Execute.
OnGridAfterInsert The script trigger after the build in + Button action Execute.
OnGridBeforePost The script trigger before post the current row record.
OnGridAfterPost The script trigger after post the current row record.
OnGridBeforeDelete The script trigger before the build in - Button action Execute.
OnGridAfteDelete The script trigger after the build in - Button action Execute.
OnGridBeforeCancel The script trigger before cancel the current row record.
OnGridAfterCancel The script trigger after cancel the current row record.
OnGridColumnValueChanged The script trigger on changes value in the current record.
Template.Tips-01.jpg
Commonly we only OnOpen, OnBeforeSave & OnGridColumnValueChanged Event
Template.Warning-01.jpg
Avoid use 2 same Event/Action in 1 Business Object (eg 2 OnOpen in Sales Invoice is NOT allow)

Get UDF & Do Calculation

Below is Example are doing following actions

  • Get UDF_Length & UDF_Width from Maintain Item UDF Fields
  • Do Calculation Qty := UDF_Length * UDF_Width * UDF_Rate

Last Script Update : 23 Oct 2017
Level : Basic

Steps - OnGridColumnValueChanged

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
DIYField-03.jpg
04. Select New Event
DIYScript-01.jpg
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnGridColumnValueChanged for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
DIYScript-02.jpg


09. Copy below script & paste to the Right Panel (Script Section)
OnGridColumnValueChanged Script 
//var EditingField, Dataset :Variant;
var FComServer, lBizObj : Variant;
    cdsData : TClientDataSet;
 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
 
function CheckAvailFld(const lcds : TClientDataset; lFld: String): Boolean;
var findField: TField;
begin
  Result := False;
  findField := nil;
  findField := lcds.FindField(lFld);
  if Assigned(findField) then
    Result := not (findField.Calculated);
end;
 
procedure GetStockInfo;
var lSQL, lCode  : String;
begin
  FComServer := null;
  cdsData    := TClientDataSet.Create(nil); // Create & preparing Temporary Table
  try
    lCode := Dataset.FindField('ItemCode').AsString;     
    //Select All Maintain Item Fields
    lSQL  := Format('SELECT * FROM ST_ITEM WHERE Code=%s',[QuotedStr(lCode)]);
 
    cdsData.Data := ComServer.DBManager.Execute(lSQL); // Execute command & Load the result
  finally
    lBizObj    := null;
    FComServer := null;
  end;
end;
 
procedure AssignValue;
var i : integer;
   AFld : String;
begin
  for i:=0 to DataSet.FieldDefs.Count-1 do begin
    AFld := DataSet.FieldDefs.Items[i].Name;
    DataSet.Edit; // Make sure is in Edit Mode
	if Copy(AFld, 1, Length('UDF_')) = 'UDF_' then begin // Set UDF Value
      if CheckAvailFld(cdsData, AFld) then
        DataSet.FindField(AFld).AsString := cdsData.FindField(AFld).AsString;
    end;
  end;
end;

procedure Recalc;
begin
  // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
  DataSet.Edit; // Make sure is in Edit Mode
  DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                             DataSet.FindField('UDF_Width').AsFloat *
                                             DataSet.FindField('UDF_Rate').AsFloat;  
end;
 
begin
  if SameText(EditingField, 'ItemCode') then begin // when selecting or change itemcode field
    try
      GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item
 
      AssignValue; // Set the Invoice detial UDF Fields from Maintain Item UDF Fields
 
      Recalc;
    finally
      cdsData.Free; // Free the temporary table after used
    end;
  end;
  
  if SameText(EditingField, 'UDF_Rate') then // when change UDF_Rate field 
    Recalc;
end.
10. Click Save button
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • Avoid update below field in same time as will cause unlimited looping updating each other
- Qty
- UnitPrice
- TaxAmt
- Amount
----------------------------------------------------------------------------------------------------------------------------------------------------

Change Title Caption

Below is Example are doing following actions

  • Change the Sales Quotation Title to My Quotation

Last Script Update : 19 Oct 2015
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var L: TComponent;
Begin
  L := Self.FindComponent('lbQt'); // Find the original label
  if Assigned(L) then
    TcxLabel(L).Caption := 'My Quotation'; // Change the label Caption
end;
10. Click Save button

Get UDF From Maintain Customer

Below is Example are doing following actions

  • At Sales Invoice On Select Customer Code prompt UDF_MSG from Maintain Customer

Last Script Update : 20 Oct 2015
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var FComServer, lBizObj : Variant;
    M : TDataSource;
    C : TcxDBTextEdit;
    lCdsDataList: TClientDataSet;
    lSQL : String;

function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;

procedure OnCompanyNameChanged(Sender: TObject);
var s      : string;
    AState : TDataSetState;
begin
  M         := TDataSource(Self.FindComponent('dsDocMaster')); 
  AState := M.DataSet.State;

  if (AState = dsInsert) or (AState = dsEdit)  then begin //Only Execute if in Edit or Insert Mode
    FComServer   := null;
    lCdsDataList := TClientDataSet.Create(nil);
  
    try
      lSQL := Format('SELECT UDF_MSG FROM AR_CUSTOMER WHERE Code= %s',[QuotedStr(M.DataSet.FindField('Code').AsString)]);
      lCdsDataList.Data := ComServer.DBManager.Execute(lSQL);

      s := lCdsDataList.FindField('UDF_MSG').AsString;
      if Trim(s) <> '' then
        MessageDlg(S, mtInformation, [mbOk], 0);
    finally
      FComServer := null;
      lCdsDataList.Free;
    end;
  end;
end;

begin
  C := TcxDBTextEdit(Self.FindComponent('edCompanyName'));
  if Assigned(C) then
   C.Properties.OnEditValueChanged := @OnCompanyNameChanged;
end.
10. Click Save button

Get Transfer Information - QT to IV

Below is Example are doing following action

  • Quotation direct Transfer to Invoice.
  • Get the Invoice number, Date & Qty which had transferred to Invoice in the Quotation and shown it below the Browse button

Last Script Update : 20 Sep 2024
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var FComServer, lBizObj : Variant;
    C : TComponent;
    T : TTimer;
    M, D : TDataSource;
    L1, L2, L3, L4, L5, L6 : TLabel;
    cdsTemp : TClientDataset;
    BtnBrowse : TcxButton;

function ComServer: Variant;
begin
  if FComServer = Null then
    FComServer := CreateOleObject('SQLAcc.BizApp');
  Result := FComServer;
end;

procedure Setup;
begin
  T  := TTimer.Create(Self);
  L1 := TLabel.Create(self);
  L2 := TLabel.Create(self);
  L3 := TLabel.Create(self);
  L4 := TLabel.Create(self);
  L5 := TLabel.Create(self);
  L6 := TLabel.Create(self);
end;

procedure GetToDocInfo;
var lSQL, lDocKey, lDtlkey : String;
begin
  lDocKey := M.Dataset.FindField('Dockey').AsString;

  if Trim(D.Dataset.FindField('Dtlkey').AsString) <> '' then
    lDtlKey := D.Dataset.FindField('Dtlkey').AsString else
    lDtlKey := '-1';
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_IV A '+
                 'INNER JOIN SL_IVDTL B ON (A.Dockey=B.Dockey) ' +
                 'WHERE B.FromDockey=%s ' +
                 'AND B.FromDtlKey=%s ' +
                 'AND B.FromDocType=''QT'' ',[lDockey, lDtlkey]);

  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
  finally
    FComServer := null;
  end;
end;

procedure OnTimer(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  if AState = dsBrowse then begin
    GetToDocInfo;
    L2.Caption := '';
    L4.Caption := '';
    L6.Caption := '';
    try
      L2.Caption := cdsTemp.FindField('DocNo').AsString;
      if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
        L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
      L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat);
    finally
      cdsTemp.Free;
    end;
  end;

end;

begin
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  D    := TDataSource(Self.FindComponent('dsDocDetail'));

  C := Self.FindComponent('frDataSetButton1');
  BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
  if TWinControl(Self.FindComponent('DetailControl')).Visible then begin
    T.Enabled  := True;
    T.Interval := 1000; // = Update every 1 second
    T.OnTimer  := @OnTimer;

    with L1 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := BtnBrowse.Top + BtnBrowse.Height+5;
      Caption    := 'Invoice No';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;
    with L2 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := L1.Top + L1.Height+5;
      Caption    := 'DocNo';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;

    with L3 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := L2.Top + L2.Height+5;
      Caption    := 'IV Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;
    with L4 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := L3.Top + L3.Height+5;
      Caption    := 'Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;

    with L5 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := L4.Top + L4.Height+5;
      Caption    := 'XF Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;
    with L6 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := L5.Top + L5.Height+5;
      Caption    := 'Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;
  end;
end.
10. Click Save button

Only Enable Some of the field on Click Edit by User

Below is Example are doing following action in Sales Cash Sales

  • Set all Field to Read Only at Header except Agent, DocNo, DocRef1, DocNoEx & UDF_Expired
  • Set all Detail Field to Read Only except Description & Account Field

Remember to Enable Back by Disable the GetAuthorised (see below) Script & Insert it to OnBeforeNew script
Cons : It will be delay about 3 to 4 sec. after click Edit & New button

Last Script Update : 03 Nov 2015
Level : Advance

Steps - OnBeforeEdit & OnBeforeNew

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Cash Sales
03. Right Click the Sales Cash Sales
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeEdit for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeEdit Script 
var lAST : Boolean;
    i : integer;
    C : TWinControl;
    S : TStringList;
    gvDocDetail: TcxGridDBTableView;
    DBNavDetail : TDBNavigator;
    btnMoveUp, btnMoveDown : TSpeedButton;
    M : TDataSource;
    FComServer, lBizObj : Variant;

function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;

procedure GetAuthorised;
var cdsTemp : TClientDataset;
    lSQL : String;
begin
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT UDF_AllowEdit FROM SY_USER WHERE Code=%s',[QuotedStr(CurrentUser)]);

  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
    if cdsTemp.RecordCount > 0 then
      lAST := cdsTemp.FindField('UDF_AllowEdit').Value = 1;
  finally
    cdsTemp.Free;
    FComServer := null;
  end;
      
end;

begin
  lAST := True; // Default All Enable
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  gvDocDetail := TcxGridDBTableView(self.FindComponent('gvDocDetail'));
  DBNavDetail := TDBNavigator(self.FindComponent('DBNavDetail'));
  btnMoveUp   := TSpeedButton(self.FindComponent('btnMoveUp'));
  btnMoveDown := TSpeedButton(self.FindComponent('btnMoveDown'));
  S := TStringList.Create;
  try
    GetAuthorised; // Just Disable/Delete this line for OnBeforeNew script
    {$Region 'Lock by Component'}
    S.Add('edDocDate');
    S.Add('edCustomer');
//    S.Add('edAgent');
    S.Add('edTerms');
    S.Add('edCompanyName');
    S.Add('edCurrencyRate');
//    S.Add('edDocNo');
//    S.Add('edDocRef1');
//    S.Add('edDocNoEx');
    S.Add('edAddress1');
    S.Add('edAddress2');
    S.Add('edAddress3');
    S.Add('edAddress4');
    S.Add('edNextDocNo');
    S.Add('edDocDescription');
    S.Add('edD_Amount');
    S.Add('pnlDeposit');

    for i := 0 to S.Count - 1 do begin
     C := TWinControl(Self.FindComponent(S[i]));
     if Assigned(C) then C.Enabled := lAST;
    end;                             
    
    DBNavDetail.Visible      := lAST;
    btnMoveUp.Visible        := lAST;
    btnMoveDown.Visible      := lAST;    
    {$EndRegion}
    
    {$Region 'Lock Detail'}
    for i := 0 to gvDocDetail.ColumnCount -1 do begin
//    if (gvDocDetail.Columns[i].Caption <> 'Item Code') and
      if (gvDocDetail.Columns[i].Caption <> 'Description') and
       (gvDocDetail.Columns[i].Caption <> 'Account') then
       gvDocDetail.Columns[i].Options.Editing := lAST;   
    end;
    {$EndRegion}
    
    {$Region 'Lock by Dataset'}
    M.DataSet.FindField('Project').ReadOnly      := not lAST;
    M.DataSet.FindField('Area').ReadOnly         := not lAST;
    M.DataSet.FindField('Validity').ReadOnly     := not lAST;
    M.DataSet.FindField('DeliveryTerm').ReadOnly := not lAST;
    M.DataSet.FindField('CC').ReadOnly           := not lAST;
    M.DataSet.FindField('DocRef2').ReadOnly      := not lAST;
    M.DataSet.FindField('DocRef3').ReadOnly      := not lAST;
    M.DataSet.FindField('DocRef4').ReadOnly      := not lAST;
    M.DataSet.FindField('BranchName').ReadOnly   := not lAST;
    M.DataSet.FindField('DAddress1').ReadOnly    := not lAST;
    M.DataSet.FindField('DAddress2').ReadOnly    := not lAST;
    M.DataSet.FindField('DAddress3').ReadOnly    := not lAST;
    M.DataSet.FindField('DAddress4').ReadOnly    := not lAST;
    M.DataSet.FindField('DAttention').ReadOnly   := not lAST;
    M.DataSet.FindField('DPhone1').ReadOnly      := not lAST;
    M.DataSet.FindField('DFax1').ReadOnly        := not lAST;
    M.DataSet.FindField('Attention').ReadOnly    := not lAST;
    M.DataSet.FindField('Phone1').ReadOnly       := not lAST;
    M.DataSet.FindField('Fax1').ReadOnly         := not lAST;
    M.DataSet.FindField('UDF_Expired').ReadOnly  := not lAST;
    {$EndRegion}
  finally
    S.Free;
  end;
end.
10. Click Save button

Calculate Age between 2 date

Below is Example are doing following actions

  • Do Calculation UDF_Age := UDF_DateTo - UDF_DateFrom

Last Script Update : 19 Nov 2015
Level : Basic

Steps - OnGridColumnValueChanged

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnGridColumnValueChanged for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnGridColumnValueChanged Script 
begin
  if SameText(EditingField, 'UDF_DateFrom') OR // when selecting or change UDF_DateFrom field
     SameText(EditingField, 'UDF_DateTo') then begin // when change UDF_DateTo field
     DataSet.FindField('UDF_Age').AsFloat := DataSet.FindField('UDF_DateTo').AsDateTime -
                                             DataSet.FindField('UDF_DateFrom').AsDateTime;
  end;
end.
10. Click Save button

Simple DropDown Selection List

Below is Example are doing following actions

  • Create Label
  • Create a drop down list at Maintain Customer for UDF_SearchKey

Last Script Update : 26 Mar 2018
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Customer
03. Right Click the Customer
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var M, dsSearchKey : TDatasource;
    cdsSearchKey : TClientDataSet;
    edCompanyCategory, edSearchKey : TcxDBExtLookupComboBox;
    gvSearchKey : TcxGridDBTableView;
    grGrid1 : TcxGrid;
    lbSearchKey : TLabel;

procedure Setup;
begin
  dsSearchKey  := TDataSource.Create(nil);
  cdsSearchKey := TClientDataset.Create(nil);
  grGrid1      := TcxGrid.Create(nil);   
end;

	
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;

procedure TearDown;
begin
  FreeCmpt(cdsSearchKey);
  FreeCmpt(dsSearchKey);
  FreeCmpt(gvSearchKey);
  FreeCmpt(grGrid1);
end;

procedure CreateTable;
begin
  with cdsSearchKey.FieldDefs do
    begin
      Add('Description', ftString,  50, False);
    end;
  cdsSearchKey.CreateDataSet;
    
  gvSearchKey := TcxGridDBTableView.Create(grGrid1);

  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;

  with grGrid1.Levels.Add do begin
    Caption  := 'SearchKeyGrid';
    GridView := TcxCustomGridView(gvSearchKey);
  end;
  with gvSearchKey.CreateColumn do begin
    Caption               := 'Description';
    DataBinding.FieldName := 'Description';
    HeaderAlignmentHorz   := taCenter;
    Options.IncSearch     := False;
  end;
  dsSearchKey.DataSet          := cdsSearchKey;
  with gvSearchKey do begin
    Name                             := 'Restricted_1';
    OptionsCustomize.ColumnFiltering := False;
    DataController.DataSource   := dsSearchKey;
    OptionsView.Footer          := True;
    OptionsView.ColumnAutoWidth := True;
    OptionsView.GroupByBox      := False;
//    ApplyBestFit(nil);//Optional
  end;
  with gvSearchKey.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvSearchKey.GetColumnByFieldName('Description');
        Position := spFooter;
        Kind     := skCount;
        Format   := 'Count = #';
      end;
    finally
      EndUpdate;
    end;
  end;    
end;

procedure AddcdsSearchKey(const lDesc : String);
begin
  cdsSearchKey.Append;
  cdsSearchKey.FieldValues['Description'] := lDesc;
  cdsSearchKey.Post; 
end;

begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  edCompanyCategory := Self.FindComponent('edControlAccount') as TcxDBExtLookupComboBox;
  edSearchKey := TcxDBExtLookupComboBox.Create(edCompanyCategory);
  lbSearchKey := TLabel.Create(edCompanyCategory);

  CreateTable;
  AddcdsSearchKey('Salesman');
  AddcdsSearchKey('Dummy');
  AddcdsSearchKey('Normal Outlet');
                                   
  with lbSearchKey do begin // Create Label
    Parent  := edCompanyCategory.Parent;
    Left    := edCompanyCategory.Left + edCompanyCategory.Width + 150;
    Top     := edCompanyCategory.Top +3;
    Caption := 'Search key :';    
  end;
  
  with edSearchKey do begin // Create Drop List
    Parent  := edCompanyCategory.Parent;
    Left    := edCompanyCategory.Left + edCompanyCategory.Width + 250;
    Top     := edCompanyCategory.Top;
    Name    := 'edSearch';
    DataBinding.DataSource       := M;
    DataBinding.DataField        := 'UDF_SearchKey';
    Properties.View              := gvSearchKey; // Set Drop view
    Properties.KeyFieldNames     := 'Description'; // Field Name to set for DataBinding.DataField
    Properties.ListFieldItem     := gvSearchKey.GetColumnByFieldName('Description'); // Search by Description
    Properties.DropDownListStyle := lsFixedList;
    Properties.ImmediatePost     := True; 
  end;
end.
10. Click Save button

Auto Add Service Charge

Below is Example are doing following actions

  • Auto Append ItemCode SERVICE
  • Do Calculation for ItemCode SERVICE UnitPrice := Total Doc Amount (Excluding GST) * 0.1 & Round to 2 decimal point

Last Script Update : 08 Nov 2022
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var D : TDataSource;
    ODocAmt : Variant;

procedure DelOldRecord;
begin
  D.DataSet.Filter   := 'ItemCode=''SERVICE'' ';
  D.DataSet.Filtered := True;
  try
    if D.DataSet.RecordCount > 0 then begin
      D.DataSet.First;
      D.DataSet.Delete;
    end;
    D.DataSet.Filter   := '';
    D.DataSet.Filtered := False;
    D.DataSet.First;
    while not D.DataSet.Eof do begin
      if D.DataSet.FindField('ItemCode').AsString <> 'RTN5Cents' then // To Excluding 5 Cents rounding
        ODocAmt := ODocAmt + D.DataSet.FindField('Amount').AsFloat; // To Get Total DocAmt before GST
      D.DataSet.Next;
    end;
  finally
    D.DataSet.Filter   := '';
    D.DataSet.Filtered := False;
  end;
end;

begin
  D := TDataSource(Self.FindComponent('dsDocDetail'));
  ODocAmt := 0.00;
  DelOldRecord;

  with D.DataSet do begin
    Append;
    FindField('ItemCode').AsString     := 'SERVICE';
    FindField('UnitPrice').Value       := SimpleRoundToEx(ODocAmt * 0.1, -2);
    FindField('Disc').AsString         := '';
    FindField('Transferable').AsString := 'F';
    Post;
  end;
end.
10. Click Save button

Auto Click Profit Estimator Button

Below is Example are doing following actions

  • Auto Click the Profit Estimator button

Last Script Update : 02 Dec 2015
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var btnProfit : Tcxbutton;
              
begin
  btnProfit  := Tcxbutton(Self.FindComponent('BtnProfitEstimator')); 
  
  if Assigned(btnProfit) then
    btnProfit.Click;   
end.
10. Click Save button

Simulate build-in Calculation using UDF Fields

Below is Example are doing following actions

  • Get Discount Amount
  • Get Tax Amount
  • Do Calculation Amt := (UDF_UnitPrice*UDF_Qty)-UDF_Disc)*(TaxRate)

Last Script Update : 03 Sep 2016
Level : Basic

Steps - OnGridColumnValueChanged

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnGridColumnValueChanged for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnGridColumnValueChanged Script 
function GetDiscountedValue(AValue: Variant; Discount: string): Variant;
var lStrLst: TStringList;
    S: string;
    I: Integer;
begin
  Result  := Null;
  lStrLst := TStringList.Create;
  try
    try
      lStrLst.Delimiter := '&';
      lStrLst.DelimitedText := Trim(StringReplace(Trim(Discount), '+', '&', [rfReplaceAll]));
      for I := 0 to lStrLst.Count - 1 do
      begin
        S := Trim(lStrLst.Strings[I]);
        if S[Length(S)] = '%' then
        begin
          S := Trim(Copy(S, 1, Length(S) - 1));
          AValue := AValue * ((100 - StrToFloat(S)) / 100);
        end
        else
          AValue := AValue - StrToFloat(S);
      end;
      Result := AValue;
    except
      RaiseException(ExceptionType, 'Discount formatting error ');
    end;
  finally
    lStrLst.Free;
  end;
end;

function GetTaxedValue(AValue: Variant; lTaxRate: string): Variant;
var lStrLst: TStringList;
    S: string;
    I: Integer;
begin
  Result  := Null;
  lStrLst := TStringList.Create;
  try
    try
      lStrLst.Delimiter := '&';
      S := Trim(StringReplace(lTaxRate, '+', '&', [rfReplaceAll])); //Change + to &
      lStrLst.DelimitedText := Trim(StringReplace(s, 'E', '', [rfReplaceAll])); //Remove the E
      for I := 0 to lStrLst.Count - 1 do
      begin
        S := Trim(lStrLst.Strings[I]);
        if S[Length(S)] = '%' then
        begin
          S := Trim(Copy(S, 1, Length(S) - 1));
          AValue := AValue * ((StrToFloat(S)) / 100);
        end;
      end;
      Result := AValue;
    except
      RaiseException(ExceptionType, 'Tax Rate formatting error ');
    end;
  finally
    lStrLst.Free;
  end;
end;

procedure RecalcFormula;
var lAmt, lDisc, lTax : Variant;
begin
  lAmt := SimpleRoundToEx(DataSet.FindField('UDF_Qty').AsFloat *
                          DataSet.FindField('UDF_UnitPrice').AsFloat, -2);
  lDisc := SimpleRoundToEx(GetDiscountedValue(lAmt, DataSet.FindField('UDF_Disc').AsString), -2); //lAmt - UDF_Disc
  lTax  := SimpleRoundToEx(GetTaxedValue(lDisc, DataSet.FindField('TaxRate').AsString), -2); //TaxAmt
  DataSet.FindField('TaxAmt').AsFloat := lTax - lDisc;
  DataSet.FindField('Amount').AsFloat := lDisc;
end;

begin
  if SameText(EditingField, 'UDF_Qty') or
     SameText(EditingField, 'UDF_UnitPrice') or
     SameText(EditingField, 'UDF_DISC') or
     SameText(EditingField, 'Tax') then
     RecalcFormula;
end.
10. Click Save button

Auto Click Edit, Save & Browse Button

Below is Example are doing following actions

  • Select Record at Grid
  • Auto Click Browse button
  • Auto Click Edit button
  • Auto Click Save button
  • Auto Click Browse button

Last Script Update : 20 Apr 2019
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
//Batch Edit Save Record(s)
var C : TComponent;
    B, BtnSave, BtnEdit, BtnBrowse : TcxButton;
    G : TDatasource;
    A : TWinControl;

procedure OnClick(Sender: TObject);
var i : integer;
   Time: TDateTime;
begin
  try
    i:= 0;
    Time := now;
    G.DataSet.First;
    while not G.DataSet.Eof do begin
      i := i + 1;
      Self.Caption := Format('%s of %s', [IntToStr(i), IntToStr(G.DataSet.RecordCount)]);
      Application.ProcessMessages;
	  BtnBrowse.Click;
      BtnEdit.Click;
      BtnSave.Click;
      BtnBrowse.Click;
      G.DataSet.Next;
    end;
  finally
  Time := Now - Time;
  MessageDlg(Format('Done [Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', Time)]),
             mtInformation, [mbOk], 0);
  end;
end;

Begin
  C := Self.FindComponent('frDataSetButton1');
  G := TDataSource(Self.FindComponent('dsGrid'));
  
  BtnSave   := TcxButton(C.FindComponent('btnSave'));
  BtnEdit   := TcxButton(C.FindComponent('btnEdit'));
  BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
  
  if Assigned(C) then
    B         := TcxButton.Create(Self);
    B.Parent  := TwinControl(c);
    B.width   := BtnBrowse.Width;
    B.Top     := BtnBrowse.Top + BtnBrowse.Height + 5;
    B.Left    := BtnBrowse.Left;
    B.Height  := BtnBrowse.Height;
    B.Caption := 'Start';
    B.OnClick := @OnClick;
end.
10. Click Save button

Get Current User Name and Current Working Date

Below is Example are doing following actions

  • Set Current Login User ID & Current Working Date to UDF

Last Script Update : 22 Dec 2015
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var M : TDataSource;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  
  with M.DataSet do begin
    if FindField('DocNo').AsString = '<<New>>' then begin // Check is New Doc
      FindField('UDF_CreatedBy').AsString := CurrentUser; 
      FindField('UDF_CreatedDate').Value := CurrentWorkingDate; 
    end;
  end;  
end;
10. Click Save button

Get Excel Data

Below is Example are doing following actions

  • Get Data from Excel & append to detail

Last Script Update : 14 Jan 2016
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
// Import excel to detail - about 2 sec per record
var xlApp, xlSheet: Variant;
    AXLSFile: String;
    btnImport   : TcxButton;
    btnMoveDown : TSpeedButton;
    M, D : TDataSource;
    
function GetSheet(const AWb,AWs: Variant): Variant;
begin
  try
    Result := AWb.Worksheets[AWs];
  except
    RaiseException(ExceptionType, 'Selected workbook does not contained worksheet ' +
                   #13#10+ QuotedStr(AWs) + '. Please select another excel workbook!');
  end;
end;

procedure ImportData;
var r : integer;
begin
  xlSheet := GetSheet(xlApp.Workbooks[1], 'Sheet1');
  if not VarIsEmpty(xlSheet) then begin
    for r:= 2 to xlSheet.UsedRange.Rows.Count do begin //Start from Row 2
      with D.Dataset do begin  //xlSheet.Cells[row,column].Value
        Append;
        FindField('Itemcode').AsString    := xlSheet.Cells[r,1].Value;
        FindField('Description').AsString := xlSheet.Cells[r,2].Value;
        FindField('Qty').AsFloat          := xlSheet.Cells[r,3].Value;
        FindField('UnitPrice').AsFloat    := xlSheet.Cells[r,5].Value;
        Post; 
      end;
    end;
  end;
end;

procedure GetExcel;
begin
  try
    if not VarIsEmpty(xlApp) then xlApp := Null;
    xlApp := CreateOleObject('Excel.Application');
  except
    RaiseException(ExceptionType, 'Microsoft Excel was not present or installed at this machine!' +
                   #13#10+ 'Please install Microsoft Excel before executing this script!' #13#13
                   '(Error ' + QuotedStr(UpperCase(ExceptionParam)) + ' occured!)');
  end;

  if PromptForFileName(AXLSFile, 'Microsoft Office Excel File(*.xls)|*.xls', 'xls', 'Open', '', False) then begin
    xlApp.WorkBooks.Open(AXLSFile);
    xlApp.ScreenUpdating := False;
    try
      ImportData;
    finally
      xlApp.ScreenUpdating := True;
      xlApp.DisplayAlerts := False;
      xlApp.Workbooks.Close;
      if not VarIsEmpty(xlApp) then xlApp.Quit;
      xlApp := Null;
    end;
  end;  
end;

procedure btnImportSOClick(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  if (AState = dsInsert) or (AState = dsEdit)  then
    GetExcel;
end;

begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  D := TDataSource(Self.FindComponent('dsDocDetail'));
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
  
  if Assigned(btnMoveDown) then
  begin
    btnImport := TcxButton.Create(btnMoveDown);
    with btnImport do begin
      Parent  := btnMoveDown.Parent;
      Top     := btnMoveDown.Top;
      Height  := btnMoveDown.Height;
      Left    := btnMoveDown.Left + btnMoveDown.Width + 130;
      Width   := 120;
      Caption := '&1. Get Excel Data';
      OnClick := @btnImportSOClick;
    end;
  end;
end.
10. Click Save button

Copy To & Paste From Clipboard

Below is Example are doing following actions

  • Copy string to Clipboard
  • Paste from Clipboard

Last Script Update : 14 Jan 2016
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var btnProfit, btnCopy, btnPaste  : TcxButton;
    lCB : TClipBoard;
    s : string;

procedure OnCopyClick(Sender: TObject);
begin
  lCB := ClipBoard;
  s := 'Copy to the Clipboard and From The ClipBoard :) ';
  MessageDlg('Copy To ClipBoard Done!', mtInformation, [mbOk], 0);
  lCb.AsText := s; //lCB.AsText := cdsOSBOM.XMLData; if using TClientDataSet;
end;

procedure OnPasteClick(Sender: TObject);
begin
  lCB := ClipBoard;
  s := lCB.AsText; //cdsTemp.XMLData := lCB.AsText; if using TClientDataSet;
  MessageDlg(s, mtInformation, [mbOk], 0);
end;

begin
  btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
  btnPaste  := TcxButton.Create(self);
  btnCopy   := TcxButton.Create(self);
  if Assigned(btnProfit) then begin
    with btnCopy do begin
      Parent  := btnProfit.Parent;
      Top     := btnProfit.Top;
      Height  := btnProfit.Height;
      Left    := btnProfit.Left + btnProfit.Width + 3;
      Width   := btnProfit.Width;
      Caption := 'Copy ClipBoard';
      OnClick := @OnCopyClick;
    end;
    with btnPaste do begin
      Parent  := btnProfit.Parent;
      Top     := btnProfit.Top;
      Height  := btnProfit.Height;
      Left    := btnProfit.Left + (btnProfit.Width*2) + 6;
      Width   := btnProfit.Width;
      Caption := 'Paste ClipBoard';
      OnClick := @OnPasteClick;
    end;
  end;
end.
10. Click Save button

Get CSV/TXT Data

Below is Example are doing following actions

  • Get Data from CSV/TXT & append to detail

Last Script Update : 03 Mar 2018
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var MasterList : TStringList;
    btnImport   : TcxButton;
    btnMoveDown : TSpeedButton;
    M, D : TDataSource;
    
function ValueOfDelimitedStr(const AStr: string; const AIndex: integer): string;
var S: TStringList;
begin
  S := TStringList.Create;
  try
    S.Delimiter       := ',';
	S.StrictDelimiter := True;
    S.DelimitedText   := AStr;
    if AIndex < S.Count then Result := Trim(S[AIndex])
    else Result := '';
  finally
    S.Free;
  end;
end;

procedure GetCSV;
var lFileName, lFilter : string;
    var i : integer;
begin
  MasterList := TStringList.Create;
  lFilter := 'Texts Files (*.txt, *.csv)|*.txt;*.csv|All Files (*.*)|*.*';
  try
    if PromptForFileName(lFileName, lFilter, 'txt', 'Open...', '.', False) then begin
      MasterList.LoadFromFile(lFileName);    
      with D.Dataset do begin  
        for i:= 0 to MasterList.Count -1 do begin 
          Append;
          FindField('Itemcode').AsString    := ValueOfDelimitedStr(MasterList[i], 0); //Get column 1
          FindField('Description').AsString := ValueOfDelimitedStr(MasterList[i], 1); //Get column 2
          FindField('Qty').AsFloat          := StrToFloat(ValueOfDelimitedStr(MasterList[i], 2));
          FindField('UnitPrice').AsFloat    := StrToFloat(ValueOfDelimitedStr(MasterList[i], 4));
          Post;
        end; 
      end;        
    end;    
  finally
    MasterList.Free;
  end;
end;

procedure btnImportSOClick(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  if (AState = dsInsert) or (AState = dsEdit)  then
    GetCSV;
end;

begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  D := TDataSource(Self.FindComponent('dsDocDetail'));
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
  
  if Assigned(btnMoveDown) then
  begin
    btnImport := TcxButton.Create(btnMoveDown);
    with btnImport do begin
      Parent  := btnMoveDown.Parent;
      Top     := btnMoveDown.Top;
      Height  := btnMoveDown.Height;
      Left    := btnMoveDown.Left + btnMoveDown.Width + 95;
      Width   := 120;
      Caption := '&1. Get CSV Data';
      OnClick := @btnImportSOClick;
    end;
  end;
end.
10. Click Save button

Set Default Filtering For Sales Report

Below is Example are doing following actions

  • Filter by Agent in Sales Document Listing
  • Disable Selection for Agent

Last Script Update : 02 Feb 2016
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Document Listing
03. Right Click the Sales Document Listing
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
//GL
//- GL Document Listing - Panel2
//- GL Journal of Transaction Listing - Panel2
//- GL Transaction Summary - Panel2
//- GL Ledger - Panel2
//- GL Receipt and Payment - Panel1

//Customer
//- Customer Document Listing - Panel1, Panel3
//- Customer Balance Report - Panel1
//- Customer Due Listing - Panel1, Panel2
//- Customer Aging - Panel2
//- Customer Statement - Panel3
//- Customer Post Dated Cheque Listing - Panel3
//- Customer Analysis by Document - Panel2
//- Customer Sales & Collection Analysis - Panel2

//Supplier
//- Supplier Document Listing - Panel1, Panel3
//- Supplier Balance Report - Panel2
//- Supplier Due Listing - Panel1, Panel2
//- Supplier Aging - Panel2
//- Supplier Statement - Panel2
//- Supplier Post Dated Cheque Listing - Panel3
//- Supplier Analysis by Document - Panel2
//- Supplier Purchase & Payment Analysis - Panel1

//Sales
//- Yearly Sales Analysis - pnParam 
//- Sales Price History - pnParam1
//- Sales Picking List - pnParam
//- Outstanding Sales Doc Listing - pnParam
//- Sales Document Listing - pnParam
//- Sales Analysis By Document - pnParam

//Purchase
//- Yearly Purchase Analysis - Panel1
//- Purchase Price History - pnParam1
//- Outstanding Purchase Doc Listing - pnParam
//- Purchase Document Listing - pnmParam
//- Purchase Analysis By Document - pnParam1

var C: TWinControl;
    L : TStringList;
begin
  C := Self.FindComponent('pnParam') as TWinControl;
  C := C.FindChildControl('edAgent_Panel') as TWinControl;
  L := TStringList.Create;
  try
    if C <> nil then C.Enabled := False; // Disable Selection
    C := TWinControl(C).FindChildControl('edAgent_Edit') as TWinControl;      
    L.Add('NF');
    L.Add('LF');
    (C as TControl).SetTextBuf(L.CommaText);// For Multi Records 
//    (C as TControl).SetTextBuf('LF');// For 1 record
  finally
    L.Free;
  end;
end.
10. Click Save button

Copy RefCost to UDF At Maintain Item

Below is Example are doing following actions

  • Set UDF_CommCost := RefCost * 1.1 (round to 2 decimal)

Last Script Update : 03 Mar 2016
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Stock Item
03. Right Click the Stock Item
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var M  : TDataSet;
begin
  M := TDataSource(Self.FindComponent('dsAccess')).Dataset;
  
  M.FindField('UDF_CommCost').AsFloat := SimpleRoundToEx(M.FindField('RefCost').AsFloat*1.1, -2); 
end.
10. Click Save button

Recalculate Formula Done at OnGridColumnValueChanged Script for Transferred Document

Below is Example are doing following actions

  • Recalculate the Formula Done at OnGridColumnValueChanged
  • Copy Original fields (eg Qty) to UDF fields for Transfer Items
  • Copy UDF fields (eg UDF_Qty) to Original fields for Non Transfer Items

Last Script Update : 08 Oct 2024
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var FComServer, lBizObj,
    VA, VD : Variant;
    D : TDataSource;
    i : Integer;
    
function ComServer: Variant;
begin
  if FComServer = Null then 
    FComServer := CreateOleObject('SQLAcc.BizApp');
  Result := FComServer;
end;
      
function GetDiscountedValue(AValue: Variant; Discount: string): Variant;
var lStrLst: TStringList;
    S: string;
    I: Integer;
begin
  Result  := Null;
  lStrLst := TStringList.Create;
  try
    try
      lStrLst.Delimiter := '&';
      lStrLst.DelimitedText := Trim(StringReplace(Trim(Discount), '+', '&', [rfReplaceAll]));
      for I := 0 to lStrLst.Count - 1 do
      begin
        S := Trim(lStrLst.Strings[I]);
        if S[Length(S)] = '%' then
        begin
          S := Trim(Copy(S, 1, Length(S) - 1));
          AValue := AValue * ((100 - StrToFloat(S)) / 100);
        end
        else
          AValue := AValue - StrToFloat(S);
      end;
      Result := AValue;
    except
      RaiseException(ExceptionType, 'Discount formatting error ');
    end;
  finally
    lStrLst.Free;
  end;
end;
          
begin
  D := TDataSource(Self.FindComponent('dsDocDetail'));
  i := 0;
  D.Dataset.First;
  D.Dataset.DisableControls;
  try
    with D.Dataset do begin      
      while not Eof do begin
        inc(i);
        VD  := FindField('Disc').AsString;      
        Edit;
        FindField('SEQ').AsFloat := (i*1000); //To make sure the SEQ field is correct...
        if Trim(FindField('FromDocType').AsString) = '' then begin // Copy UDF to Original Fields
          FindField('Qty').AsFloat       := FindField('UDF_Qty').AsFloat;
          FindField('SQty').AsFloat      := FindField('Rate').AsFloat * FindField('UDF_Qty').AsFloat;
          FindField('UnitPrice').AsFloat := FindField('UDF_UnitPrice').AsFloat;
        end else begin // Copy Original Qty to UDF
          FindField('UDF_Qty').AsFloat       := FindField('Qty').AsFloat;        
          FindField('UDF_UnitPrice').AsFloat := FindField('UnitPrice').AsFloat;
        end;
        FindField('Disc').AsString  := VD;
        //Example Formula Data at OnGridColumnValueChanged Script - begin
        VA := SimpleRoundToEx(FindField('UDF_ExcRate').AsFloat *
                              FindField('UDF_Qty').AsFloat *
                              FindField('UDF_UnitPrice').AsFloat, -2);
        D.Dataset.EnableControls;
        FindField('Amount').AsFloat := SimpleRoundToEx(GetDiscountedValue(VA, FindField('Disc').AsString), -2);        
        //Example Formula Data at OnGridColumnValueChanged Script - End
        Post;
        Next;
      end;      
    end;
  finally
    D.Dataset.EnableControls;
  end;  
end.
10. Click Save button

Hide Stock Opening Grid In Maintain Item

Below is Example are doing following actions

  • Hide the Grid & Navigator Button for Opening Balance

Last Script Update : 13 Apr 2016
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Stock Item
03. Right Click the Stock Item
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
//UOM - DBNavUOM, grUOM
//Cust. Price - DBNavCustomerPrice, grCustomerPrice
//Supp. Price - DBNavSupplierPrice, grSupplierPrice
//BOM - DBNavBOM, grBOM
//Category - cxGrid1
//Alternative - DBNavAltStockItem, grAltStockItem
//Cust. Item - DBNavCustomerItem, grCustomerItem
//Supp. Item - DBNavSupplierItem, grSupplierItem
//Barcode - DBNavBarcode, grBarcode
var A, A1 : TWinControl;
begin
  A  := TWinControl(Self.FindComponent('grOpeningBalance'));
  A1 := TWinControl(Self.FindComponent('DBNavOpeningBalance'));

  A.Visible  := not (Assigned(A));
  A1.Visible := not (Assigned(A1));
end.
10. Click Save button

Export Data To Excel

Below is Example are doing following actions

  • Export Maintain Agent List to Excel

Last Script Update : 26 May 2016
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Agent
03. Right Click the Agent
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
// Export to excel
// Useful link http://www.programmershare.com/2572647/
var xlApp : Variant;
    AXLSFile: String;
    C : TComponent;
    B : TcxButton;
    M : TDataset;

procedure ExportData;
var r : integer;
begin
  M := TDataSource(Self.FindComponent('dsGrid')).Dataset;
  {$Region 'Header'}
    xlApp.Cells [1,1].Value:= 'DistributorID';
    xlApp.Cells [1,2].Value:= 'Region';
    xlApp.Cells [1,3].Value:= 'Area';
    xlApp.Cells [1,4].Value:= 'SalesmanCode';
    xlApp.Cells [1,5].Value:= 'SalesmanName';
  {$EndRegion}
  {$Region 'Data'}
  r := 1;
  M.First;
  while not M.Eof do begin
    inc(r);
    //xlSheet.Cells[row,column].Value
    xlApp.Cells [r,1].Value:= '123456';
    xlApp.Cells [r,2].Value:= '';
    xlApp.Cells [r,3].Value:= '';
    xlApp.Cells [r,4].Value:= M.FindField('Code').AsString;
    xlApp.Cells [r,5].Value:= M.FindField('Description').AsString;    
    M.Next;
  end;
  {$EndRegion}
end;
    
procedure btnExportExcelClick(Sender: TObject);
begin
  try
    if not VarIsEmpty(xlApp) then xlApp := Null;
    xlApp := CreateOleObject('Excel.Application');
  except
    RaiseException(ExceptionType, 'Microsoft Excel was not present or installed at this machine!' +
                   #13#10+ 'Please install Microsoft Excel before executing this script!' #13#13
                   '(Error ' + QuotedStr(UpperCase(ExceptionParam)) + ' occured!)');
  end;
  
  AXLSFile := 'Salesman';
  if PromptForFileName(AXLSFile, 'Microsoft Office Excel File(*.xls)|*.xls', 'xls', 'Save As', '', True) then begin
    xlApp.ScreenUpdating := False;
    try
      xlApp.WorkBooks.Add;
      xlApp.WorkBooks[1].WorkSheets[1];
      ExportData;
      xlApp.WorkBooks[1].SaveAs(AXLSFile, -4143);
      xlApp.ActiveWorkBook.Saved := True;
      MessageDlg('Done', mtInformation, [mbOk], 0); 
    finally
      xlApp.ScreenUpdating := True;
      xlApp.DisplayAlerts := False;
      xlApp.Workbooks.Close;
//      xlApp.Quit;
      if not VarIsEmpty(xlApp) then xlApp.Quit;
      xlApp := Null;
    end;
  end;
end;

begin
  C := Self.FindComponent('frDataSetButton1');
  if Assigned(C) then
    B         := TcxButton.Create(C);
    B.Parent  := TwinControl(c);
    B.width   := 66;
    B.Top     := 173;
    B.Left    := 6;
    B.Caption := '&1. Export';
    B.OnClick := @btnExportExcelClick;
end.
10. Click Save button

Change DocRef 1 Label

Below is Example are doing following actions

  • Change the Purchase Order DocRef1 Label to C.PO # :

Last Script Update : 21 Jul 2021
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Purchase Order
03. Right Click the Purchase Order
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var L1, L2: TComponent;
    T : TTimer;
    ACount : Integer;
procedure OnTimer(Sender: TObject);
begin
  T.Tag := T.Tag + 1;
  if Assigned(L1) then begin
    TLabel(L1).left := TLabel(L2).Left;//220;
    TLabel(L1).Caption  := 'C.PO # :'; // Change the label Caption
  end;
  T.Enabled  := not (T.Tag = ACount);
end;

begin
  L1 := Self.FindComponent('lbDocRef1'); // Find the original label
  L2 := Self.FindComponent('lbTerms');
  ACount := 5;
  T          := TTimer.Create(Self);
  T.Enabled  := True;
  T.Interval := 1000; // = 1 sec
  T.OnTimer  := @OnTimer;
  T.Tag      := 0;
end;
10. Click Save button

Add Click Button

Below is Example are doing following actions

  • Create 2 buttons at Sales Quotation
  • 1 at Beside Up Down Button
  • 1 at Below Browse Button

Last Script Update : 29 Mar 2022
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var C : TComponent;
    //btnMoveDown : TSpeedButton;//for SQL Acc Version 5.2022.929.815 and below
    btnMoveDown : TcxButton;//for SQL Acc Version 5.2022.930.816 and above
    BtnBrowse, btn1, btn2  : TcxButton;

procedure OnClick1(Sender: TObject);
begin
  //Action for Button 1 
  MessageDlg('Buttton 1 Clicked', mtInformation, [mbOk], 0);
end;

procedure OnClick2(Sender: TObject);
begin
  //Action for Button 2
  MessageDlg('Buttton 2 Clicked', mtInformation, [mbOk], 0);
end;

begin
  C           := Self.FindComponent('frDataSetButton1');
  BtnBrowse   := TcxButton(C.FindComponent('btnViewDetail'));
  //btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));//for SQL Acc Version 5.2022.929.815 and below
  btnMoveDown := TcxButton(Self.FindComponent('BtnMoveDown'));//for SQL Acc Version 5.2022.930.816 and above
  
  btn1     := TcxButton.Create(btnMoveDown);
  btn2     := TcxButton.Create(C);
  
  if Assigned(btnMoveDown) then begin    
    with btn1 do begin //Create Beside the Up Down Button  
      Parent   := btnMoveDown.Parent;
      Top      := btnMoveDown.Top;
      Left     := btnMoveDown.Left + btnMoveDown.Width + 120;
      Height   := btnMoveDown.Height;
      Width    := 100;
      Caption  := '&1. Button';
      OnClick  := @OnClick1;
      ShowHint := True;
      Hint     := 'Button Click 1';
    end;
  end;
  if Assigned(C) then begin
    with btn2 do begin  //Create Below the Browse Button
      Parent   := TwinControl(c);
      Top      := BtnBrowse.Top + BtnBrowse.Height + 5;
      Height   := BtnBrowse.Height;
      Left     := BtnBrowse.Left
      Width    := BtnBrowse.Width;
      Caption  := '&2. Button';
      OnClick  := @OnClick2;
      ShowHint := True;
      Hint     := 'Button Click 2';
    end;
  end;  
end.
10. Click Save button

Using TxQuery

Below is Example are doing following actions

  • Create 1 button at Sales Quotation to Get Total Base Quantity

Last Script Update : 02 Nov 2016
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var btnNew      : TcxButton;
    btnMoveDown : TSpeedButton;
    D : TDataSet;

function GetTotal(const lField:String):Variant;
var xQ : TxQuery;
    lSQL : String;
begin 
  Result := 0.00;
  xQ := TxQuery.Create(nil);
  try
    xQ.Close;
    xQ.ClearDatasets;
    xQ.AddDataSet(D,    'Main');
    
    lSQL := Format('SELECT Sum(%s) Nos FROM MAIN',[lField]);
    with xQ.SQL do begin
      Clear;
      Add(lSQL);
    end;

    xQ.open;
    Result := xQ.FindField('Nos').AsFloat;    
  finally
    xQ.Free;
  end;
end;

procedure OnClick(Sender: TObject);
begin
  MessageDlg(GetTotal('SQty'), mtInformation, [mbOk], 0);
end;

begin
  D           := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
  btnNew      := TcxButton.Create(btnMoveDown);

  if Assigned(btnMoveDown) then begin
    with btnNew do begin
      Parent  := btnMoveDown.Parent;
      Top     := btnMoveDown.Top;
      Height  := btnMoveDown.Height;
      Left    := btnMoveDown.Left + btnMoveDown.Width + 95;
      Width   := 120;
      Caption := 'Calculate';
      OnClick := @OnClick;
    end;
  end;
end.
10. Click Save button

How to find component in different Event?

Below is Example are doing following actions

  • Create 1 button at Sales Quotation
  • On Before Save will auto Click the New Created button

Last Script Update : 24 Dec 2016
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
//OnOpen
var btnProfit, btnNew  : TcxButton;

procedure OnClick(Sender: TObject);
begin
  MessageDlg('Buttton Clicked', mtInformation, [mbOk], 0);
end;

begin
  btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
  btnNew    := TcxButton.Create(self); //Important it must be xx.Create(self)
  if Assigned(btnProfit) then begin
    with btnNew do begin
      Parent  := btnProfit.Parent;
      Top     := btnProfit.Top;
      Height  := btnProfit.Height;
      Name    := 'Calc_1'; //Name of the Component
      Left    := btnProfit.Left + btnProfit.Width + 3;
      Width   := 80;
      Caption := '&1 Calc';
      OnClick := @OnClick;
    end;
  end;
end.
10. Click Save button

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var btnNew  : TcxButton;
begin
  btnNew := TcxButton(Self.FindComponent('Calc_1')); //Find the Newly created Component 
  if Assigned(BtnNew) then
    btnNew.Click;
end.
10. Click Save button

Simple DropDown Selection List in Document Detail

Below is Example are doing following actions

  • Create a drop down list at Sales Invoice Detail for UDF_DeliveryType

Last Script Update : 27 Dec 2016
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var cdsDlvType : TClientDataSet;
    gcDlvType  : TcxGridColumn;
    grGrid1    : TcxGrid;
    dsDocMaster, dsDlvType : TDataSource;
    gvDocDetail, gvDlvType : TcxGridDBTableView;

procedure CreateXMLTable;
begin
  with cdsDlvType.FieldDefs do
      Add('Code', ftString,  50, False);
  cdsDlvType.CreateDataSet;
end;

procedure AddDlvType(const s : String);
begin
  with cdsDlvType do begin
    Append;
    FieldValues['Code'] := s;
    Post;
  end;
end;

procedure AddRecord;
begin
  cdsDlvType := TClientDataSet.Create(self);
  cdsDlvType.Name := 'cdsDlvType';
  CreateXMLTable;
  AddDlvType('By Mail');
  AddDlvType('By Air');
  AddDlvType('By E-Mail');
  AddDlvType('By Phone');
  AddDlvType('By Fax');
  AddDlvType('By Courier');
end;

procedure CreateTable;
begin
  grGrid1   := TcxGrid.Create(nil);
  gvDlvType := TcxGridDBTableView.Create(grGrid1);
  dsDlvType  := TDataSource.Create(gvDocDetail);

  AddRecord;

  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;

  with grGrid1.Levels.Add do begin
    Caption  := 'DlvTypeGrid';
    GridView := TcxCustomGridView(gvDlvType);
  end;
  with gvDlvType.CreateColumn do begin
    Caption               := 'Code';
    DataBinding.FieldName := 'Code';
    HeaderAlignmentHorz   := taCenter;
    Options.IncSearch     := False;
  end;

//  cdsDlvType.IndexFieldNames := 'Code'; //Sort by Code
  dsDlvType.DataSet          := cdsDlvType;
  with gvDlvType do begin
    OptionsCustomize.ColumnFiltering := False;
    DataController.DataSource   := dsDlvType;
    OptionsView.Footer          := True;
    OptionsView.ColumnAutoWidth := True;
    OptionsView.GroupByBox      := False;
    ApplyBestFit(nil, False, False);//Optional
  end;
  with gvDlvType.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvDlvType.GetColumnByFieldName('Code');
        Position := spFooter;
        Kind     := skCount;
        Format   := 'Count = #';
      end;
    finally
      EndUpdate;
    end;
  end;
end;

begin
  gvDocDetail   := TcxGridDBTableView(Self.FindComponent('gvDocDetail'));

  CreateTable;

  gcDlvType:= gvDocDetail.GetColumnByFieldName('UDF_DeliveryType');

  SetGridColumnPropertiesClass(gcDlvType,'TcxExtLookupComboBoxProperties');
  with TcxExtLookupComboBoxProperties(gcDlvType.Properties) do begin
    DropDownWidth      := 300;
    DropDownListStyle  := lsEditFixedList;
    ImmediatePost      := True;
    View               := gvDlvType;
    KeyFieldNames      := 'Code';
    ListFieldItem      := gvDlvType.GetColumnByFieldName('Code');
  end;
end.
10. Click Save button

Default Company Code & Payment Method In Sales Cash Sales

Below is Example are doing following actions

  • On Click New auto Set Default for
- Customer Code  := 300-C0001
- Payment Method  := 320-000
- Payment Project := ----

Last Script Update : 28 Dec 2016
Level : Basic

Steps - OnAfterNew

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Cash Sales
03. Right Click the Sales Cash Sales
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnAfterNew for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnAfterNew Script 
var M : TDataSource;
    C : TComponent;
begin
  C := Self.FindComponent('actBarcode');
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  if Assigned(M) then begin
    with M.Dataset do begin
      FindField('Code').AsString             := '300-C0001';
      FindField('P_PAYMENTMETHOD').AsString  := '320-000';
      FindField('P_PAYMENTPROJECT').AsString := '----';
    end;
  end;
end.
10. Click Save button

Get Transfer Information - QT to DO to IV

Below is Example are doing following action

  • Quotation Transfer to Delivery Order to Invoice.
  • Get the Quotation number, Date & Qty in the Invoice and shown it below the Browse button

Last Script Update : 03 Jun 2017
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var FComServer, lBizObj : Variant;
    C : TComponent;
    T : TTimer;
    M, D : TDataSource;
    L1, L2, L3, L4, L5, L6 : TLabel;
    cdsTemp : TClientDataset;
    
function ComServer: Variant;
begin
  if FComServer = Null then 
    FComServer := CreateOleObject('SQLAcc.BizApp');
  Result := FComServer;
end;

procedure Setup;
begin
  T  := TTimer.Create(Self);
  L1 := TLabel.Create(self);
  L2 := TLabel.Create(self);
  L3 := TLabel.Create(self);
  L4 := TLabel.Create(self);
  L5 := TLabel.Create(self);
  L6 := TLabel.Create(self);
end;

procedure GetToDocInfo;
var lSQL, lDtlkey : String;
begin
  lDtlKey := D.Dataset.FindField('FromDtlkey').AsString;
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_QT A '+
                 'INNER JOIN SL_QTDTL B ON (A.Dockey=B.Dockey) ' +
                 'WHERE B.Dockey=(SELECT FROMDOCKEY FROM SL_DODTL ' +
                 'WHERE Dtlkey=%s) '+
                 'AND B.DtlKey=(SELECT FROMDTLKEY FROM SL_DODTL '+
                 'WHERE Dtlkey=%s)',[lDtlkey, lDtlkey]);
  
  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
  finally
    FComServer := null;
  end;
end;

procedure OnTimer(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  L2.Caption := '';
  L4.Caption := '';
  L6.Caption := '';  
  if (AState = dsBrowse) and (Trim(D.Dataset.FindField('FromDtlkey').AsString) <> '') then begin 
    GetToDocInfo;
    try
      L2.Caption := cdsTemp.FindField('DocNo').AsString;
      if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
        L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
      L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat); 
    finally
      cdsTemp.Free;
    end;
  end;
  
end;

begin
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  D    := TDataSource(Self.FindComponent('dsDocDetail'));
      
  C := Self.FindComponent('frDataSetButton1');  
  if Assigned(C) then begin
    T.Enabled  := True;
    T.Interval := 1000; // = Update every 1 second
    T.OnTimer  := @OnTimer;  
   
    with L1 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 200;
      Caption    := 'QT No';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;    
    with L2 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 215;
      Caption    := 'DocNo';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;

    with L3 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 240;
      Caption    := 'QT Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;
    with L4 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 255;
      Caption    := 'Date';
      Font.Color := clGreen;
      Font.Style := [fsBold];
    end;

    with L5 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 280;
      Caption    := 'Orig. Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;
    with L6 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 295;
      Caption    := 'Qty';
      Font.Color := clRed;
      Font.Style := [fsBold];
    end;      
  end;   
end.
10. Click Save button

Approval by User if Document Amount Exceed Limit

Below example is to prompt Authorised if the Document Amount Exceed the limit (eg 10000) in Sales Invoice

Last Script Update : 18 Sep 2023
Level : Advance

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var FComServer : Variant;
    M : TDataSource;   
 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
 
procedure Approved(const lUN, lUPW : string);
var lAPW, lSQL : String;
    cdsUsers   : TClientDataSet;
begin
  FComServer   := null;
  cdsUsers := TClientDataSet.Create(nil);
 
  lSQL     := Format('SELECT UDF_Password FROM SY_USER WHERE Code=%s AND UDF_AllowApproved = 1',[QuotedStr(lUN)]);
  try
    FComServer       := null;    
    cdsUsers.Data := ComServer.DBManager.Execute(lSQL);
 
    lAPW := cdsUsers.FindField('UDF_Password').AsString;
 
    if (lUPW <> lAPW) or (Trim(lAPW) = '') then begin
       MessageDlg('Incorrect Password', mtWarning, [mbOK], 0);
       Abort;
    end;
  finally
    FComServer := null;
    cdsUsers.Free;
  end;
end;
 
procedure InitPWForm;
var fmPassword : TForm;
    lbUserName, lbPassword : TcxLabel;
    edUserName, edPassword : TEdit;
    btnOK      : TButton;
begin
  fmPassword := TForm.Create(nil);
  lbUserName := TcxLabel.Create(fmPassword);
  lbPassword := TcxLabel.Create(fmPassword);
  edUserName := TEdit.Create(fmPassword);
  edPassword := TEdit.Create(fmPassword);
  btnOK      := TButton.Create(fmPassword);
 
  try
    with fmPassword do begin
      Parent      := nil;
      Height      := 150;
      Width       := 280;
      Caption     := 'Please Enter User Name and Password';
      Color       := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbUserName do begin
      Parent  := fmPassword;
      Left    := 10;
      Top     := 10;
      Caption := '&User Name :';
    end;
    with edUserName do begin
      Parent   := fmPassword;
      Left     := 100;
      Top      := 10;
      Width    := 150;
      CharCase := ecUpperCase;
    end;
    with lbPassword do begin
      Parent  := fmPassword;
      Left    := 10;
      Top     := 35;
      Caption := '&Password :';
    end;
    with edPassword do begin
      Parent := fmPassword;
      Left   := 100;
      Top    := 35;
      Width  := 150;
      PasswordChar := '*';
    end;
     with btnOK do begin
       Parent      := fmPassword;
       Top         := 70;
       Width       := 100;
       Left        := (fmPassword.Width/2) - 50;
       Caption     := '&OK';
       ModalResult := mrOk;
     end;
    fmPassword.ScaleBy(fmPassword.Monitor.PixelsPerInch, 96);
    if fmPassword.ShowModal = mrOK then
       Approved(edUserName.Text, edPassword.Text) else
       Approved('','');
    end;
  finally
    fmPassword.Free;
  end;
end;
 
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  V := M.DataSet.FindField('DocAmt').AsFloat;
  if V >= 10000 then 
    InitPWForm;
end.
10. Click Save button

Add DB Check Box

Below example is to create DBCheckBox at Header in Sales Order

Last Script Update : 07 Aug 2017
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Order
03. Right Click the Sales Order
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var dsDocMaster  : TDataSource;
    edFreeDlvery : TcxDBCheckBox;
    btnMoveDown  : TSpeedButton;
 
begin
  dsDocMaster  := TDataSource(Self.FindComponent('dsDocMaster'));
  btnMoveDown  := TSpeedButton(Self.FindComponent('BtnMoveDown'));
  edFreeDlvery := TcxDBCheckBox.Create(self);
  
  if Assigned(btnMoveDown) then begin
    with edFreeDlvery do begin
      Parent := btnMoveDown .Parent;
      Top    := btnMoveDown .Top;
      Left   := btnMoveDown.Left + btnMoveDown.Width + 100;
      Width  := 100;
      Name   := 'FreeDlvry';
      Properties.Caption        := ' Free Delivery';
      Properties.NullStyle      := nssUnchecked;
      Properties.ValueChecked   := 'T';
      Properties.ValueUnChecked := 'F';
      DataBinding.DataSource    := dsDocMaster;
      DataBinding.DataField     := 'UDF_FreeDlvry';
    end;
  end;
end.
10. Click Save button

Set Default 14 days delivery date from DocDate

Below example is to set Default 14 Days from Document Date in Sales Quotation

Last Script Update : 20 Sep 2017
Level : Basic

Steps - OnGridAfterInsert

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnGridAfterInsert for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnGridAferInsert Script 
var M : TDataSet;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  Dataset.FindField('DeliveryDate').Value := M.FindField('DocDate').Value +14;  
end;
10. Click Save button

Add TcxDBTextEdit

Below example is to Create new TcxDBTextEdit for UDF_Wastage in Maintain Item

Last Script Update : 21 Dec 2017
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Stock
03. Right Click the Stock Item
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
Var edWast, edRmk : TcxDBTextEdit;
    pnlDock  : TWinControl;
    dsAccess : TDataSource;
    lblWast  : TcxLabel;
begin
  edRmk    := TcxDBTextEdit(Self.FindComponent('edBarcode'));
  dsAccess := TDataSource(Self.FindComponent('dsAccess'));
 
  edWast    := TcxDBTextEdit.Create(Self);
  lblWast   := TcxLabel.Create(Self);
 
  with lblWast do begin
    Parent  := edRmk.Parent;
    Left    := edRmk.Left - 92;
    Top     := edRmk.Top + edRmk.Height + 4;
    Width   := 38;
    Height  := 17;
    Caption := 'Wastage :';
  end;
  with edWast do begin
    Parent                 := edRmk.Parent;
    Name                   := 'edWast';
    Left                   := edRmk.Left;
    Top                    := edRmk.Top + edRmk.Height + 2;
    Width                  := 300;
    Height                 := edRmk.Height;
    TabOrder               := 20;
    DataBinding.DataSource := dsAccess;
    DataBinding.DataField  := 'UDF_Wastage';
    Properties.Alignment.Horz := taRightJustify; //Align to Right
  end;
end.
10. Click Save button

Call Report Object In Script

Below example is to Create new Button to calculate Margin for each Invoice & update to Header UDF_Margin field

Last Script Update : 23 Nov 2017
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var C : TComponent;
    B, btnOK, BtnBrowse : TcxButton;
    fmParameter    : TForm;
    lIVList        : TStringList;
    lWorkingDate   : TDateTime;
    lDF , lDT      : TcxDateEdit;
    P              : TDataProcessorProxy;
    D              : TDataSet;     
    FComServer, lBizObj, lDocKey, lDocNo, lMainDataSet, V: Variant;
 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
 
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
  if not VarIsNull(V) then
    Result := V
  else
    Result := ADefault;
end;
 
function VarToStr(const V: Variant): string;
begin
  Result := VarToStrDef(V, '');
end;
 
function FormatJSonDate(D: TDateTime): String;
var AFormat: string;
begin
  AFormat := 'yyyy-mm-dd';
  Result := '"' + FormatDateTime(AFormat, D) + '"';
end;
 
procedure SpoolParameter;
begin
  P := TDataProcessorProxy.Create('Sales.ProfitLoss.DP');
  P.AsJSONParam := '{' +
                   '  "SelectIV"         : true ' +  // Boolean (true / false)
                   ',  "SelectDO"        : false ' +
                   ',  "SelectDN"        : false ' +
                   ',  "SelectCS"        : false ' +
                   ',  "SelectCN"        : false ' +
                   ',  "Agent"           : [] ' + // List / Array - this mean select ALL
                   ',  "Company"         : [] ' +
                   ',  "Location"        : [] ' + // List / Array - this mean select location ---- & Balakong
                   ',  "CompanyCategory" : [] ' +
                   ',  "HasCategory"     : false ' +
                   ',  "WithSalesTax"    : false ' +
                   ',  "StockGroup"      : [] ' +
                   ',  "DateFrom"        : ' + FormatJSonDate(lDF.Date) + // Date format is yyyy-mm-dd
                   ',  "DateTo"          : ' + FormatJSonDate(lDT.Date) + 
                   ',  "Doc_IV"          : [] ' +
                   ',  "Doc_DO"          : [] ' +
                   ',  "Doc_DN"          : [] ' +
                   ',  "Doc_CS"          : [] ' +
                   ',  "Doc_CN"          : [] ' +
                   ',  "Category"        : [] ' +
                  ',  "ItemProject"     : [] ' +
                   ',  "DocProject"      : [] ' +
                   ',  "SelectDate"      : true ' +
                   ',  "CategoryTpl"     : "" ' + //String
                   ',  "Item"            : [] ' +
                   ',  "Area"            : [] ' +
                   ',  "UseRefCost"      : false ' +
                   ',  "Batch"           : [] ' +
                   '}';
  P.Execute;
end;
 
procedure GetIVList;
begin
  lIVList := TStringList.Create;
  lIVList.Duplicates := dupIgnore;
  lIVList.Sorted     := True;
  D.First;
  while not D.Eof do begin
    lIVList.Append(D.FindField('DocNo').AsString);
    D.Next;
  end;
end;
 
procedure OnClickApply(Sender: TObject);
var i, j : integer;
begin
  SpoolParameter;
  D := P.GetDataSetByName('Main');
  GetIVList;
  
  FComServer   := null;
  lBizObj      := null;
  lBizObj      := ComServer.BizObjects.Find('SL_IV');
  lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
 
  i := 0;
  for j := 0 to lIVList.Count -1 do begin
    i := i + 1;
    V := Format('%d of %d',[i, lIVList.Count]);
    btnOK.Caption := V;
    lDocNo  := lIVList[j];
    lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
    lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
 
    with D do begin
      Filter   := 'DocNo=' + QuotedStr(lIVList[j]);
      Filtered := True;
    end;
    
    if not VarIsNull(lDocKey) then begin
      lBizObj.Open;
      lBizObj.Edit;
      lMainDataSet.FindField('UDF_Margin').AsFloat := D.FindField('DocMargin').AsFloat;
      lBizObj.Save;
      D.Filtered := False;
    end;
  end;
  lIVList.Free;
  if Assigned(P) then
     P.Free;  
  FComServer   := null;
  lBizObj      := null;
  btnOK.Caption  := '&Apply';
  MessageDlg('Record(s) successfully Updated', mtInformation, [mbOk], 0);
  fmParameter.Close;
end;
 
procedure InitForm;
var lbDF, lbDT  : TcxLabel;
begin
  lWorkingDate := CurrentWorkingDate;
 
  fmParameter := TForm.Create(nil);
  lDF         := TcxDateEdit.Create(fmParameter);
  lDT         := TcxDateEdit.Create(fmParameter);
  btnOK       := TcxButton.Create(fmParameter);
  lbDF        := TcxLabel.Create(fmParameter);
  lbDT        := TcxLabel.Create(fmParameter);
  
  try
    with fmParameter do begin
      Parent      := nil;
      Height      := 100;
      Width       := 300;
      Caption     := 'Date range..';
      Color       := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbDF do begin
      Parent  := fmParameter;
      Left    := 5;
      Top     := 10;
      Caption := 'Date :'
     end;
     with lDF do begin
       Parent := fmParameter;
       Top    := 10;
       Left   := 40;
       Width  := 100;
       Date   := StartOfAMonth(YearOf(lWorkingDate), MonthOf(lWorkingDate));
     end;
     
    with lbDT do begin
      Parent  := fmParameter;
      Left    := 145;
      Top     := 10;
      Caption := ' To '
     end;
     with lDT do begin
       Parent := fmParameter;
       Top    := 10;
       Left   := 180;
       Width  := 100;
       Date   := Trunc(EndOfAMonth(YearOf(lWorkingDate), MonthOf(lWorkingDate)));
     end;
     with btnOK do begin
       Parent := fmParameter;
       Top    := 35;
       Width  := 100;
       Left   := (fmParameter.Width/2) - 50;
       Caption := '&Apply';
       OnClick := @OnClickApply;
     end;
    end;
    fmParameter.ScaleBy(fmParameter.Monitor.PixelsPerInch, 96);
    fmParameter.ShowModal;
  finally
    fmParameter.Free;
  end;
end;
 
procedure OnClickForm(Sender: TObject);
begin
  InitForm;
end;
 
begin
  C   := Self.FindComponent('frDataSetButton1');
  BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
 
  if Assigned(C) then
    B := TcxButton.Create(Self);
    B.Parent  := TwinControl(c);
    B.width   := BtnBrowse.Width;
    B.Top     := BtnBrowse.Top + BtnBrowse.Height + 5;
    B.Left    := BtnBrowse.Left;
    B.Height  := BtnBrowse.Height;
    B.Caption := 'Calc Margin';
    B.OnClick := @OnClickForm;
end.
10. Click Save button

Set Discount to Empty for next line

Below is Example will automatic remove the Discount field value on insert next item line.

Last Script Update : 27 Nov 2017
Level : Basic

Steps - OnGridAfterInsert

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnGridAfterInsert for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnGridAfterInsert Script 
begin
  Dataset.FindField('Disc').AsString := '';
end;
10. Click Save button

Prompt Dialog for Discount

Below is Example are doing following actions

  • Auto Append ItemCode DISC
  • Do Calculation for ItemCode DISC UnitPrice := Total Doc Amount * Rate Enter

Last Script Update : 29 Nov 2017
Level : Basic

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var M, D : TDataSource;
 
function GetDiscountedValue(AValue: Variant; Discount: string): Variant;
var
  lStrLst: TStringList;
  S: string;
  I: Integer;
begin
  Result  := Null;
  lStrLst := TStringList.Create;
  try
    try
      lStrLst.Delimiter := '&';
      lStrLst.DelimitedText := Trim(StringReplace(Trim(Discount), '+', '&', [rfReplaceAll]));
      for I := 0 to lStrLst.Count - 1 do
      begin
        S := Trim(lStrLst.Strings[I]);
        if S[Length(S)] = '%' then
        begin
          S := Trim(Copy(S, 1, Length(S) - 1));
          AValue := AValue * ((StrToFloat(S)) / 100);
        end
        else
          AValue := StrToFloat(S);
      end;
      Result := AValue*-1;
    except
      RaiseException(ExceptionType, 'Discount formatting error ');
    end;
  finally
    lStrLst.Free;
  end;
end;
 
procedure CalcDisc(const lRate:String);
var lDocAmt : Variant;
begin
  lDocAmt := M.DataSet.FindField('DocAmt').AsFloat;
  with D.DataSet do begin
    Edit;
    Append;
    FindField('ItemCode').AsString    := 'DISC';
    FindField('Description').AsString := FindField('Description').AsString + ' -' + lRate+'%';
    FindField('Qty').AsFloat          := 1;
    FindField('UnitPrice').AsFloat    := GetDiscountedValue(lDocAmt, lRate+'%');
    Post;    
  end;
end;
 
procedure InitPWForm;
var fmPassword : TForm;
    lbRate : TcxLabel;
    edRate : TcxTextEdit;
    btnOK  : TButton;
begin
  fmPassword := TForm.Create(nil);
  lbRate     := TcxLabel.Create(fmPassword);
  edRate     := TcxTextEdit.Create(fmPassword);
  btnOK      := TButton.Create(fmPassword);
 
  try
    with fmPassword do begin
      Parent      := nil;
      Height      := 100;
      Width       := 280;
      Caption     := 'Enter the Discount Rate (%)';
      Color       := clWhite;
      BorderStyle := bsDialog;
      Position    := poMainFormCenter;
    with lbRate do begin
      Parent  := fmPassword;
      Left    := 10;
      Top     := 10;
      Caption := '&Rate :';
    end;
    with edRate do begin
      Parent := fmPassword;
      Left   := 100;
      Top    := 10;
      Width  := 150;
    end;
     with btnOK do begin
       Parent      := fmPassword;
       Top         := 38;
       Width       := 100;
       Left        := (fmPassword.Width/2) - 50;
       Caption     := '&OK';
       ModalResult := mrOk;
     end;
    fmPassword.ScaleBy(fmPassword.Monitor.PixelsPerInch, 96);
    if fmPassword.ShowModal = mrOK then
       CalcDisc(edRate.Text);
    end;
  finally
    fmPassword.Free;
  end;
end;
 
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  D := TDataSource(Self.FindComponent('dsDocDetail'));
  InitPWForm;
end.
10. Click Save button

Create New Grid Tab on Existing Grid

Below is Example are create new Tab in Existing Grid in Customer Due Listing

Last Script Update : 03 Jan 2018
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Customer Due Document
03. Right Click the Customer Due Document
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var btnCalc, btnApply : TcxButton;
    M, dsGrid : TDatasource;
    FcxGrid   : TcxGrid;
    gvGrid    : TcxGridDBTableView;
    cdsMain   : TClientDataSet;
    lDisplayFormat : String;
 
procedure Setup;
begin
  dsGrid  := TDataSource.Create(nil);
  cdsMain := TClientDataSet.Create(nil);
end;
 
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
 
procedure TearDown;
begin
  FreeCmpt(dsGrid);
  FreeCmpt(cdsMain);
end;
 
procedure SetNumericDisplayFormat(AClientDataSet: TClientDataSet);
var f  : TFMTBCDField;
    f1 : TFloatField;
    i  : Integer;
begin
  for i := 0 to AClientDataSet.FieldCount - 1 do begin
    if AClientDataSet.FieldDefs.Items[i].DataType = ftFMTBcd then begin
      f := TFMTBCDField(AClientDataSet.Fields.Fields[i]);
      if not (f = nil) then f.DisplayFormat := lDisplayFormat;
    end;
    if AClientDataSet.FieldDefs.Items[i].DataType = ftFloat then begin
      f1 := TFloatField(AClientDataSet.Fields.Fields[i]);
      if not (f1 = nil) then f1.DisplayFormat := lDisplayFormat;
    end;
  end;
end;
 
procedure PrepareNewTab;
var i : integer;
begin
  FcxGrid.RootLevelOptions.DetailTabsPosition := dtpTop;
  if FcxGrid.Levels.Count > 1 then begin
    for i := FcxGrid.Levels.Count - 1 downto 1 do begin
      FcxGrid.Levels[i].GridView.Free;
      FcxGrid.Levels[i].Free;
    end;
    Self.Refresh;
  end;
  gvGrid := CreateTcxGridDBTableView(FcxGrid);
  with FcxGrid.Levels.Add do begin
    Caption  := 'Calc';
    GridView := TcxCustomGridView(gvGrid);
    Active   := True;
  end;
 
  with gvGrid do begin
    Name                             := 'Restricted_1';
    OptionsCustomize.ColumnFiltering := False;
    OptionsView.ColumnAutoWidth      := True;
    OptionsView.Footer               := True;
    OptionsView.GroupByBox           := False;
    OptionsBehavior.IncSearch        := True;
    DataController.DataSource        := dsGrid;
    OptionsData.Editing              := False;
  end;  
end;
 
procedure CreateXMLTable;
begin
  FreeCmpt(cdsMain);
  cdsMain := TClientDataSet.Create(nil);
  cdsMain.FieldDefs.Assign(M.DataSet.FieldDefs);
  cdsMain.CreateDataSet;
  cdsMain.IndexFieldNames := 'Code';// Sort By Code
  dsGrid.DataSet := cdsMain;
  gvGrid.DataController.DataSource := dsGrid;
  gvGrid.DataController.CreateAllItems(False);// Create cxGridColumn  
end;  
 
procedure TuneGridColumn;
var B : Boolean;
begin
  B := False;
  with gvGrid do begin
    //Hide Columns
    //for i := 0 to ColumnCount -1 do 
    //  Columns[i].Visible := False; // To 1 shot hide all fields
    GetColumnByFieldName('Dockey').Visible           := B;
    GetColumnByFieldName('DocNoEx').Visible          := B;
    GetColumnByFieldName('PostDate').Visible         := B;
    GetColumnByFieldName('Agent').Visible            := B;
    GetColumnByFieldName('Journal').Visible          := B;
    GetColumnByFieldName('CurrencyCode').Visible     := B;
    GetColumnByFieldName('CurrencyRate').Visible     := B;
    GetColumnByFieldName('LocalDocAmt').Visible      := B;
    GetColumnByFieldName('Cancelled').Visible        := B;
    GetColumnByFieldName('Project').Visible          := B;
    GetColumnByFieldName('Area').Visible             := B;
    GetColumnByFieldName('DocType').Visible          := B;
    GetColumnByFieldName('PaymentAmt').Visible       := B;
    GetColumnByFieldName('LocalPaymentAmt').Visible  := B;
    GetColumnByFieldName('CNAmt').Visible            := B;
    GetColumnByFieldName('LocalCNAmt').Visible       := B;
    GetColumnByFieldName('CTAmt').Visible            := B;
    GetColumnByFieldName('LocalCTAmt').Visible       := B;
    GetColumnByFieldName('GainLoss').Visible         := B;
    GetColumnByFieldName('CompanyCategory').Visible  := B;
    GetColumnByFieldName('Description').Visible      := B;
    GetColumnByFieldName('Code').Visible             := B;
    //Reposition Columns
    GetColumnByFieldName('DocNo').Index            := 0;
    GetColumnByFieldName('DocDate').Index          := 1;  
    GetColumnByFieldName('DueDate').Index          := 2;
    GetColumnByFieldName('CompanyName').Index      := 3;
    GetColumnByFieldName('Terms').Index            := 4;
    GetColumnByFieldName('DocAmt').Index           := 5;
    GetColumnByFieldName('Outstanding').Index      := 6;
    GetColumnByFieldName('LocalOutstanding').Index := 7;
    GetColumnByFieldName('Age').Index              := 8;
    //Rename Columns
    GetColumnByFieldName('DocNo').Caption          := 'Doc No';
    GetColumnByFieldName('DocDate').Caption        := 'Doc Date';  
    GetColumnByFieldName('DueDate').Caption        := 'Due Date';
    GetColumnByFieldName('CompanyName').Caption    := 'Company Name';
 
  end;
  with gvGrid.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvGrid.GetColumnByFieldName('DocNo');
        Position := spFooter;
        Kind     := skCount;
        Format   := 'Count = #';
      end;
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvGrid.GetColumnByFieldName('DocAmt');
        Position := spFooter;
        Kind     := skSum;
        Format   := lDisplayFormat;
      end;
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvGrid.GetColumnByFieldName('Outstanding');
        Position := spFooter;
        Kind     := skSum;
        Format   := lDisplayFormat;
      end;
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvGrid.GetColumnByFieldName('LocalOutstanding');
        Position := spFooter;
        Kind     := skSum;
        Format   := lDisplayFormat;
      end;            
      finally
        EndUpdate;
    end;
  end;     
end;
 
procedure PrepareData;
var i : integer;
begin
  M.Dataset.DisableControls;
  cdsMain.DisableControls;
  try
  M.Dataset.First;
  while not M.Dataset.EOF do begin
    cdsMain.Append;
    for i:=0 to M.Dataset.FieldDefs.Count-1 do
      cdsMain.FindField(M.Dataset.FieldDefs.Items[i].Name).Value := M.Dataset.FindField(M.Dataset.FieldDefs.Items[i].Name).Value;
      cdsMain.Post;
      M.Dataset.Next;
    end;
  finally
    SetNumericDisplayFormat(cdsMain); //Set DisplayFormat
    M.Dataset.EnableControls;
    cdsMain.EnableControls;
    cdsMain.First;
    gvGrid.ApplyBestFit(nil, False, False);
  end;    
end;
 
procedure OnGenClick(Sender: TObject);
var s : string;
begin
  s := self.Caption;
  lDisplayFormat := '#,0.00;-#,0.00;-';
  try
    self.Caption := 'Get Raw Information...';
    btnApply.Click;
    self.Caption := 'Prepare Tab...';
    PrepareNewTab;
    self.Caption := 'Prepare XML Table...';
    CreateXMLTable;
    self.Caption := 'Grid Tuning...';
    TuneGridColumn;
    self.Caption := 'Preparing Data...';
    PrepareData;
  finally
    self.Caption := s;
  end;
end;
 
begin
  btnApply  := Tcxbutton(Self.FindComponent('btnApply'));
  FcxGrid   := TcxGrid(Self.FindComponent('FcxGrid'));
  M      := TDatasource(Self.FindComponent('dsMain'));
  
  btnCalc := TcxButton.Create(Self);
  with btnCalc do begin
    Parent   := btnApply.Parent;
    Top      := btnApply.Top;
    Left     := btnApply.Left + btnApply.Width + 5;
    Width    := btnApply.Width + 2;
    Caption  := '&1. Generate';
    ShowHint := True;
    Hint     := 'Generate Overdue List';
    OnClick  := @OnGenClick;
  end;    
end.
10. Click Save button

How to Detect what is the Current Doc Type in Document Listing?

Last Script Update : 25 Feb 2022
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Document Listing
03. Right Click the Sales Document Listing
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var edDocumentType : TcxComboBox;
 
procedure edDocumentTypeChange(Sender: TObject);
var s : string;
begin
  s := 'Now selected Doc type is ' + edDocumentType.EditValue;
  MessageDlg(S, mtInformation, [mbOk], 0);
end;
 
begin
  edDocumentType := Self.FindComponent('edDocumentType') as TcxComboBox;
  edDocumentType.Properties.OnEditValueChanged := @edDocumentTypeChange;
end.
10. Click Save button

Simple Copy From Dataset to New Doc

Below is Example to Copy Quotation Information to Sales Invoice

Last Script Update : 08 Sep 2018
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Quotation
03. Right Click the Sales Quotation
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var FComServer, lBizObj, lMainDataSet, lDocDetail : Variant;
    btnMoveDown : TSpeedButton;
    btnCopy  : TcxButton;
    M, D : TDataSet;
    cdsMaster, cdsDetail : TClientDataSet;    
 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
 
procedure Setup;
begin
  cdsMaster   := TClientDataSet.Create(nil);
  cdsDetail   := TClientDataSet.Create(nil);
end;
 
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
 
procedure TearDown;
begin
  FreeCmpt(cdsMaster);
  FreeCmpt(cdsDetail);
end;
 
procedure CreateXMLTable;
begin
  with cdsMaster.FieldDefs do
    begin
      Add('FName', ftString,  100, False);
    end;
  with cdsDetail.FieldDefs do
    begin
      Add('FName', ftString,  100, False);
    end;
  cdsMaster.CreateDataSet;
  cdsDetail.CreateDataSet;
end;
 
procedure AddField(const lTbl, lFld:String);
begin
  if lTbl = 'M' then
    with cdsMaster do begin
      Append;
      FieldValues['FName'] := lFld
      Post;
    end else
    with cdsDetail do begin
      Append;
      FieldValues['FName'] := lFld;
      Post;
    end;
end;
 
procedure PostToAcc;
var lFName : string;
begin
  lBizObj.New;
  lMainDataSet.FindField('DOCKEY').Value   := -1;
  cdsMaster.First;
  while not cdsMaster.Eof do begin
    lFName := cdsMaster.FieldValues['FName'];
    lMainDataSet.FindField(lFName).Value := M.FindField(lFName).Value;
    cdsMaster.Next;
  end;
  lMainDataSet.Post;
  D.First;
  while not D.Eof do begin
    lDocDetail.Append;
    lDocDetail.FindField('DtlKey').Value     := -1;
    lDocDetail.FindField('DocKey').Value     := -1;
    cdsDetail.First;
    while not cdsDetail.Eof do begin
      lFName := cdsDetail.FieldValues['FName'];
      lDocDetail.FindField(lFName).Value := D.FindField(lFName).Value;
      cdsDetail.Next;
    end;
    lDocDetail.Post;
    D.Next;      
  end;     
  lBizObj.Save;
  lBizObj.Close;       
end;
 
procedure OnClick1(Sender: TObject);
begin
  FComServer   := null;
  lBizObj      := null;
  lBizObj      := ComServer.BizObjects.Find('SL_IV');
  lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
  lDocDetail   := lBizObj.DataSets.Find('cdsDocDetail');
  try
    PostToAcc;  
    MessageDlg('Posting Done', mtInformation, [mbOk], 0);
  finally
    lMainDataSet := null;
    lDocDetail   := null;
    lBizObj      := null;
    FComServer   := null;
  end;
end;
 
 
begin
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));  
  M           := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;  
  D           := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;   
  btnCopy  := TcxButton.Create(btnMoveDown);
  
  if Assigned(btnMoveDown) then begin    
    with btnCopy do begin //Create Beside the Up Down Button 
      Parent   := btnMoveDown.Parent;
      Top      := btnMoveDown.Top;
      Left     := btnMoveDown.Left + btnMoveDown.Width + 120;
      Height   := btnMoveDown.Height;
      Width    := 100;
      Caption  := '&1. Copy To IV';
      OnClick  := @OnClick1;
      ShowHint := True;
      Hint     := 'Copy To IV';
    end;
  end;
  
  CreateXMLTable;
//Add Field Master Table
  AddField('M', 'Code');
  AddField('M', 'DocNoEx');
  //AddField('M', 'CompanyName');
  //AddField('M', 'Address1');
  //AddField('M', 'Address2');
  //AddField('M', 'Address3');
  //AddField('M', 'Address4');
  //AddField('M', 'Phone1');
  //AddField('M', 'Fax1');
  //AddField('M', 'Attention');
  //AddField('M', 'Area');
  //AddField('M', 'Agent');
  AddField('M', 'Project');
  //AddField('M', 'Terms');
  //AddField('M', 'CurrencyRate');
  AddField('M', 'Description');
  //AddField('M', 'D_Amount');
  AddField('M', 'Validity');
  AddField('M', 'DeliveryTerm');
  AddField('M', 'CC');
  AddField('M', 'DocRef1');
  AddField('M', 'DocRef2');
  AddField('M', 'DocRef3');
  AddField('M', 'DocRef4');
  AddField('M', 'BranchName');
  //AddField('M', 'DAddress1');
  //AddField('M', 'DAddress2');
  //AddField('M', 'DAddress3');
  //AddField('M', 'DAddress4');
  //AddField('M', 'DPhone1');
  //AddField('M', 'DFax1');   
  
//Add Field Detail Table
  AddField('D', 'Number');
  AddField('D', 'ItemCode');
  AddField('D', 'Description');
  AddField('D', 'Description2');
  AddField('D', 'Description3');
  AddField('D', 'Remark1');
  AddField('D', 'Remark2');
  AddField('D', 'Location');
  AddField('D', 'Project');
  AddField('D', 'Qty');
  AddField('D', 'UOM');
  AddField('D', 'UnitPrice');
  AddField('D', 'Disc');
  AddField('D', 'Tax');  
end.
10. Click Save button

Create New Column on Existing Grid

Below is Example to Add UDF_Margin in the Sales Document Listing
This only work if in Report Designer in Main Pipeline/Table had the field(s)

Last Script Update : 19 Feb 2019
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Document Listing
03. Right Click the Sales Document Listing
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var C: TcxGridDBTableView;
begin
  C := TcxGridDBTableView(Self.FindComponent('gvMaster'));
  if C.GetColumnByFieldName('UDF_Margin') = nil then begin
    with C.CreateColumn do begin
      DataBinding.FieldName := 'UDF_Margin';
      Caption := 'Margin';
    end;
  end;
end.
10. Click Save button

Auto Click Apply button and Change the report Grid Data

Below Example is to Change Header & Detail Description field in Sales Document Listing

Last Script Update : 21 Mar 2019
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Document Listing
03. Right Click the Sales Document Listing
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var btnApply, btnCalc : Tcxbutton;
    M, D : TDataset;      
    
procedure OnGenerate(Sender: TObject);
var lMDesc, lDDesc, lDocNo : String;
begin
  btnApply.Click; //Auto Click Apply button
  M := TDataSource(Self.FindComponent('dsMasterTrans')).Dataset;
  D := TDataSource(Self.FindComponent('dsDetail')).Dataset;
  
  M.DisableControls;
  D.DisableControls;
  try                    
    M.First;
    while not M.Eof do begin
      lDocNo := M.FindField('DocNo').AsString;    
      lMDesc := M.FindField('Description').AsString +
               '- Hello Change Master Description';
      M.Edit;
      M.FindField('Description').AsString := lMDesc;
      M.Post;
      M.Next;
    end;
 
    D.First;
    while not D.Eof do begin
      lDDesc := D.FindField('Description').AsString +
                '- Hello Change Detail Description';
      D.Edit;
      D.FindField('Description').AsString := lDDesc;      
      D.Post;
      D.Next;
    end;
    MessageDlg('Done', mtInformation, [mbOk], 0);       
  finally    
    M.EnableControls;
    D.EnableControls;
  end;    
end;
 
begin
  btnApply  := Tcxbutton(Self.FindComponent('btnApply'));
  btnCalc   := TcxButton.Create(btnApply);
 
  with btnCalc do begin
    Parent  := btnApply.Parent;
    Top     := btnApply.Top;
    Left    := btnApply.Left + btnApply.Width + 2;
    Width   := 100;
    Caption := '&1. Generate';
    OnClick := @OnGenerate;
  end;  
end.
10. Click Save button

Update DocNo To other Document

Below Example is to Update UDF_ORNo in Sales Invoice for Knock-off Payment from Customer Payment

Last Script Update : 03 Jul 2019
Level : Advance

Steps - OnAfterSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Customer Payment
03. Right Click the Customer Payment
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnAfterSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnAfterSave Script 
var M, D : TDataSet;
    FComServer, lBizObj : Variant;
 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;
 
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
  if not VarIsNull(V) then
    Result := V
  else
    Result := ADefault;
end;
 
function VarToStr(const V: Variant): string;
begin
  Result := VarToStrDef(V, '');
end;
 
procedure UpdateIV(const lDocNo:String);
var lMainDataSet, lDocKey : Variant;
begin
  FComServer   := null;
  lBizObj      := null;  
  try
    lBizObj      := ComServer.BizObjects.Find('SL_IV');
    lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
    lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
    lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);        
    
    if not VarIsNull(lDocKey) then begin
      lBizObj.Open;
      lBizObj.Edit;
      lMainDataSet.FindField('UDF_ORNo').Value  := M.FindField('DocNo').Value;
      lBizObj.Save;
      lBizObj.Close;
    end;
        
  finally
    lBizObj    := null;
    FComServer := null;
  end;
end;
 
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  D := TDataSource(Self.FindComponent('dsaKnockOff')).Dataset;
  D.First;
  while not D.Eof do begin
    if (D.FindField('Outstanding').AsFloat = 0) and
       (D.FindField('KnockOff').AsString = 'T') then
        UpdateIV(D.FindField('DocNo').AsString);
    D.Next;
  end;
end.
10. Click Save button

How to Reset the Serial Number When update Location Field?

Below Example is to Set Back the Serial Number List when Location change/Updated

  • Applicable for Sales Module Only as Purchase will not reset when location change

Last Script Update : 19 Oct 2020
Level : Advance

Steps - OnBeforeSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnBeforeSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnBeforeSave Script 
var M, D, SN : TDataSet;
   lSN : TStringList;
   
procedure GetSN;
begin
  SN.First;
  while not SN.Eof do begin
    lSN.Append(SN.FindField('SerialNumber').AsString);
    SN.Next;
  end;  
end;
 
procedure SetSN;
var i : integer;
begin
  for i := 0 to lSN.Count -1 do begin
    SN.Append;
    SN.FindField('SerialNumber').AsString := lSN[i];
    SN.Post;
  end;
end;
   
begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet;
  D := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
  SN := TDataSource(Self.FindComponent('dsSerialNumber')).Dataset; 
  
  lSN := TStringList.Create;
  D.First;  
  try    
    while not D.EOF do begin           
      lSN.Clear;
      if D.FindField('Location').AsString <> 'KL' then begin
        GetSN;
        D.Edit;  
        D.FindField('Location').AsString := 'KL'; //Location Field is change/Updated
        SetSN;
      end;
    D.Next;
  end;
  finally 
    lSN.Free; 
  end;     
end.
10. Click Save button

How to find component in Advance Form?

Below Example is to change the label in Advance Form

Last Script Update : 11 Nov 2021
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
// Create Advance Form & Rename it to fmCalc (under Object Inspector)
// Drop 1 label & Rename it to lbBankCode (under Object Inspector)
// Load it OnOpen script
var C : TWinControl;
    edAccNo : TLabel;
    i : integer;
begin
  for i := 0 to Self.ComponentCount - 1 do begin
    if Pos('fmCalc', Self.Components[i].Name) > 0 then begin
      C := Self.Components[i] as TWinControl;
      edAccNo := TLabel(C.FindComponent('lbBankCode'));
      Break;
    end;
  end;  
  if Assigned(edAccNo) then
    edAccNo.Caption := 'Fairy';
end.
10. Click Save button

DropDown List from Maintenance Agent List

Below is Example are doing following actions

  • Create Label
  • Create a drop down list at Sales Invoice for UDF_Agent2

Last Script Update : 07 Apr 2020
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var FComServer : Variant;
    M  : TDataSource;
    lbAgent  : TLabel;
    cdsAgent : TClientDataSet;
    grGrid1    : TcxGrid;
    dsAgent : TDataSource;
    gvDocDetail, gvAgent : TcxGridDBTableView;    
    btnProfit  : TcxButton;
    cxAgent     : TcxDBExtLookupComboBox;
 
function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end; 
 
procedure Setup;
begin
  cdsAgent := TClientDataSet.Create(nil);  
end;
 
procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;
 
procedure TearDown;
begin
  FreeCmpt(cdsAgent);
end;
 
procedure PrepareXML;
var lSQL : String;
begin
  FComServer := null;
  try
    lSQL := 'SELECT Code, Description FROM Agent ' +
            'WHERE ISActive=''T'' ';
    
    cdsAgent.Data := ComServer.DBManager.Execute(lSQL);
 
  finally
    FComServer := null;
  end;   
end;
 
procedure CreateTable;
begin
  grGrid1 := TcxGrid.Create(nil);  
  gvAgent := TcxGridDBTableView.Create(grGrid1);
  dsAgent := TDataSource.Create(gvDocDetail);        
  
  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;
  
  with grGrid1.Levels.Add do begin
    Caption  := 'AgentView';
    GridView := TcxCustomGridView(gvAgent);
  end;
  
  dsAgent.DataSet := cdsAgent;
    with gvAgent.CreateColumn do begin
      Caption               := 'Code';
      DataBinding.FieldName := 'Code';
      HeaderAlignmentHorz   := taCenter;
      Width                 := 50;
      Options.Editing       := False;
    end; 
    with gvAgent.CreateColumn do begin
      Caption               := 'Description';
      DataBinding.FieldName := 'Description';
      HeaderAlignmentHorz   := taCenter;
      Width                 := 100;
      Options.Editing       := False;
    end;    
    
    with gvAgent do begin
      Name                             := 'Restricted_1';
      OptionsCustomize.ColumnFiltering := False;
      DataController.DataSource   := dsAgent;
      OptionsView.Footer          := True;
      OptionsView.ColumnAutoWidth := True;
      OptionsView.GroupByBox      := False;
      OptionsData.Editing         := False;
    end;
    with gvAgent.DataController.Summary do begin
      BeginUpdate;
      try
        with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
          Column   := gvAgent.GetColumnByFieldName('Description');
          Position := spFooter;
          Kind     := skCount;
          Format   := 'Count = #';
        end;
      finally
        EndUpdate;
      end;
    end;      
end;
 
begin
  M    := TDataSource(Self.FindComponent('dsDocMaster'));
  btnProfit   := TcxButton(Self.FindComponent('BtnProfitEstimator'));
  gvDocDetail := TcxGridDBTableView(Self.FindComponent('gvDocDetail')); 
  cxAgent     := TcxDBExtLookupComboBox.Create(btnProfit);
  
  PrepareXML; 
  CreateTable;
  
  if Assigned(btnProfit) then begin
    lbAgent := TLabel.Create(btnProfit);
    with lbAgent do begin
      Parent     := btnProfit.Parent;
      Name       := 'lbAgent2';
      Width      := 100;
      Left       := btnProfit.Left + btnProfit.Width +6;
      Top        := btnProfit.Top;
      Caption    := 'Agent 2';
    end;
    
    with cxAgent do begin
      Parent   := btnProfit.Parent;
      Top      := btnProfit.Top;
      Left     := btnProfit.Left + btnProfit.Width + lbAgent.Width+12;
      Height   := btnProfit.Height;
      Width    := 200;
      DataBinding.DataSource       := M;
      DataBinding.DataField        := 'UDF_Agent2';      
      Properties.View              := gvAgent;
      Properties.KeyFieldNames     := 'Code';
      Properties.ListFieldItem     := gvAgent.GetColumnByFieldName('Description');
      Properties.DropDownListStyle := lsFixedList;
      Properties.ImmediatePost     := True;
      Properties.DropDownWidth     := 400;
      EditValue                    := 0;
    end;                
  end;  
end.
10. Click Save button

Disable Terms Field

Below is Example is disable the Terms Field in Sales Invoice

Last Script Update : 07 Apr 2020
Level : Basic

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var edTerms : TcxDBExtLookupComboBox;
 
begin
  edTerms := Self.FindComponent('edTerms') as TcxDBExtLookupComboBox;
  if Assigned(edTerms) then
    edTerms.Enabled := False;
end.
10. Click Save button

Export CSV/TXT Data

Below is Example are doing following actions

  • Export Data to CSV/TXT from Invoice

Last Script Update : 14 May 2020
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var lSL : TStringList;
    btnMoveDown : TSpeedButton;
    btn1  : TcxButton;
    M, D  : TDataSet;
 
procedure GetData;
var s : string;
begin  
  D.First;
  while not D.Eof do begin
    s := M.FindField('DocNo').AsString + '|' +
         M.FindField('Code').AsString + '|' +
         D.FindField('ItemCode').AsString + '|' +
         D.FindField('Description').AsString + '|' +
         D.FindField('Qty').AsString + '|' +
         D.FindField('UnitPrice').AsString + '|' +
         D.FindField('Tax').AsString + '|' +
         D.FindField('TaxAmt').AsString + '|' +
         D.FindField('Disc').AsString + '|' +
         D.FindField('Amount').AsString;
    lSL.Append(s);
    D.Next;
  end;
end;
 
procedure OnClick1(Sender: TObject);
var lFileName, lFilter : string;
begin
  lFilter   := 'Texts Files (*.txt, *.csv)|*.txt;*.csv|All Files (*.*)|*.*';
  lFileName := 'SL_IV_' + FormatDateTime('YYYYMMDD', M.FindField('DocDate').Value);
  lSL := TStringList.Create;
  try
    if PromptForFileName(lFileName, lFilter, 'txt', 'Save As...', '.', True) then begin
      GetData;
      lSL.SaveToFile(lFileName);
      MessageDlg('Export Done', mtInformation, [mbOk], 0);      
    end;
  finally
    lSL.Free;
  end;
end;
 
 
begin
  M           := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  D           := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
  btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));  
  btn1     := TcxButton.Create(btnMoveDown);
 
  
  if Assigned(btnMoveDown) then begin    
    with btn1 do begin 
      Parent   := btnMoveDown.Parent;
      Top      := btnMoveDown.Top;
      Left     := btnMoveDown.Left + btnMoveDown.Width + 120;
      Height   := btnMoveDown.Height;
      Width    := 100;
      Caption  := '&1. Button';
      OnClick  := @OnClick1;
      ShowHint := True;
      Hint     := 'Button Click 1';
    end;
  end;
end.
10. Click Save button

Run External Application

Below is Example are doing following actions

  • Auto Run SQL Payroll

Last Script Update : 19 Jun 2020
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Agent
03. Right Click the Agent
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var FWshShell : Variant;
    C : TComponent;    
    BtnBrowse, btn2  : TcxButton;
 
function ComWshShell: Variant;
begin
  if FWshShell = Null then begin
    FWshShell := CreateOleObject('WScript.Shell');
  end;
  Result := FWshShell;
end;
 
procedure OnClick2(Sender: TObject);
begin
  FWshShell := Null;
  try
    ComWshShell.Exec('C:\estream\SQL Payroll\bin\SQLPay.exe');    
  finally
    FWshShell := Null;
  end;  
end;
 
begin
  C           := Self.FindComponent('frDataSetButton1');
  BtnBrowse   := TcxButton(C.FindComponent('btnViewDetail'));    
  
  btn2     := TcxButton.Create(C);
  
  if Assigned(C) then begin
    with btn2 do begin  //Create Below the Browse Button
      Parent   := TwinControl(c);
      Top      := BtnBrowse.Top + BtnBrowse.Height + 5;
      Height   := BtnBrowse.Height;
      Left     := BtnBrowse.Left
      Width    := BtnBrowse.Width;
      Caption  := '&2. Payroll';
      OnClick  := @OnClick2;
      ShowHint := True;
      Hint     := 'Open Payroll App.';
    end;
  end;  
end.
10. Click Save button

Update Same UDF in Sales Invoice in Customer Invoice

Below is Example are doing following actions

  • Auto update UDF_VehicleNo in Sales Invoice to UDF_VehicleNo in Customer Invoice

Last Script Update : 26 Nov 2024
Level : Advance

Steps - OnAfterSave

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales
03. Right Click the Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnAfterSave for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnAfterSave Script 
var FComServer : Variant;
    M : TDataSet;

function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;

function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
  if not VarIsNull(V) then
    Result := V
  else
    Result := ADefault;
end;

function VarToStr(const V: Variant): string;
begin
  Result := VarToStrDef(V, '');
end;

procedure UpdateARIV;
var lDockey, lBizObj, lMain, V : Variant;
    lDocNo, lCode : String;
begin
  lDocNo := M.FindField('DocNo').AsString;
  lCode  := M.FindField('Code').AsString;
  try
    FComServer   := null;
    lBizObj      := ComServer.BizObjects.Find('AR_IV');
    lMain        := lBizObj.DataSets.Find('MainDataSet');

    V := FComServer.CreateOleVariantArray(1);
    V.SetItem(0, lDocNo);
    V.SetItem(1, lCode);
    lDocKey      := lBizObj.FindKeyByRef('DocNo;Code', V.AsOleVariant);

    lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
    if not VarIsNull(lDocKey) then begin
      lBizObj.Open;
      lBizObj.Edit;
      lMain.FindField('UDF_VehicleNo').AsString := M.FindField('UDF_VehicleNo').AsString;
      lBizObj.Save;
    end;
  finally
    lBizObj.Close;
    lBizObj    := null;
    FComServer := null;
  end;
end;

begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
  UpdateARIV;
end.
10. Click Save button

Create Dummy Table

Below is Example are doing following actions

  • Create Button to Prompt out Dialog with grid
  • Save Grid Data to UDF_GList1

Last Script Update : 05 Feb 2022
Level : Advance

Steps - OnOpen

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Customer
03. Right Click the Customer
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnOpen for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnOpen Script 
var btnRate : Tcxbutton;
    AState    : TDataSetState;
    M, dsGrid : TDatasource;
    cdsGrid : TClientDataset;
    edCode  : TcxDBTextEdit;
    grGrid1 : TcxGrid;
    fmForm  : TForm;
    gv1     : TcxGridDBTableView;

procedure Setup;
begin
  cdsGrid := TClientDataset.Create(nil);
  dsGrid  := TDatasource.Create(nil);
  grGrid1 := TcxGrid.Create(nil);
end;

procedure FreeCmpt(lComponent :TObject);
begin
  if Assigned(lComponent) then
    lComponent.Free;
end;

procedure TearDown;
begin
  FreeCmpt(cdsGrid);
  FreeCmpt(dsGrid);
  FreeCmpt(grGrid1);
end;

procedure PrepareTable;
var gc1 : TcxGridColumn;
begin
  gv1 := TcxGridDBTableView.Create(grGrid1);
  with grGrid1.Levels.Add do begin
    Caption  := 'Grid';
    GridView := TcxCustomGridView(gv1);
  end;
  with grGrid1 do begin
    Parent  := nil;
    Visible := True;
  end;
  with gv1 do begin
    Name                             := 'Restricted_1';
    OptionsCustomize.ColumnFiltering := False;
    DataController.DataSource   := dsGrid;
    OptionsView.Footer          := True;
    OptionsView.ColumnAutoWidth := True;
    OptionsView.GroupByBox      := False;
    OptionsData.Editing         := True;
    OptionsBehavior.IncSearch   := False;
    DataController.CreateAllItems(False);
    ApplyBestFit(nil, False, False);
  end;
  with gv1.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gv1.Columns[0];
        Position := spFooter;
        Kind     := skCount;
        Format   := 'Count = #';
      end;
    finally
      EndUpdate;
    end;
  end;
  gc1 := gv1.GetColumnByFieldName('IsActive');
  SetGridColumnPropertiesClass(gc1,'TcxCheckBoxProperties');
  with TcxCheckBoxProperties(gc1.Properties) do begin
    NullStyle      := nssUnchecked;
    ValueChecked   := 'T';
    ValueUnChecked := 'F';
    ImmediatePost  := True;
  end;
end;

procedure PrepareXML;
begin
  FreeCmpt(cdsGrid);
  cdsGrid := TClientDataset.Create(nil);
  with cdsGrid.FieldDefs do begin
    Add('Description', ftString,  50, False);
    Add('Rate',        ftFMTBCD,   4, False);
    Add('IsActive',    ftString,   1, False);
  end;
  cdsGrid.CreateDataSet;
  if Trim(M.DataSet.FindField('UDF_GList1').AsString) <> '' then
    cdsGrid.XMLData := M.DataSet.FindField('UDF_GList1').AsString;
  dsGrid.Dataset := cdsGrid;
end;

procedure nvgtClick(Sender: TObject; Button: TNavigateBtn);
begin
  if Button = nbInsert then
    TDBNavigator(Sender).DataSource.DataSet.AppendRecord(['',0,'T']);
end;

procedure PostData;
begin
  cdsGrid.MergeChangeLog;
  if cdsGrid.RecordCount <> 0 then
    M.DataSet.FindField('UDF_GList1').AsString := cdsGrid.XMLData else
    M.DataSet.FindField('UDF_GList1').AsString := '';
end;

procedure OnCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if (AState = dsInsert) or (AState = dsEdit) then
    PostData;
  with grGrid1 do begin
    Parent  := nil;
    Visible := False;
  end;
  gv1.Free;
  CanClose := True;
end;

procedure InitForm;
var nvgGrid : TDBNavigator;
    pnlTop, pnlBtm, pnlBtn  : TPanel;
    btnOk  : TButton;
begin
  AState  := M.DataSet.State;
  fmForm  := TForm.Create(nil);
  pnlTop  := TPanel.Create(fmForm);
  pnlBtm  := TPanel.Create(fmForm);
  pnlBtn  := TPanel.Create(fmForm);
  btnOk   := TButton.Create(fmForm);
  nvgGrid := TDBNavigator.Create(fmForm);

  if Assigned(grGrid1) then
    grGrid1.Free;
  grGrid1 := TcxGrid.Create(nil);

  PrepareXML;
  PrepareTable;

  try
    with fmForm do begin
      Parent       := nil;
      Height       := 480;
      Width        := 800;
      Caption      := Format('Disc List(s) - %s',[M.DataSet.FindField('Code').AsString]);
      Color        := clWhite;
      BorderStyle  := bsDialog;
      Font.Size    := 10; //Optional
      Font.Name    := 'Courier New'; //Optional
      Position     := poMainFormCenter;
      OnCloseQuery := @OnCloseQuery;
    end;
    with pnlTop do begin
      Parent      := fmForm;
      Align       := alTop;
      BevelInner  := bvNone;
      BevelOuter  := bvNone;
      ParentColor := True;
      Height      := 30;
      Width       := fmForm.Width;
    end;
    with nvgGrid do begin
      Parent         := pnlTop;
      Top            := 3;
      Left           := 5;
      VisibleButtons := [nbInsert,nbDelete];
      Width          := 48;
      Height         := 25;
      DataSource     := dsGrid;
      OnClick        := @nvgtClick;
      Enabled        := (AState = dsInsert) or (AState = dsEdit);
    end;
    with pnlBtm do begin
      Parent      := fmForm;
      Align       := alClient;
      BevelInner  := bvNone;
      BevelOuter  := bvNone;
      ParentColor := True;
      Width       := fmForm.Width;
    end;
    with grGrid1 do begin
      Parent := pnlBtm;
      align  := alClient;
    end;
    with pnlBtn do begin
      Parent      := fmForm;
      Align       := alBottom;
      BevelInner  := bvNone;
      BevelOuter  := bvNone;
      ParentColor := True;
      Height      := 30;
      Width       := fmForm.Width;
    end;
    with btnOK do begin
      Parent := pnlBtn;
      Top    := 3;
      Width  := 100;
      Left   := (fmForm.Width/2) - (btnOK.Width/2);
      Caption := '&Post';
      ModalResult := mrOk;
      Enabled := (AState = dsInsert) or (AState = dsEdit);
    end;
    fmForm.ShowModal;
  finally
    fmForm.Release;
  end;
end;

procedure OnClick(Sender: TObject);
begin
  InitForm;
end;

begin
  M       := TDatasource(self.FindComponent('dsDocMaster'));
  edCode  := TcxDBTextEdit(self.FindComponent('edCode'));
  btnRate := TcxButton.Create(edCode);

  with btnRate do begin
    Parent := edCode.Parent;
    Left   := edCode.Left + edCode.Width+10;
    Top    := edCode.Top;
    Width  := 130;
    Name   := 'edRate';
    Caption := '&1.Show Rate';
    OnClick := @OnClick;
  end;
end.
10. Click Save button

Disable Prompt Dialog for Set Default DocNo Set

Last Script Update : 04 May 2024
Level : Advance

Steps - OnAfterNew

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales
03. Right Click the Sales Invoice
04. Select New Event
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
06. Select OnAfterNew for Event field
07. Click OK
08. Click the Calc (name create at Step 5 above) on the left panel
09. Copy below script & paste to the Right Panel (Script Section)
OnAfterNew Script 
var M : TDataset;

procedure OnNextDocNoChanged(Sender: TField);
begin
  //  Disable Build in Event
end;

begin
  M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet;

  M.FindField('DocNoSetKey').OnChange := @OnNextDocNoChanged;
end.
10. Click Save button

Example 50

  • More Coming Soon....

FAQ

Why when click Compile Script button prompt error [Error] (xx:xx):Unknown identifier 'Dataset'

This happen if you doing script on the event OnGridColumnValueChanged & can ignore the error if you confirm your script is correct.

How do I know my script is correct when using the script on the event OnGridColumnValueChanged ?

You can add below script at 1st line before you click Compile Script button but remember to remove it before you click Save button.
var EditingField, Dataset :Variant;

How many data source (TDatasource) it had for data entry (eg Sales Invoice) & what is each name?

Generally it had 3 data source & some had extra 1 (i.e. total 4).
Name Description
dsDocMaster Header fields
dsDocDetail Detail or Item Fields
dsSerialNumber Serial Number Fields

How to check the Record in Edit Mode?

You can use below script
... //yr other code
var AState : TDataSetState;
    M      : TDataSource;
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));
  AState := M.DataSet.State;
  if (AState = dsInsert) or (AState = dsEdit)  then
    MessageDlg('Yes In Edit Mode', mtInformation, [mbOK], 0);
end;
... //yr other code

Using Form Mode - Split Browse & Detail Windows Option Prompt Null Pointer Exception Error

You can use below script
... //yr other code
begin
  if TWinControl(Self.FindComponent('DetailControl')).Visible then begin
  ...//yr other code 
  end;      
end.

Message Dialog with Option

... //yr other code
begin
  if MessageDlg('Are you sure?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
    MessageDlg('Yes Click', mtInformation, [mbOK],0) else
    MessageDlg('No Click', mtInformation, [mbOK],0);
end.

After upgrade to Version 816 prompt error Message Cannot cast an object

DIYScript-03.gif

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=DIY_Script&oldid=13111"
Last modified
This page was last edited on 26 November 2024, at 06:53.