Источник:
http://dev.goshoom.net/en/2019/01/re...-and-group-by/
==============
This blog post explains a problem that you can run into when using reference group controls with grouped data – and a solution for this problem.
I have a table which stores references to workers. The field has
HcmWorkerRecId data type, i.e. it stores record IDs from
HcmWorker table. When I drop this field to a grid on a form, the system uses
Reference Group control, which shows human-readable data instead of RecId numbers. By default, it shows worker names.
There are multiple records for the same worker, therefore if I want to show data summarized data for each worker, I add grouping to the query:
TableWithRef_ds.queryBuildDataSource().addGroupByField(fieldNum(TableWithRef, Worker));
But the result isn’t correct – the reference group doesn’t show anything.
The grouping works correctly; I can see the right record IDs if I display them thought an Int64 control instead of a reference group.
The problem is that we group only by the record ID, but not by
Name. And because
Name is neither used in GROUP BY nor it has an aggregation function applied, its value is undefined and there is nothing to show.
Let’s fix it. Go to the data source in AOT and add reference data sources. Because
Name field isn’t directly in
HcmWorker table, we’ll need one more data source –
DirPerson. Like this:
You can add them by right-clicking the
Reference Data Sources node, choosing
New Reference Data Source and then setting properties
Join Relation and
Name.
Then we can easily add an extra field to group by, this time from
DirPerson table:
DirPerson_ds.queryBuildDataSource().addGroupByField(fieldNum(DirPerson, Name));
Voilà, names are back!
Источник:
http://dev.goshoom.net/en/2019/01/re...-and-group-by/