Assignment 2 (SQL CC - Advance): Difference between revisions

From eStream Software
(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...")
 
 
(25 intermediate revisions by the same user not shown)
Line 4: Line 4:




===Assignment : Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price ===
===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)
* Create the DIY fields such as '''UDF_PCS''', '''UDF_CTN''' in Maintain Stock Item; and  
* Purpose: User will key-in the base unit price to convert into new unit price based on the UOM rate in Maintain Stock Item.
* '''UDF_Price''' in Sales Documents (eg. sales invoice).<br />
::[[File: 01 A1-Sales Invoice Entry-UDF mUnitPrice.jpg|740px]]
* 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 <br />
* Calculation for Unit Price := UDF_Price * UDF_Rate
<br />
<br />


* Calculation for Unit Price := UDF_mUnitPrice x 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.
::{| class="wikitable"
|-
! 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
|}
::[[File:02 A2-ST - ItemFields.jpg|640px]]
: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.
::{| class="wikitable"
|-
! 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
|}
::[[File:02 A2-SL - ItemFields.jpg|640px]]
:11. Click Save.
:12. Update operation successful message. Click OK.
:13. DONE.
<br />
 
===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'''.
::[[File:02 A2-ST - ItemFields-QuckForm1.jpg|240px]]
:05. Enter the new name as '''EXTRA'''. Click OK.
::[[File:02 A2-ST - ItemFields-QuckForm3.jpg|240px]]
: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.
::[[File:02 A2-ST - ItemFields-QuckForm.jpg|540px]]
:08. Both the UDF fields will be under the Root. Click CLOSE.
::[[File:02 A2-ST - ItemFields-QuckForm1.jpg|240px]]
:09. Click SAVE.
::[[File:02 A2-ST - ItemFields-QuckForm4.jpg|540px]]
:10. DONE.
<br />
<br />


===Steps===
====Insert DIY Script====
:01. Click '''Tools | DIY | SQL Control Center...'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice''' .
:02. At the left panel look for '''Sales Invoice''' .
Line 24: Line 79:
:07. Click OK.
:07. Click OK.
:08. Click the Calc (name create at Step 5 above) on the left panel.
:08. Click the Calc (name create at Step 5 above) on the left panel.
::[[File:02 A1-SL - OnGridColValChg-Calc.jpg|840px]]<br />
::[[File:02 A2-SL - OnGridColValChg-Calc.jpg|840px]]<br />
:09. Copy below script & paste to the Right Panel (Script Section).
:09. Copy below script & paste to the Right Panel (Script Section).
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
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
begin
    if SameText(EditingField, 'ItemCode')or
  if SameText(EditingField, 'ItemCode') or
      SameText(EditingField, 'UDF_mUnitPrice') then begin
    SameText(EditingField, 'UOM') or
            DataSet.FindField('UnitPrice').AsFloat :=
    SameText(EditingField, 'UDF_Rate') then begin
            DataSet.FindField('Rate').AsFloat * DataSet.FindField('UDF_mUnitPrice').AsFloat;
    try
 
      GetStockInfo;
    end;
      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.
end.
</syntaxhighlight>
</syntaxhighlight>
Line 42: Line 135:
|-
|-
| [[File:Template.Warning-01.jpg|80px]]||  
| [[File:Template.Warning-01.jpg|80px]]||  
* Avoid update the same existing field name ''Unit Price''. You have to create different name ie. UDF_mUnitPrice.
* Avoid update the same existing field name ''Unit Price'' and "Rate". You have to create different name ie. UDF_Price and UDF_Rate.
|}
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
:::----------------------------------------------------------------------------------------------------------------------------------------------------
Line 49: Line 142:
===Result Test===
===Result Test===
:01. Go to Stock | Maintain Stock Item...
:01. Go to Stock | Maintain Stock Item...
:02. Edit the "ANT" stock item.  
:02. Create a new item code called '''PEN'''.
:03. Insert additional UOM with different RATE, eg. '''1 CARTON = 12 UNITS''' and '''1 BOX = 60 UNITS'''.
:03. Update the '''UOM''' tab. See the screenshot below.
::[[File:03 A1-ST-MaintainStk Item.jpg|740px]]
::[[File:03 A2-ST-MaintainStk Item-UOM.jpg|740px]]
:04. Click on '''EXTRA''' tab.
:05. Input the '''UDF_CTN''' and '''UDF_PCS''' value.  
::[[File:03 A2-ST-MaintainStk Item-EXTRA.jpg|740px]]
<br />
<br />
:04. Create new sales invoice from Sales | Invoice...
:06. Create new sales invoice from Sales | Invoice...
:05. Call out the columns name '''UDF_mUnitPrice''' and '''Rate'''.
:05. Call out the columns name '''UDF_Price''' and '''UDF_Rate'''.
::[[File:03 A1-Sales Invoice Entry.jpg|940px]]
::[[File:03 A2-Sales Invoice Entry.jpg|940px]]
<br />
<br />
:06. Insert and select the item code "ANT".
:06. Insert and select the item code '''PEN'''.
:07. Change the UOM to '''CARTON'''. '''Rate''' will be changed to '''12'''.
:07. Select the '''UOM''' to '''CTN'''.
:08. Input the value into UDF_mUnitPrice. '''U/Price''' will be calculated from your DIY script formula '''(UDF_mUnitPrice x Rate)'''.  
:08. '''UDF_Price''' will be changed to '''50.00''' (based on the '''UDF_CTN''' set for '''PEN''').
::[[File:03 A1-Sales Invoice -Detail.jpg|640px]]
:08. Input the value into '''UDF_Rate'''. '''U/Price''' will be calculated from your DIY script formula '''(UDF_Price x UDF_Rate)'''.  
::[[File:03 A2-Sales Invoice Entry.jpg|640px]]
<br />
<br />

Latest revision as of 07:39, 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
02 A2-ST - ItemFields.jpg
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
02 A2-SL - ItemFields.jpg
11. Click Save.
12. Update operation successful message. Click OK.
13. DONE.


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.
02 A2-ST - ItemFields-QuckForm1.jpg
05. Enter the new name as EXTRA. Click OK.
02 A2-ST - ItemFields-QuckForm3.jpg
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.
02 A2-ST - ItemFields-QuckForm.jpg
08. Both the UDF fields will be under the Root. Click CLOSE.
02 A2-ST - ItemFields-QuckForm1.jpg
09. Click SAVE.
02 A2-ST - ItemFields-QuckForm4.jpg
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.
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 A2-SL - OnGridColValChg-Calc.jpg
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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • Avoid update the same existing field name Unit Price and "Rate". You have to create different name ie. UDF_Price and UDF_Rate.
----------------------------------------------------------------------------------------------------------------------------------------------------


Result Test

01. Go to Stock | Maintain Stock Item...
02. Create a new item code called PEN.
03. Update the UOM tab. See the screenshot below.
03 A2-ST-MaintainStk Item-UOM.jpg
04. Click on EXTRA tab.
05. Input the UDF_CTN and UDF_PCS value.
03 A2-ST-MaintainStk Item-EXTRA.jpg


06. Create new sales invoice from Sales | Invoice...
05. Call out the columns name UDF_Price and UDF_Rate.
03 A2-Sales Invoice Entry.jpg


06. Insert and select the item code PEN.
07. Select the UOM to CTN.
08. UDF_Price will be changed to 50.00 (based on the UDF_CTN set for PEN).
08. Input the value into UDF_Rate. U/Price will be calculated from your DIY script formula (UDF_Price x UDF_Rate).
03 A2-Sales Invoice Entry.jpg


Retrieved from "https://wiki.sql.com.my/index.php?title=Assignment_2_(SQL_CC_-_Advance)&oldid=2140"
Last modified
This page was last edited on 9 November 2015, at 07:39.