Home » Developer & Programmer » Forms » Problem with using an LOV in form for insertion of data. (Oracle10g Forms)
Problem with using an LOV in form for insertion of data. [message #329259] Tue, 24 June 2008 14:18 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,

I have a Form for a table named A1
CREATE TABLE A1
(
  REV_TYPE_ID       NUMBER(4)                   NOT NULL,
  REV_TYPE          VARCHAR2(60 BYTE)           NOT NULL,
  ROLLUP1           VARCHAR2(60 BYTE),
  ROLLUP2           VARCHAR2(60 BYTE),
  REV_ACCOUNT_ID    NUMBER,
  CREATE_DATE       DATE,
  CREATED_BY        CHAR(6 BYTE),
  LAST_UPDATE_DATE  DATE,
  LAST_UPDATED_BY   CHAR(6 BYTE),
  ATTRIBUTE5        VARCHAR2(120 BYTE)
)


and a table B1
CREATE TABLE B1
(
  REV_ACCOUNT_ID      NUMBER(15)                NOT NULL,
  REV_ACCOUNT_NUMBER  VARCHAR2(20 BYTE)         NOT NULL,
  REV_ACCOUNT_NAME    VARCHAR2(80 BYTE),
  CREATED_BY          VARCHAR2(10 BYTE),
  CREATE_DATE         DATE,
  LAST_UPDATED_BY     VARCHAR2(10 BYTE),
  LAST_UPDATE_DATE    DATE
)


In table A1
Quote:
REV_TYPE_ID is the primary key

REV_ACCOUNT_ID is the foreign key that references
to REV_ACCOUNT_ID in B1.

ATTRIBUTE5 is combination of columns
REV_ACCOUNT_NUMBER and REV_ACCOUNT_NAME in table B1.


In table B2
Quote:
REV_ACCOUNT_ID is the primary key.


Now I have created a form for table A1. If a user needs to enter a new record into A1 then the LOV should show the user
Quote:
REV_ACCOUNT_NUMBER ||' - '||REV_ACCOUNT_NAME

which is similar to ATTRIBUTE5 in table A1.

Once the value is selected from LOV it should be posted and saved into the base table after all the mandatory values are filled up.

Now my doubts (or) question is :
1. What would be my SQL Query Statement? (in LOV Wizard)
Is it...
SELECT DISTINCT B1.REV_ACCOUNT_ID, 
B1.REV_ACCOUNT_NUMBER||' - ' ||B1.REV_ACCOUNT_NAME AS ACCOUNT_NAME
FROM B1 ;


2. What are the LOV columns required?

3.What are the return values to be specified?

4.What should be the assigned items?

5.Once the selection is made the values should be inserted into A1 and at the time of insertion I need to insert a sequence value into the column REV_TYPE_ID ( this column will be made invisible in the form built).


I am also sending the sample data so that it might be of some use to help me in sorting this problem. Hope you would help me in soving this problem.
Thanks and regards,
Vamsi K Gummadi.
Re: Problem with using an LOV in form for insertion of data. [message #329280 is a reply to message #329259] Tue, 24 June 2008 22:25 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Cant u send your sample then we can.

1. Query for lov it is ok

2. Both column you can

3. It may be Account_name as per requirement

4. you should assign on young text Attribut on TableA.


kanish
Re: Problem with using an LOV in form for insertion of data. [message #329476 is a reply to message #329259] Wed, 25 June 2008 09:17 Go to previous messageGo to next message
cjack
Messages: 22
Registered: June 2008
Junior Member
Creating LOV, you can refer blow steps.

1. Right-click the lovs in object navigator, show a sub-menu
2. Chose the lov wizard, show a new lov wizard screen
3. Select "new record group based on query", and click next button, show a new screen
4. Typing your sql in sql query statement, and click next button
5. Chose all columns which you require ex. "Type Identifier" ,"Type"(chose the colomn name in left list and click the button '>', it will be add to right list), then click next button, show a new screen.
6. This step is most important, as it will defind return value and columns relation, you will see a grid in the top of this screen, you will see a colomn in the grid named "return value", this "return value" is used to defind the value will be return to which fields, click the "return value", then clikc then "look up return item.." button, will be show a list, you can chose the fields which you want to return value from LOV, then click next button, show a new screen.
7. Type title, width and height which you need, then click next button
8. click next button
9. Move items which you want to show lov in it from left list to right list.
10. click next finish button
Re: Problem with using an LOV in form for insertion of data. [message #329841 is a reply to message #329259] Thu, 26 June 2008 13:02 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
Thank you for your clarifications and tips. I am able to solve this problem.My Thanks to Kanish and Jack. As Kanish requested I too am interested in sending the sample but due to some restrictions I can't send you the exact replica and hence I forwarded the table scripts and data with a few modifications. Anyways you helped me in overcoming this problem. Hope this continues in future also.
Regards,
Vamsi K Gummadi.
Previous Topic: get column sum if its records are checked
Next Topic: LOV
Goto Forum:
  


Current Time: Thu Dec 08 00:37:01 CST 2016

Total time taken to generate the page: 0.10168 seconds