Assignment 2 (SQL CC - Advance)

From eStream Software
Revision as of 03:26, 9 November 2015 by Csloo (talk | contribs) (Created page with "===Update Info=== Last Script Update : 09 Nov 2015<br /> Level : Advance ===Assignment : Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price === * Cre...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Update Info

Last Script Update : 09 Nov 2015
Level : Advance


Assignment : Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price

  • Create the DIY fields such as UDF_PCS', UDF_CTN in Maintain Stock Item and UDF_Price 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.
01 A1-Sales Invoice Entry-UDF mUnitPrice.jpg


  • 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.
DIYField-03.jpg
04. Select New Event.
DIYScript-01.jpg
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.
02 A1-SL - OnGridColValChg-Calc.jpg
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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • 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.
03 A1-ST-MaintainStk Item.jpg


04. Create new sales invoice from Sales | Invoice...
05. Call out the columns name UDF_mUnitPrice and Rate.
03 A1-Sales Invoice Entry.jpg


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).
03 A1-Sales Invoice -Detail.jpg