Joins In Microsoft Dynamics Ax

There are basically four types of joins in Ax,

1.  Inner Join
2. Outer Join 
3. Exists Join
4. Notexists Join

Inner Join : Inner Join will return records from both Outer table and Inner table, only the records which are available in Inner table. Inner Join will also return duplicate records.

Outer Join : Outer Join will return all the records from both Outer table and Inner table. Outer Join will also return duplicate records.

Exists Join : Exists Join will return records only from Outer table which are available in Inner Table. It will not return any duplicate records. 

NotExists Join : NotExists Join will return records only from Outer table which are not available in Inner Table. It will not return any duplicate records.

So now you are clear with the definitions of multiple types of joins in ax. To understand how practically it works use the following steps.

• First of all create two tables in Dynamics Ax Demo_Outer and Demo_Inner.
• In Demo_Outer table create two fields
• Account (PK)
• Name
• In Demo_Inner table create two fields
• Account (FK)
• Phone
• Insert some records in both the tables

To test the joins create a job and fetch records from tables by using code.

Inner Join : Write the following code to test inner join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
          join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }

}

Inner Join Output :

Inner Join

Outer Join : Write the following code to test outer join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       outer join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }
}

Outer Join Output : 

Outer Join

Exists Join :  Write the following code to test exists join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       exists join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }
}

Exists Join Output : 

Exists Join

NotExists Join : Write the following code to test notexists join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       notexists join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }

}

NotExists Join Output : 

NotExists Join

                  Thank You !

How to use Display Method in AX

Sometimes developer has requirement to show data on selecting some record. This can be achieved using display method. It saves memory space as we don't need to insert record in another tables to use data. Here I'm giving an simple example on how to use display method in axapta.

Example:

• Crate two tables TableA and TableB
• Create two fields in TableA Id and Name.
• Create a field in TableB Id.
• Now create a relation as shown below.

• Create a display method getNameMethod and write following code.

• Now create a Form to test display method
• Add TableB in Data Source of form
• Drag Id field to design and create new string field Name and change property.
• Make DataSource as TableB and DataMethod as getNameMethod.

• Insert some dummy records in Table.

• Now open form and select any record.

• Here name is coming automatically using display method.

• Similarly, you can also try with some standard table.

Note : In example Axapta 4.0 version (2006) is used.

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");
    
}