Implementing many-to-many relationships in Oracle Forms 5

Posted on 2000-02-06
Medium Priority
Last Modified: 2008-01-09
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@hotmail.com


Pardeep Nagra

Question by:pardeep_nagra
  • 3
  • 2
  • 2

Author Comment

ID: 2495045
Please, please, please can someone help??

Expert Comment

ID: 2495342
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.

Expert Comment

ID: 2495350
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.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Author Comment

ID: 2495872
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...

Accepted Solution

crsankar earned 200 total points
ID: 2496006
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.


Author Comment

ID: 2500037
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??

email: pardeep_nagra@hotmail.com

Expert Comment

ID: 2500138
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.


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question