Form Sorting Issue

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

I have done following minor customization but encounter sorting issue. Would like to have advice to fix this issue? Following are the details customization done:

Customization:

  1. Add new datasource InventBatch at InventBatchIdLookup Form

  2. Add InventBatch.prodDate and InventBatch.expDate to InventBatchIdLookup Form > On hand tab

  3. Sort the Manufacturing date in ascending order

Purpose: To have batch number sort in FIFO order for material’s picking

Issue: The manufacturing date is not sorted as per required and data in drop down grid missing when click on the header to sort the manufacturing date

Please help.

2

Hi Rinoa2016, welcome to DUG forum. First of all, there is no AX 2019 - did you mean AX 2012? Also, please use tags for this kind of information, instead of putting everything to title.

Regarding your question, is it necessary to add InventBatch table? I would expect that there already is one. Also, if you add a new datasource to an existing query, you have to link to a parent datasource. You didn’t tell us how/if you did it.

In either case, you should focus on the query. It’s clear that you’ve built a different query than what you intended. Looking at the actual query string will help you to identify the difference. You can get it by calling toString() on the root QueryBuildDataSource object, for example.

3

Hi Martin,

It’s AX2009. Sorry for the typo error.

I have tried to use existing datasource InventBatch table but it has no relation with inventDim therefore the data display incorrectly in On-hand tab. In order not to affect the standard Batch number tabs in the lookup form which using the datasource, have added a new datasource (InventBatch) with properties Join source = InventDim and LinkType = Innerjoin.

The new datasource have following init method:

public void init()

{

Query q;

QueryBuildDataSource qbds;

;

super();

qbds = this.query().dataSourceTable(tableNum(InventBatch));

qbds.clearLinks();

qbds.clearDynalinks();

qbds.addLink(fieldNum(InventDim,InventBatchId),fieldNum(InventBatch,InventBatchId));

qbds.addGroupByField(fieldNum(InventBatch,ItemId));

qbds.addGroupByField(fieldNum(InventBatch,InventBatchId));

qbds.addGroupByField(fieldNum(InventBatch,prodDate));

qbds.addGroupByField(fieldNum(InventBatch,expDate));

qbds.addSortField(fieldNum(InventBatch,prodDate),SortOrder::Ascending);

}

The added field prodDate and expDate is showing correct information. Somehow the field sorting doesn’t work. Also tried to put the sorting code in executeQuery but failed to work as well.

4

The reason is that you can’t using grouping and sorting together. But I think you’ll get your intended result if you move grouping by prodDate before ItemId. You may also have to modify the parent datasource, if it currently doesn’t use grouping. (Unfortunately I can’t look into AX in the moment and I don’t remember every query in AX :)).

5

Also, isn’t calling clearLink() a bug?

If you have further issues, please share the actual query string created by your query.

6

Hi Martin,

Thanks for the advice. The sorting is working when drop down but the issue still exist when click the header to resort.

Another issue found where duplicate batch number appear. After further checking, found that it doesn’t group the batch number according to the selected item. This happen to same batch number for different item.

Is there anything missed out in the query?

7

It seems to me that you didn’t address any of my points, so let me reiterate them.

  1. Get used to checking the query string created from your Query object. If you need help from us, share it here.
  2. Don’t call clearLinks() and clearDynalinks(), it’s useless here. Instead, add a relation between your new datasource and the parent datasource, such as by calling relations(true). Again, check the query string to see whether there is the link you want.
  3. You can use grouping and filtering together. If you want to sort the result of grouping, put it into a view or a temporary table.