Custom Lookup and Group By

Question

TLDR: Why group by clauses are disabled in lookups when the field using the lookup is not empty?

I want to do a custom lookup on a field in a form. The table CTable looks like this:

Val Spec
------------
A   alpha
A   beta
A   gamma
B   delta
C   epsilon

The look up only concerns the Val column which is defined using an EDT with a basic relationship on it: Val == CTable.Val

The lookup obviously gives me a list like this:

A
A
A
B
C

Let's to a group by to get rid of all the duplicated As, I thought! Something along the lines of:

QueryBuildDataSource.orderMode(OrderMode::GroupBy);
QueryBuildDataSource.addGroupByField(fieldNum(CTable, Val));

Now comes the strange behaviour I have and the actual point of my question. On an empty field, the group by is correctly executed and I get this:

A
B
C

Now let's select "A" in the lookup, then perform the lookup again because I wanted to click on "B" instead. The group by is now disabled for unknown reasons and I get the same look up results as the first I had before.

Why is it so? How can I overcome that?

Pointenoire

Answer 1

Same question and usefull answer: http://dynamicsuser.net/forums/t/63438.aspx

You can disable this behavior by setting useLookupValues in SysTableLookup to false. Unfortunately I don't know exactly why AX does that. I suspect that it changes OrderMode to OrderBy.

Niu

Note that link-only answers are discouraged, SO answers should be the end-point of a search for a solution (vs. yet another stopover of references, which tend to get stale over time). Please consider adding a stand-alone synopsis here, keeping the link as a reference. – Corvine

Answer 2

I had the same problem yesterday.

I guess this code is in "init" maybe?

QueryBuildDataSource.orderMode(OrderMode::GroupBy);
QueryBuildDataSource.addGroupByField(fieldNum(CTable, Val));

I had to add my "GroupBy" code (above) to the executeQuery method, becuase the following line was removing the groupBys from my query (I checked this using breakpoints);

qbsSum.sortClear();

If you use breakpoints I would expect your GroupBy options are being cleared before the query executes again.

Yamauchi

No it is not in the init. It is in the method at the table level which will perform the lookup. This method takes a form stringControl as a parameter, uses a SysTableLookup object to perform the lookup based on a local query object. This local query object has the Group By Clause on it. There is no visible "executeQuery" method but there is a "performLookup" at the end of the method. – Pointenoire

I would expect you are experiencing the same issue if it works once until the query runs again - before the query runs (ie when you click B) add the group by code to QueryBuildDataSource again. – Yamauchi

Answer 3

I had the same problem. This helped me:
sysTableLookup.parmUseLookupValue(False);

Epilate