Line 55: | Line 55: | ||
:03. Right click on te Stock Item. | :03. Right click on te Stock Item. | ||
:04. Select '''New Quick Form Design'''. | :04. Select '''New Quick Form Design'''. | ||
::[[File:02 A2-ST - ItemFields-QuckForm1.jpg| | ::[[File:02 A2-ST - ItemFields-QuckForm1.jpg|240px]] | ||
:05. Enter the new name as '''EXTRA'''. Click OK. | :05. Enter the new name as '''EXTRA'''. Click OK. | ||
::[[File:02 A2-ST - ItemFields-QuckForm3.jpg| | ::[[File:02 A2-ST - ItemFields-QuckForm3.jpg|240px]] | ||
:06. Click on the EXTRA follow by Customize button. | :06. Click on the EXTRA follow by Customize button. | ||
:07. Drag the UDF_CTN adn UDF_PCS from right to the place marked '''X'''. See the screenshot below. | :07. Drag the UDF_CTN adn UDF_PCS from right to the place marked '''X'''. See the screenshot below. | ||
::[[File:02 A2-ST - ItemFields-QuckForm.jpg| | ::[[File:02 A2-ST - ItemFields-QuckForm.jpg|240px]] | ||
:08. Both the UDF fields will be under the Root. Click CLOSE. | :08. Both the UDF fields will be under the Root. Click CLOSE. | ||
::[[File:02 A2-ST - ItemFields-QuckForm1.jpg| | ::[[File:02 A2-ST - ItemFields-QuckForm1.jpg|240px]] | ||
:09. Click SAVE. | :09. Click SAVE. | ||
::[[File:02 A2-ST - ItemFields-QuckForm4.jpg| | ::[[File:02 A2-ST - ItemFields-QuckForm4.jpg|240px]] | ||
:10. DONE. | :10. DONE. | ||
<br /> | <br /> |
Revision as of 07:19, 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 Stock | Stock Item .
- 03. Point to Stock Items Fields.
- 04. On the right panel, insert the new field as per the table below.
Name Data Type Size SubSize Caption Required Default Value Display Format PCS Float 10 2 UDF_PCS FALSE (Untick) BLANK #,0.00;-#,0.00 CTN Float 10 2 UDF_CTN FALSE (Untick) BLANK #,0.00;-#,0.00
- 05. Click Save.
- 06. Update operation successful message. Click OK.
- 07. Next...
- 08. At the left panel look for Sales | Sales Invoice.
- 09. Point to Items Fields.
- 10. On the right panel, insert the new field as per the table below.
Name Data Type Size SubSize Caption Required Default Value Display Format Price Float 10 2 UDF_Price FALSE (Untick) BLANK #,0.00;-#,0.00 Rate Float 10 2 UDF_Rate FALSE (Untick) BLANK #,0.00;-#,0.00
Create Quick Form
- 01. Click Tools | DIY | SQL Control Center...
- 02. At the left panel look for Stock | Stock Item .
- 03. Right click on te Stock Item.
- 04. Select New Quick Form Design.
- 05. Enter the new name as EXTRA. Click OK.
- 06. Click on the EXTRA follow by Customize button.
- 07. Drag the UDF_CTN adn UDF_PCS from right to the place marked X. See the screenshot below.
- 08. Both the UDF fields will be under the Root. Click CLOSE.
- 09. Click SAVE.
- 10. DONE.
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.
- 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).
var FComServer, lBizObj : Variant;
cdsData : TClientDataSet;
function ComServer: Variant;
begin
if FComServer = Null then begin
FComServer := CreateOleObject('SQLAcc.BizApp');
end;
Result := FComServer;
end;
procedure GetStockInfo;
var lSQL, lCode : String;
begin
FComServer := null;
cdsData := TClientDataSet.Create(nil); // Create & preparing Component
try
lCode := Dataset.FindField('ItemCode').AsString;
lSQL := Format('SELECT UDF_PCS, UDF_CTN FROM ST_ITEM WHERE Code=%s',[QuotedStr(lCode)]);
cdsData.Data := ComServer.DBManager.Execute(lSQL);
finally
lBizObj := null;
FComServer := null;
end;
end;
begin
if SameText(EditingField, 'ItemCode') or
SameText(EditingField, 'UOM') or
SameText(EditingField, 'UDF_Rate') then begin
try
GetStockInfo;
if Dataset.FindField('UOM').AsString = 'PCS' then
Dataset.FindField('UDF_Price').AsFloat := cdsData.FindField('UDF_PCS').AsFloat else
if Dataset.FindField('UOM').AsString = 'CTN' then
Dataset.FindField('UDF_Price').AsFloat := cdsData.FindField('UDF_CTN').AsFloat else
Dataset.FindField('UDF_Price').AsFloat := 1;
Dataset.FindField('UnitPrice').AsFloat := Dataset.FindField('UDF_Price').AsFloat *
Dataset.FindField('UDF_Rate').AsFloat;
finally
cdsData.Free;
end;
end;
end.
- 10. Click Save button.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
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).