|
|
| (10 intermediate revisions by the same user not shown) |
| Line 3: |
Line 3: |
| ==Introduction== | | ==Introduction== |
| This Additional Module where user can self | | This Additional Module where user can self |
| * Create new field (DIY Fields Module) | | * Create new field ([[DIY Fields]] Module) |
| * Modified the SQL Accounting (DIY Script Module) | | * Modified the SQL Accounting ([[DIY Script]] Module) |
|
| |
|
| ==DIY Fields== | | ==See also== |
| * Available Data Field Type | | * [[Maintain DIY]] |
| :: - Boolean
| | * Others [[Customisation]] |
| :: - Currency
| |
| :: - Date
| |
| :: - Float
| |
| :: - Integer
| |
| :: - Memo
| |
| :: - Picture
| |
| :: - String
| |
| | |
| * For Explanation for Each Data Type may refer to http://www.w3schools.com/sql/sql_datatypes_general.asp
| |
| | |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| ::::{|
| |
| |-
| |
| | [[File:Template.Warning-01.jpg|80px]]||
| |
| * Avoid use '''Picture''' data if can as it will cause your database size increase very fast.
| |
| * 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.
| |
| |}
| |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| | |
| * User can create DIY Fields either at '''Header Fields''' or '''Item Fields'''
| |
| * In each Table had the below interface
| |
| ::[[File:DIYField-01.jpg|700px]]
| |
| | |
| ::{| class="wikitable"
| |
| |-
| |
| ! Name !! Description
| |
| |-
| |
| | Name||
| |
| * Field name
| |
| * Only Alphanumeric & no spacing
| |
| |-
| |
| | Data Type ||
| |
| * Type of Field
| |
| |-
| |
| | Size ||
| |
| * The Field Size
| |
| * Only applicable for '''Float''', '''Integer''' & '''String''' Data Type
| |
| |-
| |
| | Sub Size ||
| |
| * The Field Sub Size normally is the Decimal Point
| |
| * Only applicable for '''Float''' Data Type
| |
| |-
| |
| | Caption ||
| |
| * Field name to be shown at user interface.
| |
| |-
| |
| | Required ||
| |
| * To tell the system the current field '''MUST''' not empty
| |
| |-
| |
| | Default Value ||
| |
| * Default Value to insert on New record
| |
| |-
| |
| | Display Format ||
| |
| * Set Display format for '''Float''', '''Currency''' & '''Integer''' Data Type
| |
| |-
| |
| | Load ||
| |
| * Reload again the Script from the Database (Only for DIY Script)
| |
| |-
| |
| | Load From File ||
| |
| * Load Script/Fields from the Text File.
| |
| * Be careful as system will delete the existing field before load the file to the system
| |
| |-
| |
| | Save ||
| |
| * Save Current Script/Field
| |
| * Don't do Delete, Rename & Insert in same time (Only for DIY Fields)
| |
| |-
| |
| | Save To File ||
| |
| * Save Current Script/Fields to Text File | |
| |-
| |
| |'''+''' Button ||
| |
| * Add New Field
| |
| |-
| |
| |'''-''' Button ||
| |
| * Delete Selected Field
| |
| |}
| |
| ===Header Fields===
| |
| Below is the example for Sales Invoice
| |
| ====Add New Fields====
| |
| ::[[File:DIYField-02.jpg|700px]]
| |
| :01. At the left panel look for '''Sales Invoice | Header Fields'''<br />
| |
| :02. Click the '''+''' Button on top to add New fields as above picture<br />
| |
| :03. Click '''Save''' button<br />
| |
| | |
| ====Design Layout====
| |
| They are 2 ways to creating the Layout for new fields added.<br />
| |
| :01. Right Click the '''Sales Invoice'''
| |
| ::[[File:DIYField-03.jpg]]
| |
| =====Advance Form Design=====
| |
| This mode is only for the user who known how to use the Delphi.<br />
| |
| ======Pros======
| |
| * Freely adjust & place the component.
| |
| ======Cons======
| |
| * Not Easy to design
| |
| * Not support Date & Picture Data Type<br /><br />
| |
| | |
| :02. Select '''New Advance Form Design'''
| |
| ::[[File:DIYField-04.jpg]]
| |
| :03. Enter the name (eg Freight Info) this will shown in the Sales Invoice Data Entry
| |
| :04. Click OK
| |
| :05. Click the '''Freight Info''' (name create at '''Step 3''' above) on the left panel
| |
| ::[[File:DIYField-05.jpg|650px]]<br />
| |
| | |
| ::[[File:DIYField-06.jpg]]
| |
| ::* This '''Standard''' component is just for Fixed labeling or decoration
| |
| ::* The detail of each button can be refer to http://www.delphibasics.co.uk/Article.asp?Name=Standard
| |
| | |
| ::[[File:DIYField-07.jpg]]
| |
| ::* This Data Controls component is use for Variable data which can be use at Data Entry
| |
| ::* Below is the detail of each button (From Left)
| |
| | |
| ::{| class="wikitable"
| |
| |-
| |
| ! Component !! Purpose
| |
| |-
| |
| | DBText || Data-aware label that can display a field from a currently active record.
| |
| |-
| |
| | DBEdit || Data-aware edit box that can display or edit a field from a currently active record.
| |
| |-
| |
| | DBMemo || Data-aware memo box that can display or edit text BLOB data from a currently active record.
| |
| |-
| |
| | DBImage || Data-aware image box that can display, cut, or paste bitmapped BLOB images to and from a currently active record.
| |
| |-
| |
| | DBListBox || Data-aware list box that can display values from a column in a table.
| |
| |-
| |
| | DBCheckBox || Data-aware check box that can display or edit a Boolean data field from a currently active record.
| |
| |-
| |
| | DBRichEdit || Data-aware Rich Text Editor that can display or edit text BLOB data from a currently active record.
| |
| |-
| |
| | cxDBSpinEdit || Data-aware Edit box that can display or edit integer or Float data from a currently active record.
| |
| |}
| |
| | |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| ::::{|
| |
| |-
| |
| | [[File:Template.Tips-01.jpg|60px]]||
| |
| Normally we only use
| |
| :'''Standard''' Tab component
| |
| ::- Label - For Labeling each field
| |
| :'''Data Controls''' Tab component
| |
| ::- DBEdit - For String Data
| |
| ::- DBRichEdit - For Memo Data
| |
| ::- cxDBSpinEdit - For Currency, Float or Integer Data
| |
| |}
| |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| | |
| :06. Click the '''Standard''' Tab & select Label button place on thee right panel
| |
| :07. In the '''Object Inspector''' look for
| |
| ::* '''Caption''' & set to the Caption you like (eg Container Number)
| |
| ::* '''Font''' & set the '''Size''' to 12 or any size you prefer
| |
| ::[[File:DIYField-08.jpg]]
| |
| :08. Click the '''Data Controls''' Tab & select DBEdit button place on thee right panel
| |
| :09. In the '''Object Inspector''' look for
| |
| ::* '''DataField''' & set to the UDF field name (normally is UDF_FieldName) (eg UDF_ContainerNo)
| |
| ::* '''Font''' & set the '''Size''' to 12 or any size you prefer
| |
| ::[[File:DIYField-09.jpg]]
| |
| :10. Continue repeat '''Step 6''' till '''Step 9''' for rest of the UDF field.
| |
| :11. Click Save button after done.
| |
| | |
| =====Quick Form Design (Recommended)=====
| |
| This mode is for the user who don't known how to use the Delphi.
| |
| ======Pros======
| |
| * Easy to Design
| |
| ======Cons======
| |
| * Unable to freely adjust & place the component.
| |
| * Not support Rich Text Editor.<br /><br />
| |
| | |
| :02. Select '''New Quick Form Design'''
| |
| ::[[File:DIYField-10.jpg]]
| |
| :03. Enter the name (eg Freight Info) this will shown in the Sales Invoice Data Entry
| |
| :04. Click OK
| |
| :05. Click the '''Freight Info''' (name create at '''Step 3''' above) on the left panel
| |
| ::[[File:DIYField-11.jpg|700px]]<br />
| |
| :06. Click '''Customise''' button.
| |
| :07. Drag the UDF Field from the '''Available Item''' dialog box & drop to Right Panel
| |
| :08. Continue repeat '''Step 7''' for rest of the UDF field.
| |
| :09. Click '''Save''' button after done.
| |
| | |
| ===Item Fields===
| |
| There is no layout need to be design for '''Item Field''' UDF. You can drag out all the UDF field in the '''Field Chooser''' in the data Entry
| |
| ::[[File:DIYField-12.jpg|650px]]<br />
| |
| | |
| :01. Drag the UDF field from the '''Customization''' Dialog<br />
| |
| :02. Drop it the column you wanted to shown (eg In between Description Column & Qty Column)
| |
| | |
| ==DIY Script==
| |
| This is additional Module which allow user to override or customise the SQL System.<br />
| |
| The Language use is '''Pascal'''<br />
| |
| Available Action/Event
| |
| ::{| class="wikitable"
| |
| |-
| |
| ! 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.
| |
| |}
| |
| | |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| ::::{|
| |
| |-
| |
| | [[File:Template.Tips-01.jpg|60px]]||
| |
| Commonly we only '''OnOpen''', '''OnBeforeSave''' & '''OnGridColumnValueChanged''' Event
| |
| |}
| |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| | |
| ===Script Example 1===
| |
| 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
| |
| | |
| | |
| :01. Right Click the '''Sales Invoice'''
| |
| ::[[File:DIYField-03.jpg]]
| |
| :02. Select '''New Event'''
| |
| ::[[File:DIYScript-01.jpg]]
| |
| :03. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
| |
| :04. Select '''OnGridColumnValueChanged''' for '''Event''' field
| |
| :05. Click OK
| |
| :06. Click the Calc (name create at Step 3 above) on the left panel
| |
| ::[[File:DIYScript-02.jpg|700px]]<br />
| |
| :07. Copy below script & paste to the Right Panel (Script Section)
| |
| <pre>
| |
| 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);
| |
| 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;
| |
| DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
| |
| DataSet.FindField('UDF_Width').AsFloat := cdsData.FindField('UDF_Width').AsFloat;
| |
| DataSet.FindField('Qty').AsFloat := DataSet.FindField('UDF_Length').AsFloat *
| |
| DataSet.FindField('UDF_Width').AsFloat *
| |
| DataSet.FindField('UDF_Rate').AsFloat;
| |
| finally
| |
| cdsData.Free;
| |
| end;
| |
| end;
| |
| end.
| |
| </pre>
| |
| :08. Click '''Save''' button
| |
| | |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| ::::{|
| |
| |-
| |
| | [[File:Template.Warning-01.jpg|80px]]||
| |
| * Avoid update below field in same time as will cause unlimited looping updating each other
| |
| : - Qty
| |
| : - UnitPrice
| |
| : - TaxAmt
| |
| : - Amount
| |
| |}
| |
| :::----------------------------------------------------------------------------------------------------------------------------------------------------
| |
| * More Coming Soon....
| |
