Add display method to List page (VendTableListPage) AX 2012

Hi Experts,

As per requirement of client, PAN number should be display on vendor list page. I have created a display method on VendTable and added to vendTableListPage design


display PANNumber_IN getVendPANNumber()
{
    TaxInformationVendTable_IN      taxInformationVendtable_IN;

    select firstOnly PANNumber from taxInformationVendtable_IN
        where taxInformationVendtable_IN.VendTable == this.AccountNum;

    return taxInformationVendtable_IN.PANNumber;
}

After adding fields on Design, Save and compile the form.
**Most Important**
Go to the front end i.e. AX Client, Accounts Payable/ Vendors, the vendor list page form will open, Right click on form then Click on Reset button. After this you have to close the form and reopen again.

Test Data Transfer tool

1. Download Test Data Transfer Tool
2. Install it on Database server
3. Import MetadataGenerator.XPO in Source AX application. (From where you want to export data)
4. Run job MetadataGenerator and find the  Metadata file
5. Paste Metadata file into [Lists] folder. ( check your installation path in DB server)  it will be like "C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)\[Lists]"
6. To export data from specific company, open SQL Sever management studio and select your source database. Right click and new query. Copy paste below given code into query window. Replace your company name. (Company Name highlighted in bold font). Run the script and copy ouput.
7. Create new text file and paste query output. Save this file with name Filters.xml
8. Put Filters.xml file into [Lists] folder (Path given in step 5)
9. Open command prompt with Run as administrator.
10. Brows the path of DP.exe ("C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)")
11.  Enter below command and hit enter.
DP.exe  Export "D:\Exported Data"  MicrosoftDynamicsAX  AXDBServerName

Filters.xml file content is as below.

-- Declare the variables to store the values returned by FETCH.
DECLARE @tableName varchar(100);
DECLARE @fieldName varchar(100);
SET @fieldName = 'DataAreaID';

DECLARE table_cursor CURSOR FOR
SELECT  Name FROM sys.tables ORDER BY Name;

OPEN table_cursor;

-- Perform the first fetch and store the values in variables. 
PRINT '<tables>'
FETCH NEXT FROM table_cursor
INTO @tableName;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- All tables don't have DataAreaId field, So check first that table is having dataareaid filed and then
-- add tableName to xml
IF COL_LENGTH(@tableName, @fieldName) IS NOT NULL
BEGIN
-- Concatenate and display the current values in the variables.
PRINT '<table name="' + @tableName +'"><filter><field>DataAreaID</field>=''USMF''</filter></table>'-- + @tableName --+ ' ' +  @LastName
END
-- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM table_cursor
   INTO @tableName;
END
CLOSE table_cursor;
PRINT '</tables>'
DEALLOCATE table_cursor;
GO  

Rebuild and Update balances through X++

Hi Guys,

If you are posting journals through X++ and facing issues in trial balance update you can use below code to rebuild and update balances.


public void run()
{
    DimensionFocusProcessBalance    dimFocusProcessBal = new DimensionFocusProcessBalance();
    DimensionFocusUpdateBalance     dimFocusUpdateBal = new DimensionFocusUpdateBalance();
    DimensionHierarchy focusDimensionHierarchy;

    while Select focusDimensionHierarchy
        where focusDimensionHierarchy.FocusState == DimensionFocusBalanceInitializationState::Initialized
    {
        dimFocusProcessBal = DimensionFocusProcessBalance::construct(NoYes::No, focusDimensionHierarchy);
        dimFocusProcessBal.run();
        dimFocusUpdateBal = DimensionFocusUpdateBalance::construct(focusDimensionHierarchy);
        dimFocusUpdateBal.run();
    }

    info("Balances Updated !");
}

Export data to EXCEL | AX 2009 | AX 2012 | Excel Report

Hi Techies,

Hope you are doing good. Recently due to INDIA GST Update in Microsoft dynamics AX most of the clients want to export data in excel to prepare GSTR excel files.

I designed a good excel worksheet with X++ and exported data as per requirement. I am sharing with you how can you design excel sheets with the help of X++ code. Please check below.

Step 1. Create a Class. This is class declaration method.

public class MKS_ExportToExcel extends Runbase
{
    #AviFiles
    DialogField         dlgFromDate,dlgToDate;
    TransDate           fromDate,toDate;

    //Excel file declaration
    SysExcelApplication  xlsApplication;
    SysExcelWorkBooks    xlsWorkBookCollection;
    SysExcelWorkBook     xlsWorkBook;
    SysExcelWorkSheets   xlsWorkSheetCollection;
    SysExcelWorkSheet    xlsWorkSheet;
    SysExcelRange        xlsRange;
    int                  row;
    str                  fileName;

    VendInvoiceJour     vendInvoiceJour;

    #DEFINE.CurrentVersion(1)
    #LOCALMACRO.CurrentList
        fromDate,
        toDate
    #ENDMACRO
}

Step 2. Create Dialog method.

protected Object dialog()
{
    Dialog      dialog = super();
    #resAppl
    ;

    // Set a title for dialog
    dialog.caption('Inward Supply Register Report');
    dialog.addTabPage('Selection Criteria');
    dialog.addGroup('Select date range');
    // Add a new field to Dialog
    dlgFromDate = dialog.addFieldValue(typeid(TransDate), fromDate,"From date");
    dlgToDate   = dialog.addFieldValue(typeid(TransDate), toDate, 'To date');

    return dialog;

}

Step 3. Create getFromDialog method.

public boolean getFromDialog()
{
    ;
    // Retrieve values from Dialog
    fromDate = dlgfromDate.value();
    toDate = dlgToDate.value();

    return super();

}

Step 4. main Method

public static void main(Args _args)
{
     MKS_ExportToExcel reportClass = new MKS_ExportToExcel();

    // Prompt the dialog, if user clicks in OK it returns true
    if (reportClass.prompt())
    {
        reportClass.run();
    }

}

Step 5. run method.

public void run()
{
    ;
    this.setExcelInitialsAndHeader();
    this.fetchDataIntoExcel();
    this.endExcel();

}

Step 6. setExcelInitialsAndHeader - To setup Excel report heading section.

public void setExcelInitialsAndHeader()
{
    COM                     range;
    COM                     autoFit;
    COM                     merge;
    COM                     WrapText;
    COM                     ColumnWidth;
    COM                     RowHeight;

    SysExcelStyles          styles;
    SysExcelStyle           style;
    SysExcelFont            font;
    SysExcelCells           cells;
    FileNameFilter          filter = ["Excel file", "*.xlsx"];
    ;

    row = 1;

    fileName = winapi::getSaveFileName(infolog.hWnd(), filter , @"c:\...\desktop", "Save as Excel file","xlsx","Report name");
    //fileName ="C:\\Windows\\Temp\\test.xlsx";

    if(!fileName)
    return;
    //Check whether the document already exists
    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);
    //throw error("File already exist");


    //Initialize Excel instance
    xlsApplication           = SysExcelApplication::construct();

    //Open Excel document
    //xlsApplication.visible(true);


    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

    Styles = xlsWorkBook.styles();
    // Create new style
    style = styles.add('Header');
    // Set font for this style to bold
    font = style.font();
    font.bold(true);
    font.color(51);


    //Excel Report Heading
    range = xlsWorkSheet.range('A1:F1').comObject();
    range.merge();
    RowHeight = range.RowHeight(30);

    xlsWorkSheet.rows().item(1).style('Title');

    range = xlsWorkSheet.range('A2:BE2').comObject();
    xlsWorkSheet.rows().item(2).style('Output');
    xlsWorkSheet.rows().item(2).horizontalAlignment(-4108);
    wrapText = range.wraptext(true);

    ColumnWidth = range.ColumnWidth(14.71);

    xlsWorkSheet.cells().item(row,1).value('GST Report');
    row++;

    //Excel column caption
    xlsWorkSheet.cells().item(row,1).value('Sr. No.');
    xlsWorkSheet.cells().item(row,2).value('Vendor Code');
    xlsWorkSheet.cells().item(row,3).value('Vendor Name');
   
    row++;

}

Step 6. endExcel -  To Setup process while closing excel.

public void endExcel()
{
    ;
    // Set the column width to autoFit
    xlsWorkSheet.columns().autoFit();
    //Save Excel document
    xlsWorkbook.saveAs(fileName);


    xlsWorkbook.comObject().save();
    xlsWorkbook.saved(true);
    //Open Excel document
    xlsApplication.visible(true);

}

Step 7. fetchDataIntoExcel - Used to put data into excel rows.

public void fetchDataIntoExcel()
{
    VendTable               vendTable;
    CompanyInfo             companyInfo = CompanyInfo::find();
    TaxWithholdTrans_IN     tdsTable;
    LedgerJournalTrans      ledgerJournalTrans;

    ;
    progress = new RunbaseProgress();
    progress.setCaption("Export To Excel in progress...");
    progress.setAnimation(#AviTransfer);

    while select vendInvoiceJour
    order by    vendInvoiceJour.InvoiceDate
        where vendInvoiceJour.InvoiceDate >= fromDate
        && vendInvoiceJour.InvoiceDate <= toDate

    {
        vendTable = VendTable::find(vendInvoiceJour.InvoiceAccount);
        progress.setText(strfmt("Vendor %1", vendTable.Name));

        xlsWorkSheet.cells().item(row,1).value(row-2);
        xlsWorkSheet.cells().item(row,2).value(vendInvoiceJour.InvoiceAccount);
        xlsWorkSheet.cells().item(row,3).value(vendTable.name);

row++;
    }


}

Step 8. PACK and UnPack methods.

container pack()
{
    return [#CurrentVersion,#CurrentList];

}


public boolean unpack(container _packedClass)
{
    int version = conPeek(_packedClass,1);

    switch (version)
   {
        case #CurrentVersion:
            [version,#CurrentList] = _packedClass;
            break;
        default:
            return false;
    }
    return true;

}

Ledger Account Setup Dynamics AX 2012

Hi Guys,

The Ledger Account setup is the most important setup for Dynamics AX Implementation.

Below are the Account Types and number sequence (Range), this will help you to arrange accounts in particular range.

Account Type	From	To
Assets	10000	19999
Liabilities	20000	29999
Equities/Funds	30000	39999
Revenue	40000	49999
Expense	50000	59999

Sample segment
Private Sector
Company--Department ---Cost Center ---Account --Project (maybe)

Public Sector
Appropriation Year --Cost Center --Account --Project (May be)

MB6-890 Microsoft Development Introduction - AX7 (Rainer) - Exam Topics

Exam Topics

Understand the Architecture and Development Environment (20% - 25%)

Understand the Architecture and Development Environment

This objective may include but is not limited to: identify features of Microsoft Dynamics AX; describe the development environment; describe the components in the application stack; identify cloud architecture components; explain the server architecture; describe the layer architecture.

Use Microsoft Visual Studio to manage development with Microsoft Dynamics AX

This objective may include but is not limited to: identify the windows and basic navigation of Visual Studio; describe differences between and uses for projects, models, solutions, and packages.

Use the Application Explorer to Develop New Elements (25% - 30%)

Create and manage labels and resources

This objective may include but is not limited to: create new label files; create and use labels; describe elements and uses for labels; identify best practices for labels identify uses for resources.

Create and manage data types

This objective may include but is not limited to: describe uses for base enumerations; create new base enumerations; identify best practices for base enumerations; describe primitive and extended data types; create new extended data types; identify key properties for extended data types; implement best practices for extended data types.

Create and manage tables

This objective may include but is not limited to: identify the components of a table; describe various types of table relationships; describe various types of table indexes; implement best practices for tables, relationships, and indexes.

                                                        

Read, Write and Understand Basic X++ (30% - 35%)

Describe X++

This objective may include but is not limited to: identify the characteristics of X++; use the code editor to write X++; describe the features of IntelliSense; identify basic syntax for X++; describe the use of common key words in X++; describe the features of the debugger; use the comparison tool; use the best practice checker.

Work with X++ control statements

This objective may include but is not limited to: work with variables, operators, conditional statements, and loops; use built-in functions; use communication tools.

Work with classes

This objective may include but is not limited to: create new classes; describe scoping events and parameters; describe inheritance; identify key best practices when writing X++.

Use X++ to manipulate data

This objective may include but is not limited to: identify techniques for data retrieval; explain uses for transaction integrity checking; insert, update, and delete records using X++; identify best practices for manipulating data with X++.

Manage exceptions in X++

This objective may include but is not limited to: identify exception types; use try/catch statements; describe the features of optimistic concurrency control; describe best practices for exception handling with X++ .

Manage the User Interface and Security for Developers  (20% - 25%)

Manage the user interface

This objective may include but is not limited to: identify components of various form patterns; identify common form patterns and sub patterns; identify key properties for form elements; apply and validate form patterns; describe uses for tiles; describe how to join data in forms; implement best practices for form development.

Manage user navigation

This objective may include but is not limited to: identify types of menu items; create menu items and menus; identify key properties for menu items; describe uses for menu items; implement best practices for menus and menu items.

Manage security in the development environment

This objective may include but is not limited to: describe the security architecture; describe the components of role-based security; create new privileges, duties, and roles in the application explorer; describe the extensible data security framework.