Home » Developer & Programmer » Forms » How to restrict user not to enter duplicate Record? (Forms 6i)
How to restrict user not to enter duplicate Record? [message #633542] Fri, 20 February 2015 23:11 Go to next message
manisngpl
Messages: 42
Registered: December 2014
Location: Faisalabad, Pakistan
Member

Dear Members,

I have create voucher form picture attached in Account No. field I would like to restrict the user not to enter duplicate records in each invoice.

Please help me in the matter.
  • Attachment: problem.jpg
    (Size: 84.84KB, Downloaded 871 times)
Re: How to restrict user not to enter duplicate Record? [message #633543 is a reply to message #633542] Fri, 20 February 2015 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
UNIQUE constraint prevents duplicates
Re: How to restrict user not to enter duplicate Record? [message #633544 is a reply to message #633543] Fri, 20 February 2015 23:46 Go to previous messageGo to next message
manisngpl
Messages: 42
Registered: December 2014
Location: Faisalabad, Pakistan
Member

I think you haven't studied my attached Picture. I have to maintain vouchers but detail in multiple voucher may be duplicated but in each voucher it should not be duplicate.
Re: How to restrict user not to enter duplicate Record? [message #633545 is a reply to message #633544] Sat, 21 February 2015 00:30 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

Try to find out the count of records on the basis of new input and check there values in column wheather they are already existing or not .
for ex. there are 3 columns in a row.
column 1 has value A, Column 2 has value B, Column 3 has value C.
and user entered A in column 1 , B in column 2 , C in column 3 . then in that case he will not be able to save the record else he will be able to.
you can use count function here to prevent the duplicacy of record.

Quote:
I think you haven't studied my attached Picture.


& One more thing You are not paying anyone here to help you , so if someone is trying to help you then you are supposed to respect him/her & Most Imp the senior members definitely.

Thanks.

[Updated on: Sat, 21 February 2015 00:39]

Report message to a moderator

Re: How to restrict user not to enter duplicate Record? [message #633557 is a reply to message #633545] Sat, 21 February 2015 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What's wrong with the UNIQUE constraint? It works well in detail data blocks, not only masters. You just have to create one.

As of you, preventing users from entering duplicates, perhaps you'd want to review previous discussions. That page contains search results for "prevent duplicate" in our Forms forum; feel free to change search criteria in order to find new/better results.

One of the most popular solutions is to POST changes you've made in the detail block, and then - upon entering new values - check whether it already exists in the database.
Re: How to restrict user not to enter duplicate Record? [message #633559 is a reply to message #633557] Sat, 21 February 2015 06:27 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@lf , he has not mentioned anywhere he is working on master detail block or not.
i have faced same problem , that time i also used same logic which worked for me.
Re: How to restrict user not to enter duplicate Record? [message #633562 is a reply to message #633559] Sat, 21 February 2015 08:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
jgjeetu wrote on Sat, 21 February 2015 13:27

@lf , he has not mentioned anywhere he is working on master detail block or not.


This sentence means that you didn't bother to view the image attached to the initial message.
Re: How to restrict user not to enter duplicate Record? [message #633598 is a reply to message #633562] Mon, 23 February 2015 00:52 Go to previous messageGo to next message
manisngpl
Messages: 42
Registered: December 2014
Location: Faisalabad, Pakistan
Member

Hello jgjeetu & littlefoot,

First of all I apologize for using of Harsh words in my sentences. Moreover, I will be thankful to all of you who are helping me in the matter.

Same like invoice at General Store each item must be entered at once and quantity may be varied. Same issue is here I would like to restrict the user not to enter multiple accounts in the same voucher.

Keeping in view the littlefoot suggestion the use of unique key is not possible as it will restrict the user not to enter account no (accno) as it is already Exists in database. But the invoice in new so the solution may not working.

I would like to restrict the user at Form Level that if the account no is already entered in detail then multiple entries in the same may not be allowed.

Regards,

Usman
Re: How to restrict user not to enter duplicate Record? [message #633599 is a reply to message #633598] Mon, 23 February 2015 01:04 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unique index doesn't have to be restricted to only one column - it can be a composite index, which consists of more columns. One of these would be a foreign key column, another one would be ACCNO from the details table. Something like this:
create table master
(id_master    number        constraint pk_master primary key,
 some_column  varchar2(20),
 ...
);

create table detail
(id_detail    number        constraint pk_detail primary key,
 id_master    number        constraint fk_det_mas references master(id_master),
 accno        number        not null,
 some_column  varchar2(10),
 ...
);

I think that you think that unique index must look like this:
create unique index ui1_detail on detail (accno);
and you are worried that you won't even be able to create one, as duplicates already exist. That's OK, no problem.

That's why you have to (and can) create a composite index, such as
create unique index ui1_detail on detail (id_master, accno);


Got it?
Re: How to restrict user not to enter duplicate Record? [message #633609 is a reply to message #633599] Mon, 23 February 2015 01:40 Go to previous messageGo to next message
manisngpl
Messages: 42
Registered: December 2014
Location: Faisalabad, Pakistan
Member

Thanks Littlefoot its working.....

But I would like to display message that duplicate found how can I do this when user enter multiple records before saving the voucher.
Re: How to restrict user not to enter duplicate Record? [message #633610 is a reply to message #633609] Mon, 23 February 2015 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, did you review previous discussions (I posted a link in a message #633557)? Did you read and understand what I wrote in that message?
Re: How to restrict user not to enter duplicate Record? [message #633674 is a reply to message #633562] Tue, 24 February 2015 03:46 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@LF I apologize for the wrong statement, actually i dint notice "Master/Detail" Header. I just kept an eye over it and made my assumption & provided solution according to that.
Re: How to restrict user not to enter duplicate Record? [message #633944 is a reply to message #633674] Sat, 28 February 2015 14:12 Go to previous message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
This is how i do it. Get guidance from here. Its easy to immplement

https://community.oracle.com/message/9821808#9821808
Previous Topic: Hot key to switch between open forms
Next Topic: attendence entry
Goto Forum:
  


Current Time: Thu Mar 28 08:17:58 CDT 2024