| Line 4,650: | Line 4,650: | ||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
=== | ===Disable Prompt Dialog for Set Default DocNo Set=== | ||
Last Script Update : 04 May 2024<br /> | |||
Level : Advance | |||
'''Steps - OnAfterNew''' | |||
:01. Click '''Tools | DIY | SQL Control Center...''' | |||
:02. At the left panel look for '''Sales''' | |||
:03. Right Click the '''Sales Invoice''' | |||
:04. Select '''New Event''' | |||
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing) | |||
:06. Select '''OnAfterNew''' for '''Event''' field | |||
:07. Click OK | |||
:08. Click the Calc (name create at Step 5 above) on the left panel | |||
:09. Copy below script & paste to the Right Panel (Script Section) | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! OnAfterNew Script | |||
|- | |||
| | |||
<syntaxhighlight lang="delphi"> | |||
var M : TDataset; | |||
procedure OnNextDocNoChanged(Sender: TField); | |||
begin | |||
// Disable Build in Event | |||
end; | |||
begin | |||
M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet; | |||
M.FindField('DocNoSetKey').OnChange := @OnNextDocNoChanged; | |||
end. | |||
</syntaxhighlight> | |||
|} | |||
:10. Click '''Save''' button | |||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
Revision as of 05:07, 4 May 2024
Menu: Tools | DIY | SQL Control Center...
Introduction
This is Additional Module(DIY Script Module) which allow user to override or customise the SQL System.
The Language use is Pascal
DIY Script
Available Action/Event
| Action/Event | Description |
|---|---|
| OnOpen | The script trigger On Open the Form (eg On Open the Sales Invoice Form) |
| OnClose | The script trigger On Close/Exit the Form (eg on Exit Sales Invoice Form) |
| OnBeforeNew | The script trigger before the build in New Button action execute. |
| OnAfterNew | The script trigger after the build in New Button action Execute. |
| OnBeforeEdit | The script trigger before the build in Edit Button action execute. |
| OnAfterEdit | The script trigger after the build in Edit Button action Execute. |
| OnBeforeDelete | The script trigger before the build in Delete Button action execute. |
| OnAfterDelete | The script trigger after the build in Delete Button action Execute. |
| OnBeforeSave | The script trigger before the build in Save Button action execute. |
| OnAfterSave | The script trigger after the build in Save Button action Execute. |
| OnBeforeCancel | The script trigger before the build in Cancel Button action execute. |
| OnAfterCancel | The script trigger after the build in Cancel Button action Execute. |
| OnBeforePrint | The script trigger before the build in Print or Preview Button action Execute. |
| OnGridBeforeInsert | The script trigger before the build in + Button action Execute. |
| OnGridAfterInsert | The script trigger after the build in + Button action Execute. |
| OnGridBeforePost | The script trigger before post the current row record. |
| OnGridAfterPost | The script trigger after post the current row record. |
| OnGridBeforeDelete | The script trigger before the build in - Button action Execute. |
| OnGridAfteDelete | The script trigger after the build in - Button action Execute. |
| OnGridBeforeCancel | The script trigger before cancel the current row record. |
| OnGridAfterCancel | The script trigger after cancel the current row record. |
| OnGridColumnValueChanged | The script trigger on changes value in the current record. |
|
|
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;
DataSet.Edit; // Make sure is in Edit Mode
if Copy(AFld, 1, Length('UDF_')) = 'UDF_' then begin // Set UDF Value
if CheckAvailFld(cdsData, AFld) then
DataSet.FindField(AFld).AsString := cdsData.FindField(AFld).AsString;
end;
end;
end;
procedure Recalc;
begin
// Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
DataSet.Edit; // Make sure is in Edit Mode
DataSet.FindField('Qty').AsFloat := DataSet.FindField('UDF_Length').AsFloat *
DataSet.FindField('UDF_Width').AsFloat *
DataSet.FindField('UDF_Rate').AsFloat;
end;
begin
if SameText(EditingField, 'ItemCode') then begin // when selecting or change itemcode field
try
GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item
AssignValue; // Set the Invoice detial UDF Fields from Maintain Item UDF Fields
Recalc;
finally
cdsData.Free; // Free the temporary table after used
end;
end;
if SameText(EditingField, 'UDF_Rate') then // when change UDF_Rate field
Recalc;
end.
|
- 10. Click Save button
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- Avoid update below field in same time as will cause unlimited looping updating each other
- - Qty
- - UnitPrice
- - TaxAmt
- - Amount
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Change Title Caption
Below is Example are doing following actions
- Change the Sales Quotation Title to My Quotation
Last Script Update : 19 Oct 2015
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var L: TComponent;
Begin
L := Self.FindComponent('lbQt'); // Find the original label
if Assigned(L) then
TcxLabel(L).Caption := 'My Quotation'; // Change the label Caption
end;
|
- 10. Click Save button
Get UDF From Maintain Customer
Below is Example are doing following actions
- At Sales Invoice On Select Customer Code prompt UDF_MSG from Maintain Customer
Last Script Update : 20 Oct 2015
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var FComServer, lBizObj : Variant;
M : TDataSource;
C : TcxDBTextEdit;
lCdsDataList: TClientDataSet;
lSQL : String;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure OnCompanyNameChanged(Sender: TObject);
var s : string;
AState : TDataSetState;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
AState := M.DataSet.State;
if (AState = dsInsert) or (AState = dsEdit) then begin //Only Execute if in Edit or Insert Mode
FComServer := null;
lCdsDataList := TClientDataSet.Create(nil);
try
lSQL := Format('SELECT UDF_MSG FROM AR_CUSTOMER WHERE Code= %s',[QuotedStr(M.DataSet.FindField('Code').AsString)]);
lCdsDataList.Data := ComServer.DBManager.Execute(lSQL);
s := lCdsDataList.FindField('UDF_MSG').AsString;
if Trim(s) <> '' then
MessageDlg(S, mtInformation, [mbOk], 0);
finally
FComServer := null;
lCdsDataList.Free;
end;
end;
end;
begin
C := TcxDBTextEdit(Self.FindComponent('edCompanyName'));
if Assigned(C) then
C.Properties.OnEditValueChanged := @OnCompanyNameChanged;
end.
|
- 10. Click Save button
Get Transfer Information - QT to IV
Below is Example are doing following action
- Quotation direct Transfer to Invoice.
- Get the Invoice number, Date & Qty which had transferred to Invoice in the Quotation and shown it below the Browse button
Last Script Update : 15 Jul 2021
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var FComServer, lBizObj : Variant;
C : TComponent;
T : TTimer;
M, D : TDataSource;
L1, L2, L3, L4, L5, L6 : TLabel;
cdsTemp : TClientDataset;
BtnBrowse : TcxButton;
function ComServer: Variant;
begin
if FComServer = Null then
FComServer := CreateOleObject('SQLAcc.BizApp');
Result := FComServer;
end;
procedure Setup;
begin
T := TTimer.Create(Self);
L1 := TLabel.Create(self);
L2 := TLabel.Create(self);
L3 := TLabel.Create(self);
L4 := TLabel.Create(self);
L5 := TLabel.Create(self);
L6 := TLabel.Create(self);
end;
procedure GetToDocInfo;
var lSQL, lDocKey, lDtlkey : String;
begin
lDocKey := M.Dataset.FindField('Dockey').AsString;
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');
BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
if TWinControl(Self.FindComponent('DetailControl')).Visible then begin
T.Enabled := True;
T.Interval := 1000; // = Update every 1 second
T.OnTimer := @OnTimer;
with L1 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := BtnBrowse.Top + BtnBrowse.Height+5;
Caption := 'Invoice No';
Font.Color := clBlue;
Font.Style := [fsBold];
end;
with L2 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := L1.Top + L1.Height+5;
Caption := 'DocNo';
Font.Color := clBlue;
Font.Style := [fsBold];
end;
with L3 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := L2.Top + L2.Height+5;
Caption := 'IV Date';
Font.Color := clGreen;
Font.Style := [fsBold];
end;
with L4 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := L3.Top + L3.Height+5;
Caption := 'Date';
Font.Color := clGreen;
Font.Style := [fsBold];
end;
with L5 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := L4.Top + L4.Height+5;
Caption := 'XF Qty';
Font.Color := clRed;
Font.Style := [fsBold];
end;
with L6 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := L5.Top + L5.Height+5;
Caption := 'Qty';
Font.Color := clRed;
Font.Style := [fsBold];
end;
end;
end.
|
- 10. Click Save button
Only Enable Some of the field on Click Edit by User
Below is Example are doing following action in Sales Cash Sales
- Set all Field to Read Only at Header except Agent, DocNo, DocRef1, DocNoEx & UDF_Expired
- Set all Detail Field to Read Only except Description & Account Field
Remember to Enable Back by Disable the GetAuthorised (see below) Script & Insert it to OnBeforeNew script
Cons : It will be delay about 3 to 4 sec. after click Edit & New button
Last Script Update : 03 Nov 2015
Level : Advance
Steps - OnBeforeEdit & OnBeforeNew
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Cash Sales
- 03. Right Click the Sales Cash Sales
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeEdit for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeEdit Script |
|---|
var lAST : Boolean;
i : integer;
C : TWinControl;
S : TStringList;
gvDocDetail: TcxGridDBTableView;
DBNavDetail : TDBNavigator;
btnMoveUp, btnMoveDown : TSpeedButton;
M : TDataSource;
FComServer, lBizObj : Variant;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure GetAuthorised;
var cdsTemp : TClientDataset;
lSQL : String;
begin
FComServer := null;
cdsTemp := TClientDataset.Create(nil);
lSQL := Format('SELECT UDF_AllowEdit FROM SY_USER WHERE Code=%s',[QuotedStr(CurrentUser)]);
try
cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
if cdsTemp.RecordCount > 0 then
lAST := cdsTemp.FindField('UDF_AllowEdit').Value = 1;
finally
cdsTemp.Free;
FComServer := null;
end;
end;
begin
lAST := True; // Default All Enable
M := TDataSource(Self.FindComponent('dsDocMaster'));
gvDocDetail := TcxGridDBTableView(self.FindComponent('gvDocDetail'));
DBNavDetail := TDBNavigator(self.FindComponent('DBNavDetail'));
btnMoveUp := TSpeedButton(self.FindComponent('btnMoveUp'));
btnMoveDown := TSpeedButton(self.FindComponent('btnMoveDown'));
S := TStringList.Create;
try
GetAuthorised; // Just Disable/Delete this line for OnBeforeNew script
{$Region 'Lock by Component'}
S.Add('edDocDate');
S.Add('edCustomer');
// S.Add('edAgent');
S.Add('edTerms');
S.Add('edCompanyName');
S.Add('edCurrencyRate');
// S.Add('edDocNo');
// S.Add('edDocRef1');
// S.Add('edDocNoEx');
S.Add('edAddress1');
S.Add('edAddress2');
S.Add('edAddress3');
S.Add('edAddress4');
S.Add('edNextDocNo');
S.Add('edDocDescription');
S.Add('edD_Amount');
S.Add('pnlDeposit');
for i := 0 to S.Count - 1 do begin
C := TWinControl(Self.FindComponent(S[i]));
if Assigned(C) then C.Enabled := lAST;
end;
DBNavDetail.Visible := lAST;
btnMoveUp.Visible := lAST;
btnMoveDown.Visible := lAST;
{$EndRegion}
{$Region 'Lock Detail'}
for i := 0 to gvDocDetail.ColumnCount -1 do begin
// if (gvDocDetail.Columns[i].Caption <> 'Item Code') and
if (gvDocDetail.Columns[i].Caption <> 'Description') and
(gvDocDetail.Columns[i].Caption <> 'Account') then
gvDocDetail.Columns[i].Options.Editing := lAST;
end;
{$EndRegion}
{$Region 'Lock by Dataset'}
M.DataSet.FindField('Project').ReadOnly := not lAST;
M.DataSet.FindField('Area').ReadOnly := not lAST;
M.DataSet.FindField('Validity').ReadOnly := not lAST;
M.DataSet.FindField('DeliveryTerm').ReadOnly := not lAST;
M.DataSet.FindField('CC').ReadOnly := not lAST;
M.DataSet.FindField('DocRef2').ReadOnly := not lAST;
M.DataSet.FindField('DocRef3').ReadOnly := not lAST;
M.DataSet.FindField('DocRef4').ReadOnly := not lAST;
M.DataSet.FindField('BranchName').ReadOnly := not lAST;
M.DataSet.FindField('DAddress1').ReadOnly := not lAST;
M.DataSet.FindField('DAddress2').ReadOnly := not lAST;
M.DataSet.FindField('DAddress3').ReadOnly := not lAST;
M.DataSet.FindField('DAddress4').ReadOnly := not lAST;
M.DataSet.FindField('DAttention').ReadOnly := not lAST;
M.DataSet.FindField('DPhone1').ReadOnly := not lAST;
M.DataSet.FindField('DFax1').ReadOnly := not lAST;
M.DataSet.FindField('Attention').ReadOnly := not lAST;
M.DataSet.FindField('Phone1').ReadOnly := not lAST;
M.DataSet.FindField('Fax1').ReadOnly := not lAST;
M.DataSet.FindField('UDF_Expired').ReadOnly := not lAST;
{$EndRegion}
finally
S.Free;
end;
end.
|
- 10. Click Save button
Calculate Age between 2 date
Below is Example are doing following actions
- Do Calculation UDF_Age := UDF_DateTo - UDF_DateFrom
Last Script Update : 19 Nov 2015
Level : Basic
Steps - OnGridColumnValueChanged
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnGridColumnValueChanged for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnGridColumnValueChanged Script |
|---|
begin
if SameText(EditingField, 'UDF_DateFrom') OR // when selecting or change UDF_DateFrom field
SameText(EditingField, 'UDF_DateTo') then begin // when change UDF_DateTo field
DataSet.FindField('UDF_Age').AsFloat := DataSet.FindField('UDF_DateTo').AsDateTime -
DataSet.FindField('UDF_DateFrom').AsDateTime;
end;
end.
|
- 10. Click Save button
Simple DropDown Selection List
Below is Example are doing following actions
- Create Label
- Create a drop down list at Maintain Customer for UDF_SearchKey
Last Script Update : 26 Mar 2018
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Customer
- 03. Right Click the Customer
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var M, dsSearchKey : TDatasource;
cdsSearchKey : TClientDataSet;
edCompanyCategory, edSearchKey : TcxDBExtLookupComboBox;
gvSearchKey : TcxGridDBTableView;
grGrid1 : TcxGrid;
lbSearchKey : TLabel;
procedure Setup;
begin
dsSearchKey := TDataSource.Create(nil);
cdsSearchKey := TClientDataset.Create(nil);
grGrid1 := TcxGrid.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
if Assigned(lComponent) then
lComponent.Free;
end;
procedure TearDown;
begin
FreeCmpt(cdsSearchKey);
FreeCmpt(dsSearchKey);
FreeCmpt(gvSearchKey);
FreeCmpt(grGrid1);
end;
procedure CreateTable;
begin
with cdsSearchKey.FieldDefs do
begin
Add('Description', ftString, 50, False);
end;
cdsSearchKey.CreateDataSet;
gvSearchKey := TcxGridDBTableView.Create(grGrid1);
with grGrid1 do begin
Parent := nil;
Visible := False;
end;
with grGrid1.Levels.Add do begin
Caption := 'SearchKeyGrid';
GridView := TcxCustomGridView(gvSearchKey);
end;
with gvSearchKey.CreateColumn do begin
Caption := 'Description';
DataBinding.FieldName := 'Description';
HeaderAlignmentHorz := taCenter;
Options.IncSearch := False;
end;
dsSearchKey.DataSet := cdsSearchKey;
with gvSearchKey do begin
Name := 'Restricted_1';
OptionsCustomize.ColumnFiltering := False;
DataController.DataSource := dsSearchKey;
OptionsView.Footer := True;
OptionsView.ColumnAutoWidth := True;
OptionsView.GroupByBox := False;
// ApplyBestFit(nil);//Optional
end;
with gvSearchKey.DataController.Summary do begin
BeginUpdate;
try
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvSearchKey.GetColumnByFieldName('Description');
Position := spFooter;
Kind := skCount;
Format := 'Count = #';
end;
finally
EndUpdate;
end;
end;
end;
procedure AddcdsSearchKey(const lDesc : String);
begin
cdsSearchKey.Append;
cdsSearchKey.FieldValues['Description'] := lDesc;
cdsSearchKey.Post;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
edCompanyCategory := Self.FindComponent('edControlAccount') as TcxDBExtLookupComboBox;
edSearchKey := TcxDBExtLookupComboBox.Create(edCompanyCategory);
lbSearchKey := TLabel.Create(edCompanyCategory);
CreateTable;
AddcdsSearchKey('Salesman');
AddcdsSearchKey('Dummy');
AddcdsSearchKey('Normal Outlet');
with lbSearchKey do begin // Create Label
Parent := edCompanyCategory.Parent;
Left := edCompanyCategory.Left + edCompanyCategory.Width + 150;
Top := edCompanyCategory.Top +3;
Caption := 'Search key :';
end;
with edSearchKey do begin // Create Drop List
Parent := edCompanyCategory.Parent;
Left := edCompanyCategory.Left + edCompanyCategory.Width + 250;
Top := edCompanyCategory.Top;
Name := 'edSearch';
DataBinding.DataSource := M;
DataBinding.DataField := 'UDF_SearchKey';
Properties.View := gvSearchKey; // Set Drop view
Properties.KeyFieldNames := 'Description'; // Field Name to set for DataBinding.DataField
Properties.ListFieldItem := gvSearchKey.GetColumnByFieldName('Description'); // Search by Description
Properties.DropDownListStyle := lsFixedList;
Properties.ImmediatePost := True;
end;
end.
|
- 10. Click Save button
Auto Add Service Charge
Below is Example are doing following actions
- Auto Append ItemCode SERVICE
- Do Calculation for ItemCode SERVICE UnitPrice := Total Doc Amount (Excluding GST) * 0.1 & Round to 2 decimal point
Last Script Update : 08 Nov 2022
Level : Basic
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var D : TDataSource;
ODocAmt : Variant;
procedure DelOldRecord;
begin
D.DataSet.Filter := 'ItemCode=''SERVICE'' ';
D.DataSet.Filtered := True;
try
if D.DataSet.RecordCount > 0 then begin
D.DataSet.First;
D.DataSet.Delete;
end;
D.DataSet.Filter := '';
D.DataSet.Filtered := False;
D.DataSet.First;
while not D.DataSet.Eof do begin
if D.DataSet.FindField('ItemCode').AsString <> 'RTN5Cents' then // To Excluding 5 Cents rounding
ODocAmt := ODocAmt + D.DataSet.FindField('Amount').AsFloat; // To Get Total DocAmt before GST
D.DataSet.Next;
end;
finally
D.DataSet.Filter := '';
D.DataSet.Filtered := False;
end;
end;
begin
D := TDataSource(Self.FindComponent('dsDocDetail'));
ODocAmt := 0.00;
DelOldRecord;
with D.DataSet do begin
Append;
FindField('ItemCode').AsString := 'SERVICE';
FindField('UnitPrice').Value := SimpleRoundToEx(ODocAmt * 0.1, -2);
FindField('Disc').AsString := '';
FindField('Transferable').AsString := 'F';
Post;
end;
end.
|
- 10. Click Save button
Auto Click Profit Estimator Button
Below is Example are doing following actions
- Auto Click the Profit Estimator button
Last Script Update : 02 Dec 2015
Level : Basic
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var btnProfit : Tcxbutton;
begin
btnProfit := Tcxbutton(Self.FindComponent('BtnProfitEstimator'));
if Assigned(btnProfit) then
btnProfit.Click;
end.
|
- 10. Click Save button
Simulate build-in Calculation using UDF Fields
Below is Example are doing following actions
- Get Discount Amount
- Get Tax Amount
- Do Calculation Amt := (UDF_UnitPrice*UDF_Qty)-UDF_Disc)*(TaxRate)
Last Script Update : 03 Sep 2016
Level : Basic
Steps - OnGridColumnValueChanged
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnGridColumnValueChanged for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnGridColumnValueChanged Script |
|---|
function GetDiscountedValue(AValue: Variant; Discount: string): Variant;
var lStrLst: TStringList;
S: string;
I: Integer;
begin
Result := Null;
lStrLst := TStringList.Create;
try
try
lStrLst.Delimiter := '&';
lStrLst.DelimitedText := Trim(StringReplace(Trim(Discount), '+', '&', [rfReplaceAll]));
for I := 0 to lStrLst.Count - 1 do
begin
S := Trim(lStrLst.Strings[I]);
if S[Length(S)] = '%' then
begin
S := Trim(Copy(S, 1, Length(S) - 1));
AValue := AValue * ((100 - StrToFloat(S)) / 100);
end
else
AValue := AValue - StrToFloat(S);
end;
Result := AValue;
except
RaiseException(ExceptionType, 'Discount formatting error ');
end;
finally
lStrLst.Free;
end;
end;
function GetTaxedValue(AValue: Variant; lTaxRate: string): Variant;
var lStrLst: TStringList;
S: string;
I: Integer;
begin
Result := Null;
lStrLst := TStringList.Create;
try
try
lStrLst.Delimiter := '&';
S := Trim(StringReplace(lTaxRate, '+', '&', [rfReplaceAll])); //Change + to &
lStrLst.DelimitedText := Trim(StringReplace(s, 'E', '', [rfReplaceAll])); //Remove the E
for I := 0 to lStrLst.Count - 1 do
begin
S := Trim(lStrLst.Strings[I]);
if S[Length(S)] = '%' then
begin
S := Trim(Copy(S, 1, Length(S) - 1));
AValue := AValue * ((StrToFloat(S)) / 100);
end;
end;
Result := AValue;
except
RaiseException(ExceptionType, 'Tax Rate formatting error ');
end;
finally
lStrLst.Free;
end;
end;
procedure RecalcFormula;
var lAmt, lDisc, lTax : Variant;
begin
lAmt := SimpleRoundToEx(DataSet.FindField('UDF_Qty').AsFloat *
DataSet.FindField('UDF_UnitPrice').AsFloat, -2);
lDisc := SimpleRoundToEx(GetDiscountedValue(lAmt, DataSet.FindField('UDF_Disc').AsString), -2); //lAmt - UDF_Disc
lTax := SimpleRoundToEx(GetTaxedValue(lDisc, DataSet.FindField('TaxRate').AsString), -2); //TaxAmt
DataSet.FindField('TaxAmt').AsFloat := lTax - lDisc;
DataSet.FindField('Amount').AsFloat := lDisc;
end;
begin
if SameText(EditingField, 'UDF_Qty') or
SameText(EditingField, 'UDF_UnitPrice') or
SameText(EditingField, 'UDF_DISC') or
SameText(EditingField, 'Tax') then
RecalcFormula;
end.
|
- 10. Click Save button
Auto Click Edit, Save & Browse Button
Below is Example are doing following actions
- Select Record at Grid
- Auto Click Browse button
- Auto Click Edit button
- Auto Click Save button
- Auto Click Browse button
Last Script Update : 20 Apr 2019
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
//Batch Edit Save Record(s)
var C : TComponent;
B, BtnSave, BtnEdit, BtnBrowse : TcxButton;
G : TDatasource;
A : TWinControl;
procedure OnClick(Sender: TObject);
var i : integer;
Time: TDateTime;
begin
try
i:= 0;
Time := now;
G.DataSet.First;
while not G.DataSet.Eof do begin
i := i + 1;
Self.Caption := Format('%s of %s', [IntToStr(i), IntToStr(G.DataSet.RecordCount)]);
Application.ProcessMessages;
BtnBrowse.Click;
BtnEdit.Click;
BtnSave.Click;
BtnBrowse.Click;
G.DataSet.Next;
end;
finally
Time := Now - Time;
MessageDlg(Format('Done [Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', Time)]),
mtInformation, [mbOk], 0);
end;
end;
Begin
C := Self.FindComponent('frDataSetButton1');
G := TDataSource(Self.FindComponent('dsGrid'));
BtnSave := TcxButton(C.FindComponent('btnSave'));
BtnEdit := TcxButton(C.FindComponent('btnEdit'));
BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
if Assigned(C) then
B := TcxButton.Create(Self);
B.Parent := TwinControl(c);
B.width := BtnBrowse.Width;
B.Top := BtnBrowse.Top + BtnBrowse.Height + 5;
B.Left := BtnBrowse.Left;
B.Height := BtnBrowse.Height;
B.Caption := 'Start';
B.OnClick := @OnClick;
end.
|
- 10. Click Save button
Get Current User Name and Current Working Date
Below is Example are doing following actions
- Set Current Login User ID & Current Working Date to UDF
Last Script Update : 22 Dec 2015
Level : Basic
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var M : TDataSource;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
with M.DataSet do begin
if FindField('DocNo').AsString = '<<New>>' then begin // Check is New Doc
FindField('UDF_CreatedBy').AsString := CurrentUser;
FindField('UDF_CreatedDate').Value := CurrentWorkingDate;
end;
end;
end;
|
- 10. Click Save button
Get Excel Data
Below is Example are doing following actions
- Get Data from Excel & append to detail
Last Script Update : 14 Jan 2016
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
// Import excel to detail - about 2 sec per record
var xlApp, xlSheet: Variant;
AXLSFile: String;
btnImport : TcxButton;
btnMoveDown : TSpeedButton;
M, D : TDataSource;
function GetSheet(const AWb,AWs: Variant): Variant;
begin
try
Result := AWb.Worksheets[AWs];
except
RaiseException(ExceptionType, 'Selected workbook does not contained worksheet ' +
#13#10+ QuotedStr(AWs) + '. Please select another excel workbook!');
end;
end;
procedure ImportData;
var r : integer;
begin
xlSheet := GetSheet(xlApp.Workbooks[1], 'Sheet1');
if not VarIsEmpty(xlSheet) then begin
for r:= 2 to xlSheet.UsedRange.Rows.Count do begin //Start from Row 2
with D.Dataset do begin //xlSheet.Cells[row,column].Value
Append;
FindField('Itemcode').AsString := xlSheet.Cells[r,1].Value;
FindField('Description').AsString := xlSheet.Cells[r,2].Value;
FindField('Qty').AsFloat := xlSheet.Cells[r,3].Value;
FindField('UnitPrice').AsFloat := xlSheet.Cells[r,5].Value;
Post;
end;
end;
end;
end;
procedure GetExcel;
begin
try
if not VarIsEmpty(xlApp) then xlApp := Null;
xlApp := CreateOleObject('Excel.Application');
except
RaiseException(ExceptionType, 'Microsoft Excel was not present or installed at this machine!' +
#13#10+ 'Please install Microsoft Excel before executing this script!' #13#13
'(Error ' + QuotedStr(UpperCase(ExceptionParam)) + ' occured!)');
end;
if PromptForFileName(AXLSFile, 'Microsoft Office Excel File(*.xls)|*.xls', 'xls', 'Open', '', False) then begin
xlApp.WorkBooks.Open(AXLSFile);
xlApp.ScreenUpdating := False;
try
ImportData;
finally
xlApp.ScreenUpdating := True;
xlApp.DisplayAlerts := False;
xlApp.Workbooks.Close;
if not VarIsEmpty(xlApp) then xlApp.Quit;
xlApp := Null;
end;
end;
end;
procedure btnImportSOClick(Sender: TObject);
var AState : TDataSetState;
begin
AState := M.DataSet.State;
if (AState = dsInsert) or (AState = dsEdit) then
GetExcel;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
D := TDataSource(Self.FindComponent('dsDocDetail'));
btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
if Assigned(btnMoveDown) then
begin
btnImport := TcxButton.Create(btnMoveDown);
with btnImport do begin
Parent := btnMoveDown.Parent;
Top := btnMoveDown.Top;
Height := btnMoveDown.Height;
Left := btnMoveDown.Left + btnMoveDown.Width + 130;
Width := 120;
Caption := '&1. Get Excel Data';
OnClick := @btnImportSOClick;
end;
end;
end.
|
- 10. Click Save button
Copy To & Paste From Clipboard
Below is Example are doing following actions
- Copy string to Clipboard
- Paste from Clipboard
Last Script Update : 14 Jan 2016
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var btnProfit, btnCopy, btnPaste : TcxButton;
lCB : TClipBoard;
s : string;
procedure OnCopyClick(Sender: TObject);
begin
lCB := ClipBoard;
s := 'Copy to the Clipboard and From The ClipBoard :) ';
MessageDlg('Copy To ClipBoard Done!', mtInformation, [mbOk], 0);
lCb.AsText := s; //lCB.AsText := cdsOSBOM.XMLData; if using TClientDataSet;
end;
procedure OnPasteClick(Sender: TObject);
begin
lCB := ClipBoard;
s := lCB.AsText; //cdsTemp.XMLData := lCB.AsText; if using TClientDataSet;
MessageDlg(s, mtInformation, [mbOk], 0);
end;
begin
btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
btnPaste := TcxButton.Create(self);
btnCopy := TcxButton.Create(self);
if Assigned(btnProfit) then begin
with btnCopy do begin
Parent := btnProfit.Parent;
Top := btnProfit.Top;
Height := btnProfit.Height;
Left := btnProfit.Left + btnProfit.Width + 3;
Width := btnProfit.Width;
Caption := 'Copy ClipBoard';
OnClick := @OnCopyClick;
end;
with btnPaste do begin
Parent := btnProfit.Parent;
Top := btnProfit.Top;
Height := btnProfit.Height;
Left := btnProfit.Left + (btnProfit.Width*2) + 6;
Width := btnProfit.Width;
Caption := 'Paste ClipBoard';
OnClick := @OnPasteClick;
end;
end;
end.
|
- 10. Click Save button
Get CSV/TXT Data
Below is Example are doing following actions
- Get Data from CSV/TXT & append to detail
Last Script Update : 03 Mar 2018
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var MasterList : TStringList;
btnImport : TcxButton;
btnMoveDown : TSpeedButton;
M, D : TDataSource;
function ValueOfDelimitedStr(const AStr: string; const AIndex: integer): string;
var S: TStringList;
begin
S := TStringList.Create;
try
S.Delimiter := ',';
S.StrictDelimiter := True;
S.DelimitedText := AStr;
if AIndex < S.Count then Result := Trim(S[AIndex])
else Result := '';
finally
S.Free;
end;
end;
procedure GetCSV;
var lFileName, lFilter : string;
var i : integer;
begin
MasterList := TStringList.Create;
lFilter := 'Texts Files (*.txt, *.csv)|*.txt;*.csv|All Files (*.*)|*.*';
try
if PromptForFileName(lFileName, lFilter, 'txt', 'Open...', '.', False) then begin
MasterList.LoadFromFile(lFileName);
with D.Dataset do begin
for i:= 0 to MasterList.Count -1 do begin
Append;
FindField('Itemcode').AsString := ValueOfDelimitedStr(MasterList[i], 0); //Get column 1
FindField('Description').AsString := ValueOfDelimitedStr(MasterList[i], 1); //Get column 2
FindField('Qty').AsFloat := StrToFloat(ValueOfDelimitedStr(MasterList[i], 2));
FindField('UnitPrice').AsFloat := StrToFloat(ValueOfDelimitedStr(MasterList[i], 4));
Post;
end;
end;
end;
finally
MasterList.Free;
end;
end;
procedure btnImportSOClick(Sender: TObject);
var AState : TDataSetState;
begin
AState := M.DataSet.State;
if (AState = dsInsert) or (AState = dsEdit) then
GetCSV;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
D := TDataSource(Self.FindComponent('dsDocDetail'));
btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
if Assigned(btnMoveDown) then
begin
btnImport := TcxButton.Create(btnMoveDown);
with btnImport do begin
Parent := btnMoveDown.Parent;
Top := btnMoveDown.Top;
Height := btnMoveDown.Height;
Left := btnMoveDown.Left + btnMoveDown.Width + 95;
Width := 120;
Caption := '&1. Get CSV Data';
OnClick := @btnImportSOClick;
end;
end;
end.
|
- 10. Click Save button
Set Default Filtering For Sales Report
Below is Example are doing following actions
- Filter by Agent in Sales Document Listing
- Disable Selection for Agent
Last Script Update : 02 Feb 2016
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Document Listing
- 03. Right Click the Sales Document Listing
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
//GL
//- GL Document Listing - Panel2
//- GL Journal of Transaction Listing - Panel2
//- GL Transaction Summary - Panel2
//- GL Ledger - Panel2
//- GL Receipt and Payment - Panel1
//Customer
//- Customer Document Listing - Panel1, Panel3
//- Customer Balance Report - Panel1
//- Customer Due Listing - Panel1, Panel2
//- Customer Aging - Panel2
//- Customer Statement - Panel3
//- Customer Post Dated Cheque Listing - Panel3
//- Customer Analysis by Document - Panel2
//- Customer Sales & Collection Analysis - Panel2
//Supplier
//- Supplier Document Listing - Panel1, Panel3
//- Supplier Balance Report - Panel2
//- Supplier Due Listing - Panel1, Panel2
//- Supplier Aging - Panel2
//- Supplier Statement - Panel2
//- Supplier Post Dated Cheque Listing - Panel3
//- Supplier Analysis by Document - Panel2
//- Supplier Purchase & Payment Analysis - Panel1
//Sales
//- Yearly Sales Analysis - pnParam
//- Sales Price History - pnParam1
//- Sales Picking List - pnParam
//- Outstanding Sales Doc Listing - pnParam
//- Sales Document Listing - pnParam
//- Sales Analysis By Document - pnParam
//Purchase
//- Yearly Purchase Analysis - Panel1
//- Purchase Price History - pnParam1
//- Outstanding Purchase Doc Listing - pnParam
//- Purchase Document Listing - pnmParam
//- Purchase Analysis By Document - pnParam1
var C: TWinControl;
L : TStringList;
begin
C := Self.FindComponent('pnParam') as TWinControl;
C := C.FindChildControl('edAgent_Panel') as TWinControl;
L := TStringList.Create;
try
if C <> nil then C.Enabled := False; // Disable Selection
C := TWinControl(C).FindChildControl('edAgent_Edit') as TWinControl;
L.Add('NF');
L.Add('LF');
(C as TControl).SetTextBuf(L.CommaText);// For Multi Records
// (C as TControl).SetTextBuf('LF');// For 1 record
finally
L.Free;
end;
end.
|
- 10. Click Save button
Copy RefCost to UDF At Maintain Item
Below is Example are doing following actions
- Set UDF_CommCost := RefCost * 1.1 (round to 2 decimal)
Last Script Update : 03 Mar 2016
Level : Basic
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Stock Item
- 03. Right Click the Stock Item
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var M : TDataSet;
begin
M := TDataSource(Self.FindComponent('dsAccess')).Dataset;
M.FindField('UDF_CommCost').AsFloat := SimpleRoundToEx(M.FindField('RefCost').AsFloat*1.1, -2);
end.
|
- 10. Click Save button
Recalculate Formula Done at OnGridColumnValueChanged Script for Transferred Document
Below is Example are doing following actions
- Recalculate the Formula Done at OnGridColumnValueChanged
- Copy Original fields (eg Qty) to UDF fields for Transfer Items
- Copy UDF fields (eg UDF_Qty) to Original fields for Non Transfer Items
Last Script Update : 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
Hide Stock Opening Grid In Maintain Item
Below is Example are doing following actions
- Hide the Grid & Navigator Button for Opening Balance
Last Script Update : 13 Apr 2016
Level : Basic
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Stock Item
- 03. Right Click the Stock Item
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
//UOM - DBNavUOM, grUOM
//Cust. Price - DBNavCustomerPrice, grCustomerPrice
//Supp. Price - DBNavSupplierPrice, grSupplierPrice
//BOM - DBNavBOM, grBOM
//Category - cxGrid1
//Alternative - DBNavAltStockItem, grAltStockItem
//Cust. Item - DBNavCustomerItem, grCustomerItem
//Supp. Item - DBNavSupplierItem, grSupplierItem
//Barcode - DBNavBarcode, grBarcode
var A, A1 : TWinControl;
begin
A := TWinControl(Self.FindComponent('grOpeningBalance'));
A1 := TWinControl(Self.FindComponent('DBNavOpeningBalance'));
A.Visible := not (Assigned(A));
A1.Visible := not (Assigned(A1));
end.
|
- 10. Click Save button
Export Data To Excel
Below is Example are doing following actions
- Export Maintain Agent List to Excel
Last Script Update : 26 May 2016
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Agent
- 03. Right Click the Agent
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
// Export to excel
// Useful link http://www.programmershare.com/2572647/
var xlApp : Variant;
AXLSFile: String;
C : TComponent;
B : TcxButton;
M : TDataset;
procedure ExportData;
var r : integer;
begin
M := TDataSource(Self.FindComponent('dsGrid')).Dataset;
{$Region 'Header'}
xlApp.Cells [1,1].Value:= 'DistributorID';
xlApp.Cells [1,2].Value:= 'Region';
xlApp.Cells [1,3].Value:= 'Area';
xlApp.Cells [1,4].Value:= 'SalesmanCode';
xlApp.Cells [1,5].Value:= 'SalesmanName';
{$EndRegion}
{$Region 'Data'}
r := 1;
M.First;
while not M.Eof do begin
inc(r);
//xlSheet.Cells[row,column].Value
xlApp.Cells [r,1].Value:= '123456';
xlApp.Cells [r,2].Value:= '';
xlApp.Cells [r,3].Value:= '';
xlApp.Cells [r,4].Value:= M.FindField('Code').AsString;
xlApp.Cells [r,5].Value:= M.FindField('Description').AsString;
M.Next;
end;
{$EndRegion}
end;
procedure btnExportExcelClick(Sender: TObject);
begin
try
if not VarIsEmpty(xlApp) then xlApp := Null;
xlApp := CreateOleObject('Excel.Application');
except
RaiseException(ExceptionType, 'Microsoft Excel was not present or installed at this machine!' +
#13#10+ 'Please install Microsoft Excel before executing this script!' #13#13
'(Error ' + QuotedStr(UpperCase(ExceptionParam)) + ' occured!)');
end;
AXLSFile := 'Salesman';
if PromptForFileName(AXLSFile, 'Microsoft Office Excel File(*.xls)|*.xls', 'xls', 'Save As', '', True) then begin
xlApp.ScreenUpdating := False;
try
xlApp.WorkBooks.Add;
xlApp.WorkBooks[1].WorkSheets[1];
ExportData;
xlApp.WorkBooks[1].SaveAs(AXLSFile, -4143);
xlApp.ActiveWorkBook.Saved := True;
MessageDlg('Done', mtInformation, [mbOk], 0);
finally
xlApp.ScreenUpdating := True;
xlApp.DisplayAlerts := False;
xlApp.Workbooks.Close;
// xlApp.Quit;
if not VarIsEmpty(xlApp) then xlApp.Quit;
xlApp := Null;
end;
end;
end;
begin
C := Self.FindComponent('frDataSetButton1');
if Assigned(C) then
B := TcxButton.Create(C);
B.Parent := TwinControl(c);
B.width := 66;
B.Top := 173;
B.Left := 6;
B.Caption := '&1. Export';
B.OnClick := @btnExportExcelClick;
end.
|
- 10. Click Save button
Change DocRef 1 Label
Below is Example are doing following actions
- Change the Purchase Order DocRef1 Label to C.PO # :
Last Script Update : 21 Jul 2021
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Purchase Order
- 03. Right Click the Purchase Order
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var L1, L2: TComponent;
T : TTimer;
ACount : Integer;
procedure OnTimer(Sender: TObject);
begin
T.Tag := T.Tag + 1;
if Assigned(L1) then begin
TLabel(L1).left := TLabel(L2).Left;//220;
TLabel(L1).Caption := 'C.PO # :'; // Change the label Caption
end;
T.Enabled := not (T.Tag = ACount);
end;
begin
L1 := Self.FindComponent('lbDocRef1'); // Find the original label
L2 := Self.FindComponent('lbTerms');
ACount := 5;
T := TTimer.Create(Self);
T.Enabled := True;
T.Interval := 1000; // = 1 sec
T.OnTimer := @OnTimer;
T.Tag := 0;
end;
|
- 10. Click Save button
Add Click Button
Below is Example are doing following actions
- Create 2 buttons at Sales Quotation
- 1 at Beside Up Down Button
- 1 at Below Browse Button
Last Script Update : 29 Mar 2022
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var C : TComponent;
//btnMoveDown : TSpeedButton;//for SQL Acc Version 5.2022.929.815 and below
btnMoveDown : TcxButton;//for SQL Acc Version 5.2022.930.816 and above
BtnBrowse, btn1, btn2 : TcxButton;
procedure OnClick1(Sender: TObject);
begin
//Action for Button 1
MessageDlg('Buttton 1 Clicked', mtInformation, [mbOk], 0);
end;
procedure OnClick2(Sender: TObject);
begin
//Action for Button 2
MessageDlg('Buttton 2 Clicked', mtInformation, [mbOk], 0);
end;
begin
C := Self.FindComponent('frDataSetButton1');
BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
//btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));//for SQL Acc Version 5.2022.929.815 and below
btnMoveDown := TcxButton(Self.FindComponent('BtnMoveDown'));//for SQL Acc Version 5.2022.930.816 and above
btn1 := TcxButton.Create(btnMoveDown);
btn2 := TcxButton.Create(C);
if Assigned(btnMoveDown) then begin
with btn1 do begin //Create Beside the Up Down Button
Parent := btnMoveDown.Parent;
Top := btnMoveDown.Top;
Left := btnMoveDown.Left + btnMoveDown.Width + 120;
Height := btnMoveDown.Height;
Width := 100;
Caption := '&1. Button';
OnClick := @OnClick1;
ShowHint := True;
Hint := 'Button Click 1';
end;
end;
if Assigned(C) then begin
with btn2 do begin //Create Below the Browse Button
Parent := TwinControl(c);
Top := BtnBrowse.Top + BtnBrowse.Height + 5;
Height := BtnBrowse.Height;
Left := BtnBrowse.Left
Width := BtnBrowse.Width;
Caption := '&2. Button';
OnClick := @OnClick2;
ShowHint := True;
Hint := 'Button Click 2';
end;
end;
end.
|
- 10. Click Save button
Using TxQuery
Below is Example are doing following actions
- Create 1 button at Sales Quotation to Get Total Base Quantity
Last Script Update : 02 Nov 2016
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var btnNew : TcxButton;
btnMoveDown : TSpeedButton;
D : TDataSet;
function GetTotal(const lField:String):Variant;
var xQ : TxQuery;
lSQL : String;
begin
Result := 0.00;
xQ := TxQuery.Create(nil);
try
xQ.Close;
xQ.ClearDatasets;
xQ.AddDataSet(D, 'Main');
lSQL := Format('SELECT Sum(%s) Nos FROM MAIN',[lField]);
with xQ.SQL do begin
Clear;
Add(lSQL);
end;
xQ.open;
Result := xQ.FindField('Nos').AsFloat;
finally
xQ.Free;
end;
end;
procedure OnClick(Sender: TObject);
begin
MessageDlg(GetTotal('SQty'), mtInformation, [mbOk], 0);
end;
begin
D := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
btnNew := TcxButton.Create(btnMoveDown);
if Assigned(btnMoveDown) then begin
with btnNew do begin
Parent := btnMoveDown.Parent;
Top := btnMoveDown.Top;
Height := btnMoveDown.Height;
Left := btnMoveDown.Left + btnMoveDown.Width + 95;
Width := 120;
Caption := 'Calculate';
OnClick := @OnClick;
end;
end;
end.
|
- 10. Click Save button
How to find component in different Event?
Below is Example are doing following actions
- Create 1 button at Sales Quotation
- On Before Save will auto Click the New Created button
Last Script Update : 24 Dec 2016
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
//OnOpen
var btnProfit, btnNew : TcxButton;
procedure OnClick(Sender: TObject);
begin
MessageDlg('Buttton Clicked', mtInformation, [mbOk], 0);
end;
begin
btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
btnNew := TcxButton.Create(self); //Important it must be xx.Create(self)
if Assigned(btnProfit) then begin
with btnNew do begin
Parent := btnProfit.Parent;
Top := btnProfit.Top;
Height := btnProfit.Height;
Name := 'Calc_1'; //Name of the Component
Left := btnProfit.Left + btnProfit.Width + 3;
Width := 80;
Caption := '&1 Calc';
OnClick := @OnClick;
end;
end;
end.
|
- 10. Click Save button
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var btnNew : TcxButton;
begin
btnNew := TcxButton(Self.FindComponent('Calc_1')); //Find the Newly created Component
if Assigned(BtnNew) then
btnNew.Click;
end.
|
- 10. Click Save button
Simple DropDown Selection List in Document Detail
Below is Example are doing following actions
- Create a drop down list at Sales Invoice Detail for UDF_DeliveryType
Last Script Update : 27 Dec 2016
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var cdsDlvType : TClientDataSet;
gcDlvType : TcxGridColumn;
grGrid1 : TcxGrid;
dsDocMaster, dsDlvType : TDataSource;
gvDocDetail, gvDlvType : TcxGridDBTableView;
procedure CreateXMLTable;
begin
with cdsDlvType.FieldDefs do
Add('Code', ftString, 50, False);
cdsDlvType.CreateDataSet;
end;
procedure AddDlvType(const s : String);
begin
with cdsDlvType do begin
Append;
FieldValues['Code'] := s;
Post;
end;
end;
procedure AddRecord;
begin
cdsDlvType := TClientDataSet.Create(self);
cdsDlvType.Name := 'cdsDlvType';
CreateXMLTable;
AddDlvType('By Mail');
AddDlvType('By Air');
AddDlvType('By E-Mail');
AddDlvType('By Phone');
AddDlvType('By Fax');
AddDlvType('By Courier');
end;
procedure CreateTable;
begin
grGrid1 := TcxGrid.Create(nil);
gvDlvType := TcxGridDBTableView.Create(grGrid1);
dsDlvType := TDataSource.Create(gvDocDetail);
AddRecord;
with grGrid1 do begin
Parent := nil;
Visible := False;
end;
with grGrid1.Levels.Add do begin
Caption := 'DlvTypeGrid';
GridView := TcxCustomGridView(gvDlvType);
end;
with gvDlvType.CreateColumn do begin
Caption := 'Code';
DataBinding.FieldName := 'Code';
HeaderAlignmentHorz := taCenter;
Options.IncSearch := False;
end;
// cdsDlvType.IndexFieldNames := 'Code'; //Sort by Code
dsDlvType.DataSet := cdsDlvType;
with gvDlvType do begin
OptionsCustomize.ColumnFiltering := False;
DataController.DataSource := dsDlvType;
OptionsView.Footer := True;
OptionsView.ColumnAutoWidth := True;
OptionsView.GroupByBox := False;
ApplyBestFit(nil, False, False);//Optional
end;
with gvDlvType.DataController.Summary do begin
BeginUpdate;
try
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvDlvType.GetColumnByFieldName('Code');
Position := spFooter;
Kind := skCount;
Format := 'Count = #';
end;
finally
EndUpdate;
end;
end;
end;
begin
gvDocDetail := TcxGridDBTableView(Self.FindComponent('gvDocDetail'));
CreateTable;
gcDlvType:= gvDocDetail.GetColumnByFieldName('UDF_DeliveryType');
SetGridColumnPropertiesClass(gcDlvType,'TcxExtLookupComboBoxProperties');
with TcxExtLookupComboBoxProperties(gcDlvType.Properties) do begin
DropDownWidth := 300;
DropDownListStyle := lsEditFixedList;
ImmediatePost := True;
View := gvDlvType;
KeyFieldNames := 'Code';
ListFieldItem := gvDlvType.GetColumnByFieldName('Code');
end;
end.
|
- 10. Click Save button
Default Company Code & Payment Method In Sales Cash Sales
Below is Example are doing following actions
- On Click New auto Set Default for
- - Customer Code := 300-C0001
- - Payment Method := 320-000
- - Payment Project := ----
Last Script Update : 28 Dec 2016
Level : Basic
Steps - OnAfterNew
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Cash Sales
- 03. Right Click the Sales Cash Sales
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnAfterNew for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnAfterNew Script |
|---|
var M : TDataSource;
C : TComponent;
begin
C := Self.FindComponent('actBarcode');
M := TDataSource(Self.FindComponent('dsDocMaster'));
if Assigned(M) then begin
with M.Dataset do begin
FindField('Code').AsString := '300-C0001';
FindField('P_PAYMENTMETHOD').AsString := '320-000';
FindField('P_PAYMENTPROJECT').AsString := '----';
end;
end;
end.
|
- 10. Click Save button
Get Transfer Information - QT to DO to IV
Below is Example are doing following action
- Quotation Transfer to Delivery Order to Invoice.
- Get the Quotation number, Date & Qty in the Invoice and shown it below the Browse button
Last Script Update : 03 Jun 2017
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var FComServer, lBizObj : Variant;
C : TComponent;
T : TTimer;
M, D : TDataSource;
L1, L2, L3, L4, L5, L6 : TLabel;
cdsTemp : TClientDataset;
function ComServer: Variant;
begin
if FComServer = Null then
FComServer := CreateOleObject('SQLAcc.BizApp');
Result := FComServer;
end;
procedure Setup;
begin
T := TTimer.Create(Self);
L1 := TLabel.Create(self);
L2 := TLabel.Create(self);
L3 := TLabel.Create(self);
L4 := TLabel.Create(self);
L5 := TLabel.Create(self);
L6 := TLabel.Create(self);
end;
procedure GetToDocInfo;
var lSQL, lDtlkey : String;
begin
lDtlKey := D.Dataset.FindField('FromDtlkey').AsString;
FComServer := null;
cdsTemp := TClientDataset.Create(nil);
lSQL := Format('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_QT A '+
'INNER JOIN SL_QTDTL B ON (A.Dockey=B.Dockey) ' +
'WHERE B.Dockey=(SELECT FROMDOCKEY FROM SL_DODTL ' +
'WHERE Dtlkey=%s) '+
'AND B.DtlKey=(SELECT FROMDTLKEY FROM SL_DODTL '+
'WHERE Dtlkey=%s)',[lDtlkey, lDtlkey]);
try
cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
finally
FComServer := null;
end;
end;
procedure OnTimer(Sender: TObject);
var AState : TDataSetState;
begin
AState := M.DataSet.State;
L2.Caption := '';
L4.Caption := '';
L6.Caption := '';
if (AState = dsBrowse) and (Trim(D.Dataset.FindField('FromDtlkey').AsString) <> '') then begin
GetToDocInfo;
try
L2.Caption := cdsTemp.FindField('DocNo').AsString;
if Trim(cdsTemp.FindField('DocDate').AsString) <> '' then
L4.Caption := FormatDateTime('dd/MM/yyyy', cdsTemp.FindField('DocDate').AsDateTime);
L6.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('Qty').AsFloat);
finally
cdsTemp.Free;
end;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
D := TDataSource(Self.FindComponent('dsDocDetail'));
C := Self.FindComponent('frDataSetButton1');
if Assigned(C) then begin
T.Enabled := True;
T.Interval := 1000; // = Update every 1 second
T.OnTimer := @OnTimer;
with L1 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := 200;
Caption := 'QT No';
Font.Color := clBlue;
Font.Style := [fsBold];
end;
with L2 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := 215;
Caption := 'DocNo';
Font.Color := clBlue;
Font.Style := [fsBold];
end;
with L3 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := 240;
Caption := 'QT Date';
Font.Color := clGreen;
Font.Style := [fsBold];
end;
with L4 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := 255;
Caption := 'Date';
Font.Color := clGreen;
Font.Style := [fsBold];
end;
with L5 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := 280;
Caption := 'Orig. Qty';
Font.Color := clRed;
Font.Style := [fsBold];
end;
with L6 do begin
Parent := TWinControl(C);
Width := 66;
Left := 6;
Top := 295;
Caption := 'Qty';
Font.Color := clRed;
Font.Style := [fsBold];
end;
end;
end.
|
- 10. Click Save button
Approval by User if Document Amount Exceed Limit
Below example is to prompt Authorised if the Document Amount Exceed the limit (eg 10000) in Sales Invoice
Last Script Update : 18 Sep 2023
Level : Advance
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var FComServer : Variant;
M : TDataSource;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure Approved(const lUN, lUPW : string);
var lAPW, lSQL : String;
cdsUsers : TClientDataSet;
begin
FComServer := null;
cdsUsers := TClientDataSet.Create(nil);
lSQL := Format('SELECT UDF_Password FROM SY_USER WHERE Code=%s AND UDF_AllowApproved = 1',[QuotedStr(lUN)]);
try
FComServer := null;
cdsUsers.Data := ComServer.DBManager.Execute(lSQL);
lAPW := cdsUsers.FindField('UDF_Password').AsString;
if (lUPW <> lAPW) or (Trim(lAPW) = '') then begin
MessageDlg('Incorrect Password', mtWarning, [mbOK], 0);
Abort;
end;
finally
FComServer := null;
cdsUsers.Free;
end;
end;
procedure InitPWForm;
var fmPassword : TForm;
lbUserName, lbPassword : TcxLabel;
edUserName, edPassword : TEdit;
btnOK : TButton;
begin
fmPassword := TForm.Create(nil);
lbUserName := TcxLabel.Create(fmPassword);
lbPassword := TcxLabel.Create(fmPassword);
edUserName := TEdit.Create(fmPassword);
edPassword := TEdit.Create(fmPassword);
btnOK := TButton.Create(fmPassword);
try
with fmPassword do begin
Parent := nil;
Height := 150;
Width := 280;
Caption := 'Please Enter User Name and Password';
Color := clWhite;
BorderStyle := bsDialog;
Position := poMainFormCenter;
with lbUserName do begin
Parent := fmPassword;
Left := 10;
Top := 10;
Caption := '&User Name :';
end;
with edUserName do begin
Parent := fmPassword;
Left := 100;
Top := 10;
Width := 150;
CharCase := ecUpperCase;
end;
with lbPassword do begin
Parent := fmPassword;
Left := 10;
Top := 35;
Caption := '&Password :';
end;
with edPassword do begin
Parent := fmPassword;
Left := 100;
Top := 35;
Width := 150;
PasswordChar := '*';
end;
with btnOK do begin
Parent := fmPassword;
Top := 70;
Width := 100;
Left := (fmPassword.Width/2) - 50;
Caption := '&OK';
ModalResult := mrOk;
end;
fmPassword.ScaleBy(fmPassword.Monitor.PixelsPerInch, 96);
if fmPassword.ShowModal = mrOK then
Approved(edUserName.Text, edPassword.Text) else
Approved('','');
end;
finally
fmPassword.Free;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
V := M.DataSet.FindField('DocAmt').AsFloat;
if V >= 10000 then
InitPWForm;
end.
|
- 10. Click Save button
Add DB Check Box
Below example is to create DBCheckBox at Header in Sales Order
Last Script Update : 07 Aug 2017
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Order
- 03. Right Click the Sales Order
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var dsDocMaster : TDataSource;
edFreeDlvery : TcxDBCheckBox;
btnMoveDown : TSpeedButton;
begin
dsDocMaster := TDataSource(Self.FindComponent('dsDocMaster'));
btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
edFreeDlvery := TcxDBCheckBox.Create(self);
if Assigned(btnMoveDown) then begin
with edFreeDlvery do begin
Parent := btnMoveDown .Parent;
Top := btnMoveDown .Top;
Left := btnMoveDown.Left + btnMoveDown.Width + 100;
Width := 100;
Name := 'FreeDlvry';
Properties.Caption := ' Free Delivery';
Properties.NullStyle := nssUnchecked;
Properties.ValueChecked := 'T';
Properties.ValueUnChecked := 'F';
DataBinding.DataSource := dsDocMaster;
DataBinding.DataField := 'UDF_FreeDlvry';
end;
end;
end.
|
- 10. Click Save button
Set Default 14 days delivery date from DocDate
Below example is to set Default 14 Days from Document Date in Sales Quotation
Last Script Update : 20 Sep 2017
Level : Basic
Steps - OnGridAfterInsert
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnGridAfterInsert for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnGridAferInsert Script |
|---|
var M : TDataSet;
begin
M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
Dataset.FindField('DeliveryDate').Value := M.FindField('DocDate').Value +14;
end;
|
- 10. Click Save button
Add TcxDBTextEdit
Below example is to Create new TcxDBTextEdit for UDF_Wastage in Maintain Item
Last Script Update : 21 Dec 2017
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Stock
- 03. Right Click the Stock Item
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
Var edWast, edRmk : TcxDBTextEdit;
pnlDock : TWinControl;
dsAccess : TDataSource;
lblWast : TcxLabel;
begin
edRmk := TcxDBTextEdit(Self.FindComponent('edBarcode'));
dsAccess := TDataSource(Self.FindComponent('dsAccess'));
edWast := TcxDBTextEdit.Create(Self);
lblWast := TcxLabel.Create(Self);
with lblWast do begin
Parent := edRmk.Parent;
Left := edRmk.Left - 92;
Top := edRmk.Top + edRmk.Height + 4;
Width := 38;
Height := 17;
Caption := 'Wastage :';
end;
with edWast do begin
Parent := edRmk.Parent;
Name := 'edWast';
Left := edRmk.Left;
Top := edRmk.Top + edRmk.Height + 2;
Width := 300;
Height := edRmk.Height;
TabOrder := 20;
DataBinding.DataSource := dsAccess;
DataBinding.DataField := 'UDF_Wastage';
Properties.Alignment.Horz := taRightJustify; //Align to Right
end;
end.
|
- 10. Click Save button
Call Report Object In Script
Below example is to Create new Button to calculate Margin for each Invoice & update to Header UDF_Margin field
Last Script Update : 23 Nov 2017
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var C : TComponent;
B, btnOK, BtnBrowse : TcxButton;
fmParameter : TForm;
lIVList : TStringList;
lWorkingDate : TDateTime;
lDF , lDT : TcxDateEdit;
P : TDataProcessorProxy;
D : TDataSet;
FComServer, lBizObj, lDocKey, lDocNo, lMainDataSet, V: Variant;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
if not VarIsNull(V) then
Result := V
else
Result := ADefault;
end;
function VarToStr(const V: Variant): string;
begin
Result := VarToStrDef(V, '');
end;
function FormatJSonDate(D: TDateTime): String;
var AFormat: string;
begin
AFormat := 'yyyy-mm-dd';
Result := '"' + FormatDateTime(AFormat, D) + '"';
end;
procedure SpoolParameter;
begin
P := TDataProcessorProxy.Create('Sales.ProfitLoss.DP');
P.AsJSONParam := '{' +
' "SelectIV" : true ' + // Boolean (true / false)
', "SelectDO" : false ' +
', "SelectDN" : false ' +
', "SelectCS" : false ' +
', "SelectCN" : false ' +
', "Agent" : [] ' + // List / Array - this mean select ALL
', "Company" : [] ' +
', "Location" : [] ' + // List / Array - this mean select location ---- & Balakong
', "CompanyCategory" : [] ' +
', "HasCategory" : false ' +
', "WithSalesTax" : false ' +
', "StockGroup" : [] ' +
', "DateFrom" : ' + FormatJSonDate(lDF.Date) + // Date format is yyyy-mm-dd
', "DateTo" : ' + FormatJSonDate(lDT.Date) +
', "Doc_IV" : [] ' +
', "Doc_DO" : [] ' +
', "Doc_DN" : [] ' +
', "Doc_CS" : [] ' +
', "Doc_CN" : [] ' +
', "Category" : [] ' +
', "ItemProject" : [] ' +
', "DocProject" : [] ' +
', "SelectDate" : true ' +
', "CategoryTpl" : "" ' + //String
', "Item" : [] ' +
', "Area" : [] ' +
', "UseRefCost" : false ' +
', "Batch" : [] ' +
'}';
P.Execute;
end;
procedure GetIVList;
begin
lIVList := TStringList.Create;
lIVList.Duplicates := dupIgnore;
lIVList.Sorted := True;
D.First;
while not D.Eof do begin
lIVList.Append(D.FindField('DocNo').AsString);
D.Next;
end;
end;
procedure OnClickApply(Sender: TObject);
var i, j : integer;
begin
SpoolParameter;
D := P.GetDataSetByName('Main');
GetIVList;
FComServer := null;
lBizObj := null;
lBizObj := ComServer.BizObjects.Find('SL_IV');
lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
i := 0;
for j := 0 to lIVList.Count -1 do begin
i := i + 1;
V := Format('%d of %d',[i, lIVList.Count]);
btnOK.Caption := V;
lDocNo := lIVList[j];
lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
with D do begin
Filter := 'DocNo=' + QuotedStr(lIVList[j]);
Filtered := True;
end;
if not VarIsNull(lDocKey) then begin
lBizObj.Open;
lBizObj.Edit;
lMainDataSet.FindField('UDF_Margin').AsFloat := D.FindField('DocMargin').AsFloat;
lBizObj.Save;
D.Filtered := False;
end;
end;
lIVList.Free;
if Assigned(P) then
P.Free;
FComServer := null;
lBizObj := null;
btnOK.Caption := '&Apply';
MessageDlg('Record(s) successfully Updated', mtInformation, [mbOk], 0);
fmParameter.Close;
end;
procedure InitForm;
var lbDF, lbDT : TcxLabel;
begin
lWorkingDate := CurrentWorkingDate;
fmParameter := TForm.Create(nil);
lDF := TcxDateEdit.Create(fmParameter);
lDT := TcxDateEdit.Create(fmParameter);
btnOK := TcxButton.Create(fmParameter);
lbDF := TcxLabel.Create(fmParameter);
lbDT := TcxLabel.Create(fmParameter);
try
with fmParameter do begin
Parent := nil;
Height := 100;
Width := 300;
Caption := 'Date range..';
Color := clWhite;
BorderStyle := bsDialog;
Position := poMainFormCenter;
with lbDF do begin
Parent := fmParameter;
Left := 5;
Top := 10;
Caption := 'Date :'
end;
with lDF do begin
Parent := fmParameter;
Top := 10;
Left := 40;
Width := 100;
Date := StartOfAMonth(YearOf(lWorkingDate), MonthOf(lWorkingDate));
end;
with lbDT do begin
Parent := fmParameter;
Left := 145;
Top := 10;
Caption := ' To '
end;
with lDT do begin
Parent := fmParameter;
Top := 10;
Left := 180;
Width := 100;
Date := Trunc(EndOfAMonth(YearOf(lWorkingDate), MonthOf(lWorkingDate)));
end;
with btnOK do begin
Parent := fmParameter;
Top := 35;
Width := 100;
Left := (fmParameter.Width/2) - 50;
Caption := '&Apply';
OnClick := @OnClickApply;
end;
end;
fmParameter.ScaleBy(fmParameter.Monitor.PixelsPerInch, 96);
fmParameter.ShowModal;
finally
fmParameter.Free;
end;
end;
procedure OnClickForm(Sender: TObject);
begin
InitForm;
end;
begin
C := Self.FindComponent('frDataSetButton1');
BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
if Assigned(C) then
B := TcxButton.Create(Self);
B.Parent := TwinControl(c);
B.width := BtnBrowse.Width;
B.Top := BtnBrowse.Top + BtnBrowse.Height + 5;
B.Left := BtnBrowse.Left;
B.Height := BtnBrowse.Height;
B.Caption := 'Calc Margin';
B.OnClick := @OnClickForm;
end.
|
- 10. Click Save button
Set Discount to Empty for next line
Below is Example will automatic remove the Discount field value on insert next item line.
Last Script Update : 27 Nov 2017
Level : Basic
Steps - OnGridAfterInsert
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnGridAfterInsert for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnGridAfterInsert Script |
|---|
begin
Dataset.FindField('Disc').AsString := '';
end;
|
- 10. Click Save button
Prompt Dialog for Discount
Below is Example are doing following actions
- Auto Append ItemCode DISC
- Do Calculation for ItemCode DISC UnitPrice := Total Doc Amount * Rate Enter
Last Script Update : 29 Nov 2017
Level : Basic
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var M, D : TDataSource;
function GetDiscountedValue(AValue: Variant; Discount: string): Variant;
var
lStrLst: TStringList;
S: string;
I: Integer;
begin
Result := Null;
lStrLst := TStringList.Create;
try
try
lStrLst.Delimiter := '&';
lStrLst.DelimitedText := Trim(StringReplace(Trim(Discount), '+', '&', [rfReplaceAll]));
for I := 0 to lStrLst.Count - 1 do
begin
S := Trim(lStrLst.Strings[I]);
if S[Length(S)] = '%' then
begin
S := Trim(Copy(S, 1, Length(S) - 1));
AValue := AValue * ((StrToFloat(S)) / 100);
end
else
AValue := StrToFloat(S);
end;
Result := AValue*-1;
except
RaiseException(ExceptionType, 'Discount formatting error ');
end;
finally
lStrLst.Free;
end;
end;
procedure CalcDisc(const lRate:String);
var lDocAmt : Variant;
begin
lDocAmt := M.DataSet.FindField('DocAmt').AsFloat;
with D.DataSet do begin
Edit;
Append;
FindField('ItemCode').AsString := 'DISC';
FindField('Description').AsString := FindField('Description').AsString + ' -' + lRate+'%';
FindField('Qty').AsFloat := 1;
FindField('UnitPrice').AsFloat := GetDiscountedValue(lDocAmt, lRate+'%');
Post;
end;
end;
procedure InitPWForm;
var fmPassword : TForm;
lbRate : TcxLabel;
edRate : TcxTextEdit;
btnOK : TButton;
begin
fmPassword := TForm.Create(nil);
lbRate := TcxLabel.Create(fmPassword);
edRate := TcxTextEdit.Create(fmPassword);
btnOK := TButton.Create(fmPassword);
try
with fmPassword do begin
Parent := nil;
Height := 100;
Width := 280;
Caption := 'Enter the Discount Rate (%)';
Color := clWhite;
BorderStyle := bsDialog;
Position := poMainFormCenter;
with lbRate do begin
Parent := fmPassword;
Left := 10;
Top := 10;
Caption := '&Rate :';
end;
with edRate do begin
Parent := fmPassword;
Left := 100;
Top := 10;
Width := 150;
end;
with btnOK do begin
Parent := fmPassword;
Top := 38;
Width := 100;
Left := (fmPassword.Width/2) - 50;
Caption := '&OK';
ModalResult := mrOk;
end;
fmPassword.ScaleBy(fmPassword.Monitor.PixelsPerInch, 96);
if fmPassword.ShowModal = mrOK then
CalcDisc(edRate.Text);
end;
finally
fmPassword.Free;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
D := TDataSource(Self.FindComponent('dsDocDetail'));
InitPWForm;
end.
|
- 10. Click Save button
Create New Grid Tab on Existing Grid
Below is Example are create new Tab in Existing Grid in Customer Due Listing
Last Script Update : 03 Jan 2018
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Customer Due Document
- 03. Right Click the Customer Due Document
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var btnCalc, btnApply : TcxButton;
M, dsGrid : TDatasource;
FcxGrid : TcxGrid;
gvGrid : TcxGridDBTableView;
cdsMain : TClientDataSet;
lDisplayFormat : String;
procedure Setup;
begin
dsGrid := TDataSource.Create(nil);
cdsMain := TClientDataSet.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
if Assigned(lComponent) then
lComponent.Free;
end;
procedure TearDown;
begin
FreeCmpt(dsGrid);
FreeCmpt(cdsMain);
end;
procedure SetNumericDisplayFormat(AClientDataSet: TClientDataSet);
var f : TFMTBCDField;
f1 : TFloatField;
i : Integer;
begin
for i := 0 to AClientDataSet.FieldCount - 1 do begin
if AClientDataSet.FieldDefs.Items[i].DataType = ftFMTBcd then begin
f := TFMTBCDField(AClientDataSet.Fields.Fields[i]);
if not (f = nil) then f.DisplayFormat := lDisplayFormat;
end;
if AClientDataSet.FieldDefs.Items[i].DataType = ftFloat then begin
f1 := TFloatField(AClientDataSet.Fields.Fields[i]);
if not (f1 = nil) then f1.DisplayFormat := lDisplayFormat;
end;
end;
end;
procedure PrepareNewTab;
var i : integer;
begin
FcxGrid.RootLevelOptions.DetailTabsPosition := dtpTop;
if FcxGrid.Levels.Count > 1 then begin
for i := FcxGrid.Levels.Count - 1 downto 1 do begin
FcxGrid.Levels[i].GridView.Free;
FcxGrid.Levels[i].Free;
end;
Self.Refresh;
end;
gvGrid := CreateTcxGridDBTableView(FcxGrid);
with FcxGrid.Levels.Add do begin
Caption := 'Calc';
GridView := TcxCustomGridView(gvGrid);
Active := True;
end;
with gvGrid do begin
Name := 'Restricted_1';
OptionsCustomize.ColumnFiltering := False;
OptionsView.ColumnAutoWidth := True;
OptionsView.Footer := True;
OptionsView.GroupByBox := False;
OptionsBehavior.IncSearch := True;
DataController.DataSource := dsGrid;
OptionsData.Editing := False;
end;
end;
procedure CreateXMLTable;
begin
FreeCmpt(cdsMain);
cdsMain := TClientDataSet.Create(nil);
cdsMain.FieldDefs.Assign(M.DataSet.FieldDefs);
cdsMain.CreateDataSet;
cdsMain.IndexFieldNames := 'Code';// Sort By Code
dsGrid.DataSet := cdsMain;
gvGrid.DataController.DataSource := dsGrid;
gvGrid.DataController.CreateAllItems(False);// Create cxGridColumn
end;
procedure TuneGridColumn;
var B : Boolean;
begin
B := False;
with gvGrid do begin
//Hide Columns
//for i := 0 to ColumnCount -1 do
// Columns[i].Visible := False; // To 1 shot hide all fields
GetColumnByFieldName('Dockey').Visible := B;
GetColumnByFieldName('DocNoEx').Visible := B;
GetColumnByFieldName('PostDate').Visible := B;
GetColumnByFieldName('Agent').Visible := B;
GetColumnByFieldName('Journal').Visible := B;
GetColumnByFieldName('CurrencyCode').Visible := B;
GetColumnByFieldName('CurrencyRate').Visible := B;
GetColumnByFieldName('LocalDocAmt').Visible := B;
GetColumnByFieldName('Cancelled').Visible := B;
GetColumnByFieldName('Project').Visible := B;
GetColumnByFieldName('Area').Visible := B;
GetColumnByFieldName('DocType').Visible := B;
GetColumnByFieldName('PaymentAmt').Visible := B;
GetColumnByFieldName('LocalPaymentAmt').Visible := B;
GetColumnByFieldName('CNAmt').Visible := B;
GetColumnByFieldName('LocalCNAmt').Visible := B;
GetColumnByFieldName('CTAmt').Visible := B;
GetColumnByFieldName('LocalCTAmt').Visible := B;
GetColumnByFieldName('GainLoss').Visible := B;
GetColumnByFieldName('CompanyCategory').Visible := B;
GetColumnByFieldName('Description').Visible := B;
GetColumnByFieldName('Code').Visible := B;
//Reposition Columns
GetColumnByFieldName('DocNo').Index := 0;
GetColumnByFieldName('DocDate').Index := 1;
GetColumnByFieldName('DueDate').Index := 2;
GetColumnByFieldName('CompanyName').Index := 3;
GetColumnByFieldName('Terms').Index := 4;
GetColumnByFieldName('DocAmt').Index := 5;
GetColumnByFieldName('Outstanding').Index := 6;
GetColumnByFieldName('LocalOutstanding').Index := 7;
GetColumnByFieldName('Age').Index := 8;
//Rename Columns
GetColumnByFieldName('DocNo').Caption := 'Doc No';
GetColumnByFieldName('DocDate').Caption := 'Doc Date';
GetColumnByFieldName('DueDate').Caption := 'Due Date';
GetColumnByFieldName('CompanyName').Caption := 'Company Name';
end;
with gvGrid.DataController.Summary do begin
BeginUpdate;
try
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvGrid.GetColumnByFieldName('DocNo');
Position := spFooter;
Kind := skCount;
Format := 'Count = #';
end;
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvGrid.GetColumnByFieldName('DocAmt');
Position := spFooter;
Kind := skSum;
Format := lDisplayFormat;
end;
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvGrid.GetColumnByFieldName('Outstanding');
Position := spFooter;
Kind := skSum;
Format := lDisplayFormat;
end;
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvGrid.GetColumnByFieldName('LocalOutstanding');
Position := spFooter;
Kind := skSum;
Format := lDisplayFormat;
end;
finally
EndUpdate;
end;
end;
end;
procedure PrepareData;
var i : integer;
begin
M.Dataset.DisableControls;
cdsMain.DisableControls;
try
M.Dataset.First;
while not M.Dataset.EOF do begin
cdsMain.Append;
for i:=0 to M.Dataset.FieldDefs.Count-1 do
cdsMain.FindField(M.Dataset.FieldDefs.Items[i].Name).Value := M.Dataset.FindField(M.Dataset.FieldDefs.Items[i].Name).Value;
cdsMain.Post;
M.Dataset.Next;
end;
finally
SetNumericDisplayFormat(cdsMain); //Set DisplayFormat
M.Dataset.EnableControls;
cdsMain.EnableControls;
cdsMain.First;
gvGrid.ApplyBestFit(nil, False, False);
end;
end;
procedure OnGenClick(Sender: TObject);
var s : string;
begin
s := self.Caption;
lDisplayFormat := '#,0.00;-#,0.00;-';
try
self.Caption := 'Get Raw Information...';
btnApply.Click;
self.Caption := 'Prepare Tab...';
PrepareNewTab;
self.Caption := 'Prepare XML Table...';
CreateXMLTable;
self.Caption := 'Grid Tuning...';
TuneGridColumn;
self.Caption := 'Preparing Data...';
PrepareData;
finally
self.Caption := s;
end;
end;
begin
btnApply := Tcxbutton(Self.FindComponent('btnApply'));
FcxGrid := TcxGrid(Self.FindComponent('FcxGrid'));
M := TDatasource(Self.FindComponent('dsMain'));
btnCalc := TcxButton.Create(Self);
with btnCalc do begin
Parent := btnApply.Parent;
Top := btnApply.Top;
Left := btnApply.Left + btnApply.Width + 5;
Width := btnApply.Width + 2;
Caption := '&1. Generate';
ShowHint := True;
Hint := 'Generate Overdue List';
OnClick := @OnGenClick;
end;
end.
|
- 10. Click Save button
How to Detect what is the Current Doc Type in Document Listing?
Last Script Update : 25 Feb 2022
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Document Listing
- 03. Right Click the Sales Document Listing
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var edDocumentType : TcxComboBox;
procedure edDocumentTypeChange(Sender: TObject);
var s : string;
begin
s := 'Now selected Doc type is ' + edDocumentType.EditValue;
MessageDlg(S, mtInformation, [mbOk], 0);
end;
begin
edDocumentType := Self.FindComponent('edDocumentType') as TcxComboBox;
edDocumentType.Properties.OnEditValueChanged := @edDocumentTypeChange;
end.
|
- 10. Click Save button
Simple Copy From Dataset to New Doc
Below is Example to Copy Quotation Information to Sales Invoice
Last Script Update : 08 Sep 2018
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Quotation
- 03. Right Click the Sales Quotation
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var FComServer, lBizObj, lMainDataSet, lDocDetail : Variant;
btnMoveDown : TSpeedButton;
btnCopy : TcxButton;
M, D : TDataSet;
cdsMaster, cdsDetail : TClientDataSet;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure Setup;
begin
cdsMaster := TClientDataSet.Create(nil);
cdsDetail := TClientDataSet.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
if Assigned(lComponent) then
lComponent.Free;
end;
procedure TearDown;
begin
FreeCmpt(cdsMaster);
FreeCmpt(cdsDetail);
end;
procedure CreateXMLTable;
begin
with cdsMaster.FieldDefs do
begin
Add('FName', ftString, 100, False);
end;
with cdsDetail.FieldDefs do
begin
Add('FName', ftString, 100, False);
end;
cdsMaster.CreateDataSet;
cdsDetail.CreateDataSet;
end;
procedure AddField(const lTbl, lFld:String);
begin
if lTbl = 'M' then
with cdsMaster do begin
Append;
FieldValues['FName'] := lFld
Post;
end else
with cdsDetail do begin
Append;
FieldValues['FName'] := lFld;
Post;
end;
end;
procedure PostToAcc;
var lFName : string;
begin
lBizObj.New;
lMainDataSet.FindField('DOCKEY').Value := -1;
cdsMaster.First;
while not cdsMaster.Eof do begin
lFName := cdsMaster.FieldValues['FName'];
lMainDataSet.FindField(lFName).Value := M.FindField(lFName).Value;
cdsMaster.Next;
end;
lMainDataSet.Post;
D.First;
while not D.Eof do begin
lDocDetail.Append;
lDocDetail.FindField('DtlKey').Value := -1;
lDocDetail.FindField('DocKey').Value := -1;
cdsDetail.First;
while not cdsDetail.Eof do begin
lFName := cdsDetail.FieldValues['FName'];
lDocDetail.FindField(lFName).Value := D.FindField(lFName).Value;
cdsDetail.Next;
end;
lDocDetail.Post;
D.Next;
end;
lBizObj.Save;
lBizObj.Close;
end;
procedure OnClick1(Sender: TObject);
begin
FComServer := null;
lBizObj := null;
lBizObj := ComServer.BizObjects.Find('SL_IV');
lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
lDocDetail := lBizObj.DataSets.Find('cdsDocDetail');
try
PostToAcc;
MessageDlg('Posting Done', mtInformation, [mbOk], 0);
finally
lMainDataSet := null;
lDocDetail := null;
lBizObj := null;
FComServer := null;
end;
end;
begin
btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
D := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
btnCopy := TcxButton.Create(btnMoveDown);
if Assigned(btnMoveDown) then begin
with btnCopy do begin //Create Beside the Up Down Button
Parent := btnMoveDown.Parent;
Top := btnMoveDown.Top;
Left := btnMoveDown.Left + btnMoveDown.Width + 120;
Height := btnMoveDown.Height;
Width := 100;
Caption := '&1. Copy To IV';
OnClick := @OnClick1;
ShowHint := True;
Hint := 'Copy To IV';
end;
end;
CreateXMLTable;
//Add Field Master Table
AddField('M', 'Code');
AddField('M', 'DocNoEx');
//AddField('M', 'CompanyName');
//AddField('M', 'Address1');
//AddField('M', 'Address2');
//AddField('M', 'Address3');
//AddField('M', 'Address4');
//AddField('M', 'Phone1');
//AddField('M', 'Fax1');
//AddField('M', 'Attention');
//AddField('M', 'Area');
//AddField('M', 'Agent');
AddField('M', 'Project');
//AddField('M', 'Terms');
//AddField('M', 'CurrencyRate');
AddField('M', 'Description');
//AddField('M', 'D_Amount');
AddField('M', 'Validity');
AddField('M', 'DeliveryTerm');
AddField('M', 'CC');
AddField('M', 'DocRef1');
AddField('M', 'DocRef2');
AddField('M', 'DocRef3');
AddField('M', 'DocRef4');
AddField('M', 'BranchName');
//AddField('M', 'DAddress1');
//AddField('M', 'DAddress2');
//AddField('M', 'DAddress3');
//AddField('M', 'DAddress4');
//AddField('M', 'DPhone1');
//AddField('M', 'DFax1');
//Add Field Detail Table
AddField('D', 'Number');
AddField('D', 'ItemCode');
AddField('D', 'Description');
AddField('D', 'Description2');
AddField('D', 'Description3');
AddField('D', 'Remark1');
AddField('D', 'Remark2');
AddField('D', 'Location');
AddField('D', 'Project');
AddField('D', 'Qty');
AddField('D', 'UOM');
AddField('D', 'UnitPrice');
AddField('D', 'Disc');
AddField('D', 'Tax');
end.
|
- 10. Click Save button
Create New Column on Existing Grid
Below is Example to Add UDF_Margin in the Sales Document Listing
This only work if in Report Designer in Main Pipeline/Table had the field(s)
Last Script Update : 19 Feb 2019
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Document Listing
- 03. Right Click the Sales Document Listing
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var C: TcxGridDBTableView;
begin
C := TcxGridDBTableView(Self.FindComponent('gvMaster'));
if C.GetColumnByFieldName('UDF_Margin') = nil then begin
with C.CreateColumn do begin
DataBinding.FieldName := 'UDF_Margin';
Caption := 'Margin';
end;
end;
end.
|
- 10. Click Save button
Auto Click Apply button and Change the report Grid Data
Below Example is to Change Header & Detail Description field in Sales Document Listing
Last Script Update : 21 Mar 2019
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Document Listing
- 03. Right Click the Sales Document Listing
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var btnApply, btnCalc : Tcxbutton;
M, D : TDataset;
procedure OnGenerate(Sender: TObject);
var lMDesc, lDDesc, lDocNo : String;
begin
btnApply.Click; //Auto Click Apply button
M := TDataSource(Self.FindComponent('dsMasterTrans')).Dataset;
D := TDataSource(Self.FindComponent('dsDetail')).Dataset;
M.DisableControls;
D.DisableControls;
try
M.First;
while not M.Eof do begin
lDocNo := M.FindField('DocNo').AsString;
lMDesc := M.FindField('Description').AsString +
'- Hello Change Master Description';
M.Edit;
M.FindField('Description').AsString := lMDesc;
M.Post;
M.Next;
end;
D.First;
while not D.Eof do begin
lDDesc := D.FindField('Description').AsString +
'- Hello Change Detail Description';
D.Edit;
D.FindField('Description').AsString := lDDesc;
D.Post;
D.Next;
end;
MessageDlg('Done', mtInformation, [mbOk], 0);
finally
M.EnableControls;
D.EnableControls;
end;
end;
begin
btnApply := Tcxbutton(Self.FindComponent('btnApply'));
btnCalc := TcxButton.Create(btnApply);
with btnCalc do begin
Parent := btnApply.Parent;
Top := btnApply.Top;
Left := btnApply.Left + btnApply.Width + 2;
Width := 100;
Caption := '&1. Generate';
OnClick := @OnGenerate;
end;
end.
|
- 10. Click Save button
Update DocNo To other Document
Below Example is to Update UDF_ORNo in Sales Invoice for Knock-off Payment from Customer Payment
Last Script Update : 03 Jul 2019
Level : Advance
Steps - OnAfterSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Customer Payment
- 03. Right Click the Customer Payment
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnAfterSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnAfterSave Script |
|---|
var M, D : TDataSet;
FComServer, lBizObj : Variant;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
if not VarIsNull(V) then
Result := V
else
Result := ADefault;
end;
function VarToStr(const V: Variant): string;
begin
Result := VarToStrDef(V, '');
end;
procedure UpdateIV(const lDocNo:String);
var lMainDataSet, lDocKey : Variant;
begin
FComServer := null;
lBizObj := null;
try
lBizObj := ComServer.BizObjects.Find('SL_IV');
lMainDataSet := lBizObj.DataSets.Find('MainDataSet');
lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
if not VarIsNull(lDocKey) then begin
lBizObj.Open;
lBizObj.Edit;
lMainDataSet.FindField('UDF_ORNo').Value := M.FindField('DocNo').Value;
lBizObj.Save;
lBizObj.Close;
end;
finally
lBizObj := null;
FComServer := null;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
D := TDataSource(Self.FindComponent('dsaKnockOff')).Dataset;
D.First;
while not D.Eof do begin
if (D.FindField('Outstanding').AsFloat = 0) and
(D.FindField('KnockOff').AsString = 'T') then
UpdateIV(D.FindField('DocNo').AsString);
D.Next;
end;
end.
|
- 10. Click Save button
How to Reset the Serial Number When update Location Field?
Below Example is to Set Back the Serial Number List when Location change/Updated
- Applicable for Sales Module Only as Purchase will not reset when location change
Last Script Update : 19 Oct 2020
Level : Advance
Steps - OnBeforeSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnBeforeSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnBeforeSave Script |
|---|
var M, D, SN : TDataSet;
lSN : TStringList;
procedure GetSN;
begin
SN.First;
while not SN.Eof do begin
lSN.Append(SN.FindField('SerialNumber').AsString);
SN.Next;
end;
end;
procedure SetSN;
var i : integer;
begin
for i := 0 to lSN.Count -1 do begin
SN.Append;
SN.FindField('SerialNumber').AsString := lSN[i];
SN.Post;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet;
D := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
SN := TDataSource(Self.FindComponent('dsSerialNumber')).Dataset;
lSN := TStringList.Create;
D.First;
try
while not D.EOF do begin
lSN.Clear;
if D.FindField('Location').AsString <> 'KL' then begin
GetSN;
D.Edit;
D.FindField('Location').AsString := 'KL'; //Location Field is change/Updated
SetSN;
end;
D.Next;
end;
finally
lSN.Free;
end;
end.
|
- 10. Click Save button
How to find component in Advance Form?
Below Example is to change the label in Advance Form
Last Script Update : 11 Nov 2021
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
// Create Advance Form & Rename it to fmCalc (under Object Inspector)
// Drop 1 label & Rename it to lbBankCode (under Object Inspector)
// Load it OnOpen script
var C : TWinControl;
edAccNo : TLabel;
i : integer;
begin
for i := 0 to Self.ComponentCount - 1 do begin
if Pos('fmCalc', Self.Components[i].Name) > 0 then begin
C := Self.Components[i] as TWinControl;
edAccNo := TLabel(C.FindComponent('lbBankCode'));
Break;
end;
end;
if Assigned(edAccNo) then
edAccNo.Caption := 'Fairy';
end.
|
- 10. Click Save button
DropDown List from Maintenance Agent List
Below is Example are doing following actions
- Create Label
- Create a drop down list at Sales Invoice for UDF_Agent2
Last Script Update : 07 Apr 2020
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var FComServer : Variant;
M : TDataSource;
lbAgent : TLabel;
cdsAgent : TClientDataSet;
grGrid1 : TcxGrid;
dsAgent : TDataSource;
gvDocDetail, gvAgent : TcxGridDBTableView;
btnProfit : TcxButton;
cxAgent : TcxDBExtLookupComboBox;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure Setup;
begin
cdsAgent := TClientDataSet.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
if Assigned(lComponent) then
lComponent.Free;
end;
procedure TearDown;
begin
FreeCmpt(cdsAgent);
end;
procedure PrepareXML;
var lSQL : String;
begin
FComServer := null;
try
lSQL := 'SELECT Code, Description FROM Agent ' +
'WHERE ISActive=''T'' ';
cdsAgent.Data := ComServer.DBManager.Execute(lSQL);
finally
FComServer := null;
end;
end;
procedure CreateTable;
begin
grGrid1 := TcxGrid.Create(nil);
gvAgent := TcxGridDBTableView.Create(grGrid1);
dsAgent := TDataSource.Create(gvDocDetail);
with grGrid1 do begin
Parent := nil;
Visible := False;
end;
with grGrid1.Levels.Add do begin
Caption := 'AgentView';
GridView := TcxCustomGridView(gvAgent);
end;
dsAgent.DataSet := cdsAgent;
with gvAgent.CreateColumn do begin
Caption := 'Code';
DataBinding.FieldName := 'Code';
HeaderAlignmentHorz := taCenter;
Width := 50;
Options.Editing := False;
end;
with gvAgent.CreateColumn do begin
Caption := 'Description';
DataBinding.FieldName := 'Description';
HeaderAlignmentHorz := taCenter;
Width := 100;
Options.Editing := False;
end;
with gvAgent do begin
Name := 'Restricted_1';
OptionsCustomize.ColumnFiltering := False;
DataController.DataSource := dsAgent;
OptionsView.Footer := True;
OptionsView.ColumnAutoWidth := True;
OptionsView.GroupByBox := False;
OptionsData.Editing := False;
end;
with gvAgent.DataController.Summary do begin
BeginUpdate;
try
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gvAgent.GetColumnByFieldName('Description');
Position := spFooter;
Kind := skCount;
Format := 'Count = #';
end;
finally
EndUpdate;
end;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
btnProfit := TcxButton(Self.FindComponent('BtnProfitEstimator'));
gvDocDetail := TcxGridDBTableView(Self.FindComponent('gvDocDetail'));
cxAgent := TcxDBExtLookupComboBox.Create(btnProfit);
PrepareXML;
CreateTable;
if Assigned(btnProfit) then begin
lbAgent := TLabel.Create(btnProfit);
with lbAgent do begin
Parent := btnProfit.Parent;
Name := 'lbAgent2';
Width := 100;
Left := btnProfit.Left + btnProfit.Width +6;
Top := btnProfit.Top;
Caption := 'Agent 2';
end;
with cxAgent do begin
Parent := btnProfit.Parent;
Top := btnProfit.Top;
Left := btnProfit.Left + btnProfit.Width + lbAgent.Width+12;
Height := btnProfit.Height;
Width := 200;
DataBinding.DataSource := M;
DataBinding.DataField := 'UDF_Agent2';
Properties.View := gvAgent;
Properties.KeyFieldNames := 'Code';
Properties.ListFieldItem := gvAgent.GetColumnByFieldName('Description');
Properties.DropDownListStyle := lsFixedList;
Properties.ImmediatePost := True;
Properties.DropDownWidth := 400;
EditValue := 0;
end;
end;
end.
|
- 10. Click Save button
Disable Terms Field
Below is Example is disable the Terms Field in Sales Invoice
Last Script Update : 07 Apr 2020
Level : Basic
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var edTerms : TcxDBExtLookupComboBox;
begin
edTerms := Self.FindComponent('edTerms') as TcxDBExtLookupComboBox;
if Assigned(edTerms) then
edTerms.Enabled := False;
end.
|
- 10. Click Save button
Export CSV/TXT Data
Below is Example are doing following actions
- Export Data to CSV/TXT from Invoice
Last Script Update : 14 May 2020
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales Invoice
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var lSL : TStringList;
btnMoveDown : TSpeedButton;
btn1 : TcxButton;
M, D : TDataSet;
procedure GetData;
var s : string;
begin
D.First;
while not D.Eof do begin
s := M.FindField('DocNo').AsString + '|' +
M.FindField('Code').AsString + '|' +
D.FindField('ItemCode').AsString + '|' +
D.FindField('Description').AsString + '|' +
D.FindField('Qty').AsString + '|' +
D.FindField('UnitPrice').AsString + '|' +
D.FindField('Tax').AsString + '|' +
D.FindField('TaxAmt').AsString + '|' +
D.FindField('Disc').AsString + '|' +
D.FindField('Amount').AsString;
lSL.Append(s);
D.Next;
end;
end;
procedure OnClick1(Sender: TObject);
var lFileName, lFilter : string;
begin
lFilter := 'Texts Files (*.txt, *.csv)|*.txt;*.csv|All Files (*.*)|*.*';
lFileName := 'SL_IV_' + FormatDateTime('YYYYMMDD', M.FindField('DocDate').Value);
lSL := TStringList.Create;
try
if PromptForFileName(lFileName, lFilter, 'txt', 'Save As...', '.', True) then begin
GetData;
lSL.SaveToFile(lFileName);
MessageDlg('Export Done', mtInformation, [mbOk], 0);
end;
finally
lSL.Free;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
D := TDataSource(Self.FindComponent('dsDocDetail')).Dataset;
btnMoveDown := TSpeedButton(Self.FindComponent('BtnMoveDown'));
btn1 := TcxButton.Create(btnMoveDown);
if Assigned(btnMoveDown) then begin
with btn1 do begin
Parent := btnMoveDown.Parent;
Top := btnMoveDown.Top;
Left := btnMoveDown.Left + btnMoveDown.Width + 120;
Height := btnMoveDown.Height;
Width := 100;
Caption := '&1. Button';
OnClick := @OnClick1;
ShowHint := True;
Hint := 'Button Click 1';
end;
end;
end.
|
- 10. Click Save button
Run External Application
Below is Example are doing following actions
- Auto Run SQL Payroll
Last Script Update : 19 Jun 2020
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Agent
- 03. Right Click the Agent
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var FWshShell : Variant;
C : TComponent;
BtnBrowse, btn2 : TcxButton;
function ComWshShell: Variant;
begin
if FWshShell = Null then begin
FWshShell := CreateOleObject('WScript.Shell');
end;
Result := FWshShell;
end;
procedure OnClick2(Sender: TObject);
begin
FWshShell := Null;
try
ComWshShell.Exec('C:\estream\SQL Payroll\bin\SQLPay.exe');
finally
FWshShell := Null;
end;
end;
begin
C := Self.FindComponent('frDataSetButton1');
BtnBrowse := TcxButton(C.FindComponent('btnViewDetail'));
btn2 := TcxButton.Create(C);
if Assigned(C) then begin
with btn2 do begin //Create Below the Browse Button
Parent := TwinControl(c);
Top := BtnBrowse.Top + BtnBrowse.Height + 5;
Height := BtnBrowse.Height;
Left := BtnBrowse.Left
Width := BtnBrowse.Width;
Caption := '&2. Payroll';
OnClick := @OnClick2;
ShowHint := True;
Hint := 'Open Payroll App.';
end;
end;
end.
|
- 10. Click Save button
Update Same UDF in Sales Invoice in Customer Invoice
Below is Example are doing following actions
- Auto update UDF_VehicleNo in Sales Invoice to UDF_VehicleNo in Customer Invoice
Last Script Update : 09 Mar 2021
Level : Advance
Steps - OnAfterSave
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales
- 03. Right Click the Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnAfterSave for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnAfterSave Script |
|---|
var FComServer : Variant;
M : TDataSet;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
function VarToStrDef(const V: Variant; const ADefault: string): string;
begin
if not VarIsNull(V) then
Result := V
else
Result := ADefault;
end;
function VarToStr(const V: Variant): string;
begin
Result := VarToStrDef(V, '');
end;
procedure UpdateARIV;
var lDockey, lBizObj, lMain : Variant;
lDocNo : String;
begin
lDocNo := M.FindField('DocNo').AsString;
try
FComServer := null;
lBizObj := ComServer.BizObjects.Find('AR_IV');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDocKey := lBizObj.FindKeyByRef('DocNo', lDocNo);
lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey);
if not VarIsNull(lDocKey) then begin
lBizObj.Open;
lBizObj.Edit;
lMain.FindField('UDF_VehicleNo').AsString := M.FindField('UDF_VehicleNo').AsString;
lBizObj.Save;
end;
finally
lBizObj.Close;
lBizObj := null;
FComServer := null;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset;
UpdateARIV;
end.
|
- 10. Click Save button
Create Dummy Table
Below is Example are doing following actions
- Create Button to Prompt out Dialog with grid
- Save Grid Data to UDF_GList1
Last Script Update : 05 Feb 2022
Level : Advance
Steps - OnOpen
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Customer
- 03. Right Click the Customer
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnOpen for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnOpen Script |
|---|
var btnRate : Tcxbutton;
AState : TDataSetState;
M, dsGrid : TDatasource;
cdsGrid : TClientDataset;
edCode : TcxDBTextEdit;
grGrid1 : TcxGrid;
fmForm : TForm;
gv1 : TcxGridDBTableView;
procedure Setup;
begin
cdsGrid := TClientDataset.Create(nil);
dsGrid := TDatasource.Create(nil);
grGrid1 := TcxGrid.Create(nil);
end;
procedure FreeCmpt(lComponent :TObject);
begin
if Assigned(lComponent) then
lComponent.Free;
end;
procedure TearDown;
begin
FreeCmpt(cdsGrid);
FreeCmpt(dsGrid);
FreeCmpt(grGrid1);
end;
procedure PrepareTable;
var gc1 : TcxGridColumn;
begin
gv1 := TcxGridDBTableView.Create(grGrid1);
with grGrid1.Levels.Add do begin
Caption := 'Grid';
GridView := TcxCustomGridView(gv1);
end;
with grGrid1 do begin
Parent := nil;
Visible := True;
end;
with gv1 do begin
Name := 'Restricted_1';
OptionsCustomize.ColumnFiltering := False;
DataController.DataSource := dsGrid;
OptionsView.Footer := True;
OptionsView.ColumnAutoWidth := True;
OptionsView.GroupByBox := False;
OptionsData.Editing := True;
OptionsBehavior.IncSearch := False;
DataController.CreateAllItems(False);
ApplyBestFit(nil, False, False);
end;
with gv1.DataController.Summary do begin
BeginUpdate;
try
with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
Column := gv1.Columns[0];
Position := spFooter;
Kind := skCount;
Format := 'Count = #';
end;
finally
EndUpdate;
end;
end;
gc1 := gv1.GetColumnByFieldName('IsActive');
SetGridColumnPropertiesClass(gc1,'TcxCheckBoxProperties');
with TcxCheckBoxProperties(gc1.Properties) do begin
NullStyle := nssUnchecked;
ValueChecked := 'T';
ValueUnChecked := 'F';
ImmediatePost := True;
end;
end;
procedure PrepareXML;
begin
FreeCmpt(cdsGrid);
cdsGrid := TClientDataset.Create(nil);
with cdsGrid.FieldDefs do begin
Add('Description', ftString, 50, False);
Add('Rate', ftFMTBCD, 4, False);
Add('IsActive', ftString, 1, False);
end;
cdsGrid.CreateDataSet;
if Trim(M.DataSet.FindField('UDF_GList1').AsString) <> '' then
cdsGrid.XMLData := M.DataSet.FindField('UDF_GList1').AsString;
dsGrid.Dataset := cdsGrid;
end;
procedure nvgtClick(Sender: TObject; Button: TNavigateBtn);
begin
if Button = nbInsert then
TDBNavigator(Sender).DataSource.DataSet.AppendRecord(['',0,'T']);
end;
procedure PostData;
begin
cdsGrid.MergeChangeLog;
if cdsGrid.RecordCount <> 0 then
M.DataSet.FindField('UDF_GList1').AsString := cdsGrid.XMLData else
M.DataSet.FindField('UDF_GList1').AsString := '';
end;
procedure OnCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
if (AState = dsInsert) or (AState = dsEdit) then
PostData;
with grGrid1 do begin
Parent := nil;
Visible := False;
end;
gv1.Free;
CanClose := True;
end;
procedure InitForm;
var nvgGrid : TDBNavigator;
pnlTop, pnlBtm, pnlBtn : TPanel;
btnOk : TButton;
begin
AState := M.DataSet.State;
fmForm := TForm.Create(nil);
pnlTop := TPanel.Create(fmForm);
pnlBtm := TPanel.Create(fmForm);
pnlBtn := TPanel.Create(fmForm);
btnOk := TButton.Create(fmForm);
nvgGrid := TDBNavigator.Create(fmForm);
if Assigned(grGrid1) then
grGrid1.Free;
grGrid1 := TcxGrid.Create(nil);
PrepareXML;
PrepareTable;
try
with fmForm do begin
Parent := nil;
Height := 480;
Width := 800;
Caption := Format('Disc List(s) - %s',[M.DataSet.FindField('Code').AsString]);
Color := clWhite;
BorderStyle := bsDialog;
Font.Size := 10; //Optional
Font.Name := 'Courier New'; //Optional
Position := poMainFormCenter;
OnCloseQuery := @OnCloseQuery;
end;
with pnlTop do begin
Parent := fmForm;
Align := alTop;
BevelInner := bvNone;
BevelOuter := bvNone;
ParentColor := True;
Height := 30;
Width := fmForm.Width;
end;
with nvgGrid do begin
Parent := pnlTop;
Top := 3;
Left := 5;
VisibleButtons := [nbInsert,nbDelete];
Width := 48;
Height := 25;
DataSource := dsGrid;
OnClick := @nvgtClick;
Enabled := (AState = dsInsert) or (AState = dsEdit);
end;
with pnlBtm do begin
Parent := fmForm;
Align := alClient;
BevelInner := bvNone;
BevelOuter := bvNone;
ParentColor := True;
Width := fmForm.Width;
end;
with grGrid1 do begin
Parent := pnlBtm;
align := alClient;
end;
with pnlBtn do begin
Parent := fmForm;
Align := alBottom;
BevelInner := bvNone;
BevelOuter := bvNone;
ParentColor := True;
Height := 30;
Width := fmForm.Width;
end;
with btnOK do begin
Parent := pnlBtn;
Top := 3;
Width := 100;
Left := (fmForm.Width/2) - (btnOK.Width/2);
Caption := '&Post';
ModalResult := mrOk;
Enabled := (AState = dsInsert) or (AState = dsEdit);
end;
fmForm.ShowModal;
finally
fmForm.Release;
end;
end;
procedure OnClick(Sender: TObject);
begin
InitForm;
end;
begin
M := TDatasource(self.FindComponent('dsDocMaster'));
edCode := TcxDBTextEdit(self.FindComponent('edCode'));
btnRate := TcxButton.Create(edCode);
with btnRate do begin
Parent := edCode.Parent;
Left := edCode.Left + edCode.Width+10;
Top := edCode.Top;
Width := 130;
Name := 'edRate';
Caption := '&1.Show Rate';
OnClick := @OnClick;
end;
end.
|
- 10. Click Save button
Disable Prompt Dialog for Set Default DocNo Set
Last Script Update : 04 May 2024
Level : Advance
Steps - OnAfterNew
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Sales
- 03. Right Click the Sales Invoice
- 04. Select New Event
- 05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)
- 06. Select OnAfterNew for Event field
- 07. Click OK
- 08. Click the Calc (name create at Step 5 above) on the left panel
- 09. Copy below script & paste to the Right Panel (Script Section)
| OnAfterNew Script |
|---|
var M : TDataset;
procedure OnNextDocNoChanged(Sender: TField);
begin
// Disable Build in Event
end;
begin
M := TDataSource(Self.FindComponent('dsDocMaster')).DataSet;
M.FindField('DocNoSetKey').OnChange := @OnNextDocNoChanged;
end.
|
- 10. Click Save button
Example 50
- More Coming Soon....
FAQ
Why when click Compile Script button prompt error [Error] (xx:xx):Unknown identifier 'Dataset'
- This happen if you doing script on the event OnGridColumnValueChanged & can ignore the error if you confirm your script is correct.
How do I know my script is correct when using the script on the event OnGridColumnValueChanged ?
- You can add below script at 1st line before you click Compile Script button but remember to remove it before you click Save button.
var EditingField, Dataset :Variant;
How many data source (TDatasource) it had for data entry (eg Sales Invoice) & what is each name?
- Generally it had 3 data source & some had extra 1 (i.e. total 4).
| Name | Description |
|---|---|
| dsDocMaster | Header fields |
| dsDocDetail | Detail or Item Fields |
| dsSerialNumber | Serial Number Fields |
How to check the Record in Edit Mode?
- You can use below script
... //yr other code
var AState : TDataSetState;
M : TDataSource;
begin
M := TDataSource(Self.FindComponent('dsDocMaster'));
AState := M.DataSet.State;
if (AState = dsInsert) or (AState = dsEdit) then
MessageDlg('Yes In Edit Mode', mtInformation, [mbOK], 0);
end;
... //yr other code
Using Form Mode - Split Browse & Detail Windows Option Prompt Null Pointer Exception Error
- You can use below script
... //yr other code
begin
if TWinControl(Self.FindComponent('DetailControl')).Visible then begin
...//yr other code
end;
end.
Message Dialog with Option
... //yr other code
begin
if MessageDlg('Are you sure?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
MessageDlg('Yes Click', mtInformation, [mbOK],0) else
MessageDlg('No Click', mtInformation, [mbOK],0);
end.
After upgrade to Version 816 prompt error Message Cannot cast an object
See also
- DIY Fields
- Maintain DIY
- Others Customisation