Integrating with sage line 500 sage 1000
This presentation is the property of its rightful owner.
Sponsored Links
1 / 24

Integrating with Sage Line 500 / Sage 1000 PowerPoint PPT Presentation


  • 126 Views
  • Uploaded on
  • Presentation posted in: General

Integrating with Sage Line 500 / Sage 1000. William Eastbury Sage User Network Tuesday, 08 June 2010. Session Content. Background Get information from an external source into Sage transactions. Get supplementary information into Sage and display it.

Download Presentation

Integrating with Sage Line 500 / Sage 1000

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Integrating with sage line 500 sage 1000

Integrating with Sage Line 500 / Sage 1000

William Eastbury

Sage User Network

Tuesday, 08 June 2010


Session content

Session Content

  • Background

  • Get information from an external source into Sage transactions.

  • Get supplementary information into Sage and display it.

  • Get information out of Sage, and use it elsewhere.

  • Product Tips and Best Practices

  • Middleware

  • In Summary

  • Any Questions


Background

Background

  • There are many reasons to integrate with Sage Line 500 / Sage 1000 ERP, but they mainly focus around three main areas.

    • Get information from an external source into Sage transactions.

    • Get supplementary information into Sage and display it, either on a Sage form or somewhere else.

    • Get information out of Sage, and use it elsewhere.


Get information into sage transactions

Get information into Sage transactions.

  • Where could the information come from ?

    • Traditional Electronic Data Interchange (EDI)

    • Business 2 Business Online Trading (B2B)

    • Business 2 Consumer Online Trading (B2C)

    • Separate vertical market / bespoke systems that need to be interfaced together (Promotions, Forecasting, etc.)

  • Examples of tools that can we use for this ?

    • Sage EDI Input Module

    • Defacto FS Import Module

    • APIs


Example ipath interface to sage gl

Example: iPath Interface to Sage GL

  • iPath is a bespoke trade promotions system written in-house for Simple.

    • It produces a list of accruals to be uploaded to the Sage GL system, these are passed through Datel’s NominalLedger API and posted directly to the Sage General Ledger as an unposted journal batch.

iPath

Datel NL API

Sage GL


Example edi inbound order

Example: EDI Inbound Order

  • Trade Sales order from Tesco

    • The order arrives via GXS Value Added Network

    • The order is converted to xml via Cindercone Magma

    • The order is sent to Sage

      • In batch mode via the Sage EDI Input Module (ei)

        OR

      • Directly, via The Datel FUSION SalesOrder API.

Sage EI Module

GXS VAN

Create File

Magma Server

Batch Import

Sage SOP

Direct Import

Datel API


Get supplementary information into sage and display it

Get supplementary information into Sage and display it

  • Where could the information come from ?

    • Hand keyed by users

    • External document attachments

    • Web Services

    • Separate vertical market / bespoke systems that need to be interfaced together

  • Examples of tools that can we use for this ?

    • FCE’s

    • Sage Tcr database

    • Datel FUSION Enhancer

    • Bespoke (Visual Studio (.net), Eclipse (java) etc.)


Example gl mapping for parent company s chart of accounts

Example: GL Mapping for Parent company’s chart of accounts

  • We need to report on our parent company’s consolidated accounting criteria, alongside our existing chart of accounts.

    • Use Datel’s FUSION Enhancer package to automatically create an FCE-invoked form allowing users to maintain an ‘alternative’ chart of accounts mapping for sage posting codes.

Design View

Live View

Click Here

And this appears


Get information out of sage and use it elsewhere

Get information out of Sage, and use it elsewhere.

  • Where could the information come from ?

    • Sage Company Databases

  • Examples of tools that can we use for this ?

    • ODBC / OLEDB (With Excel / Access)

    • SQL Server Reporting Services (SSRS)

    • SQL Server Integration Services (SSAS) / DTS 2000

    • Add-In Express

    • Iron Speed Designer

    • Bespoke (Visual Studio (.net), Eclipse (java) etc.)


Example referencing sage tables 1

Example : Referencing Sage Tables 1

  • We want to deliver access to view a list of Sage products from a dropdown in our web application.

  • Lets consider a hypothetical staff shop web application, built in Iron Speed Designer, but we want to allow the user only to select products that exist in Sage, and we want them to be able to select by description, not code.

    • So this needs to be a dropdown,

      populated from sage table stockm

    • Create a SQL user that has read only

      access to the required tables

    • If you want to, create a view to filter the

      warehouse from your table.

      ** USE THE WITH NOLOCK QUERY HINT

      IN A PRODUCTION SAGE SYSTEM **

    • And grant that account access.

      GRANT SELECT ON<ViewName>TO<UID>


Example referencing sage tables 2

Example : Referencing Sage Tables 2

  • Connect Iron Speed to the Sage Database

  • Create a Virtual Primary Key on the view that you created, against the ‘Product’ field.


Example referencing sage tables 3

Example : Referencing Sage Tables 3

  • Create a Formula to display the correct lookup value in a dropdown list


Voila there s your sage product lookup

Voila, There’s your Sage product Lookup!


Product tips and best practices fusion apis

Product Tips and Best Practices FUSION APIs

  • By far the easiest ‘hands off’ way we have found to handle integration into sage.

  • They are a ‘Direct Integration’, unlike the Sage Modules or Defacto modules, which require someone to either run the interface, or some kind of polling process.

  • Although these are Java based, they can be called from .NET applications and web services.

    • Use IKVM.NET to convert the Java .jar file to a .NET DLL

    • Do NOT try and use the .NET - Java Bridge

  • Be careful if using SalesOrder API with BOMs and bundles, as not all of this functionality seems to work exactly as expected... Ensure you do adequate testing...


Example referencing sage in an excel addin 1 product add in express

Example : Referencing Sage In an Excel Addin 1 Product : Add-In Express

  • Let’s suppose that we want to integrate and do some budgeting work outside of Sage, and our users want to work in Microsoft Excel.

  • We can use Add-in Express

  • to create an Excel Add-In and

  • build sage data into this Add-in.


Example referencing sage in an excel add in 2 product add in express

Example : Referencing Sage In an Excel Add-In 2 Product : Add-In Express

And here is our Add-In happily running in Excel 2003, extracting budget data.

Note: There is no reason why this could not be a 2-way process, allowing you to write back the budget data to the database.


Product tips and best practices iron speed designer

Product Tips and Best Practices Iron Speed Designer

  • This is the easiest code generator out there to use ...

  • Download the free version and try it ...

  • You should create the appropriate ‘virtual foreign keys’ and ‘virtual primary keys’ that are not created in the sage db, then Iron Speed will automatically work with those keys.

  • Will automatically discover your database and build solid code to add, change, remove or view records.

  • So use it with care !

  • You MUST create a new sql user that only has access to the tables / views that you are required to use. . . .

    Unless you want to wait for Iron Speed to read the entire schema of all 8,000 + sage tables ! ! !

  • Do not use the URL validation functionality as it does not work.


Product tips and best practices fusion enhancer

Product Tips and Best Practices FUSION Enhancer

  • This product builds FCE’s and web-pages to be called from fce’s.

  • It will also build an underlying database table for you to store your fce data in.

  • It basically consists of a Generic FCE and a set of code generation libraries, with a designer front-end.

  • The Enhancer forms database can be modified to use the Generic FCE to call other (bespoke) web pages etc.

  • It is extremely simple to use, if you can use the sage forms designer, then you can use this.

  • It requires both SQL Server and IIS to be running somewhere on your network, even when used with the GUI client.


Tip using the tools together

Tip: Using the tools together

  • You can use these tools together to create 2-way interfaces.

    • Use enhancer to create a form and FCE and modify the enhancer db tables to call an Iron Speed application which holds info in a temporary table, then calls the API to send it back once changed.

    • As above, but instead of using Enhancer / Iron Speed to create a web application, use add-in express to create a bespoke excel add-in which pulls information from ODBC / OLEDB, then holds said info in an excel spreadsheet, then calls the API to send it back once changed.


Best practices tiered integration

Best Practices : Tiered Integration

  • If doing EDI, EAI or any kind of B2B / B2C online activity, it’s a really good idea (but not mandatory) to use some kind of middleware product to handle at least the data mapping and translation tasks.

  • This is not necessary when integrating vertical market apps internally within your own organisation.

  • Example Products :-

    • Cindercone Magma

    • Microsoft BizTalk

    • Gentran Integration Suite

  • OR

    • Bespoke XML Web service


Product tips and best practices middleware cindercone magma

Product Tips and Best PracticesMiddleware : Cindercone Magma

  • Magma is an integration gateway, consisting essentially of an xml mapper (via xslt) and an http server, and a management console.

  • Very good at mapping both xml and non-xml data and moving it from a to b.

  • It requires either xslt knowledge or consultancy to configure, and can be tricky to troubleshoot.

  • In our experience, it is not great in high

    transaction-volume or real-time environments

    such as B2C commerce (Although it does work)

  • But it is perfect for batch process style jobs

    such as EDI.

  • Works with virtually all message transport

    methods you could choose to use :-

    • http, https, ftp, sftp, cifs, as/2, smtp

  • Works with the Fusion API’s or Sage EDI

    Modules as a transport method into Sage.


Product tips and best practices middleware bespoke web service

Product Tips and Best PracticesMiddleware : Bespoke Web Service

  • SIGNature is our bespoke integration gateway, that we use for B2C e-commerce

  • Based upon .NET Web Services technology and uses an open-source embedded database (sqlite.net) , so is SQL agnostic and not tied to your Sage platform DB version.

  • It is designed to be easy to administer, fast, and to be flexible in it’s messaging, which is based upon html templates (Uses wkhtmltopdf to render pdf documents from an html page).

  • Not a hint of xslt in sight,

    but does require a ASP.NET (VB)

    programmer for changes

  • Receives xml and uses .net

    Plugins for transmission into

    sage. (One of which is a .net

    wrapper around the Datel

    API’s)


In summary

In Summary

  • These are the approaches that we usually take in-house

    • Extending and adapting Sage: FUSION Enhancer

    • Extending and adapting Excel: Add-in Express with Datel APIs

    • Linking web-applications to Sage data: Iron Speed Designer

    • B2B and EDI Integration

      • Currently: Cindercone Magma with Sage EDI Modules

      • In Progress: Cindercone Magma with Datel APIs

    • B2C Integration: Bespoke Gateway with Datel APIs


Thank you for listening

Thank you for listening

  • Any Questions ?


  • Login