Dealing with many to many relationships...

Classic Servoy
1

Hi

I can see many examples of parent-to-child relations in Servoy. Like “customer to invoice”, “invoice to invoice lines” etc.

But in reality one can have many-to-many relationships. Like, for instance "Student to Teacher. Where a Student can have many Teachers; and a Teacher can have many Students. In this case you have to resolve the relationship with something like Teacher_Student_Usage. Where the table Teacher_Student_Usage contains nothing more than Teacher_ID and Student_ID - recording just the relationship. There is no point having a form based on the Teacher_Student_Usage table. But you would still want a Student Form with a sub-form showing Teachers and visa versa.

I can see relationships in Servoy are from ONE table to another… So, I am just looking for some ROUGH guidance on how to implement this type of relationship in Servoy… before I start messing around…

Thanks in advance.

Kind Regards
Jonathon

JonathonBevan

2

Jonathon,

Currently, databaseManager.convertFoundSet() provides this functionality, see http://wiki.servoy.com/display/public/D … rtFoundSet

We are planning to add more support for many-to-many relations natively in Servoy.

Regards,

Rob

3

+1

Great job for the Servoy crew

4

Actually I did something very simple to achieve the implementation of a many-to-many. I don’t know if I have done something wrong but it works perfectly.

I had a …

Teacher Table

Teacher ID
Teacher Name
etc…

Teacher_Tutor_Usage Table (that resolved the many to many)

Teacher ID
Tutor ID

Tutor Table

Tutor ID
Tutor Name
etc…

I had a form for Teacher…
Then I created an INVISIBLE form that handled the relationship of Teacher_To_Teacher_Tutor_Usage table…
Then I created the Tutor form using the relationship Tutor_To_Teacher_Usage_To_Tutor…

Well this all worked without coding… I was impressed with Servoy. Now I get (when I move from Teacher to Teacher) the appropriate Students for the Teacher and they are shown in a tab.

But to be honest I am new to Servoy and there may be something wrong with this. I will study the answers that have been sent to me… No doubt I should be doing this another way… :)