Servoy Forum

Hi all,

I'm not sure why this is but when I do a foundset.relation1.relation2 (from table 1 to table 2 to table3) I only get the first record from table 3 showing. Normal SQL inner joins would give me multiple records. The relationships are:

Table 1 to Table 2: One to Many
Table 2 to Table 3 : Many to Many

Any ideas on why this might be?

Hi,
Effectively, when you use many to many relations, you will get Just the first record of the primary table and its related from the secondary table.

In this case, I suggest you to get your foundset by Query.

akaash wrote:I'm not sure why this is but when I do a foundset.relation1.relation2 (from table 1 to table 2 to table3) I only get the first record from table 3 showing. Normal SQL inner joins would give me multiple records. The relationships are:

Table 1 to Table 2: One to Many
Table 2 to Table 3 : Many to Many

Any ideas on why this might be?

Use this:

Code: Select all

databaseManager.convertFoundSet(foundset.relation1, 'relation2')


https://wiki.servoy.com/display/public/ ... rtFoundSet

Thanks a lot for the replies! I'll try using the convert foundset. Although it would be great if servoy supported many-to-many relationships in the same ways SQL does.

akaash wrote:Thanks a lot for the replies! I'll try using the convert foundset. Although it would be great if servoy supported many-to-many relationships in the same ways SQL does.

Indeed. You can try voting for SVY-324.

akaash wrote:Thanks a lot for the replies! I'll try using the convert foundset. Although it would be great if servoy supported many-to-many relationships in the same ways SQL does.

I doubt this will happen as this will break a lot of existing solutions.

The way many to many relationships are handled is like I posted.
You can also use the query builder, which will most probably work this way and will provide you db-vendor independent way of writing queries.
But still this is more work than using the convert foundset way...