Writing Data to CSV file from Dyanamics AX

Here is a code to write date from Microsoft Dyanics Ax to comma separated file(.csv).

static void AX2CSV(Args _args)
{
    CommaTextIO file;
    container line;
    InventTable invenTable;
    #define.filename('u:\\items.CSV')
    #File
    ;
    file = new CommaTextIO(#filename, #io_write);
    if (!file || file.status() != IO_Status::Ok)
    {
        throw error("File cannot be opened.");
    }
    file.outFieldDelimiter(';'); // for semicolon seperator and default field delimitter is ,(comma)
    while select invenTable
    {
        line = [invenTable.ItemId,invenTable.ItemName,invenTable.ItemGroupId];
        file.writeExp(line);
    }
}
Good Luck.. :)

Reading a CSV file In Dynamics AX

Solution to read CSV file data to AX.
NS: you can set a delimitter to any charachter in InFielddelimitter() Method.

static void CSVtoAX(Args _args)
{
    #File
    CommaTextIo        commaTextIo;
    FileIOPermission   permission;
    container          containFromRead;
    int                x;
    int                cols;
    ;
    commaTextIo = new CommaTextIO('u:\\items.csv',#io_read);
    commaTextIo.inFieldDelimiter(';');
    containFromRead = commaTextIo.read();
    While(containFromRead)
    {
        cols = conLen(containFromRead);
        for(x=1;x<=cols;x++)
        {
            print conpeek(containFromRead,x);
        }
        containFromRead = commaTextIo.read();
    }
    pause;
    commaTextIo = null;
}

Writing date to Txt file

Here is a solution for writing a date to Text file from Dynamics Ax.

Before running this code a physical file should be created as specified in the code('u:\\items.txt')

static void DataToTxtFile(Args _args)
{
    TextIO file;
    container line;
    InventTable invenTable;

    #define.filename('u:\\items.txt')
    #File
    ;

    file = new TextIO(#filename, #io_write);

    if (!file || file.status() != IO_Status::Ok)
    {
        throw error("File cannot be opened.");
    }
    file.outFieldDelimiter(';');// for semicolon seperator
    while select invenTable 

    {
        line = [invenTable.ItemId,invenTable.ItemName,invenTable.ItemGroupId];
        file.writeExp(line);
    }
}

Reading data from txt file

static void ReadDateFromTxtFile(Args _args)
{
    #File
    TextIo                  io;
    container               c;
    str               itm, name;

    ;

    io = new TextIo("U:\\Test.txt", #io_read);
    if (!io)
    {
        throw error("Error reading file");
    }

    io.inFieldDelimiter(','); //This can be any delimiter, I'm just using Semicolon as an example
    io.inRecordDelimiter(#delimiterCRLF); //CRLF = Carriage Return Line Feed

    ttsbegin;
    while (io.status() == IO_Status::Ok)
    {
        c = io.read();
        if (io.status() != IO_Status::Ok)
        {
            break;
        }

        itm = conpeek(c, 1);
        name=  conpeek(c, 2);
        info(itm + '-->' + name);

    }
    ttscommit;

}

Reading Data from Excel sheet(.xls)

//Read Data from Excelsheet to AX.

static void ReadFromExcelFile(Args _args)
{
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorksheets sheets;
SysExcelWorksheet sheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
excel = SysExcelApplication::construct();
books = excel.workbooks();
//specify the file path that you want to read
filename = "U:\\item.xls";
try
{
books.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
book = books.item(1);
sheets = book.worksheets();
sheet = sheets.itemFromNum(1);
cells = sheet.cells();
do
{
row++;
itemId = cells.item(row, 1).value().bStr();
name = cells.item(row, 2).value().bStr();
info(strfmt('%1 - %2', itemId, name));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
excel.quit();
}

Good Luck :)

Writing Data to Excel file

//X++ Code to write data from AX to Excel sheet

static void WriteAX2ExcelFile(Args _args)
{
InventTable inventTable;
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorksheets sheets;
SysExcelWorksheet sheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
excel = SysExcelApplication::construct();
books = excel.workbooks();
book = books.add();
sheets = book.worksheets();
sheet = sheets.itemFromNum(1);
cells = sheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");

cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
row++;
cell = cells.item(row, 1);
cell.value(inventTable.ItemId);
cell = cells.item(row, 2);
cell.value(inventTable.ItemName);
}
excel.visible(true);
}

How to avoid the scaling info message while running reports

Avoiding the info message of scaling when the report is displayed.

Include this code either in init() or fetch() method

this.printJobSettings().suppressScalingMessage(True);

Hope This Will helps you to get rid of Irritating scaling message.. :)

Regards,
Prasan

Lookup() Method

Implement this code in the lookup() method of the field where you need the lookup to be displayed.

NS: The same code can be written in three places to achieve the lookup.
1) Under AOT->Table->Method and cal this method from the form design where you want to display the lookup.
2) Form->Datasource->Table->Field->method
3) Form->Design->Field->Method.

public void lookup()
{
Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource QbdsJoin;

// Instantiate sysTableLookup object using table which will provide the visible fields
SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(TableName), this);
;

// Create the query.
qbds= query.addDataSource(tableNum(TableName));
qbds.addRange(fieldNum(TableName, FieldName)).value('Value');

//Join Table
QbdsJoin= qbds.addDataSource(tableNum(TableName2));
QbdsJoin.relations(true);
QbdsJoin.joinMode(JoinMode::ExistsJoin);
QbdsJoin.addRange(fieldNum(TableName2, Fieldname)).value('Value');

// Set the query to be used by the lookup form
sysTableLookup.parmQuery(query);

// Specify the fields to show in the form.
sysTableLookup.addLookupfield(fieldNum(TableName, FiledName));
sysTableLookup.addLookupfield(fieldId2Ext(fieldNum(TableName, Dimension), 1));

// Perform the lookup
sysTableLookup.performFormLookup();
}