Get it from Lookups - Axaptapedia:

Lookups

Lookups are used in Axapta to show the user a list of possible values from which to select, whilst entering data into a control on a form.

[hide]

1 Automatic lookups
- 1.1 Introduction
- 1.2 Displayed fields
  - 1.2.1 How does Axapta determine which fields to use?
  - 1.2.2 How can we change which fields are displayed?
2 Manual lookups
- 2.1 Calling the standard lookup behaviour from code
- 2.2 Changing the lookup form used
  - 2.2.1 Construct a standard lookup form, based on your own query
  - 2.2.2 Use an entirely new lookup form

Automatic lookups

Axapta has extensive support for automatic lookups, as well as the capability to manual enhance or replace the automatic system.

The standard lookup for customer accounts

Introduction

The standard lookup system in Axapta is based on the use of table field or data type relations. These specify a link between a particular data type, or particular table field, and a corrsponding field in another table containing the base (reference) data.

Generally, relations are made on the datatype (EDT) in the AOT, and will then automatically apply to any table field using that EDT.

For example, in the standard application, the CustAccount EDT has a relation specified to CustTable.CustAccount. This means that any table field using the CustAccount EDT will be automatically given a lookup icon which allows the user to select from the list of accounts in CustTable.

It is possible to further restrict the values which will appear in the lookup by specifying a "Related fixed field" relations on the datatype. In this case, only those values satisfying the relation will be shown in the lookup. See the Dimension datatype for an example of this (each Array Element has it's own relation defined)

Displayed fields

How does Axapta determine which fields to use?

Axapta uses a simple system to determine which fields to display in an automatically created lookup.

If no special changes are made, then the fields shown on the lookup are determined by the following information from the base table:

The field(s) responsible for the relation (in the order of the content of the relation-treenode of the table or the extended datatype)
TitleField1
TitleField2
The first field of every index of the table (in the order of the index-id)

A maximum of six fields will be shown, and duplicates will be removed.

The standard lookup for inventory items

To see this logic, look at the standard ItemId lookup (for example, from a newly-created sales line) in the standard application.

Properties and indexes for the InventTable

The fields shown are as follows (with field names in brackets)

Item number (ItemId)
Item name (ItemName)
Item group (ItemGroupId)
Search name (NameAlias)
Item type (ItemType)
Dimension group (DimGroupId)

See the image to the right for the relevant properties of the InventTable table.

The first two fields are taken from the TitleField1 and TitleField2 properties of the table. The rest come from the indexed fields in the order shown. The first index contains ItemId, which is a duplicate of the field specified in TitleField1 and is therefore skipped. The second index contains ItemGroupId, the third column in our lookup, and ItemId which is again skipped. The third index contains only NameAlias, which is the fourth column on our lookup, and so on, until the maximum of six fields is reached or no more indexes are found.

How can we change which fields are displayed?

In general, it wouldn't be advisable to change either the TitleField properties, or the indexes, purely to change what is displayed on a lookup form. Luckily, Axapta gives us as easier way to achieve the same goal.

If the AutoLookup field group on the base data table has been filled, then those fields will be used irrespective of the usual logic. Modifying this field group is the easiest way to change which fields appear in lookups against that table.

Bear in mind that while you can add display and edit methods to your AutoLookup field group, the values will not display correctly unless all required fields for calculations are also included in the field group. For example, if you want to show the name of a vendor, based on the VendAccount stored on a record, then the VendAccount itself must also be included in the group. This is because the lookup query only fetches the fields from the database which are actually to be displayed, rather than the entire record.

Manual lookups

Calling the standard lookup behaviour from code

It is simple to call the standard Axapta lookup functionality from code. This allows the programmer to specify that a lookup will be based on a non-standard field or data type.

To hook into the lookup system, you can override the lookup method at the form control. It is useful to first understand what happens "under the hood" before any changes are made.

If the control is a "bound field", that is, the control has DataSource and DataField property values, then the kernel will perform a call to performDBLookup() during the super() call. It passes the tableId and fieldId from the property values to the kernel code which creates the lookup. Relations which are set directly on the specified table will be used to create the lookup if available, or the standard EDT relations otherwise.

If the control is unbound, but has a related Extended Data Type (either set directly on the control or from the signature of an associated edit method), then a performTypeLookup() call is made in super(). This has no associated table, and can therefore not use any table level relations. Therefore the lookup is based purely on the relations specified on the EDT.

With this knowledge in mind, we can modify the standard lookup functionality by passing through different parameters to the performDBLookup and performTypeLookup methods.

Changing the lookup form used

There are two possibilities when specifying a custom lookup form.

Construct a standard lookup form, based on your own query

Axapta provides a mechanism whereby we can create a standard lookup form programmatically, to easily allow us to perform a custom lookup using a query which is too complicated for the relations system to express, or where the requirement is unique and we do not wish to set up relations, which will have a global effect.

See the article on the sysTableLookup class for more information about using this method.

Use an entirely new lookup form

It is possible to create an entirely new form, following certain guidelines, and use it as a lookup. See the Lookup Form article for more information.

Get it from SysTableLookup class - Axaptapedia:

The SysTableLookup class is provided by the standard application to allow programmers to easily create their own lookup forms, in code.

The basic steps to using this class are as follows:

Create the sysTableLookup object
Create the query which will be used to select the lookup data
Specify the fields which will be shown on the lookup
Perform the lookup

In this example, we will perform a complex lookup on the customer table. We only want to include customers which are not blocked, and where one or more open transactions exists.

public void lookup()
{
  Query                   query = new Query();
  QueryBuildDataSource    dsCustTable;
  QueryBuildDataSource    dsCustTrans;
 
  // Instantiate sysTableLookup object using table which will provide the visible fields 
  SysTableLookup  sysTableLookup = sysTableLookup::newParameters(tableNum(CustTable), this);
  ;
 
  // Create the query.  Only select customers where blocked != All and one more more transactions
  // exist with no closed date
  dsCustTable = query.addDataSource(tableNum(CustTable));
  dsCustTable.addRange(fieldNum(CustTable, Blocked)).value(queryNotValue(CustVendorBlocked::All));
  
  dsCustTrans = dsCustTable.addDataSource(tableNum(CustTrans));
  dsCustTrans.relations(true);
  dsCustTrans.joinMode(JoinMode::ExistsJoin);
  dsCustTrans.addRange(fieldNum(CustTrans, Closed)).value(queryValue(dateNull()));
 
  // Set the query to be used by the lookup form
  sysTableLookup.parmQuery(query);
 
  // Specify the fields to show in the form.  In this case we have chosen
  // Account number, name, and dimension one.
  sysTableLookup.addLookupfield(fieldNum(CustTable, AccountNum));
  sysTableLookup.addLookupfield(fieldId2Ext(fieldNum(CustTable, Dimension), 1));
 
  // Perform the lookup
  sysTableLookup.performFormLookup();
}

While the above example will work perfectly, you should consider moving this code out of the lookup method on the form control itself. For ease of re-use and to maximise ease of upgrading of the forms, it is better to construct the lookup as a static method on the CustTable which can then be called from the form. Don't forget to remove the super() call from the method. Otherwise your Lookup won't work.

For example:

public static void lookupCustOpenTrans(FormControl _callingControl)
{
  Query                   query = new Query();
  QueryBuildDataSource    dsCustTable;
  QueryBuildDataSource    dsCustTrans;
 
  // Instantiate sysTableLookup object using table which will provide the visible fields 
  SysTableLookup  sysTableLookup = sysTableLookup::newParameters(tableNum(CustTable), _callingControl);
  ;
 
  ... remainder of code from above ...
}

In the form control lookup() method:

public void lookup()
{
  ;
  CustTable::lookupCustOpenTrans(this);
}

Dynamics Ax Forum

Lookups - Axaptapedia