Best Practices for Table Methods

http://raghuaxapta.blogspot.in/2015/11/best-practices-for-table-methods.html

Filtering the data(record) based on selected Input in Ax

For filtering a particular record    :

1.First create a table  i.e TableName   with fields name , Ids(Base enum) ,  Phno , Age

2.Create a form and drag the table TableName into data source.
in design take a grid and a combo box   give enum type as baseenum 

3.In class:
       public class FormRun extends ObjectRun
       {
             QueryBuildRange qbr2;  //  declartion of range variable
       }

4.In Data source level . Override a method init()

1. public void init()
   {
         super();
        qbr2 = this.query().dataSourceName(“Datasourcename”).addRange(fieldNum(TableName, BaseEnum1));
   }

5.in combo box – in methods override a selectionchange method()
        public int selectionChange()
        {
               int ret;
               ret = super();
               datasourcename_ds.executeQuery();
               return ret;
        }

6.Create a range by  overriding method executequery() in data source level: 
       public void executeQuery()
       {
           qbr2.value(comboboxName.valueStr());
           super();
       }

Note

     Set Auto Declaration :Yes for Combo box

How many Tables are Present under AOT through X++ Code

static void TotalTablesinAx(Args _args)
{
    int x;
    x = TreeNode::findNode('\\Data Dictionary\\Tables').AOTchildNodeCount();
    info(strFmt("Total Tables in AOT = %1",x));
 
}

Import and Export Data to a Text file, Excel through X++ Code in ax 2012,

Import CSV file

static void ReadCsvFile(Args _args)
{
    #File
    IO  iO;
    CustAccount custAccount;
    CustName custname;
    FilenameOpen        filename = "d:\\Rajendra.csv"; // --file path
    Container           record;
    Table                  testTable;
    boolean first = true;
    ;
    iO = new CommaTextIo(filename,#IO_Read);
    if (! iO || iO.status() != IO_Status::Ok)
    {
        throw error("@SYS19358");
    }
    while (iO.status() == IO_Status::Ok)
    {
        record = iO.read();// To read file
        if (record)
        {
            if (first)  //To skip header
            {
                first = false;
            }
            else
            {
           
                custAccount = conpeek(record, 1);//To peek record
                custGroup = conpeek(record, 2);
                testTable.CustAccount = custAccount;
                testTable.custGroup     = custGroup ;
                testTable.insert();
                //info(strfmt('%1--%2',custAccount,custname));
            }
        }
    }
}

static void UpdItemChannelCode(Args _args)

{

    #File

    str                         initPath                = @"C:\TEMP\";

    //import

    IO                          fileImport;

    FilenameOpen                fileNameImport;

    FileNameFilter              filter                  = ["CSV file", "*.csv"];

    str                         importInfieldDelimiter  = ';';

    container                   recordImport;

    boolean                     importHasHeader         = true;

    //import

    DataAreaId                  companyCode;

    HSOChannelCode              channelCode;

    HSOAPSeasonCodeId           seasonCodeId;

    HSOAPItemCategoryId         categoryId;

    ItemId                      itemId;

    InventTable                 inventTable;

    HSOChannelTable             hsoChannelTable;

    int                         counter;

    int                         errorCnt;

    boolean                     rowOk;

    ;

    //import

    fileNameImport = WinAPI::getOpenFileName(infolog.hWnd(), filter, initPath , 'Item Channel code - Load CSV file',"csv","",1);

    if(!fileNameImport)

        return ;

    fileImport = new CommaTextIo(fileNameImport,#IO_Read);

    fileImport.inFieldDelimiter(importInfieldDelimiter);

    if (! fileImport || fileImport.status() != IO_Status::Ok)

    {

        throw error("@SYS19358");

    }

    //import

 

    while (fileImport.status() == IO_Status::Ok)

    {

        recordImport = fileImport.read();// To read file

 

        if (recordImport)

        {

            if (importHasHeader)  //To skip header

            {

                importHasHeader = false;

            }

            else

            {

                rowOk               = true;

                companyCode         = "3000";

                itemId              = strLRTrim(conpeek(recordImport, 1));

                seasonCodeId        = strLRTrim(conPeek(recordImport, 2));

                categoryId          = strLRTrim(conPeek(recordImport, 3));

                channelCode         = strLRTrim(conPeek(recordImport, 4));

 

                ttsBegin;

 

                select forUpdate inventTable

                    where inventTable.ItemId    == itemId

                    && inventTable.dataAreaId   == companyCode;

               

                if (!inventTable)

                {

                    rowOk = checkFailed(strFmt(inventTable::txtNotExist(), itemId));

                }

                if (rowOk)

                {

                    inventTable.HSOAPSeasonCodeId   = seasonCodeId;

                    inventTable.HSOAPItemCategoryId = categoryId;

                    inventTable.HSOChannelCode      = channelCode;

                    inventTable.doUpdate();

                    counter ++;

                }

                else

                {

                    errorCnt ++;

                }

                ttsCommit;

            }

        }

    }

    info(strFmt("Import is finished. %1 record(s) were updated, %2 error(s) occured.", counter, errorCnt));

    info("@SYS112206");

}

Export CSV file 

static void ExportTextfile(Args _args)
{
    CustTable   custTable;
    BinData     binData;
    TextBuffer  textBuffer;
    ;

    textBuffer = new TextBuffer();
    textBuffer.setText('');

    while select custTable where custTable.AccountNum || custTable.Currency || custTable.PaymMode
    {
        textBuffer.appendText(strfmt('%1---%2---%3\r\n ',custTable.AccountNum,custTable.Currency,custTable.PaymMode));
    }

    textBuffer.getText();

    binData = new BinData();
    binData.setStrData(textBuffer.getText());
    binData.saveFile(@"C:\New2.txt");
 
}
                         Export Table Data to Excel 

static void DataExportToExcel(Args _args)
{
    //InventTable inventTable;
    Assettable  assettable;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    int row;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
   // worksheet = worksheets.itemFromNum(1);
   worksheet    = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');
    cell = cells.item(1,1);
    cell.value("Asset Id");
    cell = cells.item(1,2);
    cell.value("Asset Group");
    cell = cells.item(1,3);
    cell.value("Name");
    cell = cells.item(1,4);
    cell.value("Name Alias");
    cell = cells.item(1,5);
    cell.value("Responsible");
    cell = cells.item(1,6);
    cell.value("Location");
    cell = cells.item(1,7);
    cell.value("Asset Type");
    row = 1;
    while select assettable order by assettable.AssetId asc
    {
        row++;
        cell = cells.item(row, 1);
        cell.value(assettable.AssetId);
        cell = cells.item(row, 2);
        cell.value(assettable.AssetGroup);
         cell = cells.item(row, 3);
        cell.value(assettable.Name);
         cell = cells.item(row, 4);
        cell.value(assettable.NameAlias);
         cell = cells.item(row, 5);
        cell.value(assettable.Responsible);
         cell = cells.item(row, 6);
        cell.value(assettable.Location);
         cell = cells.item(row, 7);
        cell.value(assettable.AssetType);

    }
    application.visible(true);
}

Import Excel to Ax 

static void ImportInventLocation(Args _args)

{

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    Name                    name;

    FileName                filename;

    //ProductType productType;

    InventLocation          inventLocation;

    int row;
    Dialog          dialog;
    Dialogfield     dialogfield;

    

    boolean first = true;

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    

    //filename = @"C:\Users\Desktop\ImportWareHouse.xlsx";
    dialog = new dialog('Excel Import');
    dialogfield = dialog.addField(extendedTypeStr(FilenameOpen), 'File Name');
   
    dialog.run();
    filename =(dialogfield.value());

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1); 

    cells = worksheet.cells();

    do

    {

        row++;

       

        inventLocation.InventLocationId = cells.item(row, 1).value().bStr();

        inventLocation.Name             = cells.item(row, 2).value().bStr(); 

        inventLocation.InventSiteId     = cells.item(row, 3).value().bStr();

        inventLocation.insert();

        type = cells.item(row+1, 1).value().variantType();

        //For time import using excel
/* timesheet.StartTime             = time2Str(str2time(cells.item(row,6).value().bStr()), TimeSeparator::Colon, TimeFormat::Hour24);//cells.item(row,6).value().bStr();
timesheet.EndTime               = time2Str(str2time(cells.item(row,7).value().bStr()), TimeSeparator::Colon, TimeFormat::Hour24);//cells.item(row,7).value().bStr();
timesheet.Work                  = COMVariant2Str(cells.item(row, 8).value());*/

        

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    workbooks.close();

    

    info("Done");

}

Export Excel Template
public  void Template()
{
    SysExcelWorksheetHelper worksheetHelper;
    SysExcelHelper          sysExcelHelper;
    SysExcelWorksheet       worksheet;
    int                     column = 1;
    int                     row = 1;
    str                     worksheetName;
    SysDictField            dictField;
    SysExcelCell            excelCell;
    int                     redColor = WinAPI::RGB2int(255, 0, 0);
    SysExcelRange           range;
    COMVariant              cellValue = new COMVariant(COMVariantInOut::Out);
    DictTable DictTable;

    int fieldCount, _x, fieldId,enumValues, enumValue;

    sysExcelHelper = SysExcelHelper::construct();

    sysExcelHelper.initialize();

    worksheet = sysExcelHelper.addWorksheet("Template");
    worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);

    DictTable = new DictTable(tableNum(Fcc_TimeSheetManagementTable));// Use your table name here.

    fieldCount = DictTable.fieldCnt();
    fieldId = DictTable.fieldNext(0);

worksheetHelper.addColumn(1,"Worker Num",Types::String);
worksheetHelper.addColumn(2,"Worker Date",Types::Date);
    worksheetHelper.addColumn(3,"Worker Hospital",Types::String);
    worksheetHelper.addColumn(4,"Report No",Types::String);
    worksheetHelper.addColumn(5,"Equipment",Types::String);
    worksheetHelper.addColumn(6,"Start Time",Types::String);
    worksheetHelper.addColumn(7,"End Time",Types::String);
     worksheetHelper.addColumn(8,"Work",Types::Integer);
     worksheetHelper.addColumn(9,"OT",Types::Integer);
     worksheetHelper.addColumn(10,"Travel",Types::Integer);
     worksheetHelper.addColumn(11,"Office",Types::Integer);
     worksheetHelper.addColumn(12,"Total",Types::Integer);


    sysExcelHelper.launchExcel();
  //  return ret;


}

How to open Word Document Through Code in x++

static void OpenWordThroughCode()
{
  COM   wordApp;
  COM   wordDocuments;
  wordApp = new COM("word.application");
  wordDocuments = wordApp.Documents();

  wordDocuments.Open(@"C:\Users\rajendra.c\Desktop\Table Properties.docx");
  wordApp.visible(true);

}

How To Get System IP Address through X++ Code

static void SystemIpAddress(Args _args)

{

     System.String              hostName    =   System.Net.Dns::GetHostName();

     System.Net.IPHostEntry     hostEntry   =   System.Net.Dns::GetHostEntry(hostName);

     System.Net.IPAddress[]     address     =   hostEntry.get_AddressList();

     System.Net.IPAddress       adress ;

     System.Net.Sockets.AddressFamily   adressFamily;

     System.Collections.IEnumerator     enumerator  =   address.GetEnumerator();

    while(enumerator.MoveNext())

    {

        adress  =   enumerator.get_Current();

        adressFamily = adress.get_AddressFamily();

        if(adressFamily == System.Net.Sockets.AddressFamily::InterNetwork)

        {

            info(adress.ToString());

        }

    }

}

How to Create Purchase Order Through Code in X++

static void PurchOrderCreate(Args _args)
{
    NumberSeq numberSeq;
    PurchTable purchTable;
    PurchLine purchLine;
    ttsBegin;
    numberSeq = NumberSeq::newGetNum(PurchParameters::numRefPurchId());
    numberSeq.used();
    purchTable.PurchId = numberSeq.num();
    purchTable.initValue();
    purchTable.initFromVendTable(VendTable::find('8009'));
    if (!purchTable.validateWrite())
    {
        throw Exception::Error;
    }
    purchTable.insert();
    purchLine.PurchId = purchTable.PurchId;
    purchLine.ItemId = 'A0001';
    purchLine.createLine(true, true, true, true, true, true);
    ttsCommit;
    info(strFmt("Purchase order '%1' has been created",purchTable.PurchId));
}