Many to Many relationshiip

Classic Servoy
1

I want to use a combo box to list the “industry sectors” that a company belongs to. I am completely stumped on how to get this to work.

“Industry Sectors” to “Companies” is a many-to-many relationship so I have set up following tables

Company Table (PK is companyID)
Sector table (PK is sectorID)
Company_Sector Table, (where the PK is compsecID and there are two foreign keys - CompanyID and sectorID).

I then joined the tables by linking the PKs in the Company and Sector tables with the FKs in the compsec table.

In the company form I want to use a combo field to show a list of the sectors that a particular company belongs to.

So far I can get either

  1. The complete list of sectors
  2. One sector that is related to the company.

I can’t seem to get more than 1 sectors related to a particular company record.

I’m a newbie… where am I going wrong? Many thanks.

MerMer

2

Hi MerMer,

I would create a tiny form (list view) based on the company_sector table.
Create an “New” button in the header part which adds a record to the list and attaches it to the company record (may look something like this, but the names may be different)…

controller.newRecord();
company_id = forms.company_form.company_id

Add a combobox in the body part, with a value list showing sectors names, but setting the sector id in your sector-fk field.

Place this form in a tabpanel on your company form.

Hope this helps,

3

Christain,

Many thanks. I can see how your solution would work.

For the record, I did manage to get the combo box to list the sectors for a specific company by creating using the Value List box and using the N-M drop down option.

However, while the combo box displays the list it only does so when I click on the combo box down arrow, otherwise the combo box looks like it is empty.

MerMer