Sage is a system of management solutions automation for small and medium enterprises. It is used in such industries as accounting, payroll, manufacturing, distribution, sales, education, and finance. Sage has many software products that can be classified by business size, industry, and business needs. Sage software includes financial software solutions, CRM systems, HR solutions, and ERP systems. Let’s dig deeper into one of them.
Sage Evolution
Sage Evolution is an ERP System that offers a complete business management solution, gives the opportunity to control the financial situation, as well as relationships with customers, suppliers, and employees. All components and modules developed as part of the Sage Evolution Framework are fully integrated and have a common interface.
How it can be used
We have used Sage Evolution ERP when working on a project where we had to create/edit sales orders, customers, and inventories, synchronizing Sage ERP with another ERP. Sage Evolution works only on Windows operating system. MS SQL Server is used as a DBMS. And although you can connect directly to the Sage Evolution database, it’s better to interact through the Sage Pastel SDK as it is more convenient and ensures data integrity.
Sage Evolution SDK is packaged as a Microsoft .NET assembly and the ideal platform for using .NET Framework. But the integration via COM is possible using only such languages as Visual Basic, C ++, and Delphi.
We have used Sage SDK to synchronize company’s customers, products, transactions, and warehouses data with another system. Let’s see how exactly you can do that.
Using Sage SDK
For starters, make sure you use the same versions of Sage SDK and Sage Evolution. So, if you use Sage Evolution 7.00.210, then use Sage SDK 7.00.120.
Besides, remember you need a developer license (serial number and authorization code) to use Sage SDK.
Now, when you are all set, let’s move to actual examples.
1. Connect to the Database
First, you need to connect to the database. We have two databases: Company / Client database that contains data related to the company / client and Common database that contains registration information. We connect in this order:
| 1 2 3 4 5 | DatabaseContext.CreateCommonDBConnection(server, commonDatabase, String.Empty, String.Empty, true); DatabaseContext.SetLicense(serialNumber, authKey); DatabaseContext.CreateConnection(server, companyDatabase, login, password, true); |
Then we write queries to the database to get the necessary selections.
2. Customers
For example, here is a request for all customers, in which the date of creation / editing is more recent than the updated date:
| 1 2 3 4 5 6 7 | string criteria = $"Client_dModifiedDate > '{updatedDate}'"; var customers = Customer.List(criteria); foreach (DataRow matchP in customers.Rows) { Customer customer = new Customer(matchP["Account"].ToString()); } |
It is better to send the date in the format yyyy-MM-dd HH: mm: ss.fff, so that there is no error “SQL Server: datetime out of range error”.
You can find a customer by code. If there is no customer, the method will return null.
For example:
| 1 2 3 | Customer searchCustomer = Customer.GetByCode(code); |
You can also update customer’s fields:
| 1 2 3 4 5 6 7 | Customer customer = Customer.GetByCode(item.code); customer.Description = item.name; customer.Telephone = item.telephone; customer.IsOnHold = item.status; customer.Save(); |
3. Products
Now we sync the InventoryItem. Sage SDK has a Branch (Company) by default Global (BranchId = 0), but there may be other Branches.
For example, we can select products for a specific Branch:
| 1 2 3 4 5 6 7 | var criteria = $"StkItem_iBranchID = {branchId}"; var inventories = InventoryItem.List(criteria); foreach (DataRow matchP in inventories.Rows) { InventoryItem inventory = new InventoryItem((Int32)matchP["StockLink"]); } |
Products have a price/ and we can get both the individual (SellingPrice1, SellingPrice2 and SellingPrice3) prices and the SellingPrices collection. In terms of time, getting prices for some products can take seconds or up to 5-10 minutes.
For example, getting product price from warehouse looks like this:
| 1 2 3 4 5 6 7 8 9 10 | SellingPriceCollection sellingPrices = inventory.SellingPrices; double sellingPrice = 0; foreach (SellingPrice sp in sellingPrices) { if (sp.Warehouse != null && sp.Warehouse.Code.Equals(defaultWarehouse)) { sellingPrice = sp.PriceIncl; break; } } |
4. Transactions
Now we sync the SalesOrder. Through the Sage SDK, we can receive unprocessed, processed, and archived transactions.
For example, get processed SalesOrder, DocState = 3 (PartiallyProcessed):
| 1 2 3 4 5 6 7 | var criteria = $"DocState = {3} ORDER BY Client_dModifiedDate"; var orders = SalesOrder.List(criteria); foreach (DataRow matchP in orders.Rows) { SalesOrder order = new SalesOrder((Int32)matchP["AutoIndex"]); } |
Get archived transactions:
| 1 2 3 4 | var criteria = $"DocState = {4} ORDER BY Client_dModifiedDate"; var orders = SalesOrder.ListArchived(criteria); |
Find transaction by order number:
| 1 2 3 | var order = SalesOrder.List("ordernum = 'searchOrder'"); |
When editing transactions, you can change various fields, including order number, customer, and details. You can set the warehouse the following way:
| 1 2 3 4 5 6 7 8 9 | var order = new SalesOrder(id); order.Customer = Customer.GetByCode(customerCode); order.OrderNo = orderNumber; order.Detail[i].InventoryItem = inventory; order.Detail[i].Quantity = item.deals[i].sales_quantity; order.Detail[i].Warehouse = warehouse; order.Save(); |
5. Warehouses
We have used InventoryTransaction to detect how many pieces were moved between warehouses:
| 1 2 3 4 5 6 7 8 9 10 | var criteria = $"PostST_dModifiedDate > '{updatedDate}'"; var inventories = InventoryTransaction.List(criteria); foreach (DataRow matchP in inventories.Rows) { InventoryItem inventoryItem = new InventoryItem((Int32)matchP["AccountLink"]); Warehouse warehouse = new Warehouse((Int32)matchP["WarehouseID"]); WarehouseContext context = new WarehouseContext(inventoryItem, warehouse); double warehouseQuantity = context.QtyOnHand; } |
So, should you go with Sage SDK?
It is easier to use Sage SDK than to understand the entire database with all its relations. Besides, using Sage SDK grants data integrity.
But still, using Sage SDK results in writing not pure SQL queries and fields that do not match Sage SDK fields. An that implies that you have to get a list of all fields in order to understand which fields to select. Besides, if someone opened an order to view in Sage UI, you can’t change this order by Sage SDK or Sage UI. It means you can’t parallel work.
All in all, Sage SDK provides third-party developers access to the Sage database entities. There is also a Sage SDK documentation. That is why we recommend using Sage SDK to save time and ensure data integrity.
See how we extended an open source ERP software with new functionalities
