Servoy Forum

I have created a relation
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 ^||= table2.field3
table1.field4 ^||= table2.field4

if for example table1.field4 and table2.field4 are both null then the relation doesn't find any data.

However, with the same data

if ( table1.find()) {
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 = '^||=' + table2.field3
table1.field4 = ' ^||=' + table2.field4

returns the expected data.

Can anyone help with why the relation doesn't return any data.

TIA

Hi Richard,

What query do you get when you do:

Code: Select all

databaseManager.getSQL(relatedFoundSet)

Hi Joas,

It returns

select
Table2.Table2_id
from
Table2
where
(Table2.Field1 = ?
and Table2.Field2 = ?
and (Table2.Field3 = ?
or Table2.Field3 is null)
and (Table2.Field4 = ?
or Table2.Field4 is null))
order by
Table2.Table2_id asc

for Table2.find()
select
Table2.Table2_id
from
Table2
where
(Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and (Table2.Field4 is null
or Table2.Field4 = ?)
and Table2.Field1 = ?
order by
Table2.Table2_id asc

Table2.find() is coded in field order


Thanks

That looks OK to me too.
If you run that query yourself, do you get the right data?

And if you perform the find()-search() and afterwards do databaseManager.getSQL() on that foundset, what query do you get?

After
if ( table1.find()) {
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 = '^||=' + table2.field3
table1.field4 = ' ^||=' + table2.field4

select
Table2.Table2_id
from
Table2
where
(Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and Table2.Field4 is null
and Table2.Field1 = ?
and (Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and Table2.Field4 is null
and Table2.Field1 = ?
order by
Table2.Table2_id asc

after the search is the one in the previous post.