Stephen Smith's Blog

Musings on Machine Learning…

Archive for August 2012

Export

with 7 comments

Introduction

I’ve just returned recently from our Sage Summit 2012 conference where we showcase all the wonderful new features and technologies we are just releasing in new versions or have under development. However in giving various sessions, it was clear that besides evangelizing all the new work going on, that quite a few people aren’t aware of all the functionality that has been in the product all along. So as we develop many new reporting and inquiry tools, I thought it was a good idea to go back and talk about our good old import/export technology. In this article I’ll be concentrating on how to get data out of Sage 300 ERP using it’s built in technology.

You can run Export from pretty well any data entry screen in Sage 300 ERP. You just open the File menu and choose “Export…”. At which point you get a screen like that below:

Note that there is also a File – Export… menu on every Finder in the system.

Formats

The first question you need to answer is what format to export to. Below you can see the various export formats that we support:

Generally you are exporting the data because you know where you want it, usually in another program, so you can choose the format that works best for you. The two most popular formats are “Excel” and “Single File CSV”. Many people know Excel and like to use this as their format. Excel is a great tool for doing data analysis on ERP data with easy ways to generate graphs, pivot tables, etc.

The reason people use “Single CSV File” is that it is easy to deal with from scripting tools and other programs. These are just text files where the values are separated by commas (hence the name). Generally it’s easy to programmatically open these files and parse them. Another benefit of CSV is that it’s such a simple format, that it exports extremely quickly, so for large files this can be a real benefit. Also Excel has no problem reading CSV files.

If you are writing your own programs to process these files, consider XML as most languages, these days, have excellent libraries for parsing and processing XML files.

Another advantage of CSV and XML type files is that Excel has limits on the number of columns and rows that are allowed. This varies by version of Excel. However the CSV and XML file formats don’t have any limitations on them (you just may or may not be able to load them into Excel).

Select Fields/Tables

After you’ve selected the format and the filename to export to, next you check and uncheck all the boxes to select the various fields that you want. This includes the fields in the main table along with any detail tables. Many documents in Sage 300 consist of header records with a number of detail tables so for instance each Order header record has multiple detail records for all the items that make up the order.

A lot of times people just stick to the default of all fields selected, since it doesn’t make a lot of difference whether the extra fields are there or not.

Note that you can right click on the table name to select or un-select all the fields in that table (a bit counter-intuitive since clicking on the table check box doesn’t do anything). You can also rename the table if you want it to appear in the export file with a different name.

Set Criteria

Suppose you export all your customers to Excel, but most of it is taken up by inactive customers that you don’t care about. You could filter these in Excel or you could filter these from Export so they aren’t there getting in the way to start with. Export has a handy “Set Criteria…” dialog where you can define a filter to select the records being exported.

This dialog is an example of “Query by Example” (QBE). You basically specify the field names in the title area of the grid. Then it “and”s items going horizontally and “or”s items vertically. So in the dialog above if I added another item to the right then both would need to be true for the record to be exported. If I added another value below then it would export if the value was either of these. Using this table you can build up fairly sophisticated criteria. If you think better in SQL where clauses, you can hit the “Show Filter” button to see the where clause that you a building.

Load/Save Script

Selecting all those fields and setting the criteria can be a bit of work. Especially if you need to run the same export every day. The solution is to use the “Save Script…” button to save what you’ve done, and then when you return you can use “Load Script…” to get it back. This is the first step in automating the export process.

Exporting From Macros

If you want to automate things further you can drive export from a macro. You can do this with no user intervention whatsoever and have these run on regular basis. Basically you setup all the options you want and save it in a script file. Then from the macro you can execute this script file. Besides the code for this macro, you need to add two references: “Accpac Import/Export 1.0” and “Microsoft XML, v4.0”. The return value from the export operation is an XML file that contains all the details of what happened. I included a bit of code in the macro to parse that XML file to display the number of records exported.

Dim xmlstr, msg As String
 Dim pbstr As String
 Dim doc As New MSXML2.DOMDocument40
On Error GoTo ACCPACErrorHandler ' Set error handler
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
        Set ie = New ImportExport
        If (Not (ie Is Nothing)) Then
             Dim pStatus As AccpacCOMAPI.tagEventStatus           
            With ie
                ' Open the import/export engine.
                 .Open mDBLinkCmpRW               
                .ExecuteExportScript "c:\temp\custscript.XML", breturn               
                .GetExecuteResult pbstr
                 doc.loadXML (pbstr)
                 doc.setProperty "SelectionLanguage", "XPath"
                 xmlstr = "VIEW[@ID='AR0024']"
                 msg = doc.documentElement.selectSingleNode(xmlstr).Attributes.getNamedItem("Exported").nodeValue
                 MsgBox msg & " record(s) Exported"
                 .Close
             End With  ' ie
        End If
'Cleanup
 Set mDBLinkCmpRW = Nothing
Exit Sub
ACCPACErrorHandler:
   Dim lCount As Long
   Dim lIndex As Long
   If Errors Is Nothing Then
       MsgBox Err.Description
   Else
       lCount = Errors.Count
       If lCount = 0 Then
           MsgBox Err.Description
       Else
           For lIndex = 0 To lCount – 1
               MsgBox Errors.Item(lIndex)
           Next
           Errors.Clear
       End If
       Resume Next
   End If
 End Sub

 

Export from Crystal

Crystal Reports also supports export in quite a few formats. Generally you are choosing if you want to export the formatting or the data or a bit of both. If you want good formatting generally you would export in PDF format (or perhaps one of the Word formats). If you want only the data then export using one of the Excel data-only formats (the ones that don’t say “Data-Only” tend to try to format in Excel which sometimes makes the worksheet hard to work with).

Summary

The built in Export functionality in Sage 300 ERP has been there since version 1.0A but often gets forgotten amid many of the new features. It’s a fairly powerful tool and can solve quite a few data sharing and analysis problems.

Written by smist08

August 25, 2012 at 6:17 pm

Sage Summit 2012

with 7 comments

Introduction

Sage Summit is Sage’s North American conference which was held this year in Nashville, Tennessee at the Gaylord Opryland Resort and Convention Center.  Pascal Houillon the CEO for Sage North America gave the opening keynote along with Himanshu Palsule, CTO. They outlined how the world is changing with the proliferation of mobile devices and how these are changing our lives.  During the keynote, a video was played that showed how all the various mobile connected services Sage is developing could affect a businessperson in their daily life. The conference ran from August 12 to 17 with the first half being for partners and then with the customers joining on Tuesday. There were many announcements, demos, town halls, tutorials, labs and presentations. This blog posting looks at a few of the items that I felt were most significant (at least to me).

Sage City

The customer half of the conference kicked off with a new idea called “Sage City”. This was held in a giant meeting room with a circular stage at the center surrounded by seating for all attendees. Then around this were a number of “villages” that were dedicated to individual industry areas like manufacturing, distribution and accounting. After an initial keynote and introduction, everyone moved to a “village” of their choosing and within the village joined a focus group of 7 or 8 people to discuss common problems and to share and brainstorm solutions to these. Then all these topics and outcomes were written up and posted around the outside of the room. There were two sessions of this separated by a break where drinks and food were brought in. This was a very interesting and innovative networking session and hopefully many good ideas resulted.

Mobile Connected Services

One of the major announcements at the conference was the progress being made on Sage’s mobile connected services initiative. Several shipping mobile connected services were demonstrated along with a number that are currently in development. Below are a couple of screen shots from the Sales Management application that was show during both the partner and customer keynote speeches. This is a native iPad application that communicates back to a cloud service using SData. Note that the applications that I have screen shots here for are in the “experience testing” stage where they are getting a large amount of customer feedback including at the UCD lab at the conference, then after all this testing, a more final form of the product will be specified, so expect these to look quite different when they ship, since they will include all this feedback.

There was also a Service Billing application demonstrated that is written using the Argos SDK and which runs on all smart phones. This application was demonstrated integrated to Sage 50, 100 and 300 ERPs showing how the services charges entered in the mobile application make it back into the ERP as A/R transactions.

Below is a picture of the high level architecture that is being used to develop these applications:

I’ll be going into a lot more detail on what this all means and how it is put together in future blog posts, to explain how we will integrate to on-premise products, how we develop these applications in the Azure cloud and how ISVs can integrate into this platform.

Sage 300 ERP 20120 Release

A very highly featured product at the show is the forthcoming Sage 300 ERP 2012 release. This is coming in September, so there were many sessions highlighting all the new features it contains and several demo stations in the trade show where people can have a look at it. Beta 2 is currently shipping which gives a pretty good look at what this product looks like. If also blogged quite a bit on everything going into the release which is summarized here. Below shows the Sage 300 ERP Desktop driven by the Purchase Order Visual Process Flow rather than the usual tree of icons.

Summary

This was a very quick overview of some of the goings on at Sage Summit. I’m sure these themes along with others will be intertwined in all my blog postings over the coming year. Looking forward to the next Sage Summit 2013 at the Gaylord National Resort and Conference Center in Washington DC.

Developing Windows 8 Style UIs

with 5 comments

Introduction

Microsoft has release Windows 8 to manufacturing with a whole new User Interface technology. Up until a few days ago, this was called Metro, but now Microsoft just dropped that name in favor of “Windows 8 Style UIs”. A bit of a strange name, but full product names rarely just roll off the tongue.

I’ve spent a little time playing with developing “Windows 8 Style UIs” and thought I’d spend this blog post covering some of my experiences. Let’s just call them W8SUs for the rest of this post.

Closed Development System

One of the main goals for this new UI development system is to copy Apple’s success with iOS development and the iTunes store. In the Apple world, you can only develop native iPad and iPhone apps using the Apple SDK on a Mac computer. Further you can only distribute your applications by posting them on the Apple iTunes App store, passing a certification process and in the process allowing Apple to take 30% of the revenue. This has been making Apple billions of dollars and Microsoft would like to emulate that.

You can only develop W8SUs in Visual Studio. VS2012 generates a bunch of proprietary cryptographic code signing info must be there to run. Further you must be signed on with a Windows Live developer account. Another gotcha is that you can only develop for these on Windows 8 (or Windows Server 2012). If you install Visual Studio 2012 on a Windows 7 computer, it won’t install any Windows 8 development components there.

Once you do all this, you can’t just compile your application, zip it up and give it to a friend to run. Like Apple, W8SUs can only be installed via the Microsoft Store. There is an enterprise distribution system to install apps developed for an enterprise across an enterprise, but this again is tightly controlled. Even if you install on another computer via your developer license, it will be time bombed to only work for 1 month.

This is all very new to Windows developers. I’m not entirely sure how it will be received. Apple is successful because of all the revenue their store generates. However most of these are low cost consumer applications. Not sure how this will play out in the enterprise market.

Visual Studio 2012

You can develop these UIs in either JavaScript/HTML or C#/XAML. I chose JavaScript/HTML since that is what I already know. You can use either VS 2010 or 2012, I figured, I may as well go with the newest even though it’s a release preview. Actually VS 2012 worked pretty well. Debugging these applications is fairly easy and the tools are pretty good. Since JavaScript is object oriented more by convention than an enforced part of the language, intellisense has to guess what is valid, and although not always correct, it still does a pretty good job. The only place I found it difficult was when you get an exception as part of an event, and then it can be pretty tricky to find the true culprit, since it usually isn’t part of the call stack.

VS 2012 comes with a set of template to give you a start for your W8SUs. These templates give you a working program with some faked in data. When developing for W8SU in JavaScript/HTML, you need to interact with a number of core operating system components which are either built into the environment by some automatically included references or via some proprietary UI controls. For instance the scrolling ListView that is the hallmark of the opening Start Page is a proprietary control that includes all the standard Win8 interactions. When you are programming in JS, the core of the program consists of handling some proprietary events for the program execution state and call the API to invoke the data binding functions. Once you get away from this you can program individual pages of your application pretty much as standard web apps using standard Web libraries like JQuery or HighChart. Then you string together the page navigation using some proprietary APIs.

So you are doing full real web development with JavaScript/JQuery/HTML/CSS, but you are producing an application that will only run on Windows 8 installed from the Microsoft store. A bit of a strange concept for Web Developers, where the promise was to write once and run anywhere. I think you can structure your program to keep most of it re-usable to generate a Web app version using a separate page navigation system and some sort of alternative to the ListView control.

JavaScript Restrictions

When running under W8SU, you are essentially running under a modified version of IE 10. However there are a number of annoying restrictions compared to running IE 10 regularly. In previous versions of IE, many standard web functions, like parsing XML, were handled with ActiveX controls. Now IE can do many of these things in the standard web way, so it’s better to not use the ActiveX way. So if you try to use an older library that detects you are running under IE and tries to use one of these, then you get a very severe security exception. In general you can’t use any Add-ons or ActiveX controls, included those that used to be built into IE and Windows. I found a work around is to fool libraries to think they are running under Firefox rather than IE and that often gets around the problem.

Plus W8SU removes some features of standard JavaScript that it thinks are “dangerous” for some reason. For instance you can’t use the JavaScript alert and prompt statements. These are banned. This is annoying because again, many libraries will use these for unexpected errors and instead of seeing the error; you get a horrible security exception.

Another annoying thing is that the screen isn’t entirely standard like a standard web page. The page will not scroll, so if your content goes off the side, then it is just truncated, scroll bars are never added to the whole page. If you want scrolling then you need to put your content in a ListView or some other control which then causes other complexities and problems. I’m hoping this is really a bug that gets corrected by the real release.

Some of the controls also seem a bit buggy, which hopefully will be corrected by release. For instance if you put a ListView inside a ListView control, it gets quite confused. Also if you put a proprietary date picker in a ListView control then it ends up read-only.

Since these are based on IE, they use IE’s caching mechanisms. Currently there is no way to clear these caches from the system. The only way is to know the secret folders it uses and to go in and manually delete these. If you clear the cache in IE 10, it has no effect on W8SU programs. This is mostly annoying when doing application development, since re-running the program won’t re-download new static content from your web site. Again hopefully this is fixed by release.

SData

Using SData from a W8SU is really quite easy. There is an API called “WinJS.xhr” which makes asynchronous RESTful web service calls.

    Promise = WinJS.xhr({
       type: "POST",
       url: sdataurl,
       user: "USERID",
       password: "PASSWORD",
       data: atomdata
    });

It has the exact parameters you need for making SData calls. It returns a promise which is W8SU’s way of notifying you when an asynchronous request returns, basically you can set functions to be called if the call succeeds, fails or to get progress. You can also join promises together, so you can make a bunch of asynchronous calls and then wait for them all to finish.

Summary

I think Window’s 8 Style UIs have a lot of potential. I worry they are being rushed to market too quickly and aren’t quite ready for prime time. I also worry that the touch focus is going to turn everyone with a standard laptop or desktop off Windows 8 entirely. Hopefully the technology gets a chance to evolve and that new devices beyond the Surface tablet hit the scene to give it a really good user experience.

Developing for Mobile Devices

with one comment

Introduction

Having just posted a couple of articles on the Argos Mobile SDK here and here; and with the news that Windows 8 has just been released to manufacturing; I thought it might be a good time to reflect a bit on mobile devices and how to develop for them.

By mobile devices we tend to mean smart phones and tablets and not laptop or desktop computers. Certainly there is a lot of blurring around the edges of these categories. Phones becoming as big as tablets, laptops with detachable keyboards and touch screens, etc. There are all sorts of charts showing the growth of mobile devices such as this one from Business Insider:

Today you see iPads, iPhones, and Android devices everywhere. There is a huge market already and from these growth trends you can only see the market demand accelerating in the future. We are already at the point where many workers perform all their computing tasks through a tablet or a phone and may not have access to a desktop or laptop computer at all.

How to develop for mobile devices is a very hot topic around the web. There is a lot of debate around whether to develop using the native SDK’s from the device manufacturers, using a third party toolset that targets multiple devices or writing a web application that runs on anything. What are the pros and cons of all these approaches? What are the tradeoffs you are making when deciding between these?

Device Experience

Apple has done a great job creating the iPhone and iPad and giving them a great user experience. Anyone writing apps that run on these devices want to make their apps as great as any app from Apple when run on one of these. The same goes for creating Android apps or for creating Windows 8 Metro apps. So what are some of the things that you want in your application to fit in naturally into these environments?

  • Follow the look and feel guidelines for the platform. Look and behave like any of the applications that the manufacturer provides. Honor all the touch gestures on the device, have great professional graphics and layout at the right resolution.
  • Integrate with all the build-in hardware on the device. For instance able to access contacts, dial the phone, use the GPS, read the accelerometer, take photos, record sound, receive input via voice, film movies or any other neat hardware feature where ever they make sense.
  • Integrate with the native operating system and utilize all its features. For instance on Windows 8 Metro, support the charms, command bar and integrated application search.
  • Have great performance. Feel just as snappy as any other app on the platform. Don’t hog bandwidth; remember bandwidth can cost money.

Device Differentiators

What distinguishes all these devices? What makes them different? What do you need to support for the best experience?

  • Screen size, we have all sorts of screen sizes from small but high resolution (iPhones with retina displays) to large with low resolution (cheap large screen desktop monitors). Being adaptable is quite a challenge.
  • Input methods. Devices support touch, voice, keyboard, mouse, pen, QR codes, NFC, etc. Supporting all these can be quite a challenge.
  • Different hardware devices. How multi-touch is the device, does it have GPS, does it have a thermometer, is there a sim card, etc.
  • Operating system version. How up to date are most people? Which version do you want to support?
  • Different processor power, battery life and memory.

Using a Web App as a Device App

With all these consideration, is it possible to have a web application pose as a native app? That is what we have been doing with the Argos SDK. The nice thing about Web applications is that they run pretty much anywhere; all the mobile devices have really good browser support. Web applications are also good at adapting to different screen sizes; HTML has always been doing this. The device manufacturers have been good about adding input events to the JavaScript programming model, so you do get notified of touch gestures in your JavaScript application.

However device support is limited. It slowly makes its way into JavaScript, but generally web apps tend to trail native applications in hardware and operating system support. Another key problem is that you can’t post a URL to the app stores like iTunes, these only take native applications.

Enter systems like PhoneGap. These take a web app and wrap it in a native application. In addition to creating a native wrapped app that you can post to the app store, it also adds a lot of hardware abstraction, so you can access things like the accelerometer, but in a way that PhoneGap will make work on all devices.

The Argos SDK is fully PhoneGap compatible, so you can create mobile applications with Argos, compile them with PhoneGap and then deploy them through an app store.

Windows 8

I know Microsoft just dropped the use of the “Metro” name for its native tablet apps, but without a replacement I’m going to just keep calling it Metro. Metro is a subsystem of Windows 8 that allows you to build tablet type apps. Similar to iPhone apps, these can only be distributed via the Microsoft Store (or via a very arcane Enterprise distribution system). The intent of these is to define a new modern UX model for Windows applications. These programs run by themselves and can’t be displayed as Windows in the regular Windows desktop. There are two ways to develop these via Visual Studio 2012, either as C#/XAML apps or as JavaScript/HTML applications. PhoneGap doesn’t have Windows 8 support yet, but I would expect to see it down the road.

If you develop a Metro app in JavaScript/HTML in VS 2012, don’t expect it to run anywhere except in the Metro environment. This is standard JS, but the core of the program structure is proprietary and you have to use a number of proprietary native controls to get proper Windows 8 functionality. All that being said, you can leverage a large number of standard JavaScript libraries such as JQuery or HighChart. You can also structure your program to isolate the proprietary parts to keep as much reusable as possible.

To use operating system you need to be a native application and you have to use proprietary controls to access things like the integrated search and the charms. You can probably simulate the command bar via other means.

If you buy a Windows 8 ARM Processor based device, then it only runs Metro or Web apps, it will not run any other type of application. So if you want to participate in this world then you do need to develop for Metro (or rely on a web app). It will be interesting to see if sales of ARM based Windows 8 devices takes off. Microsoft is releasing their Surface tablet in both ARM and Intel processor based versions.

Right now there is a lot of impedance between current laptops/desktops and Windows 8. Metro is really designed for the next generation of hardware and doesn’t work at all well with current hardware. Perhaps it’s a mistake making it compatible with old hardware since it yields a bad experience, but Microsoft’s hope is that as new hardware comes to market, then the Metro experience will greatly improve.

Summary

It seems that applications need the experience of native applications, but want to leverage the portability of Web apps. After all what developer wants to create 4 or 5 different versions of their program? This is leading to new categories of hybrid applications that have large parts written as web apps, but then merged with parts that are created natively to directly interact with operating system and device features. It certainly leads to programmers needing quite a plethora of skills starting with JavaScript/HTML/CSS for Web Apps and Metro Apps, Objective C for iOS and Java for Android.