Sorting records in a form grid by multiple fields

(0) Share

Report

Posted on by Ascalab

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?

I have the same question (0)

All responses (7)

Answers (0)

GirishS 27,825 Moderator on at

Like (0)

Report

RE: Sorting records in a form grid by multiple fields

Hi Ascalab,

In which method you write the code for sorting?

Thanks,

Girish S.

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: Sorting records in a form grid by multiple fields

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.

Was this reply helpful? Yes No
Ascalab 30 on at

Like (0)

Report

RE: Sorting records in a form grid by multiple fields

Hello GirishS . I wrote the code for sorting in the form class's init method.

Was this reply helpful? Yes No
Ascalab 30 on at

Like (0)

Report

RE: Sorting records in a form grid by multiple fields

I know, goshoom that my code is not right, but do you have an idea what should be the right code?

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report
RE: Sorting records in a form grid by multiple fields

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:

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.

Was this reply helpful? Yes No
Ascalab 30 on at

Like (0)

Report

RE: Sorting records in a form grid by multiple fields

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")?

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: Sorting records in a form grid by multiple fields

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().

Was this reply helpful? Yes No

Join the conversation

Community site session details

Sorting records in a form grid by multiple fields

Helpful resources

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Sorting records in a form grid by multiple fields

Helpful resources

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates