| Line 6: | Line 6: | ||
===Assignment : How to get the data from Master table into entry form? === | ===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 | * Create the DIY fields such as '''UDF_PCS''', '''UDF_CTN''' in Maintain Stock Item; and | ||
* '''UDF_Price''' in Sales Documents (eg. sales invoice) | * '''UDF_Price''' in Sales Documents (eg. sales invoice).<br /> | ||
<br /> | |||
* Purpose is: | * Purpose is: | ||
Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price | Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price | ||
Revision as of 04:34, 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
- 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).
