Home » Developer & Programmer » Forms » Master/detail tables, restricting adding in detail table (Oracle FORMS 6)
Master/detail tables, restricting adding in detail table [message #328494] Fri, 20 June 2008 05:54 Go to next message
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.
Re: Master/detail tables, restricting adding in detail table [message #328517 is a reply to message #328494] Fri, 20 June 2008 06:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to use WHEN-VALIDATE-ITEM trigger instead?
Re: Master/detail tables, restricting adding in detail table [message #328528 is a reply to message #328494] Fri, 20 June 2008 07:03 Go to previous messageGo to next message
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.
Re: Master/detail tables, restricting adding in detail table [message #328576 is a reply to message #328494] Fri, 20 June 2008 09:27 Go to previous messageGo to next message
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
icon10.gif  Re: Master/detail tables, restricting adding in detail table [message #328605 is a reply to message #328576] Fri, 20 June 2008 15:00 Go to previous messageGo to next message
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
Re: Master/detail tables, restricting adding in detail table [message #329148 is a reply to message #328494] Tue, 24 June 2008 04:56 Go to previous message
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
Previous Topic: Transfer Control
Next Topic: How to connect 10g db with 10g forms & report?
Goto Forum:
  


Current Time: Fri Dec 09 11:59:02 CST 2016

Total time taken to generate the page: 0.08135 seconds