Demo Script Template for TAN

Demo Script Template for TAN

DevelopNew OBA for Microsoft Dynamics AX 5.0

Demo Overview

  1. Introduction
Using VSTO as Office Business Application technology incombination with .NET Business connector for Microsoft Dynamics AX 5.0 gives the developer a powerful tool that enables work on the business processes of Microsoft Dynamics AX 5.0 without using the standard Microsoft Dynamics AX Client application.
The purpose of this demo is a step by step description of Office Business Application for Microsoft Dynamics AX 5.0 development process.
  1. Audiences
    Theaudience for this demo includes developers who know the basics ofC# andneed to automate some workflows using Office Business Applicationsfeatures, and to learn how to develop Office Business Applications for Microsoft Dynamics AX.

  1. Scenario
  • Personas
  • Tim Jones – Director of Sales in The Light Company
  • Kate Light – Software Developer
  • Story
Salespeople need up-to-date information about customers’ addresses by customer group, sales district, and country. This information is available from Microsoft Dynamics AX. However, not all the employees have a license and know how to work with the system. Tim wants to provide thesepeople with the opportunity to get the information they need through Microsoft Office Excel.So he asked the company developer, Kate Light, to create a simple Office Business Application solution that will allow for inserting information from Microsoft Dynamics AX directly into the Microsoft Excel application. Kate started the development process.
  1. Purpose of Demo
The demo shows:
  • How to create Office Business Applications for Microsoft Dynamics AX 5.0
  • How to use VSTO for Microsoft Visual Studio 2008 and .NET Business Connector 5.0 for this purpose
  1. Setup/ Prerequisites
This demo script requires the following applications to be installed:
  • Microsoft Office Excel 2007
  • Microsoft Visual Studio 2008 with VSTO
  • .NET Business Connector 5.0
  • Icon.png file put to С\Documents and Settings\All Users\Shared Documents\Demo source\ExcelCustInfo
  • Template.xlsx file put to С\Documents and Settings\All Users\Shared Documents\Demo source\ExcelCustInfo\Resources

Demo

Presenter’s Discussion Points / Actions Taken
First of all, Kate launches Microsoft Visual Studio and creates a new project based on theExcel 2007 Add-intemplate.
To create a new project based on the template, Kate opens the New Project window in the opened application, and selects Office Excel 2007 Add-in. Then she types the name of the new project (ExcelCustInfo). / Click Start All programs Miscrosoft Visual Studio 2008 Microsoft Visual Studio 2008.
In the opened application, click File New Project. In the windows that appears, select Office Excel 2007 Add-in.

In the lower pane of the window, type the name of your project,ExcelCustInfo,and specify the location your project will be saved to. Click OK.

A new project is created

First Kate decides to make the application more flexible.
She wants to allow configuration files, that is,xml files thatstore application settings, and allow them tobe changed.
For this, she opens ExcelCustInfo Properties and adds lines for setting string types with default values. Kate adds thetemplatePath, AOS, Company and Languagestring type settings. / On the menu bar of the Microsoft Visual Studio application, click Project ExcelCustInfo Properties…
On the ExcelCustInfo tab of the opened window, click Settings.

Add lines to the grid by entering the setting name into theName field and pressing enter.
Add the following string type settingswith thedefault values:
  • templatePath – the absolute path to the .xltx template file. You can find an example of this file at C:\Documents and Settings\All Users\Shared Documents\Demo source files\ExcelCustInfo\template.xltx)
  • AOS –allows you to customizethe name of the Microsoft Dynamics AX Application Object Server.Leave theValue field blank.
  • Company – leave theValue field blank
  • Language – leave theValue field blank

Now, Kate creates a new ribbon which will be added to the standard Microsoft Office Excel ribbons, and customizes it as needed.
To do this, Kate adds a new item to her project byselecting theRibbon (Visual Designer) template in the Office category and names it CustRibbon.cs.
When the file is added it is now available in the Solution Explorer window.
Now, when the new ribbon is created, Kate adds atab to the blank area of the ribbon.
She opens the Office Ribbon Controls toolbox, and drag-and-drops the new tab control from the toolbox to the ribbon. The new tab is now available on the ribbon.
Since by default the tab does not have a user-friendly name, Kate renames it through the properties of the newly-created tab. She types importInfoTab in the (Name) field, and Import information in the Label field.
As soon as her ribbon with the new tab is ready, Kate adds the new Groupcontrol form the toolbox and names it Customer.
Now it is time to add theToggleButton control with the appropriate image to the newly-created Customers group. This control will allow Tim Jones’ employees to open a pane for entering customer searching criteria.To download the image from the local folder, Kate selects the Local resource check box in the Select resource window. As soon as the image is downloaded, she changes its size properties to fit the control. / Click Project Add New Item. Click the Office category and select theRibbon (Visual Designer)template. Type the name of the file:CustRibbon.cs.Click Add.

CustRibbon.cs file is added to the project. It is visible in theSolution Explorer window now.

Click View Toolbox or press Ctrl+Alt+Xto open the Toolbox.Open the Office Ribbon Controls group. Drag and drop theTabcontrol to the blank area of the ribbon to add the new tab. A new tab named tab2 will be added.
It is not very good name so rename it.
First click tab2 and open theProperties window by clicking View Properties window or pressing F4. TypeimportInfoTab in the (Name)field, and Import information in the Label field.

Drag and drop theGroup control to the ribbon area. Rename it in the same way as the tab that was renamed. Set (Name): custGroup and Label: Customer.

Add ToggleButtoncontrol to the Groupin the same wayas was previously described.
Set (Name) to getAddressBtnand Label to Get Address.

To make the user interface more user-friendly,add a picture to theGet addressbutton.
Click the dotted rectanglein theImage field to browse for the file.

Select the Local resourcecheck box and click Import. Findthe C:\Documents and Settings\All Users\Shared Documents\Demo source files\ExcelCustInfo\icon.pngfile and click OK.Close theSelect Resource window by clicking OK.

Set theControlSize property of getAddressBtn to RibbonControlSizeLarge.
In order for Tim’s employees to have the ability to enter criteria for searching customer address information, Kate creates a custom task pane. She creates a new user control, names it CustTaskPane.cs and sets the minimum size properties.
Since the created task pane is empty, Kate adds four fields for searching criteria and gives them corresponding labels: Customer name, Customer group, Sales district and Country.
This enables Tim’s employees to search the information they need by using either one or all search criteria available.
Finally, Kate adds a button by clicking which employees will retrieve the information from Microsoft Dynamics AX 5.0 to Microsoft Office Excel, and inserts Get data label into the Text field. / Click Project Add New Item. Select User Control and typeCustTaskPane.cs in the Name field.Click Add.

Set MinimumSize property to 250, 512.
Add the following controls from Common Controlsoftoolbox:
  • TextBox. Set its name to nameTextBox
  • 3 ComboBox controls. Set their names to CustGroupCb, SalesDistrictCb, CountryCb
  • Button. Set its name to getDataBtn and Text to Get data.
Add 4 labels and set their Text properties to Customer name, Customer group, Sales district, Country.

Kate creates theCustInfoDataExtractor class which will handle such tasks as connecting to Microsoft Dynamics Application Object Server, disconnecting from it and getting data.
First, she adds a reference to Microsoft Dynamics .NET Business Connector. By right-clicking the References folder, she opens theAdd Reference window, and browses for the Microsoft.Dynamics.BusinessConnectorNet.dllfile. Then Kate adds the class and names it CustInfoDataExtractor.
Now the new reference is created.
Now, she opens the CustInfodataExtractor.cs file and adds the necessary code to the using section.
Then, Kate adds the following methods to the CustInfoDataExtractor class:
  • connectaos –handles the connection to Microsoft Dynamics AX 5.0 Application Object Server
  • disconnectaos - handles the disconnection process
  • getCustGroups - collects information about customer groups, puts the list of them to theArrayList container and returns this list
  • getCustGroupId - receives the customer group name as a parameter and returns the customer group ID
  • getCountries– collects the list of countries
  • getSalesDistricts– collects the list of sales districts
/ Add the reference to Microsoft Dynamics .NET Business Connector. Right-click the References folder in theSolution Explorer window,and select Add reference.
Go to theBrowse tab, and specify the Microsoft.Dynamics.BusinessConnectorNet.dllfile location (C:\Program Files\Microsoft Dynamics AX\50\Client\Bin\)
Click OK.

The new reference is added.

Click Project Add Class. Enter class name, CustInfoDataExtractor, and click OK.
Add the following code to theCustInfodataExtractor.csfile.
To the using section add line:
using System.Collections;
using System.Windows.Forms;
using Microsoft.Dynamics.BusinessConnectorNet;
The next code should be added directly to theCustInfoDataExtractor class
The connectaos method:
public Axapta connectaos()
{
Axapta ax;
ax = new Axapta();
try
{
ax.Logon(Properties.Settings.Default.Company, null, Properties.Settings.Default.AOS,"");
}
catch (Exception)
{
MessageBox.Show("Couldn't connect to AX object server");
}
return ax;
}
The disconnectaos method:
publicvoid disconnectaos(Axapta _ax)
{
_ax.Logoff();
}
ThegetCustGroupsmethod:
publicArrayList getCustGroups(Axapta _ax)
{
AxaptaRecord axRecord = _ax.CreateAxaptaRecord("CustGroup");
axRecord.ExecuteStmt("select * from %1");
ArrayList CustGroups = newArrayList();
while (axRecord.Found)
{
CustGroups.Add(axRecord.get_Field("Name"));
axRecord.Next();
}
return CustGroups;
}
The getCustGroupId method:
publicstring getCustGroupId(string _custGroupName, Axapta _ax)
{
AxaptaRecord axRecord = _ax.CreateAxaptaRecord("CustGroup");
axRecord.ExecuteStmt("select * from %1 where %1.Name == '" + _custGroupName + "'");
return axRecord.get_Field("CustGroup").ToString();
}
ThegetCountries method:
publicArrayList getCountries(Axapta _ax)
{
AxaptaRecord axRecord = _ax.CreateAxaptaRecord("AddressCountryRegion");
axRecord.ExecuteStmt("select * from %1");
ArrayList countryList = newArrayList();
while (axRecord.Found)
{ countryList.Add(axRecord.get_Field("CountryRegionId"));
axRecord.Next();
}
return countryList;
}
ThegetSalesDistricts method:
publicArrayList getSalesDistricts(Axapta _ax)
{
AxaptaRecord axRecord = _ax.CreateAxaptaRecord("smmBusRelSalesDistrictGroup");
axRecord.ExecuteStmt("select * from %1");
ArrayList districtList = newArrayList();
while (axRecord.Found)
{ districtList.Add(axRecord.get_Field("SalesDistrictId"));
axRecord.Next();
}
return districtList;
}
Now, Kate is writing code that will control the behavior of the user interface.
For this, she opens the CustRibbon.cs file, declares the global bool variable CustPaneControlExist to the CustRibbon class, and adds the necessary code to the getAddressBtn_Clickmethod.
Then, Kate opens ThisAddIn.cs file, and adds code to the ThisAddIn class.
Afterwards, she writes theAddCustTaskPane method that will make the custom task pane appear. / Double-click theCustRibbon.cs file. Go to theimportInfoTab tab and double-click the getAddressBtnbutton.
Declare the global bool variable CustPaneControlExist to the CustRibbon class.
Afterwards, thepublic partial class CustRibbon : OfficeRibbon{
type
publicbool CustPaneControlExist;
Add the following code to thegetAddressBtn_Clickmethod:
if (!CustPaneControlExist)
Globals.ThisAddIn.AddCustTaskPane();
else
Globals.ThisAddIn.RemoveCustTaskPane();
CustPaneControlExist = !CustPaneControlExist;
Double-click Excel\ThisAddIn.cs file to open it.
Add the following code to ThisAddIn class:
Declare variable which will represent task pane:
public Microsoft.Office.Tools.CustomTaskPane ctpCustTaskPane = null;
Write theAddCustTaskPane method:
publicvoid AddCustTaskPane()
{
ctpCustTaskPane = this.CustomTaskPanes.Add(newCustTaskPane(), "Customer address information extractor");
ctpCustTaskPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight;
ctpCustTaskPane.Width = 250;
ctpCustTaskPane.DockPositionRestrict = Microsoft.Office.Core.MsoCTPDockPositionRestrict.msoCTPDockPositionRestrictNoChange;
ctpCustTaskPane.VisibleChanged += newEventHandler(CustTaskPane_VisibleChanged);
ctpCustTaskPane.Visible = true;
}
privatevoid CustTaskPane_VisibleChanged(object sender, EventArgs e)
{
Microsoft.Office.Tools.CustomTaskPane taskPane = sender as Microsoft.Office.Tools.CustomTaskPane;
if ((taskPane != null) & (!taskPane.Visible))
{ Globals.Ribbons.CustRibbon.getAddressBtn.Checked = false; Globals.Ribbons.CustRibbon.CustPaneControlExist = false;
}
}
publicvoid RemoveCustTaskPane()
{
this.CustomTaskPanes.Remove(ctpCustTaskPane);
}
Now it is time for Kate to write the custom task pane behavior and Microsoft Office Excel document generation.
To do this, Kate opens the CustTaskPane.cs file and adds the necessary code to the using section of the Load method. Then she adds code to the CustTaskPane_Load method.
Now she right-clicks the CustTaskPane.cs file and adds code to the GetDataBtn_Click method. / Double-click CustTaskPane.cs file. Click on the free area of task pane. Call the Properties window by pressing F4. Switch to the Events view by clicking theEventsbutton. Find theLoad method and double-click it.

Add the following code to theusing section:
using Microsoft.Dynamics.BusinessConnectorNet;
using Microsoft.Office.Interop.Excel;
using System.Collections;
Declare a global instance of theCustInfoDataExtractor class.
To do that add the following line after theCustTaskPanemethod of this class:
CustInfoDataExtractor extractor;
Add the following code to CustTaskPane_Load method:
extractor = newCustInfoDataExtractor();
Axapta ax = extractor.connectaos();
ArrayList custGroups = newArrayList(extractor.getCustGroups(ax));
foreach (string grName in custGroups)
CustGroupCb.Items.Add(grName);
ArrayList countryList = newArrayList(extractor.getCountries(ax));
foreach (string coName in countryList)
CountryCb.Items.Add(coName);
ArrayList salesDistrictList = newArrayList(extractor.getSalesDistricts(ax));
foreach (string districtName in salesDistrictList)
SalesDistrictCb.Items.Add(districtName);
extractor.disconnectaos(ax);
Right-click theCustTaskPane.cs file in theSolution Explorer window and select View Designer. Double-click theGet data button.
Add the following code to GetDataBtn_Click method:
int offset = 4;
Axapta ax = extractor.connectaos();
Workbook book = Globals.ThisAddIn.Application.Workbooks.Add(Properties.Settings.Default.templatePath);
Worksheet sheet = (Worksheet)(book.Worksheets[1]);
AxaptaRecord axCustTable = ax.CreateAxaptaRecord("CustTable");
AxaptaRecord axRelRecord = ax.CreateAxaptaRecord("DirPartyAddressRelationship");
AxaptaRecord axRelMappingRecord = ax.CreateAxaptaRecord("DirPartyAddressRelationshipMapping");
AxaptaRecord axAddressRecord = ax.CreateAxaptaRecord("Address");
string statement;
string whereand = " where ";
statement = "select * from %1";
if (nameTextBox.Text != "")
{
statement += whereand + "%1.Name == '" + nameTextBox.Text + "'";
whereand = " & ";
}
if (CustGroupCb.Text != "")
{
statement += whereand + "%1.CustGroup == '" + extractor.getCustGroupId(CustGroupCb.Text, ax) + "'";
whereand = " & ";
}
if (SalesDistrictCb.Text != "")
{
statement += whereand + "%1.SalesDistrictId == '" +SalesDistrictCb.Text + "'";
whereand = " & ";
}
if (CountryCb.Text != "")
{
statement += whereand + "%1.CountryRegionId == '" + CountryCb.Text + "'";
whereand = " & ";
}
axCustTable.ExecuteStmt(statement);
int i = 1;
if (!axCustTable.Found) MessageBox.Show("There are no customers that meet the criteria.");
while (axCustTable.Found)
{
sheet.get_Range("A" + (i + offset).ToString(), "XFD" + (i + offset + 2).ToString()).Style = "Heading 3";
sheet.get_Range("A" + (i + offset + 4).ToString(), "XFD" + (i + offset + 4).ToString()).Style = "Explanatory Text";
sheet.Cells[i + offset, 1] = "Account Number:";
sheet.Cells[i + offset + 1, 1] = "Name:";
sheet.Cells[i + offset + 2, 1] = "Group:";
sheet.Cells[i + offset, 2] = axCustTable.get_Field("AccountNum");
sheet.Cells[i + offset + 1, 2] = axCustTable.get_Field("Name");
sheet.Cells[i + offset + 2, 2] = axCustTable.get_Field("CustGroup");
sheet.Cells[i + offset + 4, 1] = "Addresses:";
sheet.Cells[i + offset + 5, 1] = "Street name";
sheet.Cells[i + offset + 5, 2] = "Zip/Postal Code";
sheet.Cells[i + offset + 5, 3] = "City";
sheet.get_Range("A" + (i + offset + 5).ToString(), "XFD" + (i + offset + 5).ToString()).Style = "40% - Accent1";
axRelRecord.ExecuteStmt("select * from %1 where %1.PartyId == '" + axCustTable.get_Field("PartyId") + "'");
while (axRelRecord.Found)
{
axRelMappingRecord.ExecuteStmt("select * from %1 where %1.PartyAddressRelationshipRecId ==" + axRelRecord.get_Field("RecId").ToString());
while (axRelMappingRecord.Found)
{
axAddressRecord.ExecuteStmt("select * from %1 where %1.RecId ==" + axRelMappingRecord.get_Field("AddressRecId").ToString());
sheet.Cells[i + offset + 6, 1] = axAddressRecord.get_Field("Address").ToString();
sheet.Cells[i + offset + 6, 2] = axAddressRecord.get_Field("ZipCode").ToString();
sheet.Cells[i + offset + 6, 3] = axAddressRecord.get_Field("CountryRegionId").ToString();
sheet.get_Range("A" + (i + offset + 6).ToString(), "XFD" + (i + offset + 6).ToString()).Style = "20% - Accent1";
i++;
axRelMappingRecord.Next();
}
axRelRecord.Next();
}
i += offset + 3;
axCustTable.Next();
}
extractor.disconnectaos(ax);
Click Build Build ExcelCustInfo.
The development is almost over. Now Kate needs to create an installation package. She’s going to do that by using Microsoft ClickOncetechnology fordeploying .NET applications.
The installation package will be generated automatically.
Now thesetup.exe file can be found in thepublish folder of the application. / Click menu Project ExcelCustInfo Properties. Go to Publishtab.Click Publish Now.

1

Top View