Assignment 3 (SQL CC - Advance): Difference between revisions

From eStream Software
 
(5 intermediate revisions by the same user not shown)
Line 16: Line 16:
::[[File:DIYField-03.jpg]]
::[[File:DIYField-03.jpg]]
:04. Select '''New Event'''.
:04. Select '''New Event'''.
::[[File:02 A3-SL - NewEvent-OnOpen.jpg]]
::[[File:02 A3-SL - NewEvent-OnOpen.jpg|240px]]
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing).
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing).
:06. Select '''OnOpen''' for '''Event''' field.
:06. Select '''OnOpen''' for '''Event''' field.
:07. Click OK.
:07. Click OK.
:08. Click the Calc (name create at Step 5 above) on the left panel.
:08. Click the Calc (name create at Step 5 above) on the left panel.
::[[File:02 A2-SL - OnGridColValChg-Calc.jpg|840px]]<br />
::[[File:02 A3-SL - OnOpenScript.jpg|840px]]<br />
:09. Copy below script & paste to the Right Panel (Script Section).
:09. Copy below script & paste to the Right Panel (Script Section).
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
Line 108: Line 108:
</syntaxhighlight>
</syntaxhighlight>
:10. Click '''Save''' button.
:10. Click '''Save''' button.
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Warning-01.jpg|80px]]||
* Avoid update the same existing field name ''Unit Price'' and "Rate". You have to create different name ie. UDF_Price and UDF_Rate.
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
<br />
<br />


===Result Test===
===Result Test===
:01. Go to Stock | Maintain Stock Item...
:01. Create a new sales invoice, eg. '''Invoice Amount = Rm1060.00'''
:02. Create a new item code called '''PEN'''.
::[[File:03 A3-SL - InvOS.jpg|740px]]
:03. Update the '''UOM''' tab. See the screenshot below.
:02. Make a '''payment amount = Rm636.00''' and knock-off with the invoice created in step 01.  
::[[File:03 A2-ST-MaintainStk Item-UOM.jpg|740px]]
::[[File:03 A3-AR - Pymt.jpg|740px]]
:04. Click on '''EXTRA''' tab.
:03. You can get the '''document outstanding balance''' (Rm1060.00 - Rm636.00 = '''Rm424.00''') at Sales Invoice.
:05. Input the '''UDF_CTN''' and '''UDF_PCS''' value.  
::[[File:03 A3-SL - InvOS2.jpg|740px]]
::[[File:03 A2-ST-MaintainStk Item-EXTRA.jpg|740px]]
<br />
:06. Create new sales invoice from Sales | Invoice...
:05. Call out the columns name '''UDF_Price''' and '''UDF_Rate'''.
::[[File:03 A2-Sales Invoice Entry.jpg|940px]]
<br />
:06. Insert and select the item code '''PEN'''.
:07. Select the '''UOM''' to '''CTN'''.
:08. '''UDF_Price''' will be changed to '''50.00''' (based on the '''UDF_CTN''' set for '''PEN''').
:08. Input the value into '''UDF_Rate'''. '''U/Price''' will be calculated from your DIY script formula '''(UDF_Price x UDF_Rate)'''.  
::[[File:03 A2-Sales Invoice Entry.jpg|640px]]
<br />
<br />

Latest revision as of 09:11, 9 November 2015

Update Info

Last Script Update : 09 Nov 2015
Level : Advance


Assignment : How to get Outstanding IV amount & show below the Browse button at Sales Invoice?

  • This assignment no need to create any DIY fields
  • Purpose is to display the total outstanding balance for the customer selected at the Invoice


Steps

Insert DIY Script

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice .
03. Right Click the Sales Invoice.
DIYField-03.jpg
04. Select New Event.
02 A3-SL - NewEvent-OnOpen.jpg
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.
02 A3-SL - OnOpenScript.jpg
09. Copy below script & paste to the Right Panel (Script Section).
var FComServer, lBizObj : Variant;
    C : TComponent;
    T : TTimer;
    M : TDataSource;
    L1, L2 : 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);
end;
 
procedure DocInfo;
var lSQL, lDocNo : String;
begin
  lDocNo := M.Dataset.FindField('DocNo').AsString;
 
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT (DocAmt - PaymentAmt) OS FROM AR_IV '+
                 'WHERE DocNo=%s ',[QuotedStr(lDocNo)]);
 
  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
    DocInfo;
    L2.Caption := '';
    try
      L2.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('OS').AsFloat); 
    finally
      cdsTemp.Free;
    end;
  end;       
end;
 
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));   
  C := Self.FindComponent('frDataSetButton1');             
 
  if Assigned(C) then begin
    T.Enabled  := True;
    T.Interval := 1000; // = 1 sec
    T.OnTimer  := @OnTimer;  
 
    with L1 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 200;
      Caption    := 'Outstanding';
      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;     
  end;   
end.
10. Click Save button.


Result Test

01. Create a new sales invoice, eg. Invoice Amount = Rm1060.00
03 A3-SL - InvOS.jpg
02. Make a payment amount = Rm636.00 and knock-off with the invoice created in step 01.
03 A3-AR - Pymt.jpg
03. You can get the document outstanding balance (Rm1060.00 - Rm636.00 = Rm424.00) at Sales Invoice.
03 A3-SL - InvOS2.jpg