1

When opening the query SalesTableListPage from the AOT, you can choose the field MatchingAgreement (shown as "Agreement Header record ID (Record-ID)") in the field lookup. The same is not possible for query SalesUpdate, field MatchingAgreement and several others (which seem to be related to relation fields where the relation is built with a RecId) are not shown in the lookup.

After some research I found out that the reason seems to be the FetchMode property on the joined SalesLine data source. If it is 1:n, the fields are not shown in the lookup. If it is 1:1, the fields are shown in the lookup.

I have not tested this with other tables, but I suspect the same behavior. I also have only tested this with AX 2012 R2 and R3, but I suspect the same behavior in other 2012 versions.

Why does the FetchMode of a joined data source remove some fields from the parent data source in the query dialog?

FH-Inway
  • 4,432
  • 1
  • 20
  • 37

2 Answers2

1

TL;DR Field lookups in the query dialog do not always work for relation fields that define relations to tables where the AutoIdentification field group's property AutoPopulate is set to No. One case where they not work is when a data source is joined with FetchMode 1:n.

While Alex K's answer has some interesting suggestions, in my case the culprit is the AutoPopulate property on the AutoIdentification field group of table AgreementHeader. In sys layer, this property is set to No and the group contains the fields that are shown in the query dialog field lookup if the relation in the query is 1:1. If this property is switched is to Yes the lookup will show field Agreement header record ID (Record-ID) (and only this field, no matter how the FetchMode of the query relation is defined).

Basically, AX will use the information from the AutoIdentification field group to determine the information shown in the gui in case of surrogate key references/relations. If the AutoPopulate property of the field group is Yes, AX will use the alternate key of the table to determine the fields to use. If no alternate key exists (which is the case for table AgreementHeader), AX uses the relation field. If AutoPopulate is No, the fields defined in the group are used. But as described this option does not work if the relation in the query is not 1:1 (and unfortunately no fallback option like using the relation field seem to have been implemented).

FetchMode    AutoPopulate     Lookup
1:1          Yes              AlternateKey (or Relation) fields
1:1          No               AutoIdentification fields
1:n          Yes              AlternateKey (or Relation) fields
1:n          No               Nothing

Update: I came upon this problem again, but with field SalesTaker this time. Turns out that the AutoPopulate property is only part of the story, because it did not solve the problem when set to Yes on table HcmWorker. This table (unlike table AgreementHeader) also has the property ReplacementKey set, which AX uses to populate the AutoIdentification field group. Only after I removed the ReplacementKey no fields appeared anymore in AutoIdentification and the lookup now showed "Sales taker (Record ID)". So bottom line is that the AutoIdentification field group must not contain any fields.

FH-Inway
  • 4,432
  • 1
  • 20
  • 37
0

If the dynamic property of both query fields is set to yes, then I'd suspect it has something to do with the properties of one of these relations:

\Data Dictionary\Tables\SalesTable\Relations\Agreement

\Data Dictionary\Tables\SalesLine\Relations\SalesTable

Maybe try adjusting those, refreshing your cache (AOT changes to be picked up), clearing usage data (packed queries can cause problems), and updating XRef (Advanced querying uses XRef, so may not be necessary for standard query functionality)

Alex Kwitny
  • 11,211
  • 2
  • 49
  • 71