May 29, 2017

Using MS Excel to Manage Infor XA, it’s Possible!

By Randy Kenney, Senior Consultant and Product Manager at Guide Technologies

Green screens, Power-Link, and Net-Link are all interfaces that have been around for many years as the primary ways to manage your Infor XA data.  Everything from order entry, inventory management, purchase orders, financials, and more.  You can use the interface that best suits your needs and fits the way you like to work.  But, like many people, I have grown up with MS Office and in particular I use MS Excel anytime I really want to do number crunching, analysis, graphs, and reporting.  I fell in love with spreadsheets in my first “real” job back in the early 80s using VisiCalc.  It was the first real spreadsheet created by Dan Bricklin in the basement of a building in Cambridge, Mass while working on his Harvard MBA.  Here is a link to learn more about its history for those interested: http://www.bricklin.com/visicalc.htm.  I was fascinated by what could be created using formulas and the basic programming tools that could interact and manipulate those “cells”, those rows and columns.  It seemed like only my imagination would limit what could be created.

Well, we’ve come a long, long way since those days and the power of spreadsheets that are available today is astounding!  The ability to integrate with full-blown relational databases (SQL Server, Oracle, DB400, etc) and to tie multiple worksheets together allows for unprecedented data analysis and reporting.  To go from detail to summary, using graphs and pivot tables to slice and dice data is extremely powerful in the latest versions of today’s spreadsheet programs.  I personally like MS Excel because of what you can do when you add in programming with .Net or the built-in VBA language.

At Guide Technologies, in working with our customers, we’ve developed some unique products that allow you to use Excel as your primary interface to manage your Infor XA data!  They give you some very powerful capability far above what you can do with any of the other interfaces.  And, all XA security rules are enforced as System-Link is used “under the covers” to perform all the data extraction and updates after the data has been manipulated in Excel.  As an example, everyone updates base prices in XA for yearly price increases or product introductions, etc.  Many customers would use the offline load files to update the pricing in XA after using some external tool (like Excel) to set the new pricing.

Well, in R9 as you may know, Infor took away the base price offline load programs.  And they’ve promised that with future PTFs and PCMs others will disappear.  They will all be gone by R10 as stated by Infor.  Fortunately there is an answer, and quite honestly a far better answer than using offline loads.  System-Link allows you to interact with and manipulate the XA database from any external program that supports XML.  And who doesn’t support XML these days?  Certainly Excel does, and we have written Excel “plug-ins” to allow you to work 100% in Excel to update base prices (as an example).

You define your iSeries XA login credentials within our Excel plug-in, then literally press a button and the XA pricing data is downloaded to Excel (if you have the security to do so).  You can also download all foreign currency data simultaneously for each of the items if applicable to your business.  You can then slice, dice, sort, filter, and otherwise manipulate the data in any manner you wish using standard Excel functionality.  When finished, you press the update button on the plug-in toolbar and voila….each of the items you updated, and only the items you updated, are sent back through System-Link to update XA.  Any errors are sent back from System-Link and shown to you next to item in error within Excel.  You can then fix the errors and resubmit only those items back to XA.  And all maintenance history in XA is linked to the logged in username as defined in Excel.

This could be done with any type of data you want to manage within Excel…for example BOMs, item transactions, customer orders, purchase orders…you name it, only your imagination can limit what can be done!  All without ever leaving the cozy, familiar confines of Excel…it really is very slick!