Sage 200 Integration With Magento 2 – Step By Step Guide for 2024

Magento 2

Magneto 2 is a leading eCommerce platform with lots of advanced features to build a fully functional B2B or B2C webshop. Its flexibility to implement custom functionalities and scalability makes it the best eCommerce platform in the world. Hire a professional Magento 2 upgrade service provider to migrate your website to this wonderful eCommerce platform.

Sage 200

Sage 200 is an ERP system suitable for small and medium businesses. It helps to manage your stock, accounts, etc all in one place.

Recently one of our customers wanted to integrate sage 200 ERP with their Magento 2 web store.

After discussion with Sage 200 support, we come to know that currently there are no integrations available for synchronization between Magento 2 and Sage 200 software.

There is some information provided on the website of Sage 200 about the integration but ultimately it leads you to contact their support and in the end, it results in the situation where you need to do customization for such integration as there is no channel available that can serve as a bridge between Magento 2 and Sage 200.

So our expert Magento 2 developers successfully developed a custom solution for the integration that is working really well for our customers.

Since there is no pre-made solution available for this in the market, this step-by-step guide will help you to integrate Sage 200 with Magento 2 website without wasting much time.

Sage 200 Setup

First, you need to make sure that your Sage 200 software is set up to work with the eCommerce website for import and export procedures. This includes the process of identifying the details of specific procedures and how all the details are going to be mapped with Magento because Sage 200 software has a bit different terminology for processing compared to Magento.

Also, you’ll need to set it up so that it can connect to your Magento store directory with FTP login details and in which directory it should access to fetch the updated file or do the upload.

You can also specify the interval for the import and export procedures to run periodically to process the updates. Make sure that you set this interval as per the number of procedures and amount of data these procedures process because the execution of these updates requires Magento’s resources and specifying wrong intervals can easily exhaust the resource limit of your Magento 2 instance and depending on the server resource limit it can result in downtime of the store.

Once you are done with this setup you need to check which action Import/Export you need to synchronize with your Magento 2 store and map the fields between Sage 200 and Magento.

Let’s take the Sales Order Import procedure for example which requires the Sales Orders of the Magento store to be imported and synced with Sage 200 software for stock management control and internal processing.

The Sales Order Import procedure in Sage 200 Software accepts attributes such as OrderType, CustAccRef, OrderWarehouse, DelPostalName, InvPostalName, PaymentInFull, PaymentRef, PaymentMethod, OrderProcessing, OrderPriority, SettlementDiscountDays, LineType, ProductCode, UnitPrice, CostCentre, Department, ProjectCode, ProspectPostalName, etc.

Let’s go over a few of them so we can identify how they are different in terminology compared to the Magento ecommerce platform.

• OrderType: Sage 200 has 4 types of orders acceptable – Sales Order, Sales Return, Quotation, and Quotation for Prospect. Sales Order is the normal order in the Magento store and Sales Return is the Credit Memo in terms of the Magento store where you can refund the order amount to customers. While Quotation and Quotation for Prospect do not apply to Magento because those are like providing a Sales Quote for the potential customer which can result in a Sales Order or cannot.
• CustAccRef: You may understand that this might be the Customer Number from the Magento store but you are wrong because Sage 200 does not have any feature to store customers and map them with orders. This is kind of a grouping option in Sage 200 where you can identify the Sales Order based on a group filter. For example, you can specify this value to be Guest or Registered Customer or specify Payment Methods, and based on that you can get the Orders data by those groups.
• OrderWarehouse: This helps you with tracking items and their stock through different warehouses in Sage 200 but in Magento, there is no default feature available to manage stock by warehouses.
• DelPostalName: All the fields related to “Del” in Sage 200 can be mapped directly with “Shipping” Details on Magento 2 website. Sage 200 uses the “Delivery” term instead of “Shipping”.
• InvPostalName: Same as above, all the fields related to “Inv” in Sage 200 can be mapped directly with “Billing” Details in Magento. Sage 200 uses the “Invoice” term instead of “Billing”.
• PaymentInFull: This seems like a Purchase Order or Check/Money Order but it specifies if the Payment was done on the order or not with Yes/No. This is used with Quotation order type so it’s not relevant in Magento so it should be set to “Yes” always.
• PaymentRef: This is supposed to contain the transaction number of the payment but Sage 200 only accepts a maximum of 20 characters for this field and the actual numbers on Magento are more than 20 characters so this field can be filled with any dynamic unique value in case of disputes you require to find it with Order matching.
• AnalysisCodes: Now we know that we cannot use the actual PaymentRef field as the correct Transaction Id with Magento but we do require that information as it’s vital for any Sales Order. For that, we can use extra fields provided by Sage 200 and fill in the details. We can use AnalysisCodeName1 as TransactionID and pass its value in AnalysisCodeValue1 from the Magento store and this will be mapped in Sage 200 as a custom additional field of TransactionID. Like this, we can use any other additional info in the pairs AnalysisCodeName2-AnalysisCodeValue2 to pass from the Magento store.
• PaymentMethod: This is the Payment Method name that maps with Magento but it has a restriction of 20 characters as well so you may want to add short names for payment methods in Sage 200 if they go above 20 characters.
• OrderProcessing: This could map with Magento’s Order Status but the Sales process in Magento is different than Sage 200. In this Sage 200 accepts orders as Allocate, Despatch, Invoice, and Post Invoice while Magento processes as Processing, Ship, Invoice, and Complete. Though this has different it can work with mapping on some level as per guidelines with Sage 200.
• OrderPriority: Magento does not have any priorities set in its Sales Order processing so we can consider all the Sales Orders to be a priority in Sage 200.
• SettlementDiscountDays: We can ignore this as Magento does not have any feature for any Settlement terminology.
• LineType: Sage 200 has different Types available for Products but Magento only deals with one type as Stock Items.
• ProductCode: This is the SKU of Products in the Magento store.
• UnitPrice: This is the price of each quantity in the Sales Order from the Magento store.
• CostCentre: Sage 200 provides this feature if the item is of a different type so you can specify its overridden price with this but Magento does not have that feature.
• Department: We can ignore this as Magento does not have that feature as well but can serve as a Category based on the Business’s requirement.
• ProjectCode: Sage 200 has the option to map Sales orders with specific Customers who belong to a specific Project but there is no such feature available in the Magento store.
• ProspectPostalName: All the fields related to “Prospect” can be used if this Sales Order was related to a Quotation but we can ignore this as Magento does not have that feature.

These were only a few from the Sales Order Import procedure and there are many more but following the above instructions, you can now identify them with ease to differentiate and map them with your Magento 2 website.

Magento 2 Setup

Now that we have some info on the Sales Order Import procedure, we can go ahead and work on Magento for the required setup.

As per the PDF guide Data Exchange for Sage200, it needs to have CSV files created on the website server for import into Sage 200 software and it can upload CSV files on the server for import in Magento.

For this purpose, it is recommended to create new different directories in /var/ directory of your Magento root directory and it should be for each procedure so none of them conflict with each other. For example, we can create /var/salesorderimport/ directory and in that, you have only the files exported for Sales Order Import and /var/stockitemexport/ directory and in that have only the files imported for Stock Item Export procedure which exports data of Products from Sage 200 on to website server and Magento can import that data to add new Products or Update existing ones.

Make sure that the directory permissions are set up so that files can be read and written by Sage 200 software by using an FTP connection.

There are 29 Import actions and 24 Export actions listed so for the synchronization it’s recommended to go through all of them and only implement which are required because each one needs customization after going through the setup of Sage 200 and Magento mapping.

Integration

Once you have done the setup and mapping of Sage 200 and Magento the next thing is the integration process which serves as a connection bridge to synchronize the data between Sage 200 and Magento store.

For this, we need to create a custom script and need to set it up from Cron Jobs in the website server to execute the script at regular intervals corresponding with the Import and Export schedule setup in Sage 200 software so it can be synced correspondingly with Magento and vice versa.

Now let’s create the script required for synchronization. We will take the Sales Order Import procedure to export Sales Orders from Magento 2 store and import in Sage 200 software:

1. Start with defining the Object and method for the procedure script:

class SageSalesOrderImport extends AbstractApp
{
 	public function run()
	{
	……………………
	…………………...
}
}

2. Now, we need to get authorization from Magento to execute the custom functionality smoothly and use as much fewer resources as possible. We can easily achieve this by REST or SOAP API already available in Magento 2. We will take REST for this process so make sure you have the Username and Password with role and permissions to access Sales Order data. Once you have those details then we can get the Access Token from Magento for that user:

$this->_objectManager->get('Magento\Framework\Registry')
         ->register('isSecureArea', true);

$tokenURL='http://www.yourwebsite.com/index.php/rest/V1/integration/admin/token';          
$curlConnection = curl_init();
$dataUser = array("username" => "YOUR_USER", "password" => 'PASSWORD');                                                                    
$userData = json_encode($dataUser);                       
$curlConnection = curl_init($tokenURL); 
curl_setopt($curlConnection, CURLOPT_CUSTOMREQUEST, "POST");                                                                     
curl_setopt($curlConnection, CURLOPT_POSTFIELDS, $userData);                                                                  
curl_setopt($curlConnection, CURLOPT_RETURNTRANSFER, true); 
curl_setopt($curlConnection, CURLOPT_HTTPHEADER, array(                                                                          
            'Content-Type: application/json',                                                                                
            'Content-Length: ' . strlen($userData))                                                                       
);       
$userToken = curl_exec($curlConnection);

3. After that we will fetch all the Sales Orders and Credit Memos from the last 15 minutes with the consideration that you have set your Sage 200 to fetch the Sales Orders and Credit Memos every 15 minutes.

$allOrders = $this->_objectManager->create('\Magento\Sales\Model\ResourceModel\Order\Collection')->addAttributeToSelect('*')->addFieldToFilter('created_at', array('from' => $fromTime, 'to' => $toTime))->setOrder('entity_id','ASC')->load();  

$allCreditMemos = $this->_objectManager->create('\Magento\Sales\Model\ResourceModel\Order\Creditmemo\Collection')->addAttributeToSelect('*')->addFieldToFilter('created_at', array('from' => $fromTime, 'to' => $toTime))->setOrder('entity_id','ASC')->load();

4. Next thing is to start creating the export file or check if it’s required to create the file if any new orders are not placed. We will create the file with the required headings in the specific directory and grant it the required access.

$orderCount = count($allOrders);  
$creditMemoCount = count($allCreditMemos);

if($orderCount == 0 && $ creditMemoCount == 0) {
        exit();  // No need for export file as no new Orders or Credit Memos created
}

// Continue if new Orders or Credit Memos created
$salesOrderFile = fopen('/var/salesorderimport/SalesOrders'.date('dmYHi').'.csv', 'w');

fputcsv($salesOrderFile, 
array('OrderType', 'CustAccRef', 'OrderWarehouse', 'DelPostalName', 'InvPostalName', 'PaymentInFull', 'PaymentRef', 'PaymentMethod', 'OrderProcessing',  'OrderPriority', 'SettlementDiscountDays', 'LineType', 'ProductCode', 'UnitPrice', 'CostCentre', 'Department', 'ProjectCode', 'ProspectPostalName'……)
); 

5. Now we can start fetching the Orders and insert them into the export file. For this, we will use the REST API with the User Token we obtained.

foreach($allOrders as $oneOrder) {
$orderId = $ oneOrder ->getEntityId();
$orderURL = 'http://www.yourwebsite.com/index.php/rest/V1/orders/'
$curlConnection = curl_init($orderURL.$orderId);
curl_setopt($curlConnection, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($curlConnection, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curlConnection, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Authorization: Bearer " . json_decode($userToken)));
$orderDetails = curl_exec($curlConnection);
$orderDetails = json_decode($orderDetails, 1);

$creditMemoIds = array();
$creditMemos = $oneOrder->getCreditmemosCollection();
foreach ($creditMemos as $creditMemo) { //Go through all the credit memos for the current order.
$creditMemoIds[] = $creditMemo->getId();
}

// Fetch all the order details and set up the values as per Sage 200 requirement
$incrementid = $orderDetails['increment_id'];
$orderType = 1; // For Sales Order Order Type is 1
	…………………
…………………

// Fetch all the product’s details from order and set up the values
foreach($orderDetails['items'] as $orderItem) {
$productCode = $orderItem['sku']; 
$productDescription = $orderItem['name']; 
$itemQty = $orderItem['qty_ordered'];
	…………………
…………………
}
}

6. Along with fetching the details, we will keep adding them to the file in new rows for each Sales Order and Credit Memo.

$orderSaveDetails = array($orderType, $orderId, $cashAccount, $custAccRef, ….);
fputcsv($salesOrderFile, $orderSaveDetails);

if(count($creditMemoIds)){
$orderType = 2; // For Credit Memos Order Order Type is 2
$creditMemoDetails = array($ordertype, $orderId, $cashAccount, $custAccRef, ….);
}

fputcsv($salesOrderFile, $creditMemoDetails);

fclose($salesOrderFile);

By following the above example, you can get all the order details and set them as Sage 200 requires, and create the order export file from Magento 2.

Once you are done with this process and set up this script to execute periodically in the Cron Jobs then you are ready to fetch updated orders from your Magento 2 store into Sage 200 software and have successfully synchronized the Sales Order Import procedure.

If you are looking to integrate sage 200 with your Magento 2 web store, contact a professional Magento 2 development company today.