Log In    |    Knowledge Base    |    4D Home
Tech Tip: How to handle 4D v11 SQL's changes to RELATE ONE SELECTION
Published On: May 28, 2009

It is noted in the conversion documentation for 4D v11 SQL that the behavior of RELATE ONE SELECTION has changed. In 4D 2004 and previous versions this command followed the "first" relation path that it could find. 4D would follow the field order and check each field in the Many table and see if it could follow a relation path to the One table. For example take a look at the following structure:

In 4D 2004 the relation path for RELATE ONE SELECTION from Table_3 to Table_2 would have been [Table_3]Field_1 to [Table_1]Field_2, then [Table_1]Field_3 to [Table_2]Field_2. In 4D v11 SQL the path is instead the shortest path, not the first path. In this case that would be [Table_3]Field_2 to [Table_2]Field_3.

To reproduce the old behavior in 4D v11 SQL you can use mutliple calls to RELATE ONE SELECTION as shown here:

RELATE ONE SELECTION([Table_3];[Table_1])
RELATE ONE SELECTION([Table_1];[Table_2])

Commented by Josh Fletcher on May 28, 2009 at 5:48 PM
All relation operations in 4D v11 SQL are affected by this change, not just this one command. See