Integrating Microsoft Axapta Using the Axapta Business Connector and Visual Basic .NET
Bill Thompson, Axapta Developer Support
Microsoft Business Solutions
September 2004
Applies to:
Microsoft Axapta
Microsoft Visual Studio .NET
Microsoft Visual Basic .NET
Summary: This article demonstrates how to use the Axapta Business Connector to integrate a third party-application with Microsoft Axapta. This knowledge can easily be transferred to other .NET based applications. (27 printed pages)
Contents
Introduction
Determining Required Axapta Methods
Axapta Modifications
Visual Basic .NET Application
Tying It All Together
Where to Go From Here
Conclusion
Introduction
The following discussion steps through creating an application that uses the Microsoft Axapta Business Connector to store and retrieve information from Microsoft Axapta. The external application that is created retrieves Inventory Item information and Vendor information from Axapta, and allows the end user to create a purchase order, add lines to the purchase order, and post an Invoice in Axapta. All this is done using the Business Connector.
This discussion does assume that the reader has some experience in Microsoft Axapta and Visual Basic .NET. Also, the Axapta Business Connector is assumed registered with the workstation being used.
The finished application will resemble the one shown in Figure 1.
.gif)
Figure 1. The application main screen
Determining Required Axapta Methods
The first step in this project is to determine what Axapta classes and methods are needed to perform the desired tasks. By examining the Axapta processes, it is determined that the class PurchFormLetter is used to create the invoice, quotation, and so on in the Purchase Order form. Also, the Axapta methods are used to create the purchase order (PurchTable table), and populate the lines for the purchase order (PurchLines table). The customized code that is created in Axapta needs to pass this information into Axapta, allow the tables to be populated, and call the appropriate PurchFormLetter methods to create the invoice.
Axapta Modifications
The first step in actually getting the custom application running is to decide what information is going to be coming from Microsoft Axapta (referred to as Axapta in the rest of this document) in real time, and what will be populated on the load of the form. Figure 2 shows the information that will be pulled from Axapta.
.gif)
Figure 2. Information that will be retrieved from Axapta
The account number combo box and item number combo box will be populated on form load, and the other information (other than order quantity) will be retrieved from Axapta in real time.
To do this, custom methods will need to be created in Axapta and called by the Visual Basic .NET application using the Business Connector (referred to as the COM connector in the rest of this document).
Create the Project
It is a recommended Axapta Best Practice to create projects in Axapta to store custom code, so the first step will be to create a project in Axapta to store the new code. Click the project icon .gif)
in the toolbar to open the project window. Then, right-click on Private (as this is not a multi-developer project), choose New and Project from the menus that appear.
Next, rename the new project by right-clicking and choosing Rename from the menu that appears. Name the project POIntegration, and press the Enter key to save the changes.
Now, double click on the POIntegration project to open the project.
Create the Class
Right click on the project name, and choose New | Class in the menu that appears, as shown in Figure 3.
.gif)
Figure 3. Creating a new class in the POIntegration Project
Class1 should now exist in the project. Double-click on the Class1 project to start the editor. Make the following change to the code displayed:
class POIntegration
{
VendTable vendTable;
InventTable inventTable;
InventTableModule modTable;
}
Once the above X++ code has been entered, save the modifications by clicking the disk icon in the editor, and close the editor. The editor closure ensures that the class has been saved and renamed to POIntegration.
The above X++ code simply creates references (for this class) to the tables that are going to be needed to retrieve the display information in the custom application. The VendTable contains vendor information. InventTable contains the Item Name. InventTableModule contains the Item Price, Unit, and Warehouse information.
Create the Data Return Methods
The next step is to create the methods that will be used to return the required information. As mentioned earlier, three tables will need to be used to populate the required text boxes.
The first set of information needed will be the vendor information. This information is retrieved based on the account number that is chosen through the combo box in the Visual Basic .NET application. This means the methods that are created in Axapta will need to have this account number passed into the method, and the method will return the appropriate information based on this account number. Therefore, the first step will need to be the creation of a new method. To do this, open the POIntegration class in the editor by double-clicking on the class name in the project. The editor should resemble Figure 4.
.gif)
Figure 4. Editing the POIntegration class
Now it is time to create the new method in the class. This is done by clicking the icon in the tool bar. Modify the new method to look like the following:
Name getVendName(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.Name;
}
As discussed previously, the account number will be need to be passed into the method, a select statement is run to find the proper record, and the required information will be returned. The next step is to create the rest of the required methods. The code is as follows:
//getVendState Method to return State value from Axapta:
StateId getVendState(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.State;
}
//getVendStr method to return street value from Axapta:
str getVendStr(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.Street;
}
//getVendZIP method to return ZipCode value from Axapta:
ZipCodeId getVendZIP(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.ZipCode;
}
//getVendCity method to return City value from Axapta:
City getVendCity(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.City;
}
//getVendCtry method to return Country value from Axapta:
CountryId getVendCtry(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.Country;
}
//getVendCur method to return functional currency value
//from Axapta for this vendor:
VendCurrencyCode getVendCur(AccountNum _actNum)
{
select firstonly vendTable where vendTable.AccountNum == _actNum;
return vendTable.Currency;
}
The above code should now be all that is needed to populate the vendor information. The next step is to create methods to return the item information by entering the following code:
//getItemLocation method to return warehouse information from Axapta:
InventLocationId getItemLocation(ItemId _item)
{
select firstonly modTable where modTable.ItemId == _item &&
modTable.ModuleType==ModuleInventPurchSales::Purch;
return modTable.inventLocationId;
}
//getItemName method to return item name from Axapta:
Name getItemName(ItemId _item)
{
select firstonly inventTable where inventTable.ItemId == _item;
return inventTable.ItemName;
}
//getItemPrice method to return item price from Axapta:
Price getItemPrice(ItemId _item)
{
select firstonly modTable where modTable.ItemId == _item &&
modTable.ModuleType==ModuleInventPurchSales::Purch;
return modTable.Price;
}
//getItemQuantity method to return default item quantity from Axapta:
QtyMultiples getItemQuantity(ItemId _item)
{
select firstonly modTable where modTable.ItemId == _item &&
modTable.ModuleType==ModuleInventPurchSales::Purch;
return modTable.Quantity;
}
//getItemUnitId method to return default unit type from Axapta:
UnitId getItemUnitId(ItemId _item)
{
select firstonly modTable where modTable.ItemId == _item &&
modTable.ModuleType==ModuleInventPurchSales::Purch;
return modTable.UnitId;
}
At this point, this should be all the code needed to display the required information.
Creating Posting Methods
After the data is ready to be posted, there needs to be a way of getting the information back into the system, creating the purchase order, and the posting of the invoice. To do this, a few more code additions in Axapta are required. To implement this in the current project, create another class and call it VB_PurchOrder. The class declaration should look like the following:
class VB_PurchOrder
{
VendTable vendor;
PurchTable purchTable;
PurchLine purchLine;
str PO_Number;
}
The above referenced tables will be used in creating the purchase order, and the string PO_Number will be used to return the purchase order number back to the Visual Basic .NET application.
Save the changes, and close the editor to verify that the changes are updated in the system.
Now it is time to create the methods in this class that will do the actual creation and invoicing/printing of the purchase order. The following steps are needed:
- Verify that the Vendor exists in Axapta by calling the method in the next step (method will be called Check).
- Create the purchase order for the vendor (method will be called CreatePO).
- Do the actual purchase order creation in Axapta (method will be called CreatePurchOrder).
- Insert the purchase order lines into Axapta and the newly created purchase order (method will be called InsertPurchLine).
- Create and/or print the Invoice for the purchase order (method will be called makeInvoice2).
Now, open the VB_PurchOrder class in the editor, and create the following:
Boolean Check()
{
Boolean retval = TRUE;
;
if (!vendor.AccountNum)
{
warning("Vendor is not defined!");
retval = FALSE;
}
return retval;
}
public str CreatePO(AccountNum _actNum)
{
select firstonly AccountNum from vendor where vendor.AccountNum==_actNum;
this.CreatePurchOrder();
return PO_Number;
}
void CreatePurchOrder()
{
;
if (!this.Check())
return;
PurchTable.PurchaseType = PurchaseType::Purch;
PurchTable.initValue();
PurchTable.OrderAccount = vendor.AccountNum;
PurchTable.InitFromVendTable();
PurchTable.PurchId = NumberSeq::newGetNum(SalesParameters::numRefSalesId()).num();
if (purchTable.validateWrite())
purchTable.insert();
PO_Number = purchTable.PurchId;
}
public boolean insertPurchLine(
ItemId _itemId, CostPrice _costPrice,
Qty _qty, ItemFreeTxt _text = "")
{
InventTable item;
;
purchLine.clear();
purchLine.initValue();
purchLine.PurchId = purchTable.PurchId;
item = InventTable::find(_itemId);
purchLine.ItemId = item.ItemId;
purchLine.Name = item.ItemName;
purchLine.InitFromInventTable(item);
purchLine.initFromPurchTable(purchTable);
// this field (PurchPrice) can be omitted,
// if price must be taken from item setup,
// then createLine must be called with
// parameter "searchPrice = Yes"
purchLine.PurchPrice = _costPrice;
purchLine.PurchQty = _qty;
purchLine.initPurchQty();
purchLine.lineAmount = purchLine.calcLineAmount();
purchLine.LineNum = PurchLine::LastLineNum(purchLine.PurchId) + 1;
purchLine.createLine(true, // Validate
false, // initFromSalesTable
false, // initFromInventTable
true, // calcInventQty
true, // searchMarkup - something about misc. charges
false // searchPrice
);
return true;
}
str makeInvoice2(boolean _print)
{
PurchFormLetter purchFormLetter;
PurchParmTable purchParmTable;
PurchParmLine purchParmLine;
;
purchFormLetter = PurchFormLetter::construct(DocumentStatus::Invoice);
purchFormLetter.createParmUpdate();
purchFormLetter.createParmTable( purchParmTable, purchTable);
// external invoice number
purchParmTable.Num = purchTable.PurchId;
purchParmTable.insert();
while select purchLine
where purchLine.PurchId == purchTable.purchId
{
purchParmLine.ParmId = purchParmTable.ParmId;
purchParmLine.InitFromPurchLine(purchLine);
purchParmLine.ReceiveNow = purchLine.QtyOrdered;
purchParmLine.setQty(DocumentStatus::Invoice, false, true);
purchParmLine.setLineAmount();
purchParmLine.insert();
}
purchFormLetter.proforma (false); // proforma ?
purchFormLetter.printFormLetter(_print); // print ?
purchFormLetter.specQty (PurchUpdate::All); // what to update?
purchFormLetter.transDate (today()); // update date
purchFormLetter.run();
if(_print)
return 'Invoiced and Printed';
else
return 'Invoiced';
}
Once the above has been successfully entered and the code compiles, the next step is to create the Visual Basic .NET application.
Visual Basic .NET Application
The first step in creating the application is to create a new project. Call the project AxaptaSalesOrderCOM. Once the project is created, the Axapta Business Connector is going to need to be referenced in the project. This is done by the following navigation in Visual Studio: Project | Add Reference. Choose the COM tab. The screen in Figure 5 should now be displayed.
.gif)
Figure 5. Visual Studio Add Reference screen
The Business Connector will need to be located by finding Axapta COM Connector 1.2 Type Library in the list. Highlight this item, and click the Select button. Once the component is in the Selected Components list, click the OK button.
The Visual Basic .NET application is going to need to be told to use the connector and how to log into Axapta. To do this, add the following line under the Public Class Form1 line in the Visual Studio Editor:
Dim Axapta As AxaptaCOMConnector.Axapta
This line creates the reference to the Business Connector.
Making the Connection
For this discussion, the application will connect to Axapta when the form opens and close the connection to Axapta when the form exists. To do this, the following modifications need to be made to the code:
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'connect to Axapta via COM connector
Axapta = New AxaptaCOMConnector.Axapta
Axapta.Logon("Admin", "", "", "")
End Sub
The Axapta = New AxaptaCOMConnector.Axapta line instantiates the Axapta object, and the Axapta.Logon("Admin", "", "", "") call logs into the Business Connector. The first parameter is the user used to logon. The second parameter is the server manager and is optional. The third parameter is the AOS Server to connect to and is optional (see Axapta documentation for AOS information is unfamiliar with the term). The fourth parameter is the Axapta configuration to use while logging on, and is optional as well.
Please note that the user password is NOT passed using this call. Therefore, the password must be set in the configuration that is currently being used for logging on to the system. To set this, use the Business Connector tab in the Microsoft Axapta Configuration Utility.
Private Sub Form1_Closed(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Closed
Axapta.Logoff()
End Sub
The above code simply closes the Business Connector connection when the form is closed. This call will also occur when the application is closed.
Building the Interface
The next step is to create the interface for the purchase order data. This should look like Figure 6 when completed.
.gif)
Figure 6. The completed form
The following table contains the control information based on the references in Figure 6.
| Number | Name | Type | Default Text |
|---|---|---|---|
| 1 | DateTimePicker1 | DateTimePicker | ---- |
| 2 | txSO | Textbox | Purchase Order Number |
| 3 | btNewSO | Button | New Purchase Order |
| 4 | Button2 | Button | Cancel Purchase Order |
| 5 | cbVendors | Combo box | -- None Selected -- |
| 6 | txCurrency | Textbox | txCurrency |
| 7 | TextBox1 | Textbox | Vendor Name |
| 8 | txAddress | Multiline textbox | ---- |
| 9 | txCity | textbox | ---- |
| 10 | txState | textbox | ---- |
| 11 | txZIP | textbox | ---- |
| 12 | txCountry | textbox | ---- |
| 13 | cbITem | Combo box | -- None Selected -- |
| 14 | TextBox2 | Textbox | Item Name |
| 15 | txQuantity | Textbox | 0 |
| 16 | txUnitId | Textbox | UnitId |
| 17 | txPrice | Textbox | 0.00 |
| 18 | txWarehouse | Textbox | Warehouse |
| 19 | Button1 | Button | Add Line |
| 20 | lvLines | Listview | ---- |
| 21 | btClearLines | Button | Clear Lines |
| 22 | btDeleteLine | Button | Delete Line |
| 23 | btProcess | Button | Process Order |
Note the DateTimePicker1 control is not used by the data. It is used for display purposes only. If the system date is correct, this is what will be used by Axapta to process the purchase order when it is created.
Also, please note that labels will need to be added to many of the display fields.
Populating the Items and Vendors Lists
The first step done after creating the connection is to populate the combo boxes that will be used to select the vendor and the items. After this has been accomplished, the list view box is formatted to the proper specifications. This is done by creating the following subroutines:
Private Sub formatView()
lvLines.View = View.Details
lvLines.Columns.Add("Item", 120, HorizontalAlignment.Left)
lvLines.Columns.Add("Name", 300, HorizontalAlignment.Left)
lvLines.Columns.Add("Price", 65, HorizontalAlignment.Right)
lvLines.Columns.Add("Unit", 65, HorizontalAlignment.Left)
lvLines.Columns.Add("Quantity", 65, HorizontalAlignment.Right)
lvLines.Columns.Add("Warehouse", 100, HorizontalAlignment.Left)
lvLines.FullRowSelect = True
End Sub
The above code simply defines the column headings, width of the columns, justification of the columns, and how the selection or highlighting of a line works in the list view box, which will be used to hold the purchase order line information.
Private Sub loadInfo()
Dim AxaptaQuery As AxaptaCOMConnector.IAxaptaObject
Dim AxaptaQueryRun As AxaptaCOMConnector.IAxaptaObject
Dim AxaptaDataSource As AxaptaCOMConnector.IAxaptaObject
Dim ItemTableBuffer As AxaptaCOMConnector.IAxaptaRecord
Dim VendTableBuffer As AxaptaCOMConnector.IAxaptaRecord
Dim vendTable = 505 ' ID value of the Axapta VendTable
Dim itemTable = 175 ' ID value of the Axapta InventTable
AxaptaQuery = Axapta.CreateObject("Query")
AxaptaDataSource = AxaptaQuery.Call("AddDataSource", vendTable)
AxaptaQueryRun = Axapta.CreateObject("QueryRun", AxaptaQuery)
While (AxaptaQueryRun.Call("Next"))
VendTableBuffer = AxaptaQueryRun.Call("GetNo", 1)
cbVendors.Items.Add(VendTableBuffer.field("ACCOUNTNUM"))
End While
AxaptaQuery = Axapta.CreateObject("Query")
AxaptaDataSource = AxaptaQuery.Call("AddDataSource", itemTable)
AxaptaQueryRun = Axapta.CreateObject("QueryRun", AxaptaQuery)
While (AxaptaQueryRun.Call("Next"))
ItemTableBuffer = AxaptaQueryRun.Call("GetNo", 1)
cbITem.Items.Add(ItemTableBuffer.field("ITEMID"))
End While
End Sub
The above code demonstrates how to retrieve information from a table by creating an Axapta query and assigning a table to the queries datasource. The first step is to create references to the Axapta objects. Required are the query, datasource, and queryrun objects from Axapta to process the table data, and buffer objects to hold the record as it comes from the queryrun object. Also, notice that the tables are referenced by an integer value. These values can be found by examining the properties of the required table within the AOT in Axapta. The ID property is the value that is required.
The Form1_load should now be modified to resemble the code below:
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'connect to Axapta via COM connector
Axapta = New AxaptaCOMConnector.Axapta
Axapta.Logon("Admin", "", "", "")
formatView()
loadInfo()
End Sub
Figure 7 shows the properties for the InventTable, which contains the item information.
.gif)
Figure 7. InventTable Properties window
The following code defines the Axapta Query object and adds a table to the query using the datasource call:
AxaptaQuery = Axapta.CreateObject("Query")
AxaptaDataSource = AxaptaQuery.Call("AddDataSource", vendTable)
The QueryRun actually starts the process of pulling data from the table.
AxaptaQueryRun = Axapta.CreateObject("QueryRun", AxaptaQuery)
The following code does the actual processing of the data by assigning the data record to the object previously defined, and then retrieves the required portion of that record, and inserts the information into the combo box as an item.
While (AxaptaQueryRun.Call("Next"))
VendTableBuffer = AxaptaQueryRun.Call("GetNo", 1)
cbVendors.Items.Add(VendTableBuffer.field("ACCOUNTNUM"))
End While
The last portion of the displayed code simply repeats the previous process using the InventTable and assigns the item number to the cbItem combo box.
Populating the Display Boxes
When a user makes a selection, the required behavior is to populate the information boxes that are related to the selection. So, when a Vendor is chosen, the application should display the vendor information, and when an inventory item is chosen, the application should populate the default values.
To do this, the SelectedIndexChanged sub for each control has to be modified. Here is the code for the cbCustomers control:
Private Sub cbCustomers_SelectedIndexChanged( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cbVendors.SelectedIndexChanged
Dim axClass As Object
Dim axPOReference As AxaptaCOMConnector.IAxaptaObject
Dim actNum As String
Dim ActNumIdx = cbVendors.SelectedIndex
axPOReference = Axapta.CreateObject("POIntegration")
actNum = cbVendors.Items.Item(ActNumIdx)
TextBox1.Text = axPOReference.Call("getVendName", actNum)
txCurrency.Text = axPOReference.Call("getVendCur", actNum)
txAddress.Text = axPOReference.Call("getVendStr", actNum)
txCity.Text = axPOReference.Call("getVendCity", actNum)
txState.Text = axPOReference.Call("getVendState", actNum)
txZIP.Text = axPOReference.Call("getVendZIP", actNum)
txCountry.Text = axPOReference.Call("getVendCtry", actNum)
End Sub
By examining the code, the following is performed. The axClass variable is defined as an object. The axPOReference variable is defined as an Axapta object. A string variable is defined to hold the account number, and the index of the selected item is retrieved from the control.
Once the above is done, the reference to the Axapta class POIntegration is created, and the actNum value is retrieved from the cbVendors control. Then, the display objects (controls) on the form are populated by making calls to the proper custom Axapta methods (through the Business Connector). The first parameter in the axPOReference.Call is the method name, and the actNum is the parameter that is being passed to the method.
The same is done for the cbITem control. When the selection changes, the display or default values are retrieved from Axapta using the custom methods that were created earlier in this discussion. Below is the code for that process:
Private Sub cbITem_SelectedIndexChanged( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cbITem.SelectedIndexChanged
Dim axClass As Object
Dim axPOReference As AxaptaCOMConnector.IAxaptaObject
Dim perIdx As Integer
Dim prce As String
Dim itemNum As String
Dim ActNumIdx = cbITem.SelectedIndex
Button1.Enabled = True
axPOReference = Axapta.CreateObject("POIntegration")
itemNum = cbITem.Items.Item(ActNumIdx)
TextBox2.Text = axPOReference.Call("getItemName", itemNum)
prce = axPOReference.Call("getItemPrice", itemNum)
perIdx = prce.IndexOf(".")
If perIdx = -1 Then
txPrice.Text = prce
ElseIf perIdx = 1 And prce.Length < 4 Then
txPrice.Text = prce + "0"
ElseIf perIdx = 1 Then
txPrice.Text = prce
Else
txPrice.Text = "something else"
End If
txUnitId.Text = axPOReference.Call("getItemUnitId", itemNum)
txQuantity.Text = axPOReference.Call("getItemQuantity", itemNum)
txWarehouse.Text = axPOReference.Call("getItemLocation", itemNum)
End Sub
Please notice that the same process is done here as in the previous step. The objects are defined, and the custom Axapta calls are made. The only difference is the following code:
prce = axPOReference.Call("getItemPrice", itemNum)
perIdx = prce.IndexOf(".")
If perIdx = -1 Then
txPrice.Text = prce
ElseIf perIdx = 1 And prce.Length < 4 Then
txPrice.Text = prce + "0"
ElseIf perIdx = 1 Then
txPrice.Text = prce
Else
txPrice.Text = "something else"
End If
This code is used for formatting of the price data. There are a couple of items in the Axapta North America demonstration data that do not have two decimal places in the data. This code resolves the issue.
Saving the Order Lines
The order lines are stored in the list view control until processing of the purchase order is done. The population of the control is done by modifying the click subroutine of Button1. Below is the code that will populate the list box:
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click
Dim lvITem As ListViewItem
Dim itemNum As String
Dim ActNumIdx = cbITem.SelectedIndex
itemNum = cbITem.Items.Item(ActNumIdx)
' Add the line information
lvITem = New ListViewItem
lvITem.Text = itemNum
lvITem.SubItems.Add(TextBox2.Text)
lvITem.SubItems.Add(txPrice.Text)
lvITem.SubItems.Add(txUnitId.Text)
lvITem.SubItems.Add(txQuantity.Text)
lvITem.SubItems.Add(txWarehouse.Text)
' insert line into control
lvLines.Items.Add(lvITem)
End Sub
The above code simply adds the information into a ListViewItem variable that was defined in the code, and adds the newly populated ListViewItem record into the list view control.
Creating the Purchase Order in Axapta
Once all the required line information has been entered, it is time to create the purchase order in Axapta. This is done by modifying the clicked sub of the Process button. Below is the code:
Private Sub btProcess_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btProcess.Click
Dim SONum As Integer
Dim created As Boolean
Dim stat As String
Dim axClass As Object
Dim axPOReference As AxaptaCOMConnector.IAxaptaObject
axPOReference = Axapta.CreateObject("VB_PurchOrder")
Panel1.Enabled = False
Panel2.Enabled = False
Panel3.Enabled = False
btProcess.Enabled = False '
' code to connect and update sales order goes in here
Dim txt As String
Dim ActNumIdx = cbVendors.SelectedIndex
txt = Trim(cbVendors.Items.Item(ActNumIdx))
Axapta.TTSBegin()
txSO.Text = axPOReference.Call("CreatePO", txt)
Me.Refresh()
Dim cntrEnd As Integer ' holds number of lines in the ListView
Dim cnt As Integer ' counter for processing the listview items
Dim lvCnt As Integer ' line in ListView
Dim lvLIne As ListViewItem ' holds our active line
cntrEnd = lvLines.Items.Count
For cnt = 1 To cntrEnd
lvCnt = cnt - 1
lvLIne = lvLines.Items(lvCnt)
Dim txItemNo = lvLIne.SubItems.Item(0).Text
Dim txPrice = lvLIne.SubItems.Item(2).Text
Dim txUnitId = lvLIne.SubItems.Item(3).Text
Dim txQuantity = lvLIne.SubItems.Item(4).Text
Dim txWarehouse = lvLIne.SubItems.Item(5).Text
created = axPOReference.Call("insertPurchLine", txItemNo, txPrice, txQuantity)
Next
Try
stat = axPOReference.Call("makeInvoice2", True)
Axapta.TTSCommit()
MsgBox("Purchase Order " & txSO.Text & " has been " _
& stat & ".", _
MsgBoxStyle.Information, "Purchase Order Processed")
Catch ex As Exception
MsgBox(Err.Description, MsgBoxStyle.Exclamation, "Posting Issue")
Axapta.TTSAbort()
MsgBox("Purchase Order " & txSO.Text & _
" creation has been cancelled.", _
MsgBoxStyle.Exclamation, "Order Status")
End Try
Button2.Enabled = False
InfoReset()
End Sub
Here is a description of how the code works. The following lines set up the Business Connector reference, the required Axapta object, and the reference to the class that will be used:
Dim axClass As Object
Dim axPOReference As AxaptaCOMConnector.IAxaptaObject
axPOReference = Axapta.CreateObject("VB_PurchOrder")
The next two lines introduce a new concept and an old concept into the code.
Axapta.TTSBegin()
txSO.Text = axPOReference.Call("CreatePO", txt)
The Axapta.TTSBegin() calls the ttsbegin function of the Microsoft Axapta X++ language. Use this method to start a transaction that can be either committed (IAxapta::TTSCommit) or aborted (IAxapta::TTSAbort). The Axapta online Developers Guide can give more information on the above if so desired.
txSO.Text = axPOReference.Call("CreatePO", txt) calls the CreatePO method that was introduced earlier in the discussion. The txSO.text field will contain the purchase order number if the creation of the purchase order was successful.
Now, even though the purchase order has been created within Axapta, there are not yet any line items associated with it. The next section of code loops through the list view control, retrieves the appropriate information from the control, passes it into Axapta through the methods we created, and populates the line items for the purchase order. Here is the code snippet that does this:
cntrEnd = lvLines.Items.Count
For cnt = 1 To cntrEnd
lvCnt = cnt - 1
lvLIne = lvLines.Items(lvCnt)
Dim txItemNo = lvLIne.SubItems.Item(0).Text
Dim txPrice = lvLIne.SubItems.Item(2).Text
Dim txUnitId = lvLIne.SubItems.Item(3).Text
Dim txQuantity = lvLIne.SubItems.Item(4).Text
Dim txWarehouse = lvLIne.SubItems.Item(5).Text
created = axPOReference.Call("insertPurchLine", txItemNo, txPrice, txQuantity)
Next
Please notice that the created = axPOReference.Call("insertPurchLine", txItemNo, txPrice, txQuantity) line calls (using the Business Connector) the insertPurchLine method that was created earlier in this discussion, and passes in the required information.
Creating the Invoice
At this point, if the code has executed successfully, and the Axapta purchase order window was opened, the purchase order would exist, but it would be in an Open Status. Our application will now post the purchase order, and generate an invoice reflecting this post.
The following code performs the invoice creation:
Try
stat = axPOReference.Call("makeInvoice2", True)
Axapta.TTSCommit()
MsgBox("Purchase Order " & txSO.Text & _
" has been " & stat & ".", _
MsgBoxStyle.Information, "Purchase Order Processed")
Catch ex As Exception
MsgBox(Err.Description, MsgBoxStyle.Exclamation, "Posting Issue")
Axapta.TTSAbort()
MsgBox("Purchase Order " & txSO.Text & _
" creation has been cancelled.", _
MsgBoxStyle.Exclamation, "Order Status")
End Try
Please notice the use of the Try – Catch block in the code. The call to Axapta method makeInvoice2 is performed. If there is no error, the Axapta.TTSCommit() is called to force the commit of the transaction into the database, and a message is displayed saying the posting was successful.
If there is an error during the posting process, a message box is displayed returning the error message, and the Axapta.TTSAbort() method is called. This call will roll back the creation of the purchase order. This means that the purchase order that has been created is purged from Axapta.
Note the call to makeInvoice2 passes in one parameter. If Boolean True is passed, the invoice will be printed to the Axapta default printer. If Boolean False is passed, the invoice will not be printed.
Tying It All Together
At this point, functionally, the application will work. It will interface with Axapta, retrieve vendor and item information, populate line information for a purchase order, and create and invoice the purchase order. All that is left is to enable the other minor functionality issues. For example, clear the line items control, delete a line from the line items, enable/disable the controls when needed, and so on. The following code should complete the application as currently displayed:
Private Sub btClearLines_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btClearLines.Click
lvLines.Items.Clear()
End Sub
Private Sub InfoReset()
txSO.Text = "Purchase Order Number"
cbVendors.Text = "-- None selected --"
cbITem.Text = "-- None selected --"
TextBox1.Text = ""
txCurrency.Text = ""
txAddress.Text = ""
txCity.Text = ""
txState.Text = ""
txZIP.Text = ""
txCountry.Text = ""
TextBox2.Text = "Item Name"
txQuantity.Text = "0"
txUnitId.Text = "UnitId"
txPrice.Text = "0.00"
txWarehouse.Text = "Warehouse"
lvLines.Items.Clear()
End Sub
Private Sub btDeleteLine_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btDeleteLine.Click
Dim ActNumIdx = lvLines.SelectedIndices(0)
If ActNumIdx >= 0 Then
lvLines.Items.RemoveAt(ActNumIdx)
End If
End Sub
Private Sub btNewSO_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btNewSO.Click
Panel1.Enabled = True
Panel2.Enabled = True
Panel3.Enabled = True
lvLines.Items.Clear()
btProcess.Enabled = True
Button2.Enabled = True
Button1.Enabled = False
End Sub
Private Sub MenuItem2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MenuItem2.Click
' this is the quit code
Application.Exit()
End Sub
Private Sub MenuItem6_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MenuItem6.Click
Dim myform As System.Windows.Forms.Form
myform = New Form3
myform.Show()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
Panel1.Enabled = False
Panel2.Enabled = False
Panel3.Enabled = False
btProcess.Enabled = False
txSO.Text = "Sales Order Number"
Button2.Enabled = False
InfoReset()
End Sub
Where to Go From Here
This is the basis for a standalone purchase order system that will integrate with Microsoft Axapta. This could be improved upon by performing more in depth error checking. On hand inventory could be checked when a line item is added to the order. The option to not post the purchase order could also be added.
Much of this code could be used to create a Web application that creates a purchase order as well. The choices are endless in what to do with this application. These exercises are left up to the reader.
Conclusion
Integration with Microsoft Axapta can be accomplished fairly easily, as long as some thought goes into the design process. The first step in the process is to determine what methods are used in Microsoft Axapta to perform the actions that are required. The second step is to design a set of methods that will populate the required data structures, and call the previously identified method to perform the required processes. The third step is to design the external (Visual Basic .NET) application that will acquire the information that will be passed into Microsoft Axapta, and call the Axapta methods that will complete the process.
The Business Connector is able to access all of the Microsoft Axapta business logic. Therefore, once the process has been identified and mapped in Axapta, an externally developed application should be able to use the existing business processes, with minimal customization to Axapta. Generally, the only code that needs to be created in Axapta is code that will be used to wrap existing classes and methods, or move data between the applications.
Bill Thompson is an Axapta Developer Support Consultant for Microsoft Business Solutions by day and a closet coder by night. Bill is a Sci-Fi junkie and loves to watch 50's and 60's Sci-Fi movies when he can.