OraFAQ Forum: Forms » Selected value in lov should not dispalyed agin in lov.

Home » Developer & Programmer » Forms » Selected value in lov should not dispalyed agin in lov. (Form 10g.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Selected value in lov should not dispalyed agin in lov. [message #488293]

Thu, 06 January 2011 03:56

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

Hi friends,
may be chances of posted question somebody asked earlier..but i stil did not get correct answer. SO i Posted questiona agin.

My Requirement is that i create a form , on empno field i created lov (Select * from emp) , when i select value from this value, same value should not be displayed again on that lov.

how can i achieve that...

I send u my attached form also

Attachment: Lov_pblm.fmb
(Size: 48.00KB, Downloaded 1223 times)

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488296 is a reply to message #488293]

Thu, 06 January 2011 04:28

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Post each record as it is entered - use when-new-record-instance trigger. Change the LOV query to exclude values already that exist in the table the block is based on.

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488297 is a reply to message #488296]

Thu, 06 January 2011 04:29

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

Thanks for logic.

But sir, can u give me query...

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488298 is a reply to message #488297]

Thu, 06 January 2011 04:38

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

What query?

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488299 is a reply to message #488298]

Thu, 06 January 2011 04:40

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

Code of that logic...

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488300 is a reply to message #488299]

Thu, 06 January 2011 04:43

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

You mean the LOV?
Just add a not in clause to exclude empnos that exist in the table the block is based on - there's nothing complicated about it, it really is that simple.

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488301 is a reply to message #488300]

Thu, 06 January 2011 04:49

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

You mean i write code for lov like this

select * from emp
where empno not in (select empno from emp where empno = 7369)

but it works for only one selection...
what about no. of empno if i selected...

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488303 is a reply to message #488301]

Thu, 06 January 2011 04:59

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

OK I've looked at your form. Your block is based on emp. Your LOV is based on emp.
What on earth are you actually trying to accomplish here?
Cause if you exclude records that have been added to emp from the LOV that would leave you with an empty LOV.

I assumed you meant the standard problem where somebody adds a empno to a record in a table other than emp and then wants to stop that value being used in any other record on that other table.

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488304 is a reply to message #488303]

Thu, 06 January 2011 05:11

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

Thanks you for your valuable suggestion..

Sir, i have a req., suppose i have no of item displayed data block.

when i come to first record of that data block and press double click. my lov pop up.
I select any of empno e.g. lets say empno = 7369, then
second time when i press lov all record shown except empno = 7369.

second time i select empno = 7499 ,

when third time my lov pop up all record except 7369 & 7499.

i hope i well enough explain requirement..

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488305 is a reply to message #488304]

Thu, 06 January 2011 05:18

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Is this to query data? Why would you stop a user looking at the same record multiple times?

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488306 is a reply to message #488305]

Thu, 06 January 2011 05:21

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

Because its project requirement.

Once user select a record, next time he cant select same record again on data block...by using this we avoid duplication of record selection..

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488307 is a reply to message #488306]

Thu, 06 January 2011 05:27

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

And that's a good thing?
What happens if they make a mistake and need to go back? This will just cause problems in the long run.

Add the selected value to a global temp table. Use that temp table in the not in clause in the LOV query.

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488311 is a reply to message #488307]

Thu, 06 January 2011 05:36

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

okie..

You mean i create

create global temp table dup_empno(empno number(10));
on commit delete rows;

Once i select value from lov.
whatever selected value insert into dup_empno table like this
insert into dup_empno values (:selected_values);

i write query like this,

select * from emp where empno not in (select * from dup_empno);

Is i am doing any mistake ???

Sir, but is there another way to avoid this....like populate_group_with_query function or etc. function ??

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #488313 is a reply to message #488311]

Thu, 06 January 2011 05:43

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the users are allowed to modify data then it'll have to be on commit preserve rows or it'll be useless.

For populate_group_with_query to work you need a query that excludes all the empnos you've already looked at. To do that you have to store them somewhere. A global temp table is easiest since you can reference it direct in the LOV query.

You could store them in an array in the form but then you'd have to read out the contents of the array, parse it into a comma seperated string, concatenate that string into the query, and then repopulate the LOV every time.

Report message to a moderator

Re: Selected value in lov should not dispalyed agin in lov. [message #575023 is a reply to message #488293]

Thu, 17 January 2013 23:28

sandeepgujje
Messages: 28
Registered: January 2012
Location: India

Junior Member

Hi,

I guess, you would have solved this.

But attaching the form with the solution, so that it will be useful for others..

before running the form, just create a table like


CREATE TABLE EMP_LOV_TEMP
(
EMPNO NUMBER,
ENAME VARCHAR2(40)
);

Hope this helps.

-
Sandy

Attachment: DYN_LOV_VALUES.fmb
(Size: 64.00KB, Downloaded 1231 times)

Report message to a moderator

Previous Topic:	Error not display
Next Topic:	Data Retrieval

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 20 06:26:22 CDT 2025