Sage 300 Integration: How to Connect with Sage 300 ERP Desktop (Sage Accpac)

Summary

• Sage 300 uses a COM API, which requires referencing specific DLLs and connecting via C# for integrations with third-party applications.
  
• Customer data can be extracted using the AR0024 view, but field values must be cast to the correct data types to avoid vague errors.
  
• The integration process includes setup steps like adding references, establishing a session, and querying records using defined view IDs.
  
• Successful integration requires planning around data flow direction, sync frequency, data mapping, and server performance impact.

Sage 300 ERP Desktop is a robust and feature-rich business management solution designed for small to medium-sized businesses. However, integrating Sage 300 ERP to third-party applications using the Sage 300 API can be tricky due to the limited documentation, use of a COM API, and generic error feedback that can often leave you scratching your head.

But here’s the good news: Integration of Sage 300 is still possible! This article will provide a clear pathway for extracting data from Sage 300 ERP, enabling you to leverage it within your preferred third-party applications.

What’s a “COM API”?

You might be wondering, “What exactly is a COM API?” In simple terms, a COM (Component Object Model) API is a set of programming rules and libraries that enable different software components to communicate with each other. 

It accomplishes this by exposing functionality through interfaces, which are nothing more than a set of functions that are grouped together.

Think of it as a toolbox, but for software. The key is understanding how to effectively use this toolbox to achieve your integration goals.

Getting Started with the Sage 300 ERP COM API

Integration with Sage 300 ERP may initially seem daunting, but don’t panic. This guide will use C# to show you how to extract customer information from Sage 300 ERP and push it into your third-party applications. The first thing you need to do is locate the required DLLs on your Sage 300 ERP server.

These DLLs can be found in the following locations on the server:

• C:\Windows\Microsoft.NET\assembly\GAC_32\ACCPAC.Advantage\{Sage 300 ERP Version}\ACCPAC.Advantage.dll
  
• C:\Windows\Microsoft.NET\assembly\GAC_MSIL\ACCPAC.Advantage.Types\{Sage 300 ERP Version}\ACCPAC.Advantage.Types.dll

Next, it’s time to set up your project in Visual Studio and start building your Sage 300 integration.

1. Reference the DLLs

Once your project is created, navigate to the solution explorer and right-click your project. Select Add > Reference. Here you can add the DLLs you found earlier as references to the project.

2. Connect to Sage 300 ERP

Now the exciting part: Connecting to your Sage 300 ERP instance!

Here’s a code snippet illustrating how to establish the connection:

CSharp
Session session = new Session();
session.Init("", "XY", "XY1000", "62A");
session.Open("USERNAME", "PASSWORD", "COMPANY_ID", DateTime.Today, 0);
DBLink mDBLinkCmpRW = session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadOnly);

Ensure that you put your actual credentials and company ID in place of the placeholders. We’re using read-only permissions here to fetch data. Permissions are set based on the flags specified when establishing a connection.

3. Query Customers

This is the final part of the Sage 300 integration. Once you have established a connection to Sage 300 ERP using the Sage 300 API, you can extract data from Sage and send it to your third-party application.

First, we need to identify the view for retrieving customer data. This can be determined by accessing Sage AOM. Navigate to the “AR” (Accounts Receivable) section to display a list of views associated with the AR module. In this list, we can see the customer’s view with a RotoID of AR0024. This RotoID will be used to extract data from this view.

Once navigating to this view during the Sage 300 integration, a list of all available fields is displayed, as well as their associated data type. It’s very important that you cast these fields to their appropriate data type in code. Otherwise, you will receive generic exceptions that, in most cases, don’t provide much detail as to what has occurred. 

The following snippet demonstrates how to properly query customer data and process it as needed.

CSharp
public List<Customer> GetCustomers()
{
    List<Customer> customers = new List<Customer>();

    // Open the customer view
    var view = mDBLinkCmpRW.OpenView("AR0024");

    // Check if we have any records
    if (!view.GoTop())
        return customers; // Return empty list if no customers found

    do
    {
        // Map database fields to Customer properties
        customers.Add(new Customer(
            idCust: GetString(view, "IDCUST"),
            name: GetString(view, "NAMECUST"),
            addressLine1: GetString(view, "TEXTSTRE1"),
            addressLine2: GetString(view, "TEXTSTRE2"),
            addressLine3: GetString(view, "TEXTSTRE3"),
            addressLine4: GetString(view, "TEXTSTRE4"),
            city: GetString(view, "NAMECITY"),
            state: GetString(view, "CODESTTE"),
            postalCode: GetString(view, "CODEPSTL"),
            country: GetString(view, "CODECTRY"),
            territory: GetString(view, "CODETERR"),
            contactName: GetString(view, "NAMECTAC"),
            phone: GetString(view, "TEXTPHON1"),
            email: GetString(view, "EMAIL1"),
            groupId: GetString(view, "IDGRP"),
            isActive: (short)view.Fields.FieldByName("SWACTV").Value == 1,
            creditLimit: GetDecimal(view, "AMTCRLIMT"),
            balanceDue: GetDecimal(view, "AMTBALDUET"),
            priceList: GetString(view, "PRICLIST")
        ));
    } while (view.GoNext());

    return customers;
}

// Helper methods to safely get values from fields
private string GetString(dynamic view, string fieldName)
{
    var field = view.Fields.FieldByName(fieldName);
    return field?.Value as string ?? string.Empty;
}

private decimal GetDecimal(dynamic view, string fieldName)
{
    var field = view.Fields.FieldByName(fieldName);
    return field?.Value as decimal? ?? 0m;
}

As you can see, once you get the connection opened, pulling information is relatively straightforward. You just have to be certain you’re using the correct data types to avoid pesky generic errors.

Best Practices for Sage 300 ERP Integration

When building a data integration using the Sage 300 API, consider the following:

• Data Flow: Should data synchronization be one-way or bi-directional?
  
• Sync Frequency: How often should updates occur?
  
• Data Mapping: How will data be mapped to avoid duplication?
  
• Performance: Will frequent data retrieval slow down the Sage 300 ERP Server?

Conclusion

Integrating Sage 300 ERP with third-party applications via the Sage 300 API enhances efficiency and can remove the need for manual data transfer. Despite the challenges that come along with limited documentation and generic error feedback. 

The right approach makes a seamless integration possible. If you are in need of expert assistance, Pell Software specializes in Sage 300 ERP integrations, as well as integrations with other ERP and accounting software like QuickBooks, NetSuite, Acumatica, and Salesforce.

Contact us today to discover how we can help streamline your business operations and maximize the value of your software investments!