Implementing many-to-many relationships in Oracle Forms 5

What is the best way to implement a many-to-many relationship in Oracle Forms 5?

I'll tell you what I have already tried... Ok so you got 3 tables altogether, relationship is one to many and many to one.

I tried using the built in JOIN and making the intermediary table the child of both other tables, however, although when it comes to populating this child table with values it populates them on the screen at run-time, when I try and save the record it does not insert the values into the database... I have tried everything, the data target property for the table is set to the table. I am so confused as to why it does not insert these values into the database, especially as it displays them in the Form...

Is this a bug in Forms 5???

I also tried implementing it by manually inserting the values into the intermediary table each time the parent primary keys were entered, and although this gives me the functionality I want, I get problems when trying to hide the intermediary table on the screen as I get an error message as it cannot see the items for the intermediary table...

Am I making a mountain out of a mole hill??? Is there a simple way to do this?? Could you send me some sample code for this???

Also I want to add an online Help facility into my menu system for the Forms... What is the best way to do this?? How do you create a form on which you display, say, the contents of the help document and then when the user clicks on the keyword it navigates to the relevant section?? Could you give me some example code for this??

I am writing a Change Request Application for British Telecom as my Final year University project... anyone got any ideas on how to make it an 'A' grade project?? In terms of functionality how can I demonstrate that I can do some real expert stuff in Forms?? Any help would be much appreciated...

Thank you for your help...

Could you email me with all your replies on:


Pardeep Nagra

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pardeep_nagraAuthor Commented:
Please, please, please can someone help??
When you are trying to insert the record what error message are you getting? Choose Help -> Display Error to see the actual statement that was issued. Designing a form like this is simple infact. But, let's start by looking at the error. If you could give the table structures I will be able to send you an fmb file for you.
as crsankar pointed out , post the error message.

Note: If the answers are sent by email, then the pupose of this site is lost. So i would appreciate if all the replies to this thread are posted here.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pardeep_nagraAuthor Commented:
There is no error message when I try to insert the data... infact that is the strange thing, that's why I don't understand why it doesn't insert the data, it inserts data into other tables in other blocks on he same form, but does not insert this data for the intermediary form...
As for sending the data structure, the tables are:

Staff_Member   Staff_allocation Module
------------   ---------------- ------
Staff_Id       Staff_Id         Module_Id
Name           Module_Id        Name
etc.             etc.            etc.

(Staff_Id and Module_Id are primary keys)

Could you please send an FMB file to show how to implement this? I asked one of the Forms developers at BT about this and he suggested I should insert values into the intermediary table manually by an assignement step in a trigger eg:

:Staff_allocation.Staff_Id := :Staff_member.Staff_Id;

:Staff_allocation.Module_Id := :Module.Module_Id;

But I am sure it is possible to implement using the Forms Wizard and by using a JOIN between the tables... but it doesn't seem to inser the the values into the database for some reason...

If you send the FMB could you test to see it works before sending it...
I am assuming that you want to have one form with all the three blocks. Two master blocks and one child block. The child block should be related to both the master blocks. Yes, it is possible to do it this way.

Here what I have done is

1. Create Both the master blocks i.e, Staff, Module

2. Create the child block (staff allocation). while creating the block specify the master relation ship . Specify the master block as Staff.

3. After creating the child block create a new relationship in your other master block, Module. When you create the relationship specify the master block as module and the child block as staff_allocation. You will have to type in the relation ship manually.

I have tried this and this works. I have succesfully inserted records into both the masters and the child table from the same form. You will have to take care of how you want to control querying and all.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pardeep_nagraAuthor Commented:
CRSANKAR Could you please email me the file you created for the sample form?? It would be of great help to see how it should actually look...

I know you are not supposed to email replies direct on this site as it takes away the pupose of this great aid, but if you could email me the forms file it would be of great use... I urgently need to finish the forms due to my deadline for the project...

Have you got any ideas on my other queries regarding the way to implement an online help facility in Forms 5??

Also, the way in which I could make the project more detailed to obtain a higher grade?? What are some oof the ways in which you could make Forms 5 more complex??

emailing the form kind of files is fine, but when your work is done, post a comment as to how you got the problem solved.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.