| Line 1,017: | Line 1,017: | ||
* Auto Click Browse button | * Auto Click Browse button | ||
Last Script Update : | Last Script Update : 20 Apr 2019<br /> | ||
Level : Basic | Level : Basic | ||
| Line 1,052: | Line 1,052: | ||
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)]); | ||
Application.ProcessMessages; | |||
BtnBrowse.Click; | BtnBrowse.Click; | ||
BtnEdit.Click; | BtnEdit.Click; | ||
Revision as of 03:30, 20 April 2019
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. |
|
|
Example 1 - 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
- 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 |
|---|
//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;
if Copy(AFld, 1, Length('UDF_')) = 'UDF_' then begin // Set UDF Value
DataSet.Edit; // Make sure is in Edit Mode
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
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- Avoid update below field in same time as will cause unlimited looping updating each other
- - Qty
- - UnitPrice
- - TaxAmt
- - Amount
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Example 2 - 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
Example 3 - 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
Example 4 - 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 : 30 Oct 2015
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;
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;
lDtlKey := D.Dataset.FindField('Dtlkey').AsString;
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');
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 := 'Invoice 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 := 'IV 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 := 'XF 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
Example 5 - 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 1 to 2 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
Example 6 - 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
Example 7 - 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
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 := 'CompanyName2';
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
Example 8 - 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 : 12 Mar 2016
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
ODocAmt := 0.00;
D.DataSet.DisableControls;
try
D.DataSet.First;
while not D.DataSet.Eof do begin
if D.DataSet.FindField('ItemCode').AsString = 'SERVICE' then begin
D.DataSet.Delete;
Continue;
end
else
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.EnableControls;
end;
end;
begin
D := TDataSource(Self.FindComponent('dsDocDetail'));
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
Example 9 - 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
Example 10 - 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;
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
Example 11 - 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
Example 12 - 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
Example 13 - 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 + 95;
Width := 120;
Caption := '&1. Get Excel Data';
OnClick := @btnImportSOClick;
end;
end;
end.
|
- 10. Click Save button
Example 14 - 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
Example 15 - 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
Example 16 - 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
//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
Example 17 - 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
Example 18 - 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 : 12 Mar 2016
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').Value := i; //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
Example 19 - 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
Example 20 - 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
Example 21 - Change DocRef 1 Label
Below is Example are doing following actions
- Change the Purchase Order DocRef1 Label to C.PO # :
Last Script Update : 03 Sep 2016
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 C1, C2 : TComponent;
Begin
C1 := Self.FindComponent('lbDocRef1');
C2 := Self.FindComponent('lbTerms');
if Assigned(C1) then begin
TLabel(C1).Caption := 'C.PO # :';
TLabel(C1).Left := TLabel(C2).Left;
end;
end.
|
- 10. Click Save button
Example 22 - 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 : 16 Dec 2017
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;
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'));
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
Example 23 - 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
Example 24 - 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
Example 25 - 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
Example 26 - 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
Example 27 - 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
Example 28 - 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 : 20 Jul 2017
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, lBizObj, V : 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
lBizObj := null;
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 := 120;
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;
CharCase := ecUpperCase;
PasswordChar := '*';
end;
with btnOK do begin
Parent := fmPassword;
Top := 60;
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
Example 29 - 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
Example 30 - 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
Example 31 - 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
Example 32 - 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
Example 33 - 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
Example 34 - 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 := 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
Example 35 - 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
Example 36 - How to Detect what is the Current Doc Type in Document Listing?
Last Script Update : 10 Aug 2018
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 := TcxComboBox(Self.FindComponent('edDocumentType'));
edDocumentType.Properties.OnEditValueChanged := @edDocumentTypeChange;
end.
|
- 10. Click Save button
Example 37 - 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
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
Example 38 - 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
Example 39 - 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
Example 40 -
- More Coming Soon....
FAQ
01. 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.
02. 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;
03. 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).
- Below is the list & detail
| DataSource Name | Description |
|---|---|
| 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?
- You can use below script
var AState : TDataSetState;
M : TDataSource;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
AState := M.DataSet.State;
if (AState = dsInsert) or (AState = dsEdit) then begin // Check is in Insert or Edit Mode
MessageDlg('Yes In Edit Mode', mtInformation, [mbOK], 0);
end;
end;
05. 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.
See also
- DIY Fields
- Maintain DIY
- Others Customisation