company → departments → contacts. Where departments and contacts are linked by a link table dep_cont to accomplish a n.m relation.
I know how to use this relation when creating records but the problem seems to be in showing the records. I use one form frm_company with dataprovider Company. By using the relation company_departments all departments (related to the selected company) are also shown on the frm_company form and using custom buttons with navigation methods in the onaction event I can browse through the departments. But now I am trying to show all contacts per (selected) department on the same form and I want to be able to browse through them also. So I use the multi level relation company_departments.departments_dep_cont.contacts_dep_cont but only one contact is shown on the frm_company.
Is this by design using n.m relations or am i doing something wrong?
company → departments → contacts. Where departments and contacts are linked by a link table dep_cont to accomplish a n.m relation.
I know how to use this relation when creating records but the problem seems to be in showing the records. I use one form frm_company with dataprovider Company. By using the relation company_departments all departments (related to the selected company) are also shown on the frm_company form and using custom buttons with navigation methods in the onaction event I can browse through the departments. But now I am trying to show all contacts per (selected) department on the same form and I want to be able to browse through them also. So I use the multi level relation company_departments.departments_dep_cont.contacts_dep_cont but only one contact is shown on the frm_company.
Is this by design using n.m relations or am i doing something wrong?
Thanks in advance,
Regards,
Rick van Dongen
Relationships in a chain always assume the selected index of the relationship previous in the chain is 1. To illustrate using company > departments > contacts:
1- company_to_departments returns a list of departments.
2- departments_to_contacts returns a list of contacts.
3- company_to_departments.departments_to_contacts returns a list contacts for the first department records returned from the company_to_departments relation. No matter what department record is selected in the UI.
There are two main ways to display chained relationships. The first way is to use a “stack” of related forms. The goal is to break up a chain of relations into one relation per form. In this way the left side of a relationship is always tied directly to the selected index of the form it is used from:
1- Start with top level company form
2- Show second level departments form via a tab panel on company form through relation company_to_departments.
3- Show third level contacts form via a tab panel on departments form through relation departments_to_contacts.
The second way is to move the above third level form to the second level and explicitly capture what department record is currently selected:
1- Capture the selected id_departments into a global variable (on the record select event of departments form).
2- Create a new relation company_to_contacts_by_selected_department. company.id_company = contacts.company and globals.id_department_selected = contacts.id_department.
3- Now show the previous third level contacts form on the top level company form via a tab panel through this new global relation.
With these concepts in mind, now we can tackle n-m situations. In your case:
company > departments > dep_cont
contacts > dep_cont
Leaving off for now the multiple UI approaches you can employ to add contacts to departments, displaying a list of dep_cont records on a company record can be done with either of the two methods described above. And nothing special to list dep_cont records on a contacts form.
Another easy way to do this is to create a self-join on contacts where id_department = id_department, then do company_to_departments.departments_to_contacts.contacts_to_contacts_self_join. That will give you all contacts n-m.
Another approach is to have 2 related tabpanels on you main (company) form.
First tabpanel (company_to_departments) shows departments.
Second tabpanel (company_to_departments.departments_to_contacts) show the contacts of the selected department in the first tabpanel.
rgansevles:
Second tabpanel (company_to_departments.departments_to_contacts) show the contacts of the selected department in the first tabpanel.
Didn’t make the connection when the ability to chain relations in the UI came out that selected indexes in the relation chain were now “honored”. No more global variables and global relations to track selected indexes in multi-panel forms. Just tested it out – cool. Thanks for that.
jbader:
Another easy way to do this is to create a self-join on contacts where id_department = id_department, then do company_to_departments.departments_to_contacts.contacts_to_contacts_self_join. That will give you all contacts n-m.
Thanks for your reply. But I don’t understand the self-join. I don’t have a id_department column in contacts. Because of the n.m relation de contact_id en departement_id are related by the connection table departments_to_contacts. So I have a department_id in de department table and in the departments_to_contacts table. Can you please explain?
rgansevles:
Another approach is to have 2 related tabpanels on you main (company) form.
First tabpanel (company_to_departments) shows departments.
Second tabpanel (company_to_departments.departments_to_contacts) show the contacts of the selected department in the first tabpanel.
Rob
Bug with this approach: chained relations on UI elements throw errors if no records up the chain when form is first loaded. In this example, error is thrown if no page or no area records:
Error setting the foundset of the relation web_page_to_area.web_area_to_block on the tab with form WEB_0F_page__design__content_1L_block
> java.lang.NullPointerException
=PARTLY FIXED, PARTLY WORKARROUND=
Showing the N.M relation is fixed now by using subform in subform in subform in subform. So company form contains tab with department form and department form contains empty tab with with linktable form but is empty with just a navigator and the link form contains a tab with contactsform. So now we can overview company, department and contacts where contacts and departments are connected by a link table. And we are able to navigate through the contacts per department by using the navigator on the empty link table form.
On the other hand we are now able to use valuelists for this departement N.M contacts relation by using a trigger when selecting department to dynamically constructing the contacts valuelist using a query.
I could not reproduce that, can you create a small sample and file a case with this?
Rob
Just set out to create a sample solution based on our testing last week and I also can’t reproduce now. Troy and I spent over an hour on this and thought we had it narrowed down pretty solid. Oh well, I’ll keep the one major test page we were using flipped to the new way and see if it pops up again.