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

Revision as of 03:11, 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 from your DIY script formula (UDF_mUnitPrice x Rate).

@@ Line 55: / Line 55: @@
 :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|840px]]
+::[[File:03 A1-Sales Invoice Entry.jpg|940px]]
 <br />
 :06. Insert and select the item code "ANT".