You’re offline. This is a read only version of the page.
30
I have a table that is displayed in displayed in a form grid. The table contains the field MainAccountId, which is basically RecId of the system table MainAccount (there is a relation between the tables), but it's displayed on the form by the field Account Number (string value). The table also has the field CostCenterId, which is RecId of the OMOperatingUnit table (there is a relation between the tables), but on the form, the Cost Center code is displayed by using the Replacement Field Group. The client wants the records in the grid to be sorted by Account Number, and then by Cost Center Code. There is a thread where this was discussed, but the accepted solution explains how to sort data in GUI (at run time), whereas I need the records to be sorted when the user opens the form for the first time (or in other words - at design time). I have tried using QueryBuildDataSource class. by writing a code like this:
QueryBuildDataSource qbds = MyTable_ds.queryBuildDataSource();
qbds.sortClear();
qbds.addSortField(fieldNum(MyTable, MainAccountId),SortOrder::Ascending);
qbds.addSortField(fieldNum(MyTable, CostCenterId),SortOrder::Ascending);
At first, I'm not sure whether QueryBuildDataSource supports sorting on multiple fields, but even if it does, this wouldn't work, because it wouldn't sort the records by the string values from their original tables (MainAccount.Name and OMOperatingUnit.OMOperatingUnitNumber), but by their corresponding RecId fields' values in MyTable.
Does anyone know how this sorting can be set during the design, through X++ code or through some form properties?
There are several ways. I think the best one would be checking the field used for the join relation and decide based on the field ID (there you can utilize fieldNum() instead of hard-coded the ID or name). QueryBuildDataSource class has linkCount() and link() methods that should help you with it.
Also note that you don't need to keep iterating all data sources if you're looking for a particular one. You can utilize methods like query.dataSourceTable() or query.dataSourceTableName().
Thanks, goshoom, that was a good hint. I have applied the sorting the following way:
Query query = MyTable_ds.query();
for (int i = 1; i <= query.dataSourceCount(); i++)
{
QueryBuildDataSource qbds = query.dataSourceNo(i);
if(qbds.table() == tableNum(MainAccount))
{
qbds.addSortField(fieldNum(MainAccount, MainAccountId));
break;
}
}
and it works fine. But now, I have another challenge of the same type (I suppose I don't need another thread for that). As I mentioned in the original thread's description, I need the sorting to be applied by the field Main Account Number, and then by Cost Center Code. However, my table has double relation with the OMOperatingUnit table - through the fields BusinessUnitId and CostCenterId. Therefore, the table OMOperatingUnit shows up twice in the array query.dataSourceNo. By using the debugger, I have found that these two query build data sources are almost the same, but one of the differences is that their Name properties (which can be obtained by the method qdbs.name()) are "Ref_OMOperatingUnit_OMOperatingUnit" and "Ref_OMOperatingUnit_BusinessUnit". So the code that I wrote for selecting the one that refers to CostCenterId is:
for (int i = 1; i <= query.dataSourceCount(); i++)
{
qbds = query.dataSourceNo(i);
if(qbds.name() == "Ref_OMOperatingUnit_OMOperatingUnit")
{
qbds.addSortField(fieldNum(OMOperatingUnit, OMOperatingUnitNumber ));
break;
}
}
This also works fine, but my question is - is there a way to avoid hard-coding ("Ref_OMOperatingUnit_OMOperatingUnit")?
You need to use data sources of the right tables and then refer to the right field names.
I think that you can find data sources used by replacement groups in the query. Let's verify it by this code:
Share
Report
All responses (
Answers (
Query query = myTable_ds.query();
for (int i = 1; i <= query.dataSourceCount(); i )
{
QueryBuildDataSource qbds = query.dataSourceNo(i);
info(tableId2Name(qbds.table()));
}
If it finds the data sources, you'll use them. Otherwise you could add extra query data sources for your purposes.
As already mentioned in the other thread, adding multiple sort fields in code is possible.
If you want to sort by MainAccount.Name and OMOperatingUnit.OMOperatingUnitNumber, then your code is wrong. It sorts by different fields from a different table.
#1
Martin Dráb
476
Most Valuable Professional
#2
Abhilash Warrier
310
#3
Saalim Ansari
271
