I have a situation with three DB tables where table-a records have child records in table-b and these table-b records have child records in table-c. I then need to, in servoy, produce a report of all records in table-c which are, indirectly, children of a particular record in table-a.
I must have nissed something here, as I can’t see a way of doing this without storing table-a’s primary key in records of table-c (which, AFAICS, would be an explicit violation of third normal form).
Any suggestions so far?
Then it’s gets even more interesting
In the same report, I’d like an aggregate from a field in table-b (i.e. same parent record in table-a, and count or sum a related field in table-b.
Assuming you are using the datamanager:
databaseManager.getDataSetByQuery(String, String, Object, number)
you could join like this:
select tableA.fieldName, tableB.fieldName, tableC.fieldName
from tableA, tableB,tableC
where tableA.keyFieldx = tableB.keyfieldx
and tableB.keyfieldy = tableC.keyfieldy
Aggregation can be done by using SUM,COUNT etc.. in your select statement, combined with a group by
example:
select tableA.fieldName, COUNT(tableB.fieldName)
from tableA, tableB
where tableA.keyFieldx = tableB.keyfieldx
GROUP BY tableA.fieldName
this join:
tableA.fieldName tableB.fieldName
a e
a f
b x
b y
b z
In addition to the query method demonstrated by Maarten you can also use a tabpanel within a tabpanel to show three relations. You can turn the tabs of a tabpanel off (tabOrientation none) so that you don’t see tabs and are able to see data 2 relations deep.
Thanks for the suggestions - I managed to cobble together examples implementing each of these tactics and the data returned appears to be the correct records. Hopefully one of them can be polished up to something presentable.
Should I take these suggestions as confirmation that there’s no native/direct way of doing this in Servoy?
Is there any possibility of populating a foundset from a custom SQL query? Hopefully that would make all my problems go away: I can write an appropriate SQL query and the foundset could then be presented on a nice form
It must be coincidence, 2 days ago we have built:
var query = ‘select orderid from orders where orderid in(10300,10301,10302)’;
var pkdataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 100);
controller.loadRecords(pkdataset);
This loads the form with primary keys column data, which becomes the foundset…
requirements:
-supply a dataset with only the primary keys column data from the table the form is based on.
limitations:
-sort will not work (returns to full foundset)
-invert will not work (shows no rows)
-print(preview)will not work (shows full foundset)
all other actions such as edit,delete,lock,newRecord,dup,etc will work
A couple of issues with the controller.loadRecords(pkdataset) feature:
Doesn’t seem to work first time only running developer in data mode.
I.e. fire up developer, chose solution, navigate to the list which is populated via controller.loadRecords(pkdataset) and see all records in the table. After that first time it seems to work just fine
Are aggregations another “known” problem with this feature?
I.e. I use “controller.loadRecords(pkdataset)” and then aggregation appears to aggregate over ALL records in the table (not just the foundset loaded above)
we altered the implementation and removed all limitations, but now the pkdataset should not be bigger than approx 200 items (under the hood a SQL ‘IN’ is generated with all the pk data)
I’ve been thinking about this one (for a while now …
Could the approx 200-record limitation be made to go away if instead of me first selecting a bunch of PKs and then handing the foundset of PKs to controller.loadRecords() we were to “cut out the middleman” and hand the WHERE clause of the original query to a function like controller.loadRecords()?
More flexibility on this is being researched, it has quite a few consequences that have to be worked out. As you might have noticed Servoy takes care of quite a bit of the SQL handling on updates, inserts, deletes, sorting, relations, etc and making the where freeform impacts many areas.
Create a form on table B with the portal on it, set it’s border to empty and it’s controller property to none. Then on a form of table A create a tabpanel and place the form you just created on it. Set the tabpanel to hide the tabs and sets its Border to Empty.
AFAIK, that’s exactly how it’s supposed to work. The “current record” in table B is the first child of the selected/current record in table A. The portal is displaying the records in table C which are the children of that current record in table B.
Your problem sounds similar to the one I was contemplating - See Jan’s response of Jul 25 above for a way of handling this (subject to the ~200 item limitation mentioned).
Anyone know if the ~200 item limit has-been/might-be addressed?
It will indeed be sad if Servoy doesn’t handle these indirect relationships natively. Any news on this front?