Filtered form reference group lookup

Dynamics AX 2012 introduces built-in support for resolving and looking up linked records based on reference record IDs.

When adding a reference record ID field to a form, AX will add a reference group control instead of the reference record ID, and will show a selected set of fields from the referenced table.  See the posts about tree lookups for an example.

The reference group control also does lookups into the referenced table for selecting a new value.  However, by default this lookup is not filtered and will show all records in the referenced table.  Adding filtering is quite easy to do and it follows a similar pattern as regular table lookups.

The lookupReference() method

To add a custom lookup, you’ll need to override the lookupReference() method on the reference group control, not lookup() which is used for regular lookups.

public Common lookupReference(FormReferenceControl _formReferenceControl)
{

lookupReference() is different in that it returns a record buffer, which will be used to populate the individual fields in the reference group.  Good, so we just need to return a record buffer, but how to select one from a filtered list?  That’s where the SysReferenceTableLookup class comes into play.

The SysReferenceTableLookup class

This class provides an API which is almost identical to SysTableLookup, so if you are used to that class, this should all look and feel quite similar.

We create a SysReferenceTableLookup object and point it to the table we want to reference.  (Replace ReferencedTable, OtherTable and their field names with the relevant table and field names from your application)

SysReferenceTableLookup lookup = SysReferenceTableLookup::newParameters( tableNum( ReferencedTable ), _formReferenceControl );

Next, we create a query object and add a data source for the same table.  (You could of course create a query object from an AOT query, as well)

Query q = new Query();
QueryBuildDataSource qbds = q.addDataSource( tableNum( ReferencedTable) );

We add one or more ranges to restrict the rows that will be shown in the lookup.

qbds.addRange( fieldnum( ReferencedTable, FieldToFilterOn ) ).value( queryValue( OtherTable.FilterValueField ) );

We tell the lookup object which query to use and which field(s) to show.

lookup.parmQuery( q );
lookup.addLookupfield( fieldNum( ReferencedTable, ID ) );
lookup.addLookupfield( fieldNum( ReferencedTable, Description ) );

And finally, we call performFormLookup() to show the actual lookup.  In SysReferenceTableLookup this method returns a record buffer, which we pass to the return statement.

  return lookup.performFormLookup();
}

The full method would look like this:

public Common lookupReference(FormReferenceControl _formReferenceControl)
{
    SysReferenceTableLookup lookup = SysReferenceTableLookup::newParameters( tableNum( ReferencedTable ), _formReferenceControl );
    Query q = new Query();
    QueryBuildDataSource qbds = q.addDataSource( tableNum( ReferencedTable) );

    qbds.addRange( fieldnum( ReferencedTable, FieldToFilterOn ) ).value( queryValue( OtherTable.FilterValueField ) );

    lookup.parmQuery( q );
    lookup.addLookupfield( fieldNum( ReferencedTable, ID ) );
    lookup.addLookupfield( fieldNum( ReferencedTable, Description ) );

    return lookup.performFormLookup();
}