DIY Script

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.

Commonly we only OnOpen, OnBeforeSave & OnGridColumnValueChanged Event

Avoid use 2 same Event/Action in 1 Business Object (eg 2 OnOpen in Sales Invoice is NOT allow)

Get UDF & Do Calculation

Below is Example are doing following actions

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

Last Script Update : 23 Oct 2017
Level : Basic

Steps - OnGridColumnValueChanged

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Sales Invoice

03. Right Click the Sales Invoice

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)

ExpandOnGridColumnValueChanged Script

//var EditingField, Dataset :Variant; var FComServer, lBizObj : Variant; cdsData : TClientDataSet; function ComServer: Variant; begin if FComServer = Null then begin FComServer := CreateOleObject('SQLAcc.BizApp'); end; Result := FComServer; end; function CheckAvailFld(const lcds : TClientDataset; lFld: String): Boolean; var findField: TField; begin Result := False; findField := nil; findField := lcds.FindField(lFld); if Assigned(findField) then Result := not (findField.Calculated); end; procedure GetStockInfo; var lSQL, lCode : String; begin FComServer := null; cdsData := TClientDataSet.Create(nil); // Create & preparing Temporary Table try lCode := Dataset.FindField('ItemCode').AsString; //Select All Maintain Item Fields lSQL := Format('SELECT * FROM ST_ITEM WHERE Code=%s',[QuotedStr(lCode)]); cdsData.Data := ComServer.DBManager.Execute(lSQL); // Execute command & Load the result finally lBizObj := null; FComServer := null; end; end; procedure AssignValue; var i : integer; AFld : String; begin for i:=0 to DataSet.FieldDefs.Count-1 do begin AFld := DataSet.FieldDefs.Items[i].Name; DataSet.Edit; // Make sure is in Edit Mode if Copy(AFld, 1, Length('UDF_')) = 'UDF_' then begin // Set UDF Value if CheckAvailFld(cdsData, AFld) then DataSet.FindField(AFld).AsString := cdsData.FindField(AFld).AsString; end; end; end; procedure Recalc; begin // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate DataSet.Edit; // Make sure is in Edit Mode DataSet.FindField('Qty').AsFloat := DataSet.FindField('UDF_Length').AsFloat * DataSet.FindField('UDF_Width').AsFloat * DataSet.FindField('UDF_Rate').AsFloat; end; begin if SameText(EditingField, 'ItemCode') then begin // when selecting or change itemcode field try GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item AssignValue; // Set the Invoice detial UDF Fields from Maintain Item UDF Fields Recalc; finally cdsData.Free; // Free the temporary table after used end; end; if SameText(EditingField, 'UDF_Rate') then // when change UDF_Rate field Recalc; end.

10. Click Save button

----------------------------------------------------------------------------------------------------------------------------------------------------

Avoid update below field in same time as will cause unlimited looping updating each other - Qty - UnitPrice - TaxAmt - Amount

----------------------------------------------------------------------------------------------------------------------------------------------------

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnBeforeEdit 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

[top]

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)

ExpandOnGridColumnValueChanged 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

[top]

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 : 04 Dec 2024
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)

ExpandOnOpen 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 + 200; Top := edCompanyCategory.Top +3; Caption := 'Search key :'; end; with edSearchKey do begin // Create Drop List Parent := edCompanyCategory.Parent; Left := lbSearchKey.Left + lbSearchKey.Width + 10; 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

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnBeforeSave Script
var btnProfit : Tcxbutton; begin btnProfit := Tcxbutton(Self.FindComponent('BtnProfitEstimator')); if Assigned(btnProfit) then btnProfit.Click; end.

10. Click Save button

[top]

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)

ExpandOnGridColumnValueChanged 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnOpen 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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnAfterNew 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnGridAferInsert 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnGridAfterInsert Script
begin Dataset.FindField('Disc').AsString := ''; end;

10. Click Save button

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnAfterSave 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

[top]

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)

ExpandOnBeforeSave 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen Script
var edTerms : TcxDBExtLookupComboBox; begin edTerms := Self.FindComponent('edTerms') as TcxDBExtLookupComboBox; if Assigned(edTerms) then edTerms.Enabled := False; end.

10. Click Save button

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnOpen 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

[top]

Update Same UDF in Sales Invoice in Customer Invoice

Below is Example are doing following actions

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

Last Script Update : 26 Nov 2024
Level : Advance

Steps - OnAfterSave

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Sales

03. Right Click the Invoice

04. Select New Event

05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing)

06. Select OnAfterSave for Event field

07. Click OK

08. Click the Calc (name create at Step 5 above) on the left panel

09. Copy below script & paste to the Right Panel (Script Section)

ExpandOnAfterSave Script

var FComServer : Variant; M : TDataSet; function ComServer: Variant; begin if FComServer = Null then begin FComServer := CreateOleObject('SQLAcc.BizApp'); end; Result := FComServer; end; function VarToStrDef(const V: Variant; const ADefault: string): string; begin if not VarIsNull(V) then Result := V else Result := ADefault; end; function VarToStr(const V: Variant): string; begin Result := VarToStrDef(V, ''); end; procedure UpdateARIV; var lDockey, lBizObj, lMain, V : Variant; lDocNo, lCode : String; begin lDocNo := M.FindField('DocNo').AsString; lCode := M.FindField('Code').AsString; try FComServer := null; lBizObj := ComServer.BizObjects.Find('AR_IV'); lMain := lBizObj.DataSets.Find('MainDataSet'); V := FComServer.CreateOleVariantArray(1); V.SetItem(0, lDocNo); V.SetItem(1, lCode); lDocKey := lBizObj.FindKeyByRef('DocNo;Code', V.AsOleVariant); lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey); if not VarIsNull(lDocKey) then begin lBizObj.Open; lBizObj.Edit; lMain.FindField('UDF_VehicleNo').AsString := M.FindField('UDF_VehicleNo').AsString; lBizObj.Save; end; finally lBizObj.Close; lBizObj := null; FComServer := null; end; end; begin M := TDataSource(Self.FindComponent('dsDocMaster')).Dataset; UpdateARIV; end.

10. Click Save button

[top]

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)

ExpandOnOpen 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

[top]

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)

ExpandOnAfterNew 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

[top]

Example 50

• More Coming Soon....

[top]

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