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.

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.

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 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.

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.

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:

  1. Verify that the Vendor exists in Axapta by calling the method in the next step (method will be called Check).
  2. Create the purchase order for the vendor (method will be called CreatePO).
  3. Do the actual purchase order creation in Axapta (method will be called CreatePurchOrder).
  4. Insert the purchase order lines into Axapta and the newly created purchase order (method will be called InsertPurchLine).
  5. 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.

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.

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.

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.

Show: