|
Id
|
Name
|
ParentId
|
|
N1
|
Apple
|
|
|
N2
|
Banana
|
N1
|
|
N3
|
Carrot
|
|
|
N4
|
Durian
|
|
I have a table structure like above that is having self join parent-child relationship (one-to-many).
I am building a simple list & details form to show all records (N1, N2, N3, …) When select N1, I should be able to see the child record N2 in grid.
From the grid, I should be able to select child record that is not a parent, not a child, and not itself.
For example,
When select N1 lookup, it should filter child records to N2, N3, N4.
When select N3 lookup, it should filter child records to N4 only.
I am looking to build SQL like this :
SELECT * FROM TBLNAME AS a WHERE NOT EXISTS (SELECT * FROM TBLNAME AS b WHERE b.parentid = a.id) AND a.parentid = '' AND a.id != 'N3'
But what I get in x is :
SELECT * FROM TBLNAME NOTEXISTS JOIN * FROM TBLNAME WHERE ((ParentId = '')) AND ((NOT (Id= N'N3')))
My code as follow, how can I make it to show as I need ?
qbds = q.addDataSource(tableNum(TBLNAME));
qbds2 = qbds.addDataSource(tableNum(TBLNAME));
qbds2.joinMode(JoinMode::NoExistsJoin);
qbds2.addRange(fieldNum(TBLNAME, ParentId)).value(SysQuery::valueEmptyString());
qbds2.addRange(fieldNum(TBLNAME, Id)).value(SysQuery::valueNot(_Id));
tableLookup.parmQuery(q);
tableLookup.performFormLookup();
