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 : 25 Sep 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 |
---|
var FComServer, lBizObj : Variant;
cdsData : TClientDataSet;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure GetStockInfo;
var lSQL, lCode : String;
begin
FComServer := null;
cdsData := TClientDataSet.Create(nil); // Create & preparing Temporary Table
try
lCode := Dataset.FindField('ItemCode').AsString;
lSQL := Format('SELECT UDF_Length, UDF_Width 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;
begin
if SameText(EditingField, 'ItemCode') OR // when selecting or change itemcode field
SameText(EditingField, 'UDF_Rate') then begin // when change UDF_Rate field
try
GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item
// Below is Set the Invoice detial UDF Fields from Maintain Item UDF Fields
DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
DataSet.FindField('UDF_Width').AsFloat := cdsData.FindField('UDF_Width').AsFloat;
// Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
DataSet.FindField('Qty').AsFloat := DataSet.FindField('UDF_Length').AsFloat *
DataSet.FindField('UDF_Width').AsFloat *
DataSet.FindField('UDF_Rate').AsFloat;
finally
cdsData.Free; // Free the temporary table after used
end;
end;
end.
|
- 10. Click Save button
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
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
Below is Example are doing following action
- 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 : 24 Nov 2015
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
cdsSearchKey := TClientDataset.Create(nil);
end;
procedure TearDown;
begin
cdsSearchKey.Free;
end;
procedure CreateTable;
begin
with cdsSearchKey.FieldDefs do
begin
Add('Description', ftString, 50, False);
end;
cdsSearchKey.CreateDataSet;
grGrid1 := TcxGrid.Create(nil);
gvSearchKey := TcxGridDBTableView.Create(grGrid1);
dsSearchKey := TDataSource.Create(nil);
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('edCompanyCategory') 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 + 35;
Top := edCompanyCategory.Top +3;
Caption := 'Search key :';
end;
with edSearchKey do begin // Create Drop List
Parent := edCompanyCategory.Parent;
Left := edCompanyCategory.Left + edCompanyCategory.Width + 100;
Top := edCompanyCategory.Top;
Name := 'edSearch';
DataBinding.DataSource := M;
DataBinding.DataField := 'UDF_SearchKey';
Properties.View := gvSearchKey; // Set Drop view
Properties.KeyFieldNames := 'Description'; // Field Name to set for DataBinding.DataField
Properties.ListFieldItem := gvSearchKey.GetColumnByFieldName('Description'); // Search by Description
Properties.DropDownListStyle := lsFixedList;
Properties.ImmediatePost := True;
end;
end.
|
- 10. Click Save button
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 : 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 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
D.DataSet.Delete
else
if D.DataSet.FindField('ItemCode').AsString <> 'RTN5Cents' then
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').Value := '';
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 : 09 Dec 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 |
---|
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 * ((100 + StrToFloat(S)) / 100);
end
else
AValue := AValue + StrToFloat(S);
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); //lDisc + TaxAmt
DataSet.FindField('TaxAmt').AsFloat := lTax - lDisc;
DataSet.FindField('Amount').AsFloat := lDisc;
end;
begin
if SameText(EditingField, 'UDF_Qty') or
SameText(EditingField, 'UDF_UnitPrice') or
SameText(EditingField, 'UDF_DISC') or
SameText(EditingField, 'Tax') then
RecalcFormula;
end.
|
- 10. Click Save button
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 Dec 2015
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)]);
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 := 66;
B.Top := 173;
B.Left := 6;
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 : 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 |
---|
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.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 |
---|
//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;
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 -
- 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.
See also
- DIY Fields
- Maintain DIY
- Others Customisation