Importing table data from CSV file to AX using CommaIo class in AX 2012

Create a class with the following code :

class importCSVCommaIO extends RunBase
{
    Filename        filename;
    DialogField     dialogFilename;
}

public static void main(Args    _args)
{
    importCSVCommaIO    importDept = new importCSVCommaIO();
    FormRun formRun;
    Args    args;

    if(importDept.prompt())
    {
        importDept.importDeptTable();
    }
}

public Object dialog()
{
    DialogRunbase       dialog = super();

    dialogFilename   = dialog.addField(extendedTypeStr(FilenameOpen));
    dialogFilename.value(filename);
    return dialog;
}

public boolean getFromDialog()
{
    fileName = dialogFileName.value();
    return super();
}

void importDeptTable()
{
    CommaIo         commaIO;
    DepartmentTable department;
    DepartmentTable departmentTable;

    Container       con;
    boolean first = true;
    FileIOPermission    readPermission;
    #file

    readPermission = new FileIOPermission(filename,#io_read);
    readPermission.assert();

    commaIO = new CommaIo(filename,'r');
    commaIO.inFieldDelimiter(',');
    CodeAccessPermission::revertAssert();

    if (commaIO)
    {

        while(commaIO.status() == IO_Status::OK)
        {
            con = commaIO.read();
            if (conLen(con) > 0)
            {
                if(first)
                {
                    first = false;
                }
                else
                {
                    ttsbegin;
                   
                    department = DepartmentTable ::find(conpeek(con,1), true);
                    if(department.RecId == 0)
                    {
                        department.DeptId       =   conpeek(con,1);
                        department.Description  =   conpeek(con,2);
                        department.insert();
                    }
                    else
                    {
                        department.selectForUpdate(true);
                        select departmentTable where departmentTable.DeptId == department.DeptId;
                        department.Description = conPeek(con,2);
                        department.update();
                    }
                     ttscommit;
                }

            }
        }

    }
}

select the csv file from the browse button and on press OK the data gets imported into the table.

Exporting data from AX to csv file using commaIO class in AX 2012

Write a job with the following code :

static void ExportDataToCSVcommaIO(Args _args)
{
    Query                                query;
    QueryBuildDataSource    qbds;
    QueryBuildRange            qbr;
    QueryRun                        qRun;
    CommaIo                         commaIO;
    Filename                          fileName;
 
    DepartmentTable          department;
    #WINAPI;
 
    fileName = WinAPI::getFolderPath(#CSIDL_DESKTOPDIRECTORY) + "\\" + "Department details" + ".csv";
 
    commaIO = new CommaIo(fileName,'W');
 
    query = new Query();
    qbds = query.addDataSource(tableNum(DepartmentTable));
    qbr = qbds.addRange(fieldNum(DepartmentTable,DeptId));
 
    qRun = new QueryRun(query);
 
    commaIO.write("DeptId","Description");
    while(qRun.next())
    {
        department = qRun.get(tableNum(DepartmentTable));
     
        commaIO.write(department.DeptId, department.Description);
    }
 
    WinAPI::createFile(fileName);
}

NOTE :

1) #CSIDL_DESKTOPDIRECTORY gets the desktop of the current user logged into.
2) A new file gets created with the specified name in the filename in the given folder path.

Exporting data using shellexecute method of WINAPI class:

fileName       = WINAPI::getTempPath() + "Department Details" + ".csv";

WINAPI::shellExecute(fileName);

When you run the job with the above code excel file with the table data gets opened automatically.

Moving all files from a folder of specified format from one folder to other using WINAPI class in AX 2012

The following piece of code filters the source file path folder for the files with extension .docx and copies them into the destination path specified.

public void moveAllFiles()
{
    fileFilter = '\\*.docx';

    sourcePath = dialogSource.value();

    destPath = dialogDestination.value();

    [fileHandle, filename] = WinAPI::findFirstFile(sourcePath + fileFilter);

    while(fileName)
    {
        info(strFmt("%1 %2", sourcePath , fileName));

        copyFromSourceFolder = sourcePath + "\\" + fileName;
       
        copyToDestFolder = destPath + "\\" + filename;
       
        WinAPI::copyFile(copyFromSourceFolder, copyToDestFolder);

        fileName = winAPI::findNextFile(fileHandle);
    }
}

NOTE :

1) The extension format may be anything .docx .txt .xpo or anthing.
2) Here source and destination dialog fields are of type filepath.

File Operation using WinAPI in Dynamics AX 2012

Axapta’s WinAPI class has a bunch of static methods to handle files. It is a very useful class when handling files that need to be accessed outside of the AX environment

The examples below show how to utilize some of these methods.

1) Create a dialog which takes source and destination file name and file path respectively and on click of OK button COPY the file from source folder to destination folder.

Create a class example_WINAPI with the following methods :

class example_WINAPI extends RunBase
{
    FilenameOpen    fileNameOpen;

    Filename    filename;
    Filename    filePath;
    Filename    fileType;
    Filename    destPath;

    DialogField dialogSource;
    DialogField dialogDestination;

    str sourceFileName;

    str destFileName;
}

public static void main(Args    args)

{

    example_WINAPI  fileDialog = new example_WINAPI();

    fileDialog.createDialog();

}

public void createDialog()

{

    Dialog dialog;

    dialog = new Dialog("Move files");

    dialog.caption('Move file');

    dialogSource        =   dialog.addField(extendedTypeStr(FilenameOpen), "@SYS1425");

    dialogDestination   =   dialog.addField(extendedTypeStr(FilePath), "@SYS37735");

    dialogSource.value(filename);

    if(dialog.run())

    {

        this.copyFile();

    }

}

public void copyFile()

{

    sourcefileName = dialogSource.value();

    [filePath, fileName, fileType] = fileNameSplit(sourcefileName);

    destPath = dialogDestination.value();

    destFileName = destPath + "\\" + conPeek(fileNameSplit(sourceFileName),2) +    conPeek(fileNameSplit(sourceFileName),3);

    info(queryValue(destFileName));

    WinAPI::copyFile(sourceFileName, destFileName);

}

2) Create a dialog which takes source and destination file name and file path respectively and on click of OK button MOVE the file from source folder to destination folder.

Add one more method moveFile to the above class and call it from createDialog method

public void moveFile()

{

    sourcefileName = dialogSource.value();

    if(WinAPI::fileExists(sourceFileName))

    {

        [filePath, fileName, fileType] = fileNameSplit(sourcefileName);

        destPath = dialogDestination.value();

        destFileName = destPath + "\\" + conPeek(fileNameSplit(sourceFileName),2) + conPeek(fileNameSplit(sourceFileName),3);

       

        WinAPI::moveFile(sourceFileName, destFileName);

    }

    else

    {

        warning("This file doesnot exist in the specified path");

    }

}

NOTE :

In the above method one more method fileExists is used from WINAPI class to check whether the specified file is present in the path or not. If present then executes the code else throws the warning message.

3) Delete a file in a specified path :

WINAPI::deleteFile(fileName);

Simple query filter in ax 2012

The following is an example of filtering based on itemId selected.

Basically you have a form which has a grid at the top and item id , item group text fields where you need to select both item id and item group from the lookup and the grid is filtered based on the values selected.

1) In the class declaration :

class FormRun extends ObjectRun
{
    InventTable inventTable;  // create a buffer for the datasource

    QueryBuildRange qbrItemId;
    QueryBuildRange qbrItemGroup;
}

2) Add inventTable in the datasource node and write the following code in the init of that datasource

public void init()
{
    QueryBuildDataSource qbds;

    super();

    qbds           =   this.query().dataSourceTable(tableNum(InventTable));
    qbrItemId   =  qbds.addRange(fieldNum(InventTable, ItemId));
}

3) Add InventItemGroupItem in the datasource node and write override init

public void init()
{
    QueryBuildDataSource qbds;

    super();

    qbds                   =   this.query().dataSourceTable(tableNum(InventItemGroupItem));
    qbrItemGroup    =  qbds.addRange(fieldNum(InventItemGroupItem, ItemGroupId));
}

4) Add ItemId stringedit form control to the design node and override modified method in the methods node and write the following code

public boolean modified()
{
    boolean ret;
    str     value;

    ret = super();

    if(ret)
    {
        value = qbrItemId.value('*' + InventTable_ItemId1.valueStr() + "*");
     
        InventTable_ds.executeQuery();
        InventTable_ds.refresh();
        InventTable_ds.research();
    }

    return ret;
}

NOTE :

In the above code InventTable_ItemId1 is the form control name whose auto declaration is set to yes.

5) Add ItemGroupId form control to design node and override lookup method

public void lookup()
{
    SysTableLookup        sysTableLookup =     SysTableLookup::newParameters(tableNum(InventItemGroup), this);
    Query                 query = new Query();
    QueryBuildDataSource  qbdsInventItemGroup;

    qbdsInventItemGroup = query.addDataSource(tableNum(InventItemGroup));
    qbdsInventItemGroup.addOrderByField(fieldNum(InventItemGroup, ItemGroupId));

    qbdsInventItemGroup.addRange(fieldNum(InventItemGroup, Name)).value(SysQuery::valueNotEmptyString());

    sysTableLookup.addLookupfield(fieldNum(InventItemGroup, ItemGroupId),true);
    sysTableLookup.addLookupfield(fieldNum(InventItemGroup, Name),false);

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

6) Override modified method

 public boolean modified()
{
    boolean ret;

    ret = super();

    if(ret)
    {
        qbrItemGroup.value('*' + InventItemGroupItem_ItemGroup.valueStr() + '*');
   
        InventTable_ds.executeQuery();
        InventTable_ds.research();
    }

    return ret;
}

Passing values from one form to other using args AX 2012

You have two fields in a form and on click of OK button new form should open and values should be passed to new form.

add this code in the OK clicked method :

    FormRun formRun;
    container con;
    str strcon;

    Args args = new Args();

    con =    [SMAServiceOrderLine_W_StartDate.valueStr(),SMAServiceOrderLine_W_EndDate.valueStr()];
    strcon = con2Str(con);
    args.parm(strcon);

    args.name(formstr(NEWFORMNAME));

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

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


The values entered in this form can be retrieved in other form using container. Start date and end date are the two values that are passed from this form

Enabling and disabling fields based on enum type in AX 2012

Example you have transaction type Enum in SMAServiceOrderLine table,
then you have a requirement to enable some fields in SMAServiceOrderLine_W only when smatransaction type = hours else disabled.

Then goto datasource methods and override active method and write the following code :

public int active()
{
    int ret;

    ret = super();

    if(smaserviceorderline.transactiontype != smatransactiontype::hour)
    {
        smaserviceorderline_w_ds.allowedit(false);
    }
    else
    {
         smaserviceorderline_w_ds.allowedit(true);
    }

    return ret;
}