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