Excel Importing from MSD AX using x++ code

static void Testing_ExcelImporting(Args _args)
{
    //Excel Classes Declaration
   SysExcelApplication          xlsApplication;
   SysExcelWorkBooks            xlsWorkBookCollection;
   SysExcelWorkBook             xlsWorkBook;
   SysExcelWorkSheets           xlsWorkSheetCollection;
   SysExcelWorkSheet            xlsWorkSheet;
   SysExcelRange                xlsRange;

   //Tables Buffer
   TestingTable                 testingTable;
   tmpTestingTable              tmpTestingTable;

   //Variables
   int                          row = 1;
   counter                      c=0;
   str                          fileName;
   transdate                    fromdate,todate;
   FileName                     fileNametoSave;

   //Dialog instances
   Dialog                       dlg;
   DialogGroup                  dlgGroup;
   DialogField                  digfield,digfield1,digfield2;
   DialogField                  dialogFilename;
   ;

    Box::warning("Please save your work and close all excel sheets and run this Excel report");
    dlg         = new Dialog("AX Export to Excel");
    dlg.addText("Please save your work and close all excel sheets and run this Excel report");
    dlgGroup    = dlg.addGroup("Enter details");
    digfield    = dlg.addField(TypeID(transdate),"From Date");
    digfield1   = dlg.addField(TypeID(transdate),"To Date");
    dialogFilename = dlg.addFieldValue(typeid(Filepath),filename);
    dlg.run();
    if (dlg.closedOk())
    {
       fromdate     =   digfield.value();
       todate       =   digfield1.value();
       filename     =   dialogFilename.value();
    }
    else
    {
    continue;
    }
    if(!fromdate||!todate||!filename)//validating dialog values
        {
        throw error("Enter complete data");
        }
   filename = dialogFilename.value();

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

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

   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("ID");
   xlsWorkSheet.cells().item(row,2).value("Name");

   row++;

   tmpTestingTable.clear();

   // Inserting record from main table to temporary table
   insert_recordset tmpTestingTable(ID,Name)
   select ID,Name from  testingTable where TestingTable.Date >= fromDate
                               && TestingTable.Date <= toDate
                               && testingtable.ID != "";


   row=2;
   try
   {
     while select tmpTestingTable
     {

        xlsWorkSheet.cells().item(row,1).value(tmpTestingTable.ID);
        xlsWorkSheet.cells().item(row,2).value(tmpTestingTable.Name);
        row++;
      }
    }
   catch
    {
     if(Exception::Error)
        info("Something went Wrong, Please close all the Excel sheets and try agin");
        else
        continue;
    }


//Validation before saving into excel
   if(WinApi::fileExists(fileName))
   WinApi::deleteFile(fileName);//if found delete it
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   xlsApplication.quit();
   xlsApplication.finalize();
   }

Configure email notification in MSD AX-2012

Set up SMTP mail server

The very first step you need to do is to set up a Simple Mail Transfer protocol (SMTP) in your environment. Just you need to follow the following procedure.

•  Install the SMTP ServerInstall the SMTP server on a computer that is running Window Server. The following steps are applicable if you are using Windows Server 2012.

o   Go to Server Manager.

o   Click the Manage menu and then click Add Roles and Features.

o   New window will open, Click Next in Before you begin page.

o   The Select installation type Select the Role-base or feature-based installation option and then, click next.

o   The Select destination server page is displayed. Select the server on which you want to install the SMTP sever. Click Next.

o   The Select server role page is displayed. Click Next.

o   The Select Features window is displayed. Click the SMTP Server check box.

o   A window is displayed that asks whether you want to add role services and features for the SMTP server. Click Add Features.

o   The Select Features page is redisplayed. Click Next.

o   The Web Server (IIS) page is displayed. Click Next.

o   The Select Role Services page is displayed. Click Next.

o   The Confirm Installation Services page is displayed. Click Install.

•   Configure the SMTP server
  
         Use following steps to configure the SMTP server to use integrated Windows authentication.

o   Go to Administrative Tools.

o   Click Internet Information Services (IIS) 6.0 Manager to open IIS Manager.

o   In the left pane, select the name of your server.

o   In the right pane, right-click [SMTP Virtual Server #1] and then click Properties.

o   The [SMTP Virtual Server #1] Properties window is displayed. Click the Access tab.

o   Click the Authentication button.

o   The Authentication window is displayed. Select the Integrated Windows Authentication check box. Click OK.

o   The Access tab is redisplayed. Click the Relay button.

o   The Relay Restrictions window is displayed. Select the Allow all computers which successfully authenticate to relay, regardless of the list above check box. Click OK.

o   The [SMTP Virtual Server #1] Properties window is redisplayed. Click OK to close the window.

• Go to Services. 
  
  You have to change some properties of SMTP services.

o   Go to services in start menu, find Simple Mail Transfer Protocol (SMTP).

o   Right click on Simple Mail Transfer Protocol (SMTP), new window will open.Now go to Log On        tab check Local System account and Allow services to interact with desktop.

• Create a unique port (Optional)
  Now you need to create a specific port that you can use for email.

Note: The default port is 25 for mail configuration, if the default port is not working then try to create different port by using following steps.

o   Search firewall with Advanced Security in start and click.

o   Right click on Inbound Rules, select Create new Rule.

o   New window will open select Port, click next.

o   Give an specific port number e.g. 587 and click next.

  

o   For Action step check, Allow the connection and click Next.

o   Click next for Profile step, and in Name step give an unique name to port.

• Connect Microsoft Dynamics AX to the SMTP mail server
  Use the following procedure to connect Microsoft Dynamics Ax to the SMTP mail server.

o   Open the Microsoft Dynamics AX client.

o   Click System administration > Setup > System > E-mail parameters.

o   In the Outgoing mail server field, enter the name of the computer on which you installed the SMTP server.

o   In the Local computer name field, enter the name of the local computer.

o   In the SMTP port number field, enter the port number to use with SMTP. The default port number is 25.

o   If the SMTP server requires authentication, enter a valid username and password.

o   Select the Use NTLM check box.

o   In the Attachment size limit (MB) field, enter a size limit for email attachments.

o   The max buffer size registry key also limits the acceptable size for email attachments. If the size that you enter in this field exceeds the maximum buffer size, the maximum buffer size limit will apply.

o   In the Allow embedded data and attached files from field, enter or browse to the location where email attachments or other embedded data in an email is stored.

Use batch processing to send email messages

•  Create a batch group
  
        A batch job, which is named E-mail Distributor Batch Job, is used to send email messages that are generated from Microsoft Dynamics AX. The batch job must run within the context of a batch group. Complete the following steps to create a batch group for the E-mail Distributor Batch Job.

o   Click System administration > Setup > Batch group.

o   Click New to create a new batch group.

o   In the Group field, enter a unique name for the batch group. For example, enter Email.

o   In the Description field, enter a description to help you identify the batch group.

o   Click the Batch servers tab.

o   The Selected servers list displays the AOS instances that the batch group runs on. The Remaining servers list displays the remaining AOS instances that are available as batch servers.

o   Use the arrow buttons to add servers to the Selected Servers list or to remove servers from the Selected Servers list.

• Configure the batch jobComplete the following steps to configure the E-mail Distributor Batch Job.

o   Click System administration > Periodic > E-mail processing > Batch.

o   Select the Batch processing check box.

o   In the Task description field, enter a description for this batch job.

o   In the Batch group list, select the batch group that you created in the previous procedure.

o   Select the Private check box if you want to restrict other users from running this batch job. A private batch job can be run only by the user who specified it and only on the computer where the user is logged on.

o   Click Recurrence to specify how often this batch job will run.

o   Click Alerts to send notifications when this batch job ends, has an error, or is canceled.

•  Create a new job in AX to test email (Use the following code)

static void EmailTestJob(Args _args)
{

    SysEmailParameters parameters = SysEmailParameters::find();
    SMTPRelayServerName relayServer;
    SMTPPortNumber portNumber;
    SMTPUserName userName;
    SMTPPassword password;
    Str1260 subject,body;
    InteropPermission interopPermission;
    SysMailer mailer;
    System.Exception e;
    UserGroupList   _UserGroupList;
    SysUserInfo     _sysuser;

    container  con;
    int i=0;
    str reciever;
    Str 30 _emailAlert;//SysEmailParameters_MailAlert

    if (parameters.SMTPRelayServerName)
    {
        relayServer = parameters.SMTPRelayServerName;
    }
    else
    {
        relayServer = parameters.SMTPServerIPAddress;
        portNumber = parameters.SMTPPortNumber;
        userName = parameters.SMTPUserName;
        password = SysEmailParameters::password();

        subject = "Subject line for the email";
        body = "Body of the email";

        CodeAccessPermission::revertAssert();
    }
        
    try
    {
        interopPermission = new InteropPermission(InteropKind::ComInterop);
        interopPermission.assert();
        mailer = new SysMailer();
        mailer.SMTPRelayServer(relayServer,portNumber,userName,password, parameters.NTLM);
        //instantiate email
        mailer.fromAddress(userName);
        MAILER.TOS().appendAddress("User@DomainName.com");
        MAILER.SUBJECT(SUBJECT);
        MAILER.HTMLBODY(BODY);
        MAILER.SENDMAIL();
        CODEACCESSPERMISSION::REVERTASSERT();
        MAILER.TOS().CLEAR();
        info("Email has been sent");         
    }
    catch   (Exception::CLRError)
    {
        e = ClrInterop::getLastException();
        while (e)
        {  
            info(e.get_Message());
            e = e.get_InnerException();
        }
    }

    CodeAccessPermission::revertAssert();
    info ("Failed to Send Email some Error occur");
    
}

How to use generated number sequence at table level

How to use generated number sequence in table instead of form

     NOTE : Before starting, you must know how to generate a new number sequence. You can               check with the below link if you don't know already.

    Generating a new number sequence 

• Very first thing you need to do is create a field in your table with the same data type as in your number sequence. Now go to the properties of newly created field and specify the EDT of number sequence.
  
  
   
• Suppose, you've generated number sequence in HR module then create a new method in HRMParameters table. If you've generated number sequence in Customer module then create new method in CustParameters table. Similary if you've generated number sequence in any other module then check respective parameter table and create a new method there.
  

• Write the following code in your method.
  
  
  

      Code:  

                    client server static NumberSequenceReference methodNamme()

                       {

                              return NumberSeqReference::findReference(extendedTypeNum(Your Number Sequence EDT name));

                        }

• Now go to your table where you want to use number sequence and override initValue() method.
  

     Code:

                public void initValue()

                  {

                         NumberSeq numSeq;

                        super();

                        numSeq = NumberSeq::newGetNum(HRMParameters::numRefDemo());

                       this.Offer=numSeq.num();

                   }

• Now open your table and check by creating new record.

• Now you don't need to write code in form to generate number sequence.

Note: Generally in ax we don't use this concept because it's better to create new number sequence at form level where we can apply validation. But in exceptional case such as "inserting records from a class to table " where we can't create new sequence by using form so we have to use this concept.

Database error in AX

Error : Cannot select a record, Name: 0. The SQL database has issued an error.

Solution : System Administration  →  Periodic → Database → SQL administration → Table Actions → Synchronize database.

How to create number sequence in ax at form level

CREATE NUMBER SEQUENCE

OVERVIEW

Number sequences are unique identifiers that can be associated with a master record so that they can be individually distinguished. They can be either formatted as alpha-numeric strings or simply as numbers. Microsoft Dynamics AX 2012 provides an easy way to implement framework to generate custom number sequences. To create custom number sequence need to follow some steps:

STEPS

•  First create a new Extended Data Type (EDT). Open AOT→ Data Dictionary → Extended Data Types
• Right Click on Extended Data Types and create a new EDT ‘MyNumSeq’ of type String
  

•    Set the properties as shown below

• Now go to AOT → Classes and open the NumberSeqModuleCustomer class by right clicking it and selecting View Code

• In  the loadModule method, add the following code after the last line of code

//define the EDT
datatype.parmDatatypeId(extendedTypeNum(MyNumSeq));//define its default properties
datatype.parmReferenceHelp(literalStr(“Unique number for customer group”));
datatype.parmWizardIsContinuous(true);
datatype.parmWizardIsManual(NoYes::No);
datatype.parmWizardIsChangeDownAllowed(NoYes::No);
datatype.parmWizardIsChangeUpAllowed(NoYes::No);
datatype.parmWizardHighest(999999);
datatype.parmSortField(27);

//define its scope
datatype.addParameterType(NumberSeqParameterType::DataArea, true, false);
this.create(datatype);

·         Now, go to AOT → Jobs  and create a new job loadMyNumSeq

Write the following code in the job and then run it

static void loadMyNumSeq(Args _args)
{
//define the class variable
NumberSeqModuleCustomer numSeqMod = new NumberSeqModuleCustomer();

//load the number sequences that were not generated
numSeqMod.load();
}

• Now, go to  System Administration → Organization administration → Common → Number sequences → Number sequences

• Click on Number Sequence button in the New button group
  

•  In New Record change value of tabs according to your requirement.

Identification → Name and Code of Number Sequence
Scope parameter → Scope → company
                              →company → “Your company”

·          Set values of segment tab as shown in picture below:

 

•        Set values of General tab

•    Set values in Largest and continuous as shown in picture and press close button

•         Now click on Generate button and wait for some time it will create new number sequence.

• In the Setup number sequences wizard, Press Next

• In the last step, Click Finish to generate the number sequences

•    Now check your generated number sequence by typing name of your number sequence in filter

•    Now goto Account Receivable , expand setup and click on account receivable parameters

•    Now click on Number Sequence (left below as visible in picture) , in reference column find mynumseq (EDT) and in number sequence code column find mynum (your number sequence code)

•     Create a new table, if you already have a table where you want to use number sequence add a new string field and set properties as follows

•  Create a new form, if you already have a form in which you want to use number sequence then  simply add the table to form’s data source.

•  Write the following code on the Class declaration node

          NumberSeqFormHandler numberSeqFormHandler;

•   Create a new method on the form and write the following code

  NumberSeqFormHandler numberSeqFormHandler()
   {
      if (!numberSeqFormHandler)
   {

//create a reference of number sequence form handler class specifying the         EDT, Data source name and the field of the table

numberSeqFormHandler =NumberSeqFormHandler::newForm(NumberSeqReference::findReference
(extendedtypenum(NumSeqDemoCustGroupNum)).NumberSequenceId, element,MyNumSeqTable_DS,fieldnum(MyNumSeqTable,MyNumSeq));
}
return numberSeqFormHandler;
}

•       Override the close method of the form and write the following code

public void close()
{
if (numberSeqFormHandler)
{
numberSeqFormHandler.formMethodClose();
}

super();
}

•      Override the create method on the MyNumSeqTable data source and add the following code

public void create(boolean _append = false)
{
element.numberSeqFormHandler().formMethodDataSourceCreatePre();

super(_append);

element.numberSeqFormHandler().formMethodDataSourceCreate(true);
}

•       Override the write method on the MyNumSeqTable  data source and add the following code

public void write()
{
super();

element.numberSeqFormHandler().formMethodDataSourceWrite();
}

•     Override the validateWrite method on the MyNumSeqTable  data source and add the following code

public boolean validateWrite()
{
boolean ret;

ret = super();

ret = element.numberSeqFormHandler().formMethodDataSourceValidateWrite(ret) && ret;

return ret;
}

•     Override the delete method on the MyNumSeqTable  data source and add the following code

public
void delete()
{

element.numberSeqFormHandler().formMethodDataSourceDelete();

super();
}

•          Override the linkActive method on the MyNumSeqTable  data source and add the following code

public
void linkActive()
{

element.numberSeqFormHandler().formMethodDataSourceLinkActive();

super();
}

•     Now open form.

• The number in number sequence will be automatically created every time you create a new record in form.