Many-To-Many relationships


The Many-To-Many panel may be used for a child panel when a child table has a many-to-many relationship in the database. There are three tables involved in the relationship, two of which are parent and one is child. This panel will be generated in place of the usual child panel when any one of the parents is used to create a page.


The following pages allow the generation of a Many -To- Many child panel

-          Edit Record

-          Enhanced with Detail Below

-          Group By

-          Show Record


One can control the generation of the Many-To-Many panel and  generate One-To-Many instead using the application generation option for “Select which panel to generate”. This option is availabe for all the page types mentioned earlier. Generation of the child panel will depend on both the option setting and the relationship of the child table. If there is no Many-To-Many relationship for the child table, One-To-Many will be generated.




How it works:


Using an example from the SouthWind sample database, consider the Roles and SalesRep tables, which have SalesRepRoles as a child table. When an EditRoles page is generated, it will have the SalesRep table in a Many-To-Many panel instead of the usual SalesRepRoles, however records will be saved in the SalesRepRoles table. 



The idea is to show both the parent tables of a Many-To-Many child table. The Many-To-Many child table stores the two foreign key values of the parents. Thus the user can select the check box and click the save button, which will save the two pimary keys as a record in the Many-To-Many table.


The panel has two internal variables which records all the data selected/unselected so the user does not have to save the record on pagination. These hidden variables will be used when save button is clicked.

A Many-To-Many panel has its own save button, which can be use to save just this panel.


Selection Filter:


This panel consist of a static list filter with two initial values: ShowAll is the default selected option, and ShowIncluded only shows selected record or related record to the parent. In the case of an EditRoles page, ShowIncluded selection will show all the SalesRep who belongs to this Role.



Formula tab for the Filter


The user can go to the formula tab for the filter in the Layout Editor and rename the values or set the initialize value to ShowIncluded.


Initially the filter type is dropdown, however it can be converted to checkbox from the property sheet or generated as Checkbox out of the box by changing the Many-To-Many Option “Selection filter type”.