Many-to-Many Relationships

Classic Servoy
1

I’m evaluating Servoy for developing applications that would normally have been done in Swing & a middleware layer. We have used Filemaker in the past with much success, however have always met it’s limitations.

My question is: how to define many-to-many relationships?

Thanks,

A.

2

Hi Adam,

Many to many relations isn’t supported yet, but is definitely on our requested features list.
(as well as operators… keyfieldA >= keyfieldB etc..)

3

Is there an update on when many to many relationships will be supported in Servoy or have I missed this update ?

Otherwise are there any workarounds which will allow me to get similar benefits/results ?

Example tables :

  1. Organisations
  2. Members
  3. People
  4. Addresses

Tables 1, 2 & 3 above can all have none, 1 or more addresses
I want to create my join tables to sit between each of the main tables and the address table as follows :

Organisations -< org_address >- Addresses
Members -< member_address >- Addresses
People -< people_address >- Addresses

Thus, for example, on a form showing an Organisation I would like to display related address records from the addresses table via the join table.

Thanks
Harry

4

check [u]this[/u] out

5

Thanks, Jan

I will read this through thoroughly.

At first glance it does not seem to give me exactly what I require, though - which is to be able to scan through records in Organisation in Form view and show multiple addresses (e.g. Office, Accounts, Registered address etc) for each organisation record through a portal or tabpanel !

Harry

6

I read through the linked paper by Jan and I was correct in that it does not allow me to achieve what I need to be able to do.

I already had the handling of related record and join record creation resolved in the same format which Jan described.

It is the display of relative data which is still the problem.

I still need to be able to show related addresses in a form view of Organisations through a portal or tabpanel !

In FileMaker I would perform one of a variety of processes depending upon solution structure etc in order to show my addresses :

e.g.

  1. FileMaker up to V6.x did not support data tunneling and so if you wanted to have address data displayed through a join file you would need to create calculated fields inside the jojn file which resolve the related address data and then you show a portal in Organisations from the join file and display the calculated field results
  2. I would run a scripted loop which collected the serial ids of the address records and placed them into a global field in the Organisation table which then had a direct relation to addresses (this would create a multiline key which was a return delimited list of the serial ids)

I could attempt to do one of the above within Servoy, but am unsure of which type of technique is best suited to the Servoy application or what other options I have to choose from.

I am also toying with the idea of using a nonrelated tabpanel and performing a find in the addresses file to isolate the related address records and showing the found set from addresses within the tabpanel.

So many possibilities for a relative Servoy newcomer !

Can anyone offer any advice on which would be the best way to achieve the required result !?

Thanks
Harry

dumpservoy.zip (14.3 KB)

7

I don’t see the issue. If you create a listview in the middle table and display that in a tabpanel on the main form does that not show what you want to see?

8

With these three tables existing :

Organisations -< org_address >- Addresses

The middle table, ‘org_address’, contains three fields :

org_address_id // the table own unique serial
org_id // the Organisation serial id
address_id //The Addresses table serial id

The addresses table contains the physiacal address data as in :
MaJic House
Swanwick Walk
Milton Keynes

In the Organisation table I want to display a list of the physical addresses as above and not just the address_id field which is all that I can reference from the ‘org_address’ table.

I hope that I am explaining myself a little more clearly

9

Hi Jan,

I don’t see the issue. If you create a listview in the middle table and display that in a tabpanel on the main form does that not show what you want to see?

A thousand apologies.

I have just re-read through your reply and tried this and it works !

Chalk this down to my inexperience and sorry to waste bandwidth !

Sometimes it is difficult to see the Servoy forest for the FileMaker trees !!

If I may ask one more potentially stupid question :
‘Does this mean that Servoy DOES support n-m relations now or is there a limit to what you can do ?’

Cheers
Harry

10

Harry Catharell:
If I may ask one more potentially stupid question :
‘Does this mean that Servoy DOES support n-m relations now or is there a limit to what you can do ?’

Servoy supports n-m relations and by using tab-panels you can go to any level even n-n-n-n-n-n-n-m (or deeper), as n-m is frequently used we are investigating in even easier ways to apply it in development. For example the auto creation of rows in the ‘middle’ table is on our list.

ServoySlider.zip (5.41 KB)