Dynamics AX – Form lookups and how they work

There are few different ways to achieve a lookup field on a form.

1.     Create an Extended data type – EDT123

2.     Create a table Table123

3.     Add EDT123 to the table and the Description EDT field

4.     Go back to the EDT and create a relation between table123.EDT123 and the EDT123

5.     Automatically when the EDT field is added to another table lets say CustTable a lookup displaying the values from table123 will be displayed in the drop down.

6.     Now let’s say you want to add the description field to the lookup as well. Add the description field to the AutoLookup field group on the table. Now the lookup will display EDT123 and the description field from table123.

That is a simple way to create a lookup. Another way to create a lookup is to write a dynamic lookup on the table in which the data is coming from. This method will be a static method and will require the passing of arguments – typically the formstringcontrol that is to be the point of lookup. I prefer this method and use it often.

This a very simple example of a static lookup from a table

static void lookupTruckLoadIdEndingInv(FormStringControl  _ctrl)
{
    SysTableLookup          sysTableLookup  = SysTableLookup::newParameters(tablenum(WfsRMTruckLoadStatus), _ctrl);
    Query                   query           = new Query();
    QueryBuildRange         qbr;
    ;

    query.addDataSource(tablenum(WfsRMTruckLoadStatus));

    sysTableLookup.addLookupfield(fieldnum(WfsRMTruckLoadStatus, truckLoadId ));

    query.dataSourceTable(tablenum(WfsRMTruckLoadStatus)).addRange(fieldnum(WfsRMTruckLoadStatus,retTransferred)).value(SysQuery::value(NoYes::No));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

Joins can be peformed as well to pull back the desired data so you are not limited to querying on one table to return the proper data back to your lookup

static void lookupSettledTruckLoadIdCashier(FormStringControl  _ctrl)
{
    SysTableLookup          sysTableLookup  = SysTableLookup::newParameters(tablenum(WfsRMTruckLoadHeader), _ctrl);
    Query                   query           = new Query();
    QueryBuildRange         qbr;
    queryBuildDataSource    qbdsTruckLoadStatus,qbdsTruckLoadHeader,qbdsTenderSlipHeader;
    ;

    qbdsTruckLoadHeader = query.addDataSource(tablenum(WfsRMTruckLoadHeader));

    qbdsTruckLoadStatus         = qbdsTruckLoadHeader.addDataSource(tablenum(WfsRMTruckLoadStatus));
    qbdsTruckLoadStatus.relations(true);

    qbdsTruckLoadStatus.addRange(fieldnum(WfsRMTruckLoadStatus,settled)).value(enum2str(NoYes::No));
    qbdsTruckLoadStatus.addRange(fieldnum(WfsRMTruckLoadStatus,HHTruckLoadIdEnded)).value(enum2str(NoYes::Yes));
    qbdsTruckLoadStatus.addRange(fieldnum(WfsRMTruckLoadStatus,RetTransferred)).value(enum2str(NoYes::Yes));

    qbdsTenderSlipHeader         = qbdsTruckLoadStatus.addDataSource(tablenum(wfsRMTenderSlipHeader));
    //qbdsTenderSlipHeader.relations(true);
    qbdsTenderSlipHeader.addLink(fieldnum(WfsRMTruckLoadStatus, truckLoadId),fieldnum(wfsRMTenderSlipHeader, truckLoadId));
    qbdsTenderSlipHeader.joinMode(joinMode::NoExistsJoin);

    sysTableLookup.addLookupfield(fieldnum(WfsRMTruckLoadHeader, truckLoadId));
    sysTableLookup.addLookupfield(fieldnum(WfsRMTruckLoadHeader, routeId));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

Another way is to create an actual form and call it in a method on the table. You will
still need to pass in the formstringcontrol object but in this case you will be
calling an actual existing form that you have created. Now why do this? Well
one reason may be that you want to be able to filter on a specific field in the
lookup that maybe you could not on a typical lookup or maybe you need to add a
field to the lookup that would otherwise not be possible like you can add a
display method to a lookup but maybe you want to see the field referenced in
the display method and have the ability to sort on the field or filter on the
field. For example, like DirPartyTable.Name. Sure, you can access it using a
display method but maybe you want to see it in your lookup and be able to filter on it.
So you will create a form and call it like a lookup so you can have all the the filtering of a standard form

public client static void WfsRMlookupEmplIdCashier(Object _ctrl)
{
    Args        args;
    FormRun     formRun;
    ;

    args = new Args();
    args.name(formstr(WfsRMEmplIdLookupCashier));
    args.caller(_ctrl);
    formRun = classfactory.formRunClass(args);
    formRun.init();
    _ctrl.performFormLookup(formRun);
}

The standard lookups present in AX like the item number lookup and the customer lookup are very interesting in that you only see one field on the relation but no fields in the autolookup. In these cases the lookup fields are coming from the standard indexes on the table. Take note of what you see in the itemId lookup when unchanged and then reference the indexes coincidence not really. this can apply to any new lookup you create as well.
You can also override the lookup on the datasource - field of a form or an actual field string edit control on a form and perform a lookup
Form design object

public void lookup()
{

    SysTableLookup        sysTableLookup;
    Query                 query=new Query();
    QueryBuildDataSource  qbds;

    ;
    sysTableLookup=SysTableLookup::newParameters(tablenum(Dimensions),this);

    sysTableLookup.addLookupfield(fieldnum(Dimensions,Num));
    sysTableLookup.addLookupfield(fieldnum(Dimensions,Description));

    qbds = query.addDataSource(tablenum(Dimensions));

    qbds.addRange(fieldnum(Dimensions, DimensionCode)).value(queryValue(COSAllowedDimensions::getAllowedDimensionValue(sysDim)));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

//Lookup from a field on the form data source

public void lookup(FormControl _formControl, str _filterStr)
{
    Args    args;
    FormRun formRun;
    SysTableLookup      sysTableLookup = sysTableLookup::newParameters(tablenum(InventTable),_formControl);
    Query               query = new Query();
    QueryBuildDataSource     queryBuildDataSource;
    QueryBuildRange     queryBuildRange;
    ;
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemID));
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemName));
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemGroupID));
    sysTableLookup.addLookupfield(fieldnum(InventTable,NameAlias));
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemType));
    sysTableLookup.addLookupfield(fieldnum(InventTable,DimGroupID));

    queryBuildDataSource = query.addDataSource(tablenum(Inventtable));
    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable,ItemGroupID));
    //FGL, FGR, Returns
    queryBuildRange.value('xxx');

    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable,ItemGroupID));
    queryBuildRange.value('yyy');

    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable,ItemGroupID));
    queryBuildRange.value('Returns');

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}

-Harry

Exporting data to Excel from axapta x++

Exporting data to Excel from axapta x++

Hi All!
Sometimes we need to export data from Microsoft Dynamics AX to Excel using axapta x++ code and we don't know how to do this...
Exists some differents ways to do this, but I think the best way is using the SysExcel class of Dynamics AX and its related.
The only problem I found using this class... is that it can not be used in a batch process.
Sample code:

static void TheaxaptaCreateExcel(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   CustTable            custTable;
   int                  row = 1;
   str                  fileName;
   ;
   //Filename
   fileName = "C:\\Test.xlsx";
   //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();
   //Open Excel document
   //xlsApplication.visible(true);
   //Create Excel WorkBook and WorkSheet
   xlsWorkBookCollection    = xlsApplication.workbooks();
   xlsWorkBook              = xlsWorkBookCollection.add();
   xlsWorkSheetCollection   = xlsWorkBook.worksheets();
   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("Account Num");
   xlsWorkSheet.cells().item(row,2).value("Name");
   row++;
   //Fill Excel with CustTable AccountNum and Name fields (only 20 records)
   while select custTable
   {
      if(row == 20)
        break;
      xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
      xlsWorkSheet.cells().item(row,2).value(custTable.Name);
      row++;
   }
   //Check whether the document already exists
   if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   //xlsApplication.quit();
   //xlsApplication.finalize();
}
-Harry