| Designing form - Please help [message #83202] |
Thu, 21 August 2003 01:40  |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
Suppose i have a table named "Discount"
with following colums
Pack varchar2(20)
Start_date date
end_date date
Value number
and suppose this table contains some rows showing various products/packs , their discount amount and the starting and end date of discount period.
Now i want to design my form in such a way that , when the user selects some pack , then it should automatically show all the rows corresponing to that very pack.
==============================================
http://geocities.com/katyal_rinku/discount.zip
===============================================
The way the form should look like , please download the zip file from above link , it contains one bmp file.
I mean , user need not manually go to enter/execute query.
Just when he presses tab after selecting a pack , it should show all the rows pertaining to that pack.
Please help.Thanks in advance.
Katyal.
|
|
|
|
| Re: Designing form - Please help [message #83203 is a reply to message #83202] |
Thu, 21 August 2003 02:45  |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
Katyal,
You can try this method :-
1) Create a control block (Non Database block = CTRL)
Depot :
Product Line :
Product Pack :
2) Create a database block (i.e. Datasource Name = DISCOUNT)
Product Pack : <Hidden Database field>
Start Date : <Displayed database field>
End Date : <Displayed database field>
..
..
..
3) In the block property (Block = DISCOUNT), code the following :
Query Data Source Type : Table
Query Data Source Name : DISCOUNT
DML Data Target Type : Table
DML Data Target Name : DISCOUNT
Where Clause :
product_pack = :CTRL.product_pack
4) Create a "Key-Next-Item" on the product_pack field
in the control block
Block : CTRL
Field : product_pack
Trigger_Name : KEY-NEXT_ITEM
Trigger Code
------------------------------
Go_Block ('DISCOUNT');
Clear_Block (No_Validate);
Execute_Query;
First_Record;
|
|
|
|
| Re: Designing form - Please help [message #83216 is a reply to message #83203] |
Fri, 22 August 2003 02:17 |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
Dear Shailender,
Firstly thanks a lot , i am able to do it now.
Although i didn't mention that i am using forms 4.5 , so i had to change only "where clause " property, it is working perfectly.
But i have further 2 queries.Actually since last 1 year i am into in sql's only and not in D2k Front End , So i need your help.
Consider Following 2 Tables
=========Table 1: Discount============
Pack_Pack_code varchar2(6)
Start_date date
end_date date
Value number
========Table 2:Product_Pack============
Pack_code varchar2(6)
pack_desc varchar2(20)
product_code varchar2(5)
product_desc varchar2(20)
And the structure is like this :
Actually there are various products and then different packs under them.
e.g.
Product Butter ( Product_code=Btr ) contains 3 packs
pack_code pack_desc product_code product_desc
-------------------------------------------------
abt01s Btr 10gm btr Butter
b100ss Btr 100gm btr Butter
b500ss Btr 500gm btr Butter
ch001s Cheese100 che Cheese
ch002s Cheese200 che Cheese
=====================================
Now the requirement is
=====================================
When the user selects some product it should show all the packs details ( from discount table ) pertaning to that product only.
e.g. if he selects "btr" then it should show discount details of following three packs only.
abt01s
b100ss
b500ss
================================
Now i want to know the following
===============================
1.How to code relation of product_pack table with discount table ( Common fields are pack_code and pack_pack_code )
2.In my first post , i had asked the way to show the pack_code from discount table.
Now i want it should show pack_desc instead of pack_code.
Do we need to use record groups , or some thing else is required.
Please help.
Regards,
Katyal
|
|
|
|
| Re: Designing form - Please help [message #83226 is a reply to message #83216] |
Sun, 24 August 2003 19:07 |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
Katyal,
I'm using D2K ver6i. The solution which i'm suggesting should also
be available in Forms v4.5 :-
1) Use the DATA BLOCK WIZARD to create the blocks in the form
i.e. Product_Pack (This will be the Master
block)
followed by,
using the same data block wizrd to create the detail block
i.e. Discount
2) Create Relationship when creating Database block DISCOUNT
using Database block wizard
NOTE :- Make sure that you select pack_code from both the tables when creating data blocks on forms as pack_code will be used to join both the tables.
Create a Join Condition (Using Data Block Wizard),
Product_Pack.Pack_Code = Discount.Pack_Pack_Code
3) When creating blocks using the DATA BLOCK WIZARD, choose whatever
columns you require from both the tables.
i.e Since you want pack_desc to be displayed instead of pack_code
use the property palette to hide the pack_code column.
Set the Physical -> Visible -> No (i.e. Set the property to No
for pack_code).
Shailender
|
|
|
|
| Re: Designing form - Please help [message #83230 is a reply to message #83226] |
Mon, 25 August 2003 02:49 |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
Dear Shailender,
I need some more help.
--> 1)What format is required
what should be the format of the form ..please download from the following link
http://geocities.com/katyal_rinku/new.zip
--> 2)What i have done
(A) Created 3 blocks
prod_block(no base table block)
mssa_discount(base table discount)
desc (base table mssav_product_pack)
(B) have created one trigger "key-next-item" on product_pack field of prod_block which populates all the packs in mssa_discont block(as guided by you earlier and it is working fine)
(C) now to show the pack description , have created one more block named "desc"
(D) created one relation among mssa_discount and desc block i.e.
mssa_discount.sbp_Sbp_code=desc.sbp_code
as i interate through the 2nd block it shows me the pack description in 3rd block i.e. desc block.
--> 3)Problem Part
now the problem is that all the time it displays only 1 record in "desc" block. Instead of it , it should show multiple records.
i have uploaded my .fmb at following link
http://geocities.com/katyal_rinku/new.zip
Please see , if you can help further.
Regards,
Rajeev Katyal
|
|
|
|
| Re: Designing form - Please help [message #83239 is a reply to message #83230] |
Tue, 26 August 2003 03:18 |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
Hi Katyal,
I was busy lately and was not able to look into your request.
If by now, you are still facing the problem, I suggest you start
from fresh.
Here's your request.
You have 2 tables (described below). For a given product, you want to
display all pack_code,start_date,end_date,value from discount table and
pack_desc from the discount table - Correct.
=========Table 1: Discount============
Pack_Pack_code varchar2(6)
Start_date date
end_date date
Value number
========Table 2:Product_Pack============
Pack_code varchar2(6)
pack_desc varchar2(20)
product_code varchar2(5)
product_desc varchar2(20)
************************************************
Try this way out,
************************************************
1) Create a control block (Header)- This will not be based on any table
Block = CONTROL_BLOCK
This table will have 2 columns
1) Product_code - Displayed non-database column
2) Poduct_Desc - Displayed non-database column
You may have to validate the item (product_code) by LOV.
2) Create a DETAIL block on table : product_pack
Table : product_pack
This block will have the following columns :
1) pack_code Displayed and Database field
2) pack_desc Displayed and database field
3) product_code database field and not displayed
4) start date displayed and non-database field
5) end date displayed and non-database field
6) amount displayed and non-database field
3) In the block property of DETAIL block which is based on product_pack table
set the WHERE condition for query,
Where product_code = :CONTROL_block.product_code
4) Write a KEY-NEXT-ITEM trigger on CONTROL_BLOCK.product_code item
Trigger Text :
Go_Block ('DETAIL');
Clear_Block (No_Validate);
Execute_Query;
First_Record;
5) Create a POST-QUERY trigger on DETAIL block
Trigger : POST-QUERY
Trigger Text :
Select start_date
,end_date
,value
Into :detail_block.start_date
,:detail_block.end_date
,:detail_block.amount
From discount
Where pack_pack_code = :DETAIL.pack_code;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
