Relation not giving expected results

Questions
1

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

2

Hi Richard,

What query do you get when you do:

databaseManager.getSQL(relatedFoundSet)
3

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

4

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?

5

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.