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

Revision as of 03:07, 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 the same existing field name Unit Price. You have to create different name ie. UDF_mUnitPrice

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

Result Test

01. Go to Stock | Maintain Stock Item...

02. Edit the "ANT" stock item.

03. Insert additional UOM with different RATE, eg. 1 CARTON = 12 UNITS and 1 BOX = 60 UNITS.

04. Create new sales invoice from Sales | Invoice...

05. Call out the columns name UDF_mUnitPrice and Rate.

06. Insert and select the item code "ANT".

07. Change the UOM to CARTON. Rate will be changed to 12.

08. Input the value into UDF_mUnitPrice. U/Price will be calculated based on the your DIY script formula.

@@ Line 54: / Line 54: @@
 :04. Create new sales invoice from Sales | Invoice...
 :05. Call out the columns name '''UDF_mUnitPrice''' and '''Rate'''.
-::[[File:03 A1-Sales Invoice Entry.jpg|740px]]
+::[[File:03 A1-Sales Invoice Entry.jpg|840px]]
 <br />
 :06. Insert and select the item code "ANT".
 :07. Change the UOM to ''CARTON''. ''Rate'' will be changed to ''12''.
 :08. Input the value into UDF_mUnitPrice. ''U/Price'' will be calculated based on the your DIY script formula.
-::[[File:03 A1-Sales Invoice -Detail.jpg|540px]]
+::[[File:03 A1-Sales Invoice -Detail.jpg|640px]]
 <br />