Select Statement Examples [AX 2012]

All of the following examples use the CustTable.

General Examples

The following X++ job shows several small examples of how you can use the select statement.

X++

static void SelectRecordExamples3Job(Args _args)
{
    CustTable custTable;  // As of AX 2012.

    // A customer is found and returned in custTable
    select * from custTable;
    info("A: " + custTable.AccountNum);

    // A customer with account number > "100" is found
    select * from custTable
        where custTable.AccountNum > "100";
    info("B: " + custTable.AccountNum);

    // Customer with the lowest account number > "100" found:
    select * 
        from custTable 
            order by accountNum
                where custTable.AccountNum > "100";
    info("C1: " + custTable.AccountNum);

    // The next customer is read
    next custTable;
    info("C2: " + custTable.AccountNum);

    // Customer with higest account number
    // (greater than 100) found: Fourth Coffee
    select * 
        from custTable 
            order by accountNum desc
                where custTable.accountNum > "100";
    info("D1: " + custTable.AccountNum);
    
    // The next record is read (DESC): Fabrikam, Inc.
    next custTable; 
    info("D2: " + custTable.AccountNum);

    // Customer with highest account number found: Fourth Coffee
    select reverse custTable 
        order by accountNum;
    info("E: " + custTable.AccountNum);

    // Customer with "lowest" name and account number
    // in the interval 100 to 1000 is found. This is Coho Winery.
    select * 
        from custTable 
            order by DlvMode
                where custTable.accountNum > "100"
                    && custTable.accountNum < "1000";
    info("F: " + custTable.AccountNum);

    // The count select returns the number of customers.
    select count(AccountNum) 
        from custTable;
    // Prints the result of the count
    info(strFmt("G: %1 = Count of AccountNums", custTable.accountNum));

    // Returns the average credit max for non-blocked customers.
    select avg(CreditMax) 
        from custTable
            where custTable.blocked == CustVendorBlocked::No;
    // Prints the result of the avg
    info(strFmt("H: %1 = Average CreditMax", custTable.CreditMax));
}
/*** Display from infolog:
Message (02:00:34 pm)
A: 4000
B: 4000
C1: 4000
C2: 4001
D1: 4507
D2: 4506
E: 4507
F: 
G: 29 = Count of AccountNums
H: 103.45 = Average CreditMax
***/

Join Sample

This X++ code sample shows how an inner join can be performed as part of an SQL select statement.

The sample also shows an order by clause that has each field qualified by a table name. This enables you to control how the retrieved records are sorted by using only one order by clause.

X++

static void SelectJoin22Job(Args _args)
{
    CustTable xrecCustTable;
    CashDisc xrecCashDisc;
    struct sut4;

    sut4 = new struct("str AccountNum; str CashDisc; str Description");

    while select firstOnly10 *
        from xrecCustTable
            order by xrecCashDisc.Description
                join xrecCashDisc
                    where xrecCustTable.CashDisc ==
                        xrecCashDisc.CashDiscCode
                        && xrecCashDisc.Description LIKE "*Days*"
    {
        sut4.value("AccountNum", xrecCustTable.AccountNum );
        sut4.value("CashDisc", xrecCashDisc.CashDiscCode );
        sut4.value("Description", xrecCashDisc.Description );

        info(sut4.toString());
    }
/*********  Actual Infolog output
Message (02:29:37 pm)
(AccountNum:"1101"; CashDisc:"0.5%D10"; Description:"0.5% 10 days")
(AccountNum:"4001"; CashDisc:"0.5%D10"; Description:"0.5% 10 days")
(AccountNum:"1102"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"1201"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"2211"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"1202"; CashDisc:"1%D15"; Description:"1% 15 days")
(AccountNum:"1203"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2212"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2213"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2214"; CashDisc:"1%D07"; Description:"1% 7 days")
*********/
}

Group By and Order By

This X++ code sample shows that the fields in the group by clause can be qualified with a table name. There can be multiple group by clauses instead of just one. The fields can be qualified by table name in only one group by clause. Use of table name qualifiers is recommended.

The order by clause follows the same syntax patterns that group by follows. If provided, both clauses must appear after the join (or from) clause, and both must appear before the where clause that might exist on the same join. It is recommended that all group by and order by and whereclauses appear immediately after the last join clause.

X++

static void SelectGroupBy66Job(Args _args)
{
    CustTable xrecCustTable;
    CashDisc xrecCashDisc;
    struct sut4;

    sut4 = new struct("str AccountNum_Count; str CashDisc; str Description");

    while select
        count(AccountNum)
        from xrecCustTable
            order by xrecCashDisc.Description
                join xrecCashDisc
        group by
            xrecCashDisc.CashDiscCode
                    where xrecCustTable.CashDisc ==
                        xrecCashDisc.CashDiscCode
                        && xrecCashDisc.Description LIKE "*Days*"
    {
        sut4.value("AccountNum_Count", xrecCustTable.AccountNum );
        sut4.value("CashDisc", xrecCashDisc.CashDiscCode );
        sut4.value("Description", xrecCashDisc.Description );

        info(sut4.toString());
    }
/*********  Actual Infolog output
Message (02:45:26 pm)
(AccountNum_Count:"2"; CashDisc:"0.5%D10"; Description:"")
(AccountNum_Count:"3"; CashDisc:"0.5%D30"; Description:"")
(AccountNum_Count:"4"; CashDisc:"1%D07"; Description:"")
(AccountNum_Count:"1"; CashDisc:"1%D15"; Description:"")
(AccountNum_Count:"1"; CashDisc:"2%D30"; Description:"")
(AccountNum_Count:"1"; CashDisc:"3%D10"; Description:"")
*********/
}

Count Sample

This X++ code sample shows how to count number of records in a table through an SQL select statement. I don't want to use while loop.

X++

static void recordCountInInventTable(Args _args)
{
    InventTable     inventTable;
    
    select count(RecId) from inventTable
        where inventTable.NameAlias != "Obsolete"
        &&    inventTable.NameAlias != "Use to Depletion"
        &&    inventTable.FSProductApprovalStatus == FSProductApprovalStatus::Approved;
    //return inventTable.RecId;
    info(strFmt('%1', inventTable.RecId));
}

Or

/// <summary>
/// Get resource poool count.
/// </summary>
/// <returns>
/// resource pool count.
/// </returns>
protected int getResourcePoolCount()
{
select count(Rank) from resourceTable
where resourceTable.UserSession == _userSession
&& resourceTable.ResourceSet == ProjResourceSet::Pool;

return resourceTable.Rank;
}

Happy DAXing...

How to Open a table in AX 2012 through X++ code

Opening the table from x++ code

This code helps you to open the any Table from X++ code. Here is an example for SalesTable, just copy and paste this into a job you will get the table.

static void TableBrowser(Args _args)
{
     SysTableBrowser sysTableBrowser = new SysTableBrowser();

    //Browse the SalesTable table
   sysTableBrowser.run(tablenum(SalesTable ));
}

How to open a form by using AX code

In the shortest example, all it takes is one line of code.

new MenuFunction(MenuItemDisplayStr(CustTable),MenuItemType::Display).run();

The above code will open the CustTable form. That's all it takes, it's that simple.
Now if you want to supply some arguments to the opening form, this is also possible with the optional args parameter.
Like this for example:

static void OpenFormByCodeA()
{ Args args = new Args();
 
args.record(CustTable::find('ABC'));
new MenuFunction(MenuItemDisplayStr(CustTable),MenuItemType::Display).run(Args);
}

This code will open the CustTable form and filter out the customer with accountnumber ABC.
Use the args methods like parm and parmEnum to provide your target form with more data.

If you want even more control on opening the form from code, this is also possible.
This next example gives the same result as the previous one.

static void OpenFormByCodeB()
{ 

FormRun formRun;
Args args = new Args();

args.name(formstr(CustTable));
args.record(CustTable::find('ABC'));

formRun = ClassFactory.formRunClass(args);
formRun.init();
formRun.run();
formRun.wait();
}

Now if we tweak this a little bit, we can add our code
Like this:

static void OpenFormByCodeB()
{ 

Object formRun;
Args args = new Args();

args.name(formstr(CustTable));
args.record(CustTable::find('ABC'));

formRun = ClassFactory.formRunClass(args);
formRun.init();

formRun.yourmethodgoeshere(); /* !!

formRun.run();
formRun.wait();
}

Happy DAXing...

How to Open a table in AX 2012 through X++ code

Opening the table from x++ code

This code helps you to open the any Table from X++ code. Here is an example for SalesTable, just copy and paste this into a job you will get the table.

static void TableBrowser(Args _args)
{
     SysTableBrowser sysTableBrowser = new SysTableBrowser();

    //Browse the SalesTable table
   sysTableBrowser.run(tablenum(SalesTable ));
}

How to open a form by using AX code

In the shortest example, all it takes is one line of code.

new MenuFunction(MenuItemDisplayStr(CustTable),MenuItemType::Display).run();

The above code will open the CustTable form. That's all it takes, it's that simple.
Now if you want to supply some arguments to the opening form, this is also possible with the optional args parameter.
Like this for example:

static void OpenFormByCodeA()
{ Args args = new Args();
 
args.record(CustTable::find('ABC'));
new MenuFunction(MenuItemDisplayStr(CustTable),MenuItemType::Display).run(Args);
}

This code will open the CustTable form and filter out the customer with accountnumber ABC.
Use the args methods like parm and parmEnum to provide your target form with more data.

If you want even more control on opening the form from code, this is also possible.
This next example gives the same result as the previous one.

static void OpenFormByCodeB()
{ 

FormRun formRun;
Args args = new Args();

args.name(formstr(CustTable));
args.record(CustTable::find('ABC'));

formRun = ClassFactory.formRunClass(args);
formRun.init();
formRun.run();
formRun.wait();
}

Now if we tweak this a little bit, we can add our code
Like this:

static void OpenFormByCodeB()
{ 

Object formRun;
Args args = new Args();

args.name(formstr(CustTable));
args.record(CustTable::find('ABC'));

formRun = ClassFactory.formRunClass(args);
formRun.init();

formRun.yourmethodgoeshere(); /* !!

formRun.run();
formRun.wait();
}

Happy DAXing...

Export Obsolete Material from AX to Excel

static void ExportObsoluteItemIntoExcel(Args _args)
{

EcoResProduct ecoResProduct;
 //EcoResProductMaster ecoResProductMaster;


InventTable inventTable;

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

SysExcelCell cell;

SysExcelFont font;

int row = 1;


application = SysExcelApplication::construct();

workbooks = application.workbooks();

workbook = workbooks.add();

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells(); cells.range('A:A').numberFormat('@');

// Setting Header values

/*cell = cells.item(1, 1);

cell.value("Item number");

font = cell.font();

font.bold(true);*/
cells.item(1, 1).value("Item number");

cells.item(1, 1).font().bold(true);

cells.item(1, 2).value("Search name");

cells.item(1, 2).font().bold(true);

cells.item(1, 3).value("Item name");

cells.item(1, 3).font().bold(true);

while select ecoResProduct

where ecoResProduct.SearchName == "Obsolete"

|| ecoResProduct.SearchName == "Use to Depletion"
{
row++;/*cell = cells.item(row, 1);
cell.value(ecoResProduct.DisplayProductNumber);*/ cells.item(row, 1).value(ecoResProduct.DisplayProductNumber);

cells.item(row, 2).value(ecoResProduct.SearchName);

cells.item(row, 3).value(ecoResProduct.productName());
}application.visible(true);
}