OraFAQ Forum: Forms » Master/detail tables, restricting adding in detail table

Home » Developer & Programmer » Forms » Master/detail tables, restricting adding in detail table (Oracle FORMS 6)

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

Master/detail tables, restricting adding in detail table [message #328494]

Fri, 20 June 2008 05:54

averno9
Messages: 30
Registered: June 2008
Location: Spain

Member

Hello everyone.

I have 3 tables, PERSON, EMPLOYEE and CUSTOMER, where PERSON is the master table and EMPLOYEE and CUSTOMER are detail tables. A person can be only once an employee, a customer, or both.

So, my problem resides in I can't let the user add more than one employee (or customer) for one person.

Well, I tried this. I made 3 triggers, for the events KEY-NXTREC (used when we push the 'next record' button), KEY-CREREC (used when pushed 'create record' button) and KEY-DOWN (used when we strike the down key), in the block for employee. (in customer would be the same, so i explain it only with employee)

What i do in these events, (in all of them) is to check if the person of the master table is an employee. If it is, i cancel the trigger, so nothing happens and nothing can be added.

Ok, it's working fine, but this is not a good solution, because if, for example, someone creates a shortcut to insert a register, this triggers won't activate and you will be able to add more than one employee to one person.

I have been looking for some event or trigger, wich is fired when you try to insert something, but what i found is when you are gonna make the commit. (ON-INSERT)

Any ideas? Thanks for the help and sorry for my english.

Report message to a moderator

Re: Master/detail tables, restricting adding in detail table [message #328517 is a reply to message #328494]

Fri, 20 June 2008 06:42

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

Senior Member
Account Moderator

Did you try to use WHEN-VALIDATE-ITEM trigger instead?

Report message to a moderator

Re: Master/detail tables, restricting adding in detail table [message #328528 is a reply to message #328494]

Fri, 20 June 2008 07:03

averno9
Messages: 30
Registered: June 2008
Location: Spain

Member

Yes, that's what i tried in first instance, sorry for not mentioning it. It won't work for what i want, because that event raises when you are going to make commit, and i want to cut it before that. Example:

I have this:

ID_EMPLOYEE SALARY
BLANK BLANK

I push insert, and i put some input:

ID_EMPLOYEE SALARY
1 100

Ok, what i want to do, is if you push now insert, down key, or whatever, it doesn't let you put any input, just appears a warning message. If i use WHEN-VALIDATE-ITEM, of course it works, but if i want it to work i have to put some input. And in every case, this input will be wrong.

That's why i used 3 events, to prevent inserting with insert, down key and next register key. But that's not a good idea, i was just desperate to finish it.

Thanks for your reply.

Report message to a moderator

Re: Master/detail tables, restricting adding in detail table [message #328576 is a reply to message #328494]

Fri, 20 June 2008 09:27

Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium

Member

write a when-new-record-instance on your detail block :

if get_block_property('<detail_block>', current_record) > 1 
  and <masterblock.item> = 'employee' then
	message('only 1 record allowed per customer');
	previous_record;
end if;

it's not failsafe, if a user now goes into enter-query mode in your detail block, then cancels the query, he will now be able to insert another one Smile

to prevent this I would write a pre-insert on your detail block
that checks the db to see if there is already a record

hope this helps

Report message to a moderator

Re: Master/detail tables, restricting adding in detail table [message #328605 is a reply to message #328576]

Fri, 20 June 2008 15:00

averno9
Messages: 30
Registered: June 2008
Location: Spain

Member

This sounds fantastic! I'm at home now, I'll prove it on monday morning at work, but i think it's done. I'll give feedback when i've finished it, thanks a lot!! Smile

Report message to a moderator

Re: Master/detail tables, restricting adding in detail table [message #329148 is a reply to message #328494]

Tue, 24 June 2008 04:56

averno9
Messages: 30
Registered: June 2008
Location: Spain

Member

Hi again. As you said Martin, with when-new-record-instance i finally did it, the user can't even try to add anything on employee, if there exists one employee, thanks a lot again!.

But, as you said too, if the user insert one new employee, then he can insert as many as he want, because when-new-record-instance occurs just once in this case. But the pre-insert don't work too here, because it won't happen until the program make the commit, so the user will be able to put input that will always be wrong.

My boss finally told me, to leave it to the DB, if the user inserts more than one, when he tries to save it, (commit) an error message appears and forces you to erase records until you have only one, so there is no need to finish the program as i was trying.

But i'm still intrigued, i'll keep on it until i find a solution. If i find any, i'll post it, thanks to everyone who read this post.

PS: i didn't want to post another post, i would have prefered to edit the last, but i don't know how to do that in this forum, sorry

Report message to a moderator

Previous Topic:	Transfer Control
Next Topic:	How to connect 10g db with 10g forms & report?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 23:13:05 CST 2025