Showing posts with label Dynamics AX 2012. Show all posts
Showing posts with label Dynamics AX 2012. Show all posts

Thursday, September 26, 2019

Update cross reference in batch job - AX 2012 R2, R3

Below is the code snippets to update cross references of all AOT objects through batch job.

static void UpdateCrossRefBatch(Args _args)
{
   ;
   xRefUpdate::truncateXrefTables();
   xRefUpdateIL::updateAllXref(true, false, true);
   info("Done, cross reference update batch job created.");
}

Batch job will get added with the description as "Update xref for complete AOT" .

Happy Daxing !!

Saturday, August 19, 2017

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;

}

Wednesday, June 17, 2015

How to get Paper sizes in AX 2012

static void Job1(Args _args)
{
    System.Drawing.Printing.PrinterSettings printSetting = new System.Drawing.Printing.PrinterSettings();
    System.Drawing.Printing.PaperSize  size = new System.Drawing.Printing.PaperSize();
    System.Collections.ArrayList    paperSizes;
    System.Collections.IEnumerator  enumerator;

    paperSizes= printSetting.get_PaperSizes();
    enumerator = paperSizes.GetEnumerator();

    while (enumerator.MoveNext())
    {
        size = enumerator.get_Current();
        info(size.get_PaperName());   
    }

}

Thursday, May 14, 2015

How to delete Label Files from AX 2012

Here are the steps to delete Label Files from AX 2012:

  • Create a new model. You could call it "Test".
  • Open the AOT and move the Label File(s) you want to get rid of to the new model.
  • Close AX and stop the AOS.
  • Use AXUtil to delete the new model.
  • Select the label files (name start with 'ax' and followed with label id. select all files) and Delete from the server folder; C:\Program Files\Microsoft Dynamics AX\60\Server\...\bin\Application\Appl\Standard
  • Start the AOS.
  • Skip the upgrade wizard.

Friday, February 6, 2015

Read text file and SubStr to fetch fixed length columns - AX 2012

static void readTextFile(Args _args)
{
    Container               con;
    Filename                filename, Filename2;
    FileIOPermission        permission;
    TextIO                  textIO, textIO1;
    Str1260                 line;
    #File
    ;
    
    Filename = @"C:\Test\testFile.txt";
    permission = new fileIOpermission(filename,"RW");
    permission.assert();
    textIO = new TextIO(filename,#io_read);
    //textIO.inFieldDelimiter('|');
    if(textIO)
    {
        while(textIO.status() == IO_Status::Ok)
        {
            con = textIO.read();
            if(con)
            {
                line = Global::con2Str(con);
                info(strFmt("text - %1",line));
                info(strFmt("Company id - %1",subStr(line,1,4)));
                info(strFmt("code - %1",subStr(line,5,6)));
                info(strFmt("account num - %1",subStr(line,12,14)));
                info(strFmt("transdate - %1",subStr(line,26,6)));
                info(strFmt("Cheque num - %1",subStr(line,32,10)));
                info(strFmt("amount - %1",subStr(line,42,8)));
                break;
            }
        }
    }
}

Customer payment journal through X++ Code - AX 2012

static void cust_GLPosting(Args _args)
{
    Ledgerjournalname       ledgerjournalname;
    LedgerjournalTable      LedgerjournalTable;
    LedgerjournalTrans      LedgerjournalTrans;
    LedgerjournalCheckPost  LedgerjournalCheckPost;
    NumberSeq               numberSeq;
    Container               con;
    Filename                filename, Filename2;
    FileIOPermission        permission;
    TextIO                  textIO, textIO1;
    Dialog                  dialog;
    DialogField             dialogField;
    AccountNum              customerAccount= 'BRMF-000001';
    AccountNum              offsetAccNum = 'B01';
    #File

    select ledgerjournalname where ledgerjournalname.JournalName == "Test";
    ttsBegin;
    LedgerjournalTable.JournalName = ledgerjournalname.JournalName;
    LedgerjournalTable.initFromLedgerJournalName();
    LedgerjournalTable.JournalNum  = JournalTableData::newTable(LedgerjournalTable).nextJournalId();
    LedgerjournalTable.insert();
    ttsCommit;
        
    ttsBegin;
    numberSeq                               =   NumberSeq::newGetVoucherFromId((ledgerjournalname.NumberSequenceTable));
    LedgerjournalTrans.Voucher              =   numberSeq.voucher();
    LedgerjournalTrans.JournalNum           =   LedgerjournalTable.JournalNum;
    LedgerjournalTrans.CurrencyCode         =   "USD";
    LedgerjournalTrans.ExchRate             =   Currency::exchRate(LedgerjournalTrans.CurrencyCode);
    LedgerjournalTrans.AccountType          =   LedgerJournalACType::Cust;
    LedgerjournalTrans.parmAccount(customerAccount,LedgerjournalTrans.AccountType);
    LedgerjournalTrans.Txt                  =   'Testing';//conPeek(con,1);
    LedgerjournalTrans.AmountCurCredit      =   200;//conPeek(con,3);
    LedgerjournalTrans.TransDate            =   systemDateGet();//str2Date(conPeek(con,4),123);
    LedgerjournalTrans.OffsetAccountType    =   LedgerJournalACType::Bank;//LedgerjournalTable.OffsetAccountType;
    //LedgerJournalTrans.offsetacco
    LedgerjournalTrans.OffsetLedgerDimension =   22565431567; //LedgerjournalTable.OffsetLedgerDimension;
    LedgerjournalTrans.DefaultDimension     =   CustTable::find(customerAccount).DefaultDimension;
    LedgerjournalTrans.OffsetDefaultDimension=  CustTable::find(customerAccount).DefaultDimension;
    LedgerjournalTrans.insert();
    ttsCommit;
               
    LedgerjournalCheckPost = LedgerjournalCheckPost::newLedgerJournalTable(LedgerjournalTable,NoYes::Yes);
    LedgerjournalCheckPost.run();
    
}

Tuesday, December 23, 2014

How to import Contoso Demo Data to Dynamics AX 2012 R3

The setup contoso demo data for Dynamics Ax 2012 R3 is different from previous version. I remember, I used .dat file to load demo data into Dynamics Ax 2012 R2. Now DynamicsAX2012R3DemoData.exe available partner Resource.
03-Demo setup
DynamicsAX2012R3DemoData.exe extracted files round 15 GB. Extracted folder contains three types of files. According to MSDN
  • Xml (A bcp data file that contains table data. Columns are separated by #|EOC|#. Rows are separated by #|EOR|#\n.)
  • Out (A bcp data file that contains the table metadata (column descriptions).
  • OutModel (This metadata includes all names and IDs of the table and its fields. This file also includes the elementType attribute, which stores the names and IDs of any Microsoft Dynamics AX tables, classes, or extended data types that are referenced by the table)
Installation of Test Data Transfer Tool:
The MSDN Described The Import export with Test Data Transfer Tool (beta) as follow.
IC665318


Test Data transfer Tool is available on partner resource.
Go on following link

04-InformationSource

On login I found download page
05-download

There will be zip file downloaded.  On running I found following installation wizard.

11-Beta Setup

12-Beta Setup next

Setup run will be generated as following
06-folderDetail

Import Data Into Dynamics Ax 2012 R3 instance:
And import MetaDataXMLGenerator.xpo in dynamics Ax.
07-ImportGenerator

This xpo imports results a job inside job node under AOT.

08-Job



This job will generate Metadata.xml. This Metadata.xml will be created in window temporary folder.
Path of file in metadata.xml can be get from infobox which will appear after successful run of job.
13-metaFile

if you lost infobox,You can get temporary folder path with following command echo %temp%. where you can get Metadata.xml
Copied metadata.xml into [list] folder of test tool Data folder. If it already exists then overwrite it.
15-copy meta data

Now open command prompt. Go to directory where Test import tool is extracted.
Run the following command
DP.exe IMPORT “E:\Contoso_Demo_Data\DynamicsAXR3DemoData” MicrosoftDynamicsAx

Please update path according to your installation.
09-Command Prompt

After running the command, you will find following window which describe the process by remaining table to number data, and number of error occurs during import.
10-Remaining

This process takes hours and hours with respect to your machine. After completion, I found demo data inside my dynamics Ax 2012 R3.

14-DataAppear

Thanks to Ali

Friday, December 19, 2014

List all the objects from the project node in AX 2012

Hi MSDAX technical consultants,

In Microsoft dynamics AX 2012 to find all the objects from the projects you can use following code. 

static void listAllObjectosFromProject(Args _args)
{
    #define.Shared("Shared")
    
    ProjName            projName = "Test";
    ProjectListNode     list = infolog.projectRootNode().AOTfindChild(#Shared);
    TreeNodeIterator    ir = list.AOTiterator();
    ProjectNode         pnProj;
    ProjectNode         pn = list.AOTfindChild(projName);

    void searchAllObj(projectNode rootNode)
    {
        #TreeNodeSysNodeType
        TreeNode          childNode;
        TreeNodeIterator  rootNodeIterator;
        ;

        if (rootNode)
        {
            rootNodeIterator = rootNode.AOTiterator();
            childNode = rootNodeIterator.next();
            while (childnode)
            {
                if (childNode.treeNodeType().id() == #NT_PROJECT_GROUP)
                {
                    searchAllObj(childNode);
                }
                else
                {
                    //info(strfmt("Group :%1 - Object: %2", rootNode.AOTname(), childNode.AOTname()));
                    info(strfmt("%1", childNode.AOTname()));
                }
                childNode = rootNodeIterator.next();
            }
        }
    }
    ;

    if (pn)
    {
        //info(strFmt("Project %1:", projName));
        pnProj = pn.loadForInspection();
        searchAllObj(pnProj);
        pnproj.treeNodeRelease();
    }
    else    
    {
        info("Projet objects");
    }

}

Tuesday, October 7, 2014

Create Menuitem at runtime or through X++ and set properties

Hi Guys,

Here is the example of creating menuitem through X++ code.


static void CreatingMenuItemRutime(Args _args)
{
    
    TreeNode          treeMenuItem;
    str                    menuName = "TestOutputMenuItem";
    str                    properties;
    #AOT
    #Properties
    ;
    
    treeMenuItem = TreeNode::findNode(#MenuItemsOutputPath);
    treeMenuItem.AOTadd(menuName);
    
    treeMenuItem = treeMenuItem.AOTfindChild(menuName);
    properties = treeMenuItem.AOTgetProperties();
    
    properties = setProperty( properties, #PropertyLabel,'Sales Report');
    properties = setProperty( properties, #PropertyObjectType, 'Class');
    properties = setProperty( properties, #PropertyObject,'SalesFormLetter');
    properties = setProperty( properties, #PropertyParameters, 'ReportX1\\Report');
    
    treeMenuItem.AOTsetProperties(properties);
    
    treeMenuItem.AOTsave();
    print "New Menuitem is created under output menuitem";
    pause;
}

Thursday, January 23, 2014

How to: Connect to an External Database from X++ Code [AX 2012] [AX 2009]

Create a DSN


To create a Data Source Name (DSN) go to Administrative Tools > Data Sources (ODBC).
Create the DSN on the tier where the X++ code will call the DSN from. This will be either on the client computer or on the Application Object Server (AOS) computer.
NoteNote
Ongoing maintenance is simpler if the DSN is created on the AOS tier.
X++ Code Example with ODBC


The following X++ code example uses ODBC to connect to an external database. The code example assumes that you have already created the DSN in Windows.
// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("databaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

32 Bit and 64 Bit Windows Operating System

The preceding code example can run on either the client tier or the server tier. The following table shows how the operating system architecture affects the choice of tier.

32 bit Windows
64 bit Windows
Client tier (MorphX)
Runnable.
Not runnable.
Server tier (AOS)
Runnable.
Runnable.
Consider adding the server keyword to the declaration of the Main method.
If you want to do Insert/Update operation through this code then you must use following code.

int      _returnId;

sql = " Your insert / Update Query"
 //Prepare the sql statement.
statement = odbcConnection.createStatement();
_returnId = statement.executeUpdate(sql);

reference :1. Microsoft Documentation MSDN 2. ArtofCreation 3. GalaxyofDynamics


Thursday, December 5, 2013

Enable/Disable Fact boxes, Preview Panes in AX2012 UX

In Microsoft Dynamics AX 2012, if you want to disable factboxes/preview panes across application then you can do so.
It's not recommended to do this activity but this will help in troubleshooting performance issues assoicated with opening forms in ax2012 by controlling 'Timeout' parameter as below.

Go to ->System Administration->Setup->Client performance options 

Visit  Ax2012-client-performance-options for detailed explaination by Ax performance team.

Thanks axperf team :)

reference from : http://learnax.blogspot.in/2012/02/enabledisable-fact-boxes-preview-panes.html

Wednesday, October 23, 2013

Sequence of methods in form and table in AX


Form:
Sequence of Methods calls while opening the Form
Form — init ()
Form — Datasource — init ()
Form — run ()
Form — Datasource — execute Query ()
Form — Datasource — active ()
Sequence of Methods calls while closing the Form
Form — canClose ()
Form — close ()
Sequence of Methods calls while creating the record in the Form
Form — Datasource — create ()
Form — Datasource — initValue ()
Table — initValue ()
Form — Datasource — active ()
Sequence of Method calls while saving the record in the Form
Form — Datasource — ValidateWrite ()
Table — ValidateWrite ()
Form — Datasource — write ()
Table — insert ()
Sequence of Method calls while deleting the record in the Form
Form — Datasource — validatedelete ()
Table — validatedelete ()
Table — delete ()
Form — Datasource — active ()
Sequence of Methods calls while modifying the fields in the Form
Table — validateField ()
Table — modifiedField ()

Table:
When you press CTR+N
initValue()->

When you change data in a field
validateField() -> validateFieldValue() -> ModifiedField() -> ModifiedFieldValue()

When you close the table after entering some data
validateWrite() – > Insert() -> aosValidateInsert()

When you Save the Record for the first time
validateWrite() ->Insert() – > aosValidateInsert()

When you modify the record and saving
validateWrite() -> update() – > aosValidateUpdate()

When you delete the record
validateDelete() -> delete() -> aosValidateDelete()


reference : http://axvuongbao.blogspot.in/2013/09/sequence-of-methods-in-form-and-table.html