Monday, April 19, 2010

How to perform a lookup by code

Custom lookups are a recurring topic in Axapta discussions and blog posts. The lookup functionality in Ax is versatile, flexible and has changed over the different versions.

This post is about programming your lookup without the need of defining a separate lookup form, as this functionality isn't that well documented in Ax. We'll do this by using class SysTableLookup.

Let's look at a full code example first.

public void lookup()
{
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(CustTable),this,true);

Query query;
QueryBuildDataSource queryBuildDataSource;
;

sysTableLookup.addLookupfield(fieldnum(CustTable,AccountNum),true);
sysTableLookup.addLookupfield(fieldnum(CustTable,Name));

query = new Query();

queryBuildDataSource = query.addDataSource(tablenum(CustTable));
queryBuildDataSource.addSortField(fieldnum(CustTable,AccountNum));

queryBuildDataSource.addRange(fieldnum(CustTable,Currency)).value(queryvalue('EUR'));

sysTableLookup.parmQuery(query);
sysTableLookup.parmUseLookupValue(true);

sysTableLookup.performFormLookup();
}

You can use the code above to override the lookup method of a control in a form.

Now let's have a look at the different parts in the code.

First, we create the table lookup by instantiating the sysTableLookup class.

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(CustTable),this,true);

The form control from which the code is called is included (this).

We define which fields to include in our lookup form.
The second parameter is set to true, if that's the field you wish to return from your lookup.
If you accidentally set it multiple times to true, the field where it's last set will be the lookup field, so your lookup may not work correctly.

sysTableLookup.addLookupfield(fieldnum(CustTable,AccountNum),true);
sysTableLookup.addLookupfield(fieldnum(CustTable,Name));

Now we create the actual query, setting sorting fields and ranges as desired.

query = new Query();

queryBuildDataSource = query.addDataSource(tablenum(CustTable));
queryBuildDataSource.addSortField(fieldnum(CustTable,AccountNum));

queryBuildDataSource.addRange(fieldnum(CustTable,Currency)).value(queryvalue('EUR'));


We pass along the parameters to our sysTableLookup...

sysTableLookup.parmQuery(query);
sysTableLookup.parmUseLookupValue(true);

And we're ready to go

sysTableLookup.performFormLookup();

I tend to use this functionality, when using edit methods instead of display methods in a form (where the return EDT is for example Name, and not a specific extended data type).

3 comments:

  1. Hello All,
    Just struggling with a look up form and wondering if someone could help me please. On my sales ledger form under sales Line Tab I have inserted a new field called Job id which is EDT. I am calling a lookup form on this field. The data source of this look up form is a table contains sales id, job id and name. I want to add filter on the sales id of this look up form.
    So if user creates a new sales line select item number and when he clicks on job ID he can only see the record which is linked with the current sales id of sales line.
    At the moment if user clicks on the field he see following values in look up form
    Sales Id - Job Id - Name
    SO7546 701 Futaba
    SO7546 702 Futaba
    SO7546 703 Futaba
    SO7547 704 Futaba
    SO7547 705 Futaba
    SO7548 706 Futaba
    As i am creating a sales line on Sales ID SO7547 so i only want to see this record in my look up form.
    SO7547 704 Futaba
    SO7547 705 Futaba
    Kind Regards,

    ReplyDelete
  2. This is good technical blog which which is good for new dynamics learner
    and problem's solutions

    Axapta ERP Technical blog-ALL Version

    ReplyDelete
  3. I don't quite understand this because I don't understand the context you're working from. Which tables are a datasource for the form? Or do the tables used in this type of lookup HAVE to be a datasource for the form?

    ReplyDelete