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?

score 5 · Accepted Answer · edited Aug 14 '13 at 10:10

5

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.

edited Aug 14 '13 at 10:10

pckill

3,709
36
48

answered Aug 14 '13 at 09:45

user2681892

74
1
2

3

Note that [link-only answers](http://meta.stackoverflow.com/tags/link-only-answers/info) 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. – kleopatra Aug 14 '13 at 09:50

score 2 · Answer 2 · answered Jul 12 '12 at 14:55

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.

answered Jul 12 '12 at 14:55

AnthonyBlake

2,334
1
25
39

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. – Max Jul 12 '12 at 15:07
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. – AnthonyBlake Jul 12 '12 at 15:11

score 1 · Answer 3 · answered Aug 05 '15 at 12:27

1

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

answered Aug 05 '15 at 12:27

Buck

11
2

Custom Lookup and Group By

3 Answers3

Linked