(47 intermediate revisions by the same user not shown) | |||
Line 5: | Line 5: | ||
[[File:MaintainDIY-01.jpg|center]] | [[File:MaintainDIY-01.jpg|center]] | ||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |- | ||
! Function !! Description | ! Function !! Description | ||
Line 37: | Line 37: | ||
:07. Close the windows. | :07. Close the windows. | ||
* Available Data Field Type | * Available Data Field Type | ||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |- | ||
! Field Type !! Definition | ! Field Type !! Definition | ||
Line 67: | Line 67: | ||
* Only '''String''' data can increase the field size so plan properly before add the field. | * 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't change from 1 data type to another once it save. | ||
* Avoid create too many UDF field as it will reduce system performance & take longer time to save 1 data entry. | |||
|} | |} | ||
:::---------------------------------------------------------------------------------------------------------------------------------------------------- | :::---------------------------------------------------------------------------------------------------------------------------------------------------- | ||
Line 90: | Line 91: | ||
[[File:MaintainDIY-09.jpg|center]] | [[File:MaintainDIY-09.jpg|center]] | ||
:21. Done. | :21. Done. | ||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==Script== | ==Script== | ||
Line 99: | Line 102: | ||
Available forms to customise | Available forms to customise | ||
{| class="wikitable" | {| class="wikitable" style="margin: 1em auto 1em auto;" | ||
|- | |- | ||
! Form Type !! Example !! Description | ! Form Type !! Example !! Description | ||
Line 113: | Line 116: | ||
Available Action/Event | Available Action/Event | ||
::{| class="wikitable" | ::{| class="wikitable" style="margin: 1em auto 1em auto;" | ||
|- | |- | ||
! Action/Event !! Description | ! Action/Event !! Description | ||
Line 144: | Line 147: | ||
|} | |} | ||
===Example 1=== | <div style="float: right;"> [[#top|[top]]]</div> | ||
===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<br /> | |||
Level : Advance | |||
'''Steps''' | |||
[[File:MaintainDIY-14.jpg|center]] | |||
:01. Right Click at '''Stock Physical Worksheet - Data Form''' | |||
[[File:MaintainDIY-15.jpg|center]] | |||
:02. Select '''New Script''' | |||
[[File:MaintainDIY-16.jpg|center]] | |||
:03. Select '''OnApply''' | |||
:04. Click '''OK''' button | |||
[[File:MaintainDIY-17.jpg|center]] | |||
:05. Click '''New''' Button | |||
[[File:MaintainDIY-18.jpg|center]] | |||
:06. Enter any description (eg Stock_Physical_Worksheet-DataForm-OnApply) in the '''Description''' field (Only Alphanumeric & no spacing) | |||
:07. Copy below script & paste to the '''Script''' Field | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! DataForm-OnApply Script | |||
|- | |||
| | |||
<syntaxhighlight lang="delphi"> | |||
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. | |||
</syntaxhighlight> | |||
|} | |||
:08. Click Save (the Blue Disc Icon) | |||
:09. Close the window. | |||
[[File:MaintainDIY-19.jpg|center]] | |||
:10. Select the '''Item''' just created (eg. Stock_Physical_Worksheet-DataForm-OnApply) | |||
:11. Click '''OK''' button | |||
[[File:MaintainDIY-20.jpg|center]] | |||
:12. Click Stock | Print Stock Physical Worksheet | Apply | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 2 - Set Default Filtering for Sales=== | |||
Below is Example are doing following actions | |||
* Filter by Agent in Sales Profit & Loss By Document | |||
* Disable Selection for Agent | |||
Last Script Update : 03 Feb 2016<br /> | |||
Level : Basic | |||
'''Steps''' | |||
:01. Right Click at '''Sales Profit & Loss By Document - Param Form''' | |||
:02. Select '''New Script''' | |||
:03. Select '''OnOpenForm''' | |||
:04. Click '''OK''' button | |||
:05. Click '''New''' Button | |||
:06. Enter any description (eg SL-PnL-ParamForm-OnOpen) in the '''Description''' field (Only Alphanumeric & no spacing) | |||
:07. Copy below script & paste to the '''Script''' Field | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Param Form - OnOpenForm Script | |||
|- | |||
| | |||
<syntaxhighlight lang="delphi"> | |||
uses SysUtils, Dialogs, Forms; | |||
var C, D : TControl; | |||
L: TStringList; | |||
begin | |||
C := Self.FindChildControl('edAgent_Panel') as TWinControl; | |||
L := TStringList.Create; | |||
try | |||
if C <> nil then C.Enabled := False; // Disable Selection | |||
C := TWinControl(C).FindChildControl('edAgent_Edit') as TWinControl; | |||
L.Add('NF'); | |||
L.Add('LF'); | |||
(C as TControl).SetTextBuf(L.CommaText);//For Multi Records | |||
// (C as TControl).SetTextBuf('LF');//For 1 record | |||
finally | |||
L.Free; | |||
end; | |||
end. | |||
</syntaxhighlight> | |||
|} | |||
:08. Click Save (the Blue Disc Icon) | |||
:09. Close the window. | |||
:10. Select the '''Item''' just created (eg. SL-PnL-ParamForm-OnOpen) | |||
:11. Click '''OK''' button | |||
:12. Click Sales | Print Profit & Loss By Document... | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 3 - Drop Down List at Maintain User=== | |||
Below is Example are doing following actions | |||
* Create a drop down list of Project List at Maintain User for UDF_Project | |||
Last Script Update : 15 Feb 2016<br /> | |||
Level : Advance | |||
'''Steps''' | |||
:01. Right Click at '''User - Entry Form''' | |||
:02. Select '''New Script''' | |||
:03. Select '''OnOpenForm''' | |||
:04. Click '''OK''' button | |||
:05. Click '''New''' Button | |||
:06. Enter any description (eg UserEntryForm-OnOpen) in the '''Description''' field (Only Alphanumeric & no spacing) | |||
:07. Copy below script & paste to the '''Script''' Field | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Entry Form - OnOpen Script | |||
|- | |||
| | |||
<syntaxhighlight lang="delphi"> | |||
uses Forms, StdCtrls, DBCtrls, Dialogs, ComObj, DBClient; | |||
Var edProject : TDBLookupComboBox; | |||
btnCPassword : Tbutton; | |||
M, dsList : TDataSource; | |||
lbProject : TComponent; | |||
C : TControl; | |||
FComServer : Variant; | |||
cdsList : TClientDataset; | |||
function ComServer: Variant; | |||
begin | |||
if FComServer = Null then begin | |||
FComServer := CreateOleObject('SQLAcc.BizApp'); | |||
end; | |||
Result := FComServer; | |||
end; | |||
procedure GetList; | |||
var lSQL : String; | |||
begin | |||
FComServer := null; | |||
cdsList := TClientDataset.Create(C); | |||
lSQL := 'SELECT Code, Description FROM Project WHERE IsActive=''T'' '; | |||
try | |||
cdsList.Data := ComServer.DBManager.Execute(lSQL); | |||
dsList.Dataset := cdsList; | |||
finally | |||
FComServer := null; | |||
end; | |||
end; | |||
begin | |||
M := TDataSource(Self.FindComponent('dsMain')); | |||
C := Self.FindChildControl('lbName'); | |||
dsList := TDataSource.Create(C); | |||
GetList; | |||
if Assigned(C) then begin | |||
edProject := TDBLookupComboBox.Create(C); | |||
lbProject := TLabel.Create(C); | |||
with TLabel(lbProject) do begin | |||
Parent := C.Parent; | |||
Caption := '&1. Project :'; | |||
Left := 200; | |||
Top := 68; | |||
FocusControl := edProject; | |||
end; | |||
with edProject do begin | |||
Parent := C.Parent; | |||
Top := 65; | |||
Left := 260; | |||
Width := 120; | |||
Name := 'edProject'; | |||
Datasource := M; | |||
ListSource := dsList; | |||
DataField :='UDF_Project'; | |||
DropDownWidth := 500; | |||
KeyField := 'Code'; | |||
ListFieldIndex := 1; //Search By Description | |||
ListField := 'Code;Description'; //Show 2 Column | |||
end; | |||
end; | |||
end. | |||
</syntaxhighlight> | |||
|} | |||
:08. Click Save (the Blue Disc Icon) | |||
:09. Close the window. | |||
:10. Select the '''Item''' just created (eg. UserEntryForm-OnOpen) | |||
:11. Click '''OK''' button | |||
:12. Click Tools | Maintain User... | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 4 - === | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
* More Coming Soon... | * More Coming Soon... | ||
==Calculation== | |||
This section is mostly use in [[payroll:Contents|SQL Payroll]]. User can override the build in Calculation/Formula to suite their requirement.<br /> | |||
The Language use is '''Pascal'''.<br /> | |||
[[File:MaintainDIY-21.jpg|center]] | |||
Available Calculation Types | |||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |||
! Type !! Description | |||
|- | |||
| Generate Document Number || Manipulate the Next Document Number Format. | |||
|- | |||
| Leave Entitlement Policy || Manipulate yearly Leave Entitlement | |||
|- | |||
| Leave Entitlement Processor || Manipulate Calculation of Year of working | |||
|- | |||
| Leave Calculate Amount || Manipulate the Leave Calculation Amount | |||
|- | |||
| Leave Calculate BF || Calculate how many leave can be Brought Forward if not fully taken | |||
|- | |||
| Overtime Pay Rate || Manipulate the Overtime Pay rate | |||
|- | |||
| Payroll Rounding || Manipulate the Net Pay Amount | |||
|- | |||
| SOCSO Table || Manipulate the Sosco Deduction Amount | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 1 - Leave Entitlement Processor - Calculate from Confirm Date=== | |||
Below is Example to change the Leave Entitlement Processor to be calculate from the Confirmation Date<br /> | |||
Last Script Update : 15 Jan 2016<br /> | |||
Level : Basic<br /> | |||
'''Steps''' | |||
[[File:MaintainDIY-21.jpg|center]] | |||
:01. Right Click at '''Leave Entitlement Processor''' | |||
[[File:MaintainDIY-22.jpg|center]] | |||
:02. Select '''New Calculation''' | |||
[[File:MaintainDIY-23.jpg|center]] | |||
:03. Click '''New''' Button | |||
[[File:MaintainDIY-24.jpg|center]] | |||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |||
! Formula !! Description | |||
|- | |||
| Based On 365 Days Proportion, Round To 1 decimal place || Round to 1 Decimal point if Result had fraction | |||
|- | |||
| Based On 365 Days Proportion, No Rounding || Truncate the Result | |||
|- | |||
| Base On By Calendar Year, No Proportion, Rounding To 1 decimal place || Calculate by month & No Prorata (Base on Malaysia Labour Law) | |||
|} | |||
:04. Select any Formula which suit you & Click OK | |||
[[File:MaintainDIY-25.jpg|800px|center]] | |||
:05. Enter any '''Description''' (eg Leave_Entitlement_Processor-Confirm Date) in the Description field (Only Alphanumeric & no spacing) | |||
:06. In the '''Script''' look for the word ''lJoinDate'' & change it to ''lConfirmDate'' or <br /> | |||
===Example 2 - Leave Calculate Amount - Unpaid Leave Amount Base on Working Day=== | |||
Below is example to change the Unpaid Leave Amount to base on Working Days in [[Maintain Contribution]] | |||
Last Script Update : 19 Jan 2016<br /> | |||
Level : Basic | |||
'''Steps''' | |||
:01. Right Click at '''Leave Calculate Amount''' | |||
:02. Select '''New Calculation''' | |||
:03. Click '''New''' Button | |||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |||
! Formula !! Description | |||
|- | |||
| Leave Calculate Amount - Based On Employee Default Wages || The Calculation will use Wages amount at [[Maintain Employee]] | |||
|- | |||
| Leave Calculate Amount - Based On Employee Default Wages And Allowance || The Calculation will use Wages & Allowance amount at [[Maintain Employee]] | |||
|} | |||
:04. Select any Formula which suit you & Click OK | |||
:05. Enter any '''Description''' (eg Leave_Calculate_Amt-Unpaid) in the Description field (Only Alphanumeric & no spacing) | |||
:06. In the Script look for the word ''DaysBetweenStartAndEndDate'' & change it to ''WorkingDayPerMonth'' or <br /> | |||
::Copy below script & paste to the Script Section | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Leave Calculate Amount - Based On Employee Default Wages | |||
|- | |||
| | |||
<syntaxhighlight lang="delphi"> | |||
// Example - Leave Calculate Amount - Based On Employee Default Wages | |||
uses DB, DateUtils; | |||
begin | |||
Amount := DefaultWages / WorkingDayPerMonth; | |||
end. | |||
</syntaxhighlight> | |||
|} | |||
:07. Click '''Compile''' button to make sure is '''Compile success'''. | |||
:08. Click '''Save''' button | |||
:09. Click X to Close | |||
:10. Select the Item just created (eg Leave_Calculate_Amt-Unpaid) | |||
:11. Click Ok button | |||
:12. Done & Remember to set it at [[Maintain Leave Group]] under the ''Amount'' Column for ''UL'' | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 3 - Limit Overtime Rate=== | |||
Below is example to set Maximum limit the Overtime Rate. | |||
Last Script Update : 21 Mar 2023<br /> | |||
Level : Basic | |||
'''Steps''' | |||
:01. Right Click at '''Overtime Pay Rate''' | |||
:02. Select '''New Calculation''' | |||
:03. Click '''New''' Button | |||
:04. Select any Formula which suit you & Click OK | |||
:05. Enter any '''Description''' (eg Overtime_Max_Rate) in the Description field (Only Alphanumeric & no spacing) | |||
::Copy below script & paste to the Script Section | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Overtime Max Rate | |||
|- | |||
| | |||
<syntaxhighlight lang="delphi"> | |||
uses Math, DB; | |||
var V: Double; | |||
begin | |||
V := 0; | |||
if OTAmount > 1500.00 then | |||
V := 10.82 else //(1500/26/8) | |||
V := (OTAmount / 26 /8); | |||
PayRate := SimpleRoundToEx(V, -2); | |||
end. | |||
</syntaxhighlight> | |||
|} | |||
:06. Click '''Compile''' button to make sure is '''Compile success'''. | |||
:07. Click '''Save''' button | |||
:08. Click X to Close | |||
:09. Select the Item just created (eg Overtime_Max_Rate) | |||
:10. Click Ok button | |||
:11. Done & Remember to set it at [[Maintain Overtime]] | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 4 - === | |||
* More coming soon... | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==See also== | ==See also== |
Latest revision as of 02:20, 11 October 2024
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. |
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- 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 at Stock Physical Worksheet - Data Form
- 02. Select New Script
- 03. Select OnApply
- 04. Click OK button
- 05. Click New Button
- 06. Enter any description (eg Stock_Physical_Worksheet-DataForm-OnApply) in the Description field (Only Alphanumeric & no spacing)
- 07. Copy below script & paste to the Script Field
DataForm-OnApply Script |
---|
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.
|
- 08. Click Save (the Blue Disc Icon)
- 09. Close the window.
- 10. Select the Item just created (eg. Stock_Physical_Worksheet-DataForm-OnApply)
- 11. Click OK button
- 12. Click Stock | Print Stock Physical Worksheet | Apply
Example 2 - Set Default Filtering for Sales
Below is Example are doing following actions
- Filter by Agent in Sales Profit & Loss By Document
- Disable Selection for Agent
Last Script Update : 03 Feb 2016
Level : Basic
Steps
- 01. Right Click at Sales Profit & Loss By Document - Param Form
- 02. Select New Script
- 03. Select OnOpenForm
- 04. Click OK button
- 05. Click New Button
- 06. Enter any description (eg SL-PnL-ParamForm-OnOpen) in the Description field (Only Alphanumeric & no spacing)
- 07. Copy below script & paste to the Script Field
Param Form - OnOpenForm Script |
---|
uses SysUtils, Dialogs, Forms;
var C, D : TControl;
L: TStringList;
begin
C := Self.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.
|
- 08. Click Save (the Blue Disc Icon)
- 09. Close the window.
- 10. Select the Item just created (eg. SL-PnL-ParamForm-OnOpen)
- 11. Click OK button
- 12. Click Sales | Print Profit & Loss By Document...
Example 3 - Drop Down List at Maintain User
Below is Example are doing following actions
- Create a drop down list of Project List at Maintain User for UDF_Project
Last Script Update : 15 Feb 2016
Level : Advance
Steps
- 01. Right Click at User - Entry Form
- 02. Select New Script
- 03. Select OnOpenForm
- 04. Click OK button
- 05. Click New Button
- 06. Enter any description (eg UserEntryForm-OnOpen) in the Description field (Only Alphanumeric & no spacing)
- 07. Copy below script & paste to the Script Field
Entry Form - OnOpen Script |
---|
uses Forms, StdCtrls, DBCtrls, Dialogs, ComObj, DBClient;
Var edProject : TDBLookupComboBox;
btnCPassword : Tbutton;
M, dsList : TDataSource;
lbProject : TComponent;
C : TControl;
FComServer : Variant;
cdsList : TClientDataset;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure GetList;
var lSQL : String;
begin
FComServer := null;
cdsList := TClientDataset.Create(C);
lSQL := 'SELECT Code, Description FROM Project WHERE IsActive=''T'' ';
try
cdsList.Data := ComServer.DBManager.Execute(lSQL);
dsList.Dataset := cdsList;
finally
FComServer := null;
end;
end;
begin
M := TDataSource(Self.FindComponent('dsMain'));
C := Self.FindChildControl('lbName');
dsList := TDataSource.Create(C);
GetList;
if Assigned(C) then begin
edProject := TDBLookupComboBox.Create(C);
lbProject := TLabel.Create(C);
with TLabel(lbProject) do begin
Parent := C.Parent;
Caption := '&1. Project :';
Left := 200;
Top := 68;
FocusControl := edProject;
end;
with edProject do begin
Parent := C.Parent;
Top := 65;
Left := 260;
Width := 120;
Name := 'edProject';
Datasource := M;
ListSource := dsList;
DataField :='UDF_Project';
DropDownWidth := 500;
KeyField := 'Code';
ListFieldIndex := 1; //Search By Description
ListField := 'Code;Description'; //Show 2 Column
end;
end;
end.
|
- 08. Click Save (the Blue Disc Icon)
- 09. Close the window.
- 10. Select the Item just created (eg. UserEntryForm-OnOpen)
- 11. Click OK button
- 12. Click Tools | Maintain User...
Example 4 -
- More Coming Soon...
Calculation
This section is mostly use in SQL Payroll. User can override the build in Calculation/Formula to suite their requirement.
The Language use is Pascal.
Available Calculation Types
Type | Description |
---|---|
Generate Document Number | Manipulate the Next Document Number Format. |
Leave Entitlement Policy | Manipulate yearly Leave Entitlement |
Leave Entitlement Processor | Manipulate Calculation of Year of working |
Leave Calculate Amount | Manipulate the Leave Calculation Amount |
Leave Calculate BF | Calculate how many leave can be Brought Forward if not fully taken |
Overtime Pay Rate | Manipulate the Overtime Pay rate |
Payroll Rounding | Manipulate the Net Pay Amount |
SOCSO Table | Manipulate the Sosco Deduction Amount |
Example 1 - Leave Entitlement Processor - Calculate from Confirm Date
Below is Example to change the Leave Entitlement Processor to be calculate from the Confirmation Date
Last Script Update : 15 Jan 2016
Level : Basic
Steps
- 01. Right Click at Leave Entitlement Processor
- 02. Select New Calculation
- 03. Click New Button
Formula | Description |
---|---|
Based On 365 Days Proportion, Round To 1 decimal place | Round to 1 Decimal point if Result had fraction |
Based On 365 Days Proportion, No Rounding | Truncate the Result |
Base On By Calendar Year, No Proportion, Rounding To 1 decimal place | Calculate by month & No Prorata (Base on Malaysia Labour Law) |
- 04. Select any Formula which suit you & Click OK
- 05. Enter any Description (eg Leave_Entitlement_Processor-Confirm Date) in the Description field (Only Alphanumeric & no spacing)
- 06. In the Script look for the word lJoinDate & change it to lConfirmDate or
Example 2 - Leave Calculate Amount - Unpaid Leave Amount Base on Working Day
Below is example to change the Unpaid Leave Amount to base on Working Days in Maintain Contribution
Last Script Update : 19 Jan 2016
Level : Basic
Steps
- 01. Right Click at Leave Calculate Amount
- 02. Select New Calculation
- 03. Click New Button
Formula | Description |
---|---|
Leave Calculate Amount - Based On Employee Default Wages | The Calculation will use Wages amount at Maintain Employee |
Leave Calculate Amount - Based On Employee Default Wages And Allowance | The Calculation will use Wages & Allowance amount at Maintain Employee |
- 04. Select any Formula which suit you & Click OK
- 05. Enter any Description (eg Leave_Calculate_Amt-Unpaid) in the Description field (Only Alphanumeric & no spacing)
- 06. In the Script look for the word DaysBetweenStartAndEndDate & change it to WorkingDayPerMonth or
- Copy below script & paste to the Script Section
Leave Calculate Amount - Based On Employee Default Wages |
---|
// Example - Leave Calculate Amount - Based On Employee Default Wages
uses DB, DateUtils;
begin
Amount := DefaultWages / WorkingDayPerMonth;
end.
|
- 07. Click Compile button to make sure is Compile success.
- 08. Click Save button
- 09. Click X to Close
- 10. Select the Item just created (eg Leave_Calculate_Amt-Unpaid)
- 11. Click Ok button
- 12. Done & Remember to set it at Maintain Leave Group under the Amount Column for UL
Example 3 - Limit Overtime Rate
Below is example to set Maximum limit the Overtime Rate.
Last Script Update : 21 Mar 2023
Level : Basic
Steps
- 01. Right Click at Overtime Pay Rate
- 02. Select New Calculation
- 03. Click New Button
- 04. Select any Formula which suit you & Click OK
- 05. Enter any Description (eg Overtime_Max_Rate) in the Description field (Only Alphanumeric & no spacing)
- Copy below script & paste to the Script Section
Overtime Max Rate |
---|
uses Math, DB;
var V: Double;
begin
V := 0;
if OTAmount > 1500.00 then
V := 10.82 else //(1500/26/8)
V := (OTAmount / 26 /8);
PayRate := SimpleRoundToEx(V, -2);
end.
|
- 06. Click Compile button to make sure is Compile success.
- 07. Click Save button
- 08. Click X to Close
- 09. Select the Item just created (eg Overtime_Max_Rate)
- 10. Click Ok button
- 11. Done & Remember to set it at Maintain Overtime
Example 4 -
- More coming soon...