Assignment 1 (SQL CC - Basic): Difference between revisions

Revision as of 02:32, 9 November 2015

Update Info

Last Script Update : 09 Nov 2015
Level : Basic

Assignment : To Create the Unit Price Calculation

Create a UDF_mUnitPrice DIY fields in Sales documents (eg. sales invoice)
Purpose: User will key-in the base unit price to convert into new unit price based on the UOM rate in Maintain Stock Item.

Calculation for Unit Price := UDF_mUnitPrice x Rate

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)

begin
    if SameText(EditingField, 'ItemCode')or
       SameText(EditingField, 'UDF_mUnitPrice') then begin
            DataSet.FindField('UnitPrice').AsFloat :=
            DataSet.FindField('Rate').AsFloat * DataSet.FindField('UDF_mUnitPrice').AsFloat;

    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

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

@@ Line 24: / Line 24: @@
 :07. Click OK
 :08. Click the Calc (name create at Step 5 above) on the left panel
-::[[File:02 SL - OnGridColValChg-Calc.jpg|740px]]<br />
+::[[File:02 SL - OnGridColValChg-Calc.jpg|840px]]<br />
 :09. Copy below script & paste to the Right Panel (Script Section)
 <syntaxhighlight lang="delphi">
-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;
+     if SameText(EditingField, 'ItemCode')or
-  cdsData    := TClientDataSet.Create(nil); // Create & preparing Component
+       SameText(EditingField, 'UDF_mUnitPrice') then begin
-  try
+            DataSet.FindField('UnitPrice').AsFloat :=
-    lCode := Dataset.FindField('ItemCode').AsString;
+            DataSet.FindField('Rate').AsFloat * DataSet.FindField('UDF_mUnitPrice').AsFloat;
-    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.
 </syntaxhighlight>