OraFAQ Forum: Forms » Repeat items

Home » Developer & Programmer » Forms » Repeat items (form developer 6i second release)

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

Repeat items [message #299146]

Sun, 10 February 2008 01:45

ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq

Member

Dear All

I have a form one to many
in the many type there are these fields
id,item_type,qty,price

The ID will be already repeated because it is the foreign key
but i want if the item_type is repeated in the same invoice an error message will appear to tell him the item code has been entered before in the same invoice (item_code can not be repeated in the same invoice)

best wishes

Report message to a moderator

Re: Repeat items [message #299161 is a reply to message #299146]

Sun, 10 February 2008 04:11

bbaz
Messages: 138
Registered: April 2007

Senior Member

Hi there,

I think that your question is very Vague.

What do you mean One-to-Many relation in your form? Is this a Master-detail Form?

Please provide some screen shots and elaborate more on your problem so that you get the desired response from this forum.

Goodluck,
Baz

Report message to a moderator

Re: Repeat items [message #299187 is a reply to message #299146]

Sun, 10 February 2008 09:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why would you bother? Let Oracle do its job!

Create unique index on (invoice (is it the 'id' column?), item_type).

Report message to a moderator

Re: Repeat items [message #299228 is a reply to message #299146]

Sun, 10 February 2008 23:33

ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq

Member

ok no problem with that
but i want to display a message to tell the user there is a problem of repeating the same item_code

best regards

Report message to a moderator

Re: Repeat items [message #299251 is a reply to message #299228]

Mon, 11 February 2008 00:54

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Search this forum for 'on-error', there are plenty of threads with the solution.

David

Report message to a moderator

Re: Repeat items [message #299315 is a reply to message #299146]

Mon, 11 February 2008 07:05

ashraf_al_ani
Messages: 92
Registered: October 2007
Location: Iraq

Member

Dear All

I searched yet and didnt ind any thing
pls I will explain my problem again
i have a details table and its fields are (id,item_code,qty)
(id) is foreign key so it will be repeated with any record according to the master table
the field (id)and (item_code) are combined key

for example

id item_code Qty
1 1 5 (ok)
1 2 4 (ok)
1 1(message will apear telling him that he cant write the item_code again
1 4 100 (ok)

please some one help me ,waiting for your advice

best regards

Report message to a moderator

Re: Repeat items [message #299326 is a reply to message #299146]

Mon, 11 February 2008 07:44

dillango
Messages: 145
Registered: January 2008

Senior Member

Mr.Ashraf,

I understand from your question that when the user enters the same item in the current form (Tabular form?) it should display an error message.. If it so.. please use the below syntax
and apply in When_validate_item trigger.

declare
mrec number := :system.cursor_record;
mval varchar2(40) := your fieldname
begin
First_record;
loop
if :desig_desc = mval AND mrec <> :SYSTEM.CURSOR_RECORD THEN
message('Same code already entered');
go_record(mrec);
raise form_trigger_failure;
end if;
exit when :system.last_record = 'TRUE';
next_Record;
end loop;
end;

Report message to a moderator

Re: Repeat items [message #299682 is a reply to message #299326]

Tue, 12 February 2008 23:33

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

If you are happy to trap the Oracle message, then use the on-error trigger at the block level. If you want to do the validation earlier then you need a method wherein you do not try to navigate out of the When-Validate-Item trigger. The method promoted by many people in this forum is that you build a record_group each time you validate your special field and then 'loop' through it to see whether a value has been used previously.

Look at:
Unique key during data entry
http://www.orafaq.com/forum/m/201107/67467/?srch=create+record+group#msg_201107
also
http://www.orafaq.com/forum/m/147107/67467/?srch=validate+master+detail+record+group#msg_147107

David

Report message to a moderator