Home » Developer & Programmer » Forms » prevent duplication on the form (window7)
prevent duplication on the form [message #662420] Mon, 01 May 2017 11:29 Go to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
i have tabular form and want to check values entering on then form before insert how can do this validation in trigger
when-validate-item
Re: prevent duplication on the form [message #662421 is a reply to message #662420] Mon, 01 May 2017 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
why not just place UNIQUE constraint on the table column?
Re: prevent duplication on the form [message #662426 is a reply to message #662421] Mon, 01 May 2017 14:21 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
do that but after commit ora message appeared but i want check it before commit
Re: prevent duplication on the form [message #662433 is a reply to message #662426] Tue, 02 May 2017 03:47 Go to previous messageGo to next message
shawaj
Messages: 81
Registered: January 2016
Member
provide detailed structure of your problem ,i am not getting what you want ??
Re: prevent duplication on the form [message #662434 is a reply to message #662433] Tue, 02 May 2017 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
The way I do that is to put post in the When-new-record-instance trigger. This tells forms to issue the appropriate insert/update to the DB but not commit it.
Then you can just write a select to check what you need in WVI.
Re: prevent duplication on the form [message #662482 is a reply to message #662434] Wed, 03 May 2017 09:20 Go to previous messageGo to next message
shawaj
Messages: 81
Registered: January 2016
Member
hello hassan08,

You can try like this code
declare
	v_last number;
	v_first number;
	v_empno number;
	v_current number;
begin
	v_current := :system.cursor_record;
	v_empno := :empno;
	if :system.cursor_record > 1 then
		first_record;
	while ( :system.cursor_record < v_current) loop
		if :empno = v_empno then 
			message('This is duplicate record');
			go_record(v_current);
			delete_record;
		end if;
		exit when :system.last_record='true';
		next_record;		
	end loop;	
	else
		go_record(v_current);
	end if;
	next_item;
exception when others then
	null;
end;
*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Wed, 03 May 2017 12:55] by Moderator

Report message to a moderator

Re: prevent duplication on the form [message #662486 is a reply to message #662482] Wed, 03 May 2017 10:21 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?
And indent your code
And never ever write exception when others then null. Ever. See WHEN OTHERS

What trigger did you plan for the OP to put this code in? Cause it won't run from when-validate-item
Re: prevent duplication on the form [message #662489 is a reply to message #662486] Wed, 03 May 2017 12:37 Go to previous messageGo to next message
shawaj
Messages: 81
Registered: January 2016
Member
yes ,i have read already .
my code is formatted by toad but when i past here it removes all formatting ...i do not know why its happening


I was try on When-Key-Next item

[Updated on: Wed, 03 May 2017 12:40]

Report message to a moderator

Re: prevent duplication on the form [message #662490 is a reply to message #662489] Wed, 03 May 2017 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my code is formatted by toad but when i past here it removes all formatting ...i do not know why its happening
Just read How to use [code] tags and make your code easier to read.

Re: prevent duplication on the form [message #662491 is a reply to message #662489] Wed, 03 May 2017 14:37 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't test it, but - if it works as described - it is supposed to prevent duplicates that currently reside in data block. What about values that already exist in a table?

shawaj

I was try on When-Key-Next item
What happens if user uses a mouse to navigate through the form?
Re: prevent duplication on the form [message #662493 is a reply to message #662491] Wed, 03 May 2017 15:06 Go to previous messageGo to next message
shawaj
Messages: 81
Registered: January 2016
Member
Thanks Michel ,
Yes, as per requirement i suggest this code. This will prevent duplication on data block not for database table.
If we want to prevent duplication at database table level then we have to use constraints.

Here we talking about "prevent duplication on the form"

and absolutely you are right, it will not work if user use mouse navigate through the form ,then you have to choose appropriate trigger
Thanks Littlefoot
Re: prevent duplication on the form [message #662498 is a reply to message #662493] Thu, 04 May 2017 02:54 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
The only appropriate triggers are ones that don't allow restricted procedures - when-validate-item, when-validate-record, pre-insert and pre-update.
So your code just won't work. My initial suggestion will.

And if you really read the page on how to use code tags, why didn't you use them?
Re: prevent duplication on the form [message #662693 is a reply to message #662498] Tue, 09 May 2017 12:51 Go to previous messageGo to next message
shawaj
Messages: 81
Registered: January 2016
Member
ok, i am satisfied from you and i will try any other possible solution

Thanks cookiemonster
Re: prevent duplication on the form [message #662697 is a reply to message #662693] Tue, 09 May 2017 14:25 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
thanks all
but how can navigation to the same item in block
example
i have 2 duplicated value how can navigation to first value because this is not duplicated and clear second value
Re: prevent duplication on the form [message #662698 is a reply to message #662697] Tue, 09 May 2017 15:21 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, you want to navigate to the second value and DELETE_RECORD. However, as you already are on the second value (once you found it), just delete it.
Re: prevent duplication on the form [message #662699 is a reply to message #662698] Tue, 09 May 2017 15:26 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
but the sequence of the values
example
1
2
3
1
how can navigate to first value 1
another example
if values like this
2
4
3
4
how can to second value 4 in second
Re: prevent duplication on the form [message #662702 is a reply to message #662699] Wed, 10 May 2017 00:12 Go to previous message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

how can navigate to first value 1
But, why would you return to the first "1"? As far as I understood, you are now in row #4 whose value is "1" and you know that it is a duplicate. So delete it; why do you want to delete the "1" that resides in row #1?

The same goes for your next example.
Previous Topic: Update Field if the Checkbox is Checked
Next Topic: How to check file exist in folder
Goto Forum:
  


Current Time: Sat Jan 18 17:09:10 CST 2020