File import in Ax 2012

Excel And CSV File import in Ax 2012

CSV File Import

Create a table like below figure and also add your needed fields.

Create a class and paste code, alter your fields and you're based on the requirement

Create a class

class RRCSVFileImport

{

    // Title  : CSV File import to ax 2012 R3

    // Author : Rajaram.E

}

Create a method and paste code like below

private void fileImport()

{

    // Table

    RRCSVFileImportTable    csvFileImportTbl;

    // File macro

    #File

    // Input Output File  - System class we are notable to view

    IO                      iO;

    // Variable

    CustAccount             custAccount;

    CustName                custname;

    FilenameOpen            filename;

    Container record;

    str one;

    boolean first = true;

    str Delimiter = ",";

    int totalRecords;

    // Create dialog a

    Dialog          dialog;

    DialogField     dialogFilename;

    dialog = new dialog();

    dialog.caption("select a file");

    // get file url

    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));

    //To filter the files while selecting

    dialog.filenameLookupFilter(record);

    dialog.run();

    if(dialog.closedOk())

    {

        filename = dialogFilename.value();

        iO = new CommaTextIo(filename,#IO_Read);

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

        {

            throw error("@SYS19358");

        }

        record = iO.read();

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

        {

            record = iO.read();

            if(!record)

            break;

            // Retrive from container

            csvFileImportTbl.CustName=conpeek(record, 1);

            csvFileImportTbl.ItemName=conpeek(record, 2);

            csvFileImportTbl.insert();

        }

    }

     info("Import completed");

}

Create the main method and call the fileImport method

static void main(Args _args)

{

    RRCSVFileImport objClass = new RRCSVFileImport();

    objClass.fileImport();

}

CSV File format

Open your class

Output

//---------------------------------------------------------------------

Excel file import

Create a table like below figure and also add your needed fields.

Create a class and paste code, alter your fields and you're based on the requirement

Create a class

class RRExcelImport

{

    // Title  : Excel file import in Ax 2012.

    // Author : Rajaram.E

}

Create a method

private void excelImportFile()

{

    // Excel file classes

   

    // Ms-Office Application class

    SysExcelApplication     application;

    // workbooks class find the number of workbooks

    SysExcelWorkbooks       workbooks;

    // get workbook name in the class

    SysExcelWorkbook        workbook;

    // Get number of worksheets

    SysExcelWorksheets      worksheets;

    // Get worksheet number

    SysExcelWorksheet       worksheet;

    // Worksheet cells

    SysExcelCells           cells;

   

    // Variable

    COMVariantType          type;

    int row=1;

    ItemId                  itemid;

    Name                    name;

    FileName                filename;

    // Table

    RRExcelImportTable      importTable;

   

    //To filter the files while selecting

    container      conFilter = ["Microsoft Excel 97-2003 Worksheet (.xls)" ,"*.xlsx"];

    // Dialog

    Dialog          dialog;

    DialogField     dialogFilename;

    dialog = new dialog();

    dialog.caption("select a file");

    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));

    //To filter the files while selecting

    dialog.filenameLookupFilter(conFilter);

    dialog.run();

    if(dialog.closedOk())

    {

        filename = dialogFileName.value();

        // Create construct of application - casting

        application = SysExcelApplication::construct();

        workbooks = application.workbooks();

       

        try

        {

            // excel file open

            workbooks.open(filename);

        }

        catch (Exception::Error)

        {

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

        }

        // which workbook item whant to import

        workbook    = workbooks.item(1);

        // get number of worksheets

        worksheets  = workbook.worksheets();

        // worksheet item name

        worksheet   = worksheets.itemFromNum(1);

        // find the cells

        cells       = worksheet.cells();

       

        do

        {

            row++;// skip header

            name    = cells.item(row, 1).value().bStr();// bStr() is string, tThere is many datatype you can change your requirements

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

            // assign values

            importTable.CustName    = name;

            importTable.ItemName    = itemid;

            importTable.insert();

            // find and variant increase column

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

        }

        // while checking when we quit import file

        while (type != COMVariantType::VT_EMPTY);

        // quit application

        application.quit();

    }

    info("Import completed");

}

Main method

public static void main(Args args)

{

    RRExcelImport importExcel = new RRExcelImport();

    importExcel.excelImportFile();

}

Excel file format:

Run your class

Output

Valid time state type in ax 2012

Valid time state type in ax 2012

A valid time state table property provides you maintenance of data for which changes must be tracked at different points in time. For example where benefits become effective and expire at certain times for employee.

On a table in the AOT, you can set the “ValidTimeStateFieldType” property to “Date” or “UtcDataTime” make it a valid time state table. When you set property system automatically will add the “ValidFrom” and” ValidTo” columns which track a date range in each row. The system guarantees that the values in these date or date-time fields remain valid by automatically preventing overlap among date ranges.

Note:

                    1.The "ValidTimeStateFieldType" property cannot be set for any table that inherits from another table.

2. Each valid time state table depends on an alternate key index.
3. The “ValidFrom” and “ValidTo” columns can both be of the date data type, or can both be of the utcDateTime data type.
4. In X++ select statement we can use “validTimeState” keyword to filter rows by date or date range. And in Query class, we can use “validTimeStateAsOfDateTimeRange ()” methods that provide filtering by date range.

Now we take simple example for understanding this functionality. First, we create a new table called “RRValidTimeStateType” and create two fields “Id” and Reason like figure 1.

After, we’ll set table property’s “ValidTimeStateFieldType – Date”. Then the system will automatically create two new date fields called "ValidFrom" and "ValidTo" like figure 2. Next, we'll add an index on the table, containing our Id identifier and the two date fields.

On “Idx” index set the property “AllowDuplicates – No” and “AlternateKey-Yes”. After this we'll set “ValidTimeStateKey – Yes” that will enable you to set “ValidTimeStateMode- NoGap/Gap” (default to NoGap) and we’ll set “NoGap”. The NoGap value tells the system that we do not allow gaps in the date ranges.

Now open the table and add a new record. Notice how the record defaults to ValidFrom with today's date, and ValidTo set to "never". 

Now, if you create another new record, it will again default in the same date values. If we enter the Id "01" again and try to save the record (CTRL+S), we’ll get the error "Cannot create a record in RRValidTimeStateType. Insert not supported with the values specified for 'ValidFrom' and 'ValidTo'. New record overlaps with multiple existing records”. 

Default dimension in Ax 2012

Creating default dimension in ax 2012

Description 

Financial dimensions are data classifiers that are used for financial reporting. Financial dimensions identify information such as the purpose, cost center, and department. Financial dimensions have changed considerably since Microsoft Dynamics AX 2012

Screen shoot and code are below

1. Create a table and fields 

2. Add int 64 data type fields - > Add EDT ' DimensionDefault'

3. Create a normal relations

4. RRFinancialDimensionTable.DimensionDefault == DimensionAttributeValueSet.RecId

5. Create a custom form 

6. Add datasource in the form 

7. Add design Tab - Tab page : General and add another TabPage : Financial dimension

8. Set Auto declaration for Financial dimension tab properties  : Yes 

9. Need permission : Manual for Financial dimension

10. Set HidIfEmpty :: No

11. Add data source and Caption

12. Drag and drop fields in the general tab

13. form run code below

DimensionDefaultingController dimDefaultingController;

14. Write form method  below code 

public void init()

{

    super();

    dimDefaultingController=DimensionDefaultingController::constructInTabWithValues(

        true, 

        true, 

        true, 

        0, 

        this, 

        Financialdimension, // Tabpage name 

        "@SYS138487"        // display label

        );

    dimDefaultingController.parmAttributeValueSetDataSource(

        RRFinancialDimensionTable_ds,       // Datasource

        fieldstr(RRFinancialDimensionTable, // Table

        DimensionDefault                    // fields 

        ));

}

15. Write below code in the datasource method

// Active override method

public int active()

{

    int ret;

    ret = super();

    dimDefaultingController.activated();

    return ret;

}

// write override method

public void write()

{

    dimDefaultingController.writing();

    super();

}

// delete override method

public void delete()

{

    super();

    dimDefaultingController.deleted();

}

16. Write Tabpage method below code

// page Activated override method

public void pageActivated()

{

    dimDefaultingController.pageActivated();

    super();

}