Maintain DIY

Menu: Tools | DIY | Maintain DIY...

Introduction

This Additional Module(DIY Fields & DIY Script Module)

Function	Description
Field and Quick Form	User can Self Create new User Define Field(s) & Design the Layout
Script	Customise the Application
Calculation	Override the build in Calculation (SQL Payroll ONLY)
View Template	For Advance Form Mode
Report Data	Deprecated

Field and Quick Form

Below is Example are doing following actions

• Create UDF_JobTitle at Maintain User
• Create New Form at Maintain User

Steps

01. Right Click at User

02. Select New Field

03. Click New button

04. Enter any Description (eg Calc-Field) in the Description field (Only Alphanumeric & no spacing)

05. Click the + button to add the New UDF Field & Enter/Select the option as above picture

06. Click Save (the Blue Disc icon) after done

07. Close the windows.

• Available Data Field Type

Field Type	Definition
String	Text data of a fixed length (up to 8192 bytes)
Unicode String	A field representing a Unicode (16 bits per character) string.
Boolean	A Boolean value.
Integer	Whole numbers in the range of long integers (32 bits).
Currency	Currency values with the same range as the Real data type.
Bcd	Real numbers with a fixed number of digits after the decimal point.
Date	A date value.
Blob	Binary data with no size limit (BLOB stands for binary large object). The theoretical maximum limit is 2 GB.
Memo	Text of arbitrary length.

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

Only String data can increase the field size so plan properly before add the field. User can't change from 1 data type to another once it save.

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

08. Select the Item just created (eg. Calc-Field)

09. Click Ok button

10. Right Click at User again

11. Select New Quick Form

12. Click New button

13. Enter any Description to be shown at Maintain User(eg Info) in the Description field (Only Alphanumeric & no spacing)

14. Click Customize button (Icon 1)

15. Drag the UDF Field from the Available Items (Icon 2) to empty space (Icon 3)

16. Click Close button to Customize windows

17. Click Save (Icon 4) after done

18. Close the windows.

19. Select the Item just created (eg. Info)

20. Click Ok button

21. Done.

Script

This is Additional Module(DIY Script Module) which allow user to override or customise the SQL System.
The Language use is Pascal

Available forms to customise

Form Type	Example	Description
Browse Form		Script Run/Execute when in Browse window form
Entry Form		Script Run/Execute when in Data Entry window form
Param Form		Script Run/Execute when at in Icon 1 (Seldom use)
Data Form		Script Run/Execute when at in Icon 2 (Frequently use)

Available Action/Event

Action/Event	Description
OnOpenForm	The script trigger On Open the Form (eg On Open the Sales Invoice Form)
OnApply	The script trigger After Click Apply button (for Data Form)
OnBeforeOpen	The script trigger On Before the Form Open
OnAfterOpen	The script trigger On After the Form Open
OnNew	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.
OnBeforeCancel	The script trigger before the build in Cancel Button action execute.
OnAfterCancel	The script trigger after the build in Cancel 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.

Example 1 - List Serial Number in Stock Physical Worksheet

Below is Example are doing following actions

• After apply Stock Physical Worksheet will auto Create New Tab
• List all the Serial Number in the new Grid

Last Script Update : 11 Nov 2015
Level : Advance

Steps

01. Right Click Stock Physical Worksheet - Data Form

02.

uses Forms, Dialogs, DataProcessor, DBClient, cxGridDBTableView, cxGrid, cxGridLevel,
     xQuery, Math, ComObj, SysUtils, DateUtils;

var M, SN     : TDataSet;
    dsGrid    : TDatasource;
    FcxGrid   : TcxGrid;
    gvGrid    : TcxGridDBTableView;
    lTime     : TDateTime;
    cdsOutput : TClientDataset;
    lDisplayFormat, S : String;    

procedure SetNumericDisplayFormat(AClientDataSet: TClientDataSet);
var f  : TFMTBCDField;
    f1 : TFloatField;
    i  : Integer;
    DT : TFieldType;
begin
  for i := 0 to AClientDataSet.FieldCount - 1 do begin
    DT := AClientDataSet.FieldDefs.Items[i].DataType; 
    if DT = ftFMTBcd then begin
      f := TFMTBCDField(AClientDataSet.Fields.Fields[i]);
      if not (f = nil) then f.DisplayFormat := lDisplayFormat;
    end;
    if DT = ftFloat then begin
      f1 := TFloatField(AClientDataSet.Fields.Fields[i]);
      if not (f1 = nil) then f1.DisplayFormat := lDisplayFormat;
    end;
  end;
end;

procedure PrepareNewTab;
begin
  FcxGrid.RootLevelOptions.DetailTabsPosition := dtpTop;
  gvGrid := FcxGrid.CreateView('TcxGridDBTableView') as TcxGridDBTableView;
  with FcxGrid.Levels.Add do begin
    Caption  := 'Calc';
    GridView := TcxCustomGridView(gvGrid);
    Active   := True;
  end;
 
  with gvGrid do begin
    Name                             := 'Restricted_1';
    OptionsCustomize.ColumnFiltering := True;
    OptionsView.ColumnAutoWidth      := False;
    OptionsView.Footer               := True;
    OptionsView.GroupByBox           := False;
    OptionsBehavior.IncSearch        := True;
    DataController.DataSource        := dsGrid;
    OptionsData.Editing              := False;
  end;  
end;

procedure TuneGrid;
var i : integer;
    DT : TFieldType;
begin
  SetNumericDisplayFormat(cdsOutput);
  with gvGrid.DataController do begin
    DataSource := dsGrid;
    CreateAllItems(False);    
  end;
  with gvGrid do begin
    //Hide Columns
    //GetColumnByFieldName('DtlKey').Visible          := False;  
    //Rename Columns Caption
    GetColumnByFieldName('ItemCode').Caption        :='Item Code';
  end;
   
  with gvGrid.DataController.Summary do begin
    BeginUpdate;
    try
      with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
        Column   := gvGrid.Columns[0];
        Position := spFooter;
        Kind     := skCount;
        Format   := 'Count = #';
      end;
      for i := 0 to cdsOutput.FieldDefs.Count-1 do begin
        DT := cdsOutput.FieldDefs.Items[i].DataType;
        if (DT = ftFMTBcd) or (DT = ftFloat) then begin
          with TcxGridDBTableSummaryItem(FooterSummaryItems.Add) do begin
            Column   := gvGrid.Columns[i];
            Position := spFooter;
            Kind     := skSum;
            Format   := lDisplayFormat;
          end;  
        end;
      end;           
      finally
        EndUpdate;
    end;
  end; 
  gvGrid.ApplyBestFit(nil, False, False);     
end;

procedure CreateXMLTable;
begin
  if Assigned(cdsOutput) then
    cdsOutput.Free;
  cdsOutput := TClientDataSet.Create(FcxGrid);
  cdsOutput.FieldDefs.Assign(SN.FieldDefs);
  cdsOutput.CreateDataSet;
  dsGrid.DataSet := cdsOutput;
end;

procedure AppendData;
var i : integer;
begin
  M.First;
  while not M.Eof do begin
    SN.DisableControls;
    SN.First;
    While not SN.Eof do begin
      cdsOutput.Append;
      for i:=0 to SN.FieldDefs.Count-1 do
        cdsOutput.FindField(SN.FieldDefs.Items[i].Name).Value := SN.FindField(SN.FieldDefs.Items[i].Name).Value;
      cdsOutput.Post;      
      SN.Next;
    end;
    SN.EnableControls;
    M.Next;
  end;
end;
    
begin
  M       := Self_DataProcessor.GetDataSetByName('Main');
  SN      := Self_DataProcessor.GetDataSetByName('SerialNumber');
  FcxGrid := TcxGrid(Self.FindComponent('cxGrid1'));
  dsGrid  := TDataSource.Create(M);
  
  lTime := now;
  s := 'Stock Physical Worksheet';
  lDisplayFormat := '#,0.00;-#,0.00;-';
                                        
  
  try
    Self.Caption := s + '- Prepare Tab';
    PrepareNewTab;
    Self.Caption := s + '- Prepare XML';
    CreateXMLTable;
    Self.Caption := s + '- Append Data';
    AppendData;
    Self.Caption := s + '- Tuning Grid';
    TuneGrid;
  finally

    lTime := Now - lTime;
    Self.Caption := Format(s + ' - [Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]);    
  end;    
end.

Example 2

• More Coming Soon...

See also

• SQL Control Center
• Others Customisation
• Lock Report by User