Sage 200

The Lyftron Provider for Sage 200 offers the most natural way to access Sage 200 data from Lyftron with ease and also enables to connect with BI, MDM & ML tools, Data warehouses, Databases and other SAAS based applications with zero code and zero infrastructure requirements. The provider wraps the complexity of accessing Sage 200 data into easy-to-integrate relational fully managed ANSI Sql format. Make faster and better business decisions with Lyftron’s Sage 200 data provider and automatically build your data migration pipelines in minutes, not months

The provider hides the complexity of accessing data and provides additional powerful security features, smart caching, batching, socket management, and more.

Key Features

• Comprehensive Delta load mechanism.
• Real-time access to Sage 200.
• Comprehensive full support of ANSI Sql to query data with ease.
• Collaborative query processing.

Prerequisites

The user must have credentials for Sage 200, Lyftron and your destination data warehouse, lake or database to perform the data pipeline operation with Lyftron

Establishing a Connection with Lyftron's Quickstart Steps

Create your Sage 200 connection with Lyftron by following the 5 easy steps show below:

Step1. Add your connection

Click on Connect section on the left panel →  Click on Add Connection button

Step2. Select your connector

In the connector selection panel, search and click Sage 200 for your connection 

Step3. Enter your connection details

In the Connection String section enter the values of the below parameters. The following connection string is required to establish Sage 200 connection with Lyftron.

"InitiateOAuth=GETANDREFRESH;SubscriptionKey=12345;Schema=StandardUK"

Key	Value	Field
Connection Name	Enter your connection details	Required
InitiateOAuth	GETANDREFRESH	Required
SubscriptionKey	Enter Your SubscriptionKey	Required
Schema	Enter Your Schema	Required
Logfile**	Use the logfile option to debug your job and provide your connection name to generate the log file.  [ConnectionConfigurationPath]\Connection_name_log.tx	Optional
Verbosity**	Choose verbosity 1-5 based on the severity of debugging	Optional

** For more information, check the Lyftron logging and debugging section.

If you want more detailed information about how to establish a connection with Lyftron, click on Lyftron Connection Quick Start guide.

Step4. Test your connection

Once you are done entering your connection details, simply click on the Test Connection button to test the connectivity. In case your connection fails, add Logfile and Verbosity parameters and check the Lyftron logging and debugging section, to debug the error.

 Step5. Save your connection

Hurray! Now you have successfully connected with the Lyftron Sage 200 connector and can utilize the connector to Extract, Warehouse, Analyze, Visualize and Share your data.

Data Model

The Lyftron Provider for Sage 200 models entities in the Sage 200 API as tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

The Lyftron Provider for Sage 200 models Item Listings in tables so that they can be easily queried, updated, and deleted. You can list items on your Sage 200 store by calling the AddItem stored procedure.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Name	Type	Description
BankPostedTransactions	Views	Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.
Banks	Views	Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.
CompanyDetails	Views	Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.
CostCentres	Views	Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.
Currencies	Views	Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.
CustomerContacts	Views	Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
CustomerDeliveryAddresses	Views	A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.
Customers	Views	Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.
Departments	Views	Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.
FinancialReportLayouts	Views	Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.
FinancialYearPeriods	Views	This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.
NominalCodes	Views	Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.
NominalReportCategories	Views	Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).
NominalTransactionAnalysisCodes	Views	Nominal transaction analysis codes are used to group nominal analysis items on various transaction types
PendingEmails	Views	When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.
PriceBands	Views	A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.
ProductGroups	Views	Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.
ProductPrices	Views	This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.
Products	Views	Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.
ProductTransactions	Views	A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.
PurchaseOrderLines	Views	When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.
PurchaseOrders	Views	Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.
PurchasePostedTransactions	Views	Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.
SalesHistoricalTransactions	Views	Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions.
SalesOrderAdditionalCharges	Views	Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.
SalesOrderLines	Views	When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.
SalesOrderProformaLines	Views	This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.
SalesOrderQuoteLines	Views	This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.
SalesOrders	Views	Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.
SalesPostedTransactions	Views	Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.
SalesTradingPeriods	Views	This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)
SalesTransactions	Views	When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.
StockSettings	Views	These settings to define how to process and manage your stock.
SupplierContacts	Views	Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
Suppliers	Views	Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).
TaxCodes	Views	Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.
WarehouseHoldings	Views	All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.
Warehouses	Views	All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.