How to Create or Update Customers with the Sage 100 API in Python

Introduction to Sage 100 API Integration

Sage 100 is a comprehensive ERP solution designed to streamline business operations, including accounting, inventory management, and customer relationship management. It is widely used by small to medium-sized businesses seeking to enhance their operational efficiency and financial management.

Integrating with the Sage 100 API allows developers to automate and manage customer data effectively. For example, developers can create or update customer records directly from their applications, ensuring that customer information is always up-to-date and accurate. This integration can be particularly useful for businesses looking to synchronize their CRM systems with Sage 100, thereby improving customer service and operational workflows.

Setting Up a Sage 100 Test/Sandbox Account for API Integration

Before you can begin integrating with the Sage 100 API, it's essential to set up a test or sandbox account. This environment allows you to safely experiment with API calls without affecting live data. Follow these steps to configure your Sage 100 environment for API development.

Install and Configure the Sage 100 ODBC Driver

The first step in setting up your Sage 100 test environment is to install and configure the Sage 100 ODBC driver. This driver is crucial for establishing a connection to the Sage 100 database. Here's how you can do it:

• Access the ODBC Data Source Administrator on your system.
• Create a Data Source Name (DSN) that connects to the Sage 100 ERP system. Ensure you use the correct server, database, and authentication settings.
• Refer to the official Sage 100 documentation for detailed instructions on configuring the ODBC driver.

Configure the Sage 100 Server

Once the ODBC driver is set up, you need to configure the Sage 100 server to allow API interactions:

• Decide whether to run the client/server ODBC driver as an application or a service.
• For application mode, locate and double-click the pvxiosvr.exe file in the Sage 100 installation directory.
• For service mode, run the pvxiosvr.exe with the -i parameter to install it as a service.
• Configure the server settings through the Server Manager to ensure the service starts automatically.

Test the ODBC Connection

After configuring the server, it's important to test the ODBC connection to ensure everything is working correctly:

• Open the ODBC Data Source Administrator and locate the SOTAMAS90 DSN.
• Use the Test Connection feature to verify the connection is successful.
• If you encounter issues, consult the Sage 100 documentation for troubleshooting tips.

With your Sage 100 test environment set up, you are now ready to start integrating with the API using Python. This setup ensures that you can safely test your API calls and handle customer data effectively.

sbb-itb-96038d7

Making API Calls to Create or Update Customers in Sage 100 Using Python

To interact with the Sage 100 API for creating or updating customer records, you'll need to use Python. This section will guide you through the necessary steps, including setting up your Python environment, writing the code to make API calls, and handling responses and errors effectively.

Setting Up Your Python Environment for Sage 100 API Integration

Before you begin coding, ensure that you have the following prerequisites installed on your machine:

• Python 3.x
• The Python package installer, pip

You'll also need to install the pyodbc library to interact with the Sage 100 ODBC driver. Run the following command in your terminal:

pip install pyodbc

Writing Python Code to Create or Update Customers in Sage 100

Now, let's write the Python code to create or update customer records in Sage 100. Start by creating a new Python file named sage100_customers.py and add the following code:

import pyodbc

# Define the DSN and connection parameters
dsn = 'SOTAMAS90'
user = 'your_username'
password = 'your_password'

# Establish a connection to the Sage 100 database
connection = pyodbc.connect(f'DSN={dsn};UID={user};PWD={password}')

# Create a cursor object
cursor = connection.cursor()

# Define the SQL query to create or update a customer
customer_id = 'CUST001'
customer_name = 'New Customer'
sql_query = f"""
IF EXISTS (SELECT * FROM AR_Customer WHERE CustomerNo = '{customer_id}')
    UPDATE AR_Customer SET CustomerName = '{customer_name}' WHERE CustomerNo = '{customer_id}'
ELSE
    INSERT INTO AR_Customer (CustomerNo, CustomerName) VALUES ('{customer_id}', '{customer_name}')
"""

# Execute the SQL query
cursor.execute(sql_query)

# Commit the transaction
connection.commit()

# Close the connection
cursor.close()
connection.close()

print("Customer record created or updated successfully.")

Replace your_username and your_password with your actual Sage 100 credentials. This script checks if a customer record exists and updates it; otherwise, it creates a new record.

Verifying Successful API Calls and Handling Errors

After running the script, you should verify the changes in your Sage 100 test environment:

• Log in to your Sage 100 test account.
• Navigate to the customer records section to confirm the creation or update of the customer.

To handle potential errors, wrap your database operations in a try-except block:

try:
    # Execute the SQL query
    cursor.execute(sql_query)
    connection.commit()
    print("Customer record created or updated successfully.")
except pyodbc.Error as e:
    print("An error occurred:", e)
finally:
    cursor.close()
    connection.close()

This ensures that any connection issues or SQL errors are caught and managed appropriately.

Conclusion and Best Practices for Sage 100 API Integration

Integrating with the Sage 100 API using Python offers a powerful way to automate customer data management, ensuring that your business processes are efficient and up-to-date. By following the steps outlined in this guide, you can create or update customer records seamlessly, enhancing your operational workflows.

Best Practices for Secure and Efficient Sage 100 API Usage

• Secure Credentials: Store your Sage 100 credentials securely, using environment variables or a secure vault, to prevent unauthorized access.
• Handle Rate Limiting: Be mindful of any rate limits imposed by Sage 100 to avoid service disruptions. Implement retry logic with exponential backoff if necessary.
• Data Validation: Ensure that customer data is validated and sanitized before making API calls to prevent errors and maintain data integrity.
• Error Handling: Use try-except blocks to manage exceptions gracefully, providing meaningful error messages and logging for troubleshooting.

Enhance Your Integration Strategy with Endgrate

While integrating with Sage 100 directly is effective, leveraging a tool like Endgrate can further streamline your integration efforts. Endgrate provides a unified API endpoint that connects to multiple platforms, allowing you to build once and deploy across various systems. This approach saves time and resources, enabling you to focus on your core product development.

Explore how Endgrate can simplify your integration processes and provide an intuitive experience for your customers by visiting Endgrate.

Read More

• https://endgrate.com/provider/sage100
• https://help-sage100.na.sage.com/InstallGuide/2022/Content/InstallGuide/Config_ODBC_Driver.htm
• https://help-sage100.na.sage.com/2022/FLOR/index.htm#File_Layouts/Accounts_Receivable/AR_Customer.htm?Highlight=AR_Customer