How to connect to an external DB from Dynamics AX using X++

How  to connect to an external DB from Dynamics AX using X++

1. Create a DSN

To create a Data Source Name (DSN) go to Administrative Tools > Data Sources (ODBC).

Create the DSN on the tier where the X++ code will call the DSN from

2. X++ code

static void TestOdbcJob()

{

    LoginProperty login;

    OdbcConnection con;

    Statement stmt;

    ResultSet rs;

    str strQuery, criteria;

    SqlStatementExecutePermission perm;

    ;

    // Set the information on the ODBC.
    login = new LoginProperty();

    login.setDSN("dsnName");

    login.setDatabase("databaseName");

    //Create a connection to external database.
    con = new OdbcConnection(login);

    if (con)

    {

        strQuery = strfmt("SELECT * from tableName WHERE XXX = ‘%1′ ORDER BY  FIELD1, FIELD2", criteria);

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(strQuery);

        perm.assert();

        //Prepare the sql statement.
        stmt = con.createStatement();

        rs = stmt.executeQuery(strQuery);

       

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (rs.next())

        {

            //It is not possible to get field 2 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print rs.getString(1);

            print rs.getString(2);

        }

        rs.close();

        stmt.close();

    }

    else

    {

        error("Failed to log on to the database through ODBC");

    }

}

How to print a different Sales Invoice per company in AX

How to print a different Sales Invoice per company in AX

If you want to print a different Sales Invoice per every company you have,  change the method printJournal in the table CustInvoiceJour and the form CustInvoiceJournal (MenuButton "SalesInvoiceShow" -> Copy, Original and Original print):

Modified method printJournal for the table CustInvoiceJour:

server void  printJournal(SalesFormLetter      salesFormLetter = null,

                          RecordSortedList     journalList     = null,

                          NoYes                copy            = NoYes::No)

{

    Args                parameters = new Args();

    MenuFunction        salesInvoiceMenu;

    ;

    // Show the correct report for the every company in AX

    switch (strupr(curExt()))

    {

        case "CEU":

            salesInvoiceMenu = new MenuFunction(menuitemoutputstr(OPPSalesInvoice),MenuItemType::Output);

            break;

       

        default:

            salesInvoiceMenu = new MenuFunction(menuitemoutputstr(SalesInvoice),MenuItemType::Output);

    }

    // End

    parameters.caller(salesFormLetter);

    if (journalList)

        parameters.object(journalList);

    else

        parameters.record(this);

    salesInvoiceMenu.run(parameters);

}

For every MenuItemButton below the SalesInvoiceShow, you must override the clicked method as follows:

void clicked()

{

    Args                parameters = new Args();

    MenuFunction        salesInvoiceMenu;

    ;

    // Let the menuItemButton as this, with original parameters but

    // don't call super, to avoid call directly to report SalesInvoice

    //super();

    switch (strupr(curExt()))

    {

        case "OPP":

            salesInvoiceMenu = new MenuFunction(menuitemoutputstr(OPPSalesInvoiceCopy),MenuItemType::Output);

            break;

        default:

            salesInvoiceMenu = new MenuFunction(menuitemoutputstr(SalesInvoiceCopy),MenuItemType::Output);

Thanks,

Vikas Mehta.

Change Color of your Dynamics AX Environments/Forms

 Change Color of your Dynamics AX  Environments/Forms

To change the color of the Dynamics forms to help indicate what environment is in use. It involved overriding the SysSetupFormRun.run() method, which will be called every time a form is opened. On the class SysSetupFormRun, create a new method with this code:

public void run()
{
SysSQLSystemInfo systemInfo = SysSQLSystemInfo::construct();
; 
super();


// Set the color scheme of this instance of the SysFormRun to RGB
this.design().colorScheme(FormColorScheme::RGB);


// If the database name is not the live version, change the color of the form

if (systemInfo.getloginDatabase() != 'DynamicsAX_test')
this.design().backgroundColor(0x112255);
}


 If your live and test systems use the same database name, but the AOS is running on different servers you can modify this code to to match on systemInfo.getLoginServer() != 'MyServerName'. You can change the color by setting the hex value. It uses the RGB values in reverse order: 0xBBGGRR. 

How to select multiple/all records in AX

Recently I came across an issue regarding the selection of multiple records in AX ,


You can do that in many ways:


1) Press offcourse CTRL and then select the records with the mouse.


2) Press SHIFT and scroll the mouse and select the end record(Make sure you keep the mouse button pressed.)


3) Incase you want to select all the records you need to press

 Ctrl+Shift+Home/End : To mark all records between the currently selected record and the first/last    record

 You will get the following warning.

Do you want to continue  loading all the lines? 

Click YES

Thanks,

Vikas Mehta.

AX Log Files

Just in case your system is running out of space or AX is too slow then delete the log files in the following location.


C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS


Thanks,
Vikas Mehta.