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)

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

Example 1 - Get UDF & Do Calculation

Below is Example are doing following actions

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

Last Script Update : 25 Sep 2015

Steps

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)

var FComServer, lBizObj : Variant;
    cdsData : TClientDataSet;

function ComServer: Variant;
begin
  if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
  Result := FComServer;
end;

procedure GetStockInfo;
var lSQL, lCode  : String;
begin
  FComServer := null;
  cdsData    := TClientDataSet.Create(nil); // Create & preparing Component
  try
    lCode := Dataset.FindField('ItemCode').AsString;     
    lSQL  := Format('SELECT UDF_Length, UDF_Width FROM ST_ITEM WHERE Code=%s',[QuotedStr(lCode)]);

    cdsData.Data := ComServer.DBManager.Execute(lSQL);
  finally
    lBizObj    := null;
    FComServer := null;
  end;
end;

begin
  if SameText(EditingField, 'ItemCode') OR // when selecting or change itemcode field
     SameText(EditingField, 'UDF_Rate') then begin // when change UDF_Rate field
    try
      GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item

      // Below is Set the Invoice detial UDF Fields from Maintain Item UDF Fields
      DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
      DataSet.FindField('UDF_Width').AsFloat  := cdsData.FindField('UDF_Width').AsFloat;

      // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
      DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                                 DataSet.FindField('UDF_Width').AsFloat *
                                                 DataSet.FindField('UDF_Rate').AsFloat;
    finally
      cdsData.Free; // Free the Component after used
    end;
  end;
end.

10. Click Save button

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

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

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

Example 2

Below is Example are doing following actions

• Change the Sales Quotation Title to My Quotation

Last Script Update : 19 Oct 2015

Steps

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)

var L: TComponent;
Begin
  L := Self.FindComponent('lbQt');
  if Assigned(L) then
    TcxLabel(L).Caption := 'My Quotation';
end;

10. Click Save button

Example 3

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

Steps

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)

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
    FComServer   := null;
    lCdsDataList := TClientDataSet.Create(nil);
  
    try
      lSQL := Format('SELECT UDF_MSG FROM AR_CUSTOMER WHERE Code= %s',[QuotedStr(M.DataSet.FindField('Code').AsString)]);
      lCdsDataList.Data := ComServer.DBManager.Execute(lSQL);

      s := lCdsDataList.FindField('UDF_MSG').AsString;
      if Trim(s) <> '' then
        MessageDlg(S, mtInformation, [mbOk], 0);
    finally
      FComServer := null;
      lCdsDataList.Free;
    end;
  end;
end;

begin
  C := TcxDBTextEdit(Self.FindComponent('edCompanyName'));
  if Assigned(C) then
   C.Properties.OnEditValueChanged := @OnCompanyNameChanged;
end.

10. Click Save button

Example 4

• More Coming Soon....

See also

• DIY Fields