Difference between revisions of "Assignment 2 (SQL CC - Advance)"

From eStream Software
Jump to: navigation, search
(Steps)
(Insert DIY Field)
Line 20: Line 20:
 
:02. At the left panel look for '''Sales Invoice'''.
 
:02. At the left panel look for '''Sales Invoice'''.
 
:03. Point to Items Fields.
 
:03. Point to Items Fields.
:04. On the right panel, insert the DIY field as per the screenshot below.
+
:04. On the right panel, insert the new field as per the screenshot below.
 
::[[File:02 A1-SL - ItemFields.jpg|640px]]
 
::[[File:02 A1-SL - ItemFields.jpg|640px]]
 
:05. Click Save.  
 
:05. Click Save.  

Revision as of 04:46, 9 November 2015

Update Info

Last Script Update : 09 Nov 2015
Level : Advance


Assignment : How to get the data from Master table into entry form?

  • Create the DIY fields such as UDF_PCS, UDF_CTN in Maintain Stock Item; and
  • UDF_Price in Sales Documents (eg. sales invoice).
  • Purpose is:
 Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price
 # If selected itemcode UOM is PCS then use UDF_PCS
 # if selected itemcode UOM is CTN then use UDF_CTN 
 # if selected itemcode UOM not PCS or CTN then default is 1 
  • Calculation for Unit Price := UDF_Price * UDF_Rate


Steps

Insert DIY Field

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice.
03. Point to Items Fields.
04. On the right panel, insert the new field as per the screenshot below.
02 A1-SL - ItemFields.jpg
05. Click Save.
06. Update operation successful message. Click OK.


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.
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