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.
