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

How to Connect the Host Hard Drive to a VirtualBox OS

How to Connect the Host Hard Drive to a VirtualBox Guest OS

VirtualBox from Oracle is an amazing and free application if you like testing different operating systems without messing up your original operating system or Master Boot Record (MBR). VirtualBox is also useful if you like to test operating systems before their final release. 
However, most people want to connect their host hard disk drives to the guest operating system. For your information, the host OS is the operating system you’re actually using, while the Guest OS refers to the OS virtually installed inside VirtualBox. 

We’ve used Windows 7 as the host operating system and Windows XP as the guest OS. Let’s begin.

Step 1 : Install Guest Additions

First off, you need to start up the operating system you want to connect your hard disk drives to. Choose your OS and click the start button. When the OS is ready, click Devices  

> Install Guest Additions.

.

Clicking this will prompt the Guest Addition setup menu on Windows XP (Guest OS). Install the guest addition and reboot your guest OS as required.

Step 2 : Share Folder

When installed and rebooted, click Devices again and choose Shared Folders.



A box will open. Simply click the add icon (+ sign) on the right. From the Folder Path pull down menu, click Other.



A folder browser (similar to Windows Explorer) will open up. From here, you can select any hard drive or any folder inside a hard drive. Upon selecting click OK.

Step 3 : Connect to Shared Folder

Now, VirtualBox has shared the selected hard drive/folder (in our case, E drive) with the guest OS. You now need to access it. To do so, go to My Computer and in Other Places, right click on My Network Places and choose Map Network Drive.



Here in the Drive box, you have to select a Drive letter for the folder/hard disk drive you’re connecting to. You can choose randomly or select as your choice from the pull down menu. From the Folder box, click the browse button and click the plus sign on the left of ‘VirtualBox Shared Folders’ and then ‘\\vboxsvr’.
This will show you a list of folders or hard drives shared with the guest OS. Click the one you want to connect to and click OK. On the Map Network Drive, make sure the Reconnect at logon checkbox is checked if you want to automatically connect to this drive each time your guest OS starts up.

Step 4 : Access Shared Folder

You’re done! Now, go to My Computer and you’ll be able to access the hard disk drive of your computer right from the guest operating system installed inside VirtualBox.


I hope it was easy for you to follow this tutorial and now you are able to access your hard disk and install any software from your local hard disk drive.

-Harry

Installing Microsoft SharePoint Foundation

Installing Microsoft SharePoint Foundation

Note: Microsoft SharePoint Foundation (MSF) can be only installed on a 64-bit hardware

Recently i installed MSF on my local system , I just sharing my exp about how to install MSF. Installation and configuration of Microsoft SharePoint Server 2010. We will complete this tutorial in four main steps as:
      1.     Hardware and Software requirements
      2.     Pre-requisites
      3.     Installation
      4.     Post Setup Configuration



1.     Hardware and Software requirements

Microsoft SharePoint Foundation (MSF) can be only installed on a 64-bit hardware and Microsoft provides the detailed hardware and software requirements in the following TechNet article.This is the new name for Windows SharePoint Services V4.
http://technet.microsoft.com/en-us/library/cc262485(office.14).aspx#section2

2.     Pre-requisites

In this release Microsoft has included a Prerequisite Installer (Preparation Tool) which will take care of all the pre-requisites which are required to setup a SharePoint Server.The tool would connect to Internet to download and install any prerequisites as well as enabling and configuring any Windows features and roles required by setup. Also the administrator can override this if the necessary software is already present in the hard disk or in a network location.
The Preparation Tool executable, Prerequisiteinstaller.exe, is located at the root of the setup folder. It can also be launched from the setup splash screen.When you run the Prerequisiteinstaller.exe /? , the following screen is shown which will list the command-line parameters that can be used with this executable.



You can also install the pre-requisite from a folder if it is already downloaded from the internet and the SharePoint server doesn’t have internet access.

C:\setup\prerequinstaller.exe /IDFX:c:\GenevaFramework.amd64.msi

It is possible to include multiple file location parameters for the different components. In the following example, we've also added local file location parameter for both the IDFX and Sync framework:

C:\setup\prerequinstaller.exe /IDFX:c:\GenevaFramework.amd64.msi /SYNC c:\sync.msi

To install the Pre-requisites, run the prerequisiteinstaller.exe in the installation media of SharePoint or run the Splash which will provide you the following screen.



Click on the “Install Software prerequisites” option which will start the installation of the necessary pre-requisites.



You can get the download link for each of the required product when you click Learn more about these prerequisites in the above screen.

Click Next will start the download and installation of pre-requisites from the internet.



Accept the EULA and click Next and this will start the installation of the required product.



Some of them may require a Restart and the pre-requisite installer will continue once the system is restarted.



There is a link called “Review the log file” in the above screen which will help to find out any issue with the installation of the Pre-requisites.Once the installation is complete, you will see the following screen and it means that all the required Pre-requisites are installed and configured on the server and you can start the installation of SharePoint.

Installation of SharePoint Server 2010

When installing SharePoint Foundation, there are primarily three setup options.Following is a short description of each type of installation:

Standalone Installation:

  SQL Server 2008 Express Edition is the database type automatically installed (instead of Windows Internal Database/SQL Server 2005 Embedded Edition used in Windows SharePoint Services 3.0).   This is almost a “one-click” installation, no questions are asked during setup or during Post Setup Configuration Wizard (PSConfig)
A Web application and team site collection are automatically created in the newly created farm. The search service is started automatically.
Cannot add servers to join a farm.
When the installation is complete, the browser opens taking you to a newly created site collection. Installer is not prompted for farm passphrase, it is automatically generated.
Standalone Same as Basic installation, except it allows changing installation directory location. Complete SQL Server 2005 SP2/SQL Server 2008 is the database type, not installed by setup.
Administrator can pick whether or not to create a site and the site template to use.
Prompted for farm passphrase during PSConfig phase of installation. The farm passphrase will be discussed later in this document.
To run setup you must at minimum be a local administrator on the computer where SharePoint Server 2010 is installed.



Run the Splash from the setup media which will show the above screen and click on “Install SharePoint Foundation” which will start the installation process.



-Harry