In Development - Sage Accpac R&D Team Blog

User Assistance: Thinking Outside the Manual

Curtis Foreman — Mon, 26 Jul 2010 21:03:00 GMT

Last month, Stephen Smith described some of the changes we've made to user assistance in the Sage ERP Accpac 6.0 Portal, including integrating content directly into the new interface and delivering articles in a new web-based Learning Center.

Since then, our team has continued making changes and improvements to user assistance for the upcoming release. We're also planning to introduce a number of exciting new features in future releases that will make it easier than ever to learn and use Sage ERP Accpac -- and as those plans develop, we're looking to our business partners and customers to help us determine exactly what those new features will look like.

As a member of the Sage ERP Accpac User Assistance team, part of my job is to create product documentation -- but there's more to it than writing gudes and manuals. Working with other groups here in Richmond, B.C. and throughout Sage, my team creates pretty much all the content you see in the software, from field labels and button text to in-product tips and (sorry!) error messages.

If you read it while you're using Sage ERP Accpac, my team probably wrote it. And if it isn't clear, correct, and useful, we're not doing our job as well as we could be.

As we continue working toward transforming Sage ERP Accpac from a Windows desktop application to a web-based one, we're doing our best to take advantage of the many new and exciting possibilities that open up when you deliver content in a web browser. In the portal, you'll see a number of new features we've added with the goal of delivering the content you need to get your work done while minimizing interruptions and helping you stay focused on the task at hand. These features include:

Quick video overviews of new features
Tips, descriptions, and formulas that appear in pop-up windows when you mouse over program elements
Embedded links to Learning Center articles and Sage web resources
A Getting Started snapshot on the home page with links to key information, resources, and video content
A Help menu that lets you quickly access key resources anytime while you're working in the portal, including Learning Center articles, Sage University courses, community resources, and our Ideas site

As Sage ERP Accpac 6.0 nears completion, we're happy to have successfully added this many new user assistance features. Overhauling an enterprise resource planning application is a long journey, and Sage ERP Accpac 6.0 is a great first step on that journey.

We've based our decisions about how to deliver user assistance on many factors: industry best practices, customer and partner surveys, usability testing, and competitive analysis, plus some plain old-fashioned good ideas and trial-and-error testing. But there are lots of decisions to make as we move forward, and we're hoping you'll help us determine what happens next.

Of course, we'll continue to deliver the print documentation our partners and customers have come to expect, such as update notices, system administrator's guides, integration guides, and so on -- and we'll continue to deliver these in future releases as long as our customers find them useful. But we'll also focus on exploring ways we can take the content that's buried deep in those guides and deliver it "just in time" while you're using the software, so you can find the information and answers you need fast and get back to work without stopping to thumb through a manual or scroll through a long PDF file.

Here are three possibilities we're exploring as we look for effective ways to "think outside the manual":

1. In-Product Content

We'll be refining the design of many Sage ERP Accpac data entry screens for future releases, so we're researching ways to deliver more content in the interface as you need it. There's a trade-off here between adding value and cluttering up a clean interface, so we want to make sure we do this right.

There are a number of ways to deliver this type of content. For example, in SageCRM, our customer relationship management software, relevant "coaching text" appears in an expandable panel at the top of the screen:

Another approach is to add "hover help" that appears when you mouse over a "?" icon. This type of help usually serves up one or two sentences of content, and sometimes includes a link to more resources. Here's an example from Google AdSense:

This type of user assistance can be an effective in-product learning tool, but nobody wants to see a screen littered with dozens of question marks or a huge panel full of text written for beginners, so thoughtful and strategic implementation is critical. (One way to minimize the clutter is by allowing users to turn this type of assistance on and off as required.)

We'll be consulting with users, testing a few approaches, and refining what works to make sure we deliver content that's valuable without interrupting workflow.

2. Integrated Search

As we add articles to the Sage ERP Accpac Learning Center, we're also looking for ways to make it easier to move seamlessly between the software interface and the content in the Learning Center. We've already incorporated context-sensitive help links (such as the "?" icon in the menu bar of each snapshot on the home page of the portal), and our next step is to take a closer look at search.

In the Getting Started snapshot, you'll find the first iteration of integrated search in the form of a link you can click to search the Learning Center:

And here's what the search results look like after you type a search term:

This is a good start, but it still takes you out of the software as soon as you decide to search for content. We're looking at ways to integrate the search box, and ideally the results, directly into the interface so you can simply type your term and find what you need fast.

A good example of this type of integrated search is the the panel that appears in many Microsoft products, often along with additional links and other content. The panel appears at the right of the screen, and can be switched on and off as required. Here's what it looks like in MS Word:

Search results appear directly in the panel:

Only if you click a result are you taken out of the software. This gives you a way to see what resources are available and decide whether you'd like to explore them -- all before you navigate away from the task at hand.

3. Video

We're excited to be working with Sage's Learning Services group to create videos that introduce key new features of the portal. You'll be able to browse and launch these videos by clicking a link in the Getting Started snapshot, and they'll include overviews and quick tutorials to help you learn the new features and get started using them.

As we continue adding new features and refining existing ones, we'll be looking at how to make video content easier to access directly from the software.

So what do you think? Are in-product content, integrated search, and video effective formats for delivering content, or just more bells and whistles? Would you use them?

Accpac and Business Continuity

smist08 — Sat, 24 Jul 2010 22:44:00 GMT

Introduction

I remember long ago when hard-disks in PCs were new, that I never trusted what I saved until I had it backed up to some floppy disks. These days, a lot of people don’t seem to worry about the hard drive on their PC failing. In fact I think more people back up because they are worried they will get a virus or Trojan and have to re-install their computer from scratch. But even with better reliability hard drives can fail and even with virus scanning software, you can still get infected.

It used to be that you did a backup every night and if something failed, you said oh well and restored from backup. Often you would lose the day’s work when things failed, and then get behind another day (if you were lucky) while the system was restored. Generally you backed up to tape, perhaps doing a full backup on the weekend and incremental backups on weekdays; but, you could backup your entire hard drive to one tape. Tapes were never that reliable and restoring a backup was always a nail-biting experience. People started using CDs and later DVDs to backup to as these were more reliable, longer lasting and far easier to restore from (especially individual files).

However hard disk technology fast outpaced all these backup technologies. You can now get 2 tera-byte hard drives for around $100. This is 500 4Gig DVDs. So as it stands, really, the only way to backup a large hard drive is to another large hard drive. Along these lines there are many solutions from various RAID and disk mirroring solutions to replicated database servers.

Further today’s businesses can’t afford to have their computer systems down for any length of time. Businesses are now much reliant on their computerized systems and can’t run manually for a few days while the automated system is fixed. Today’s businesses require their systems are available and working properly 24x7x365. Most businesses now develop a formal Business Continuity Plan (http://en.wikipedia.org/wiki/Business_continuity_planning). This is a formal plan that documents how the business will recover from all sorts of common and uncommon disasters.

All these backup and redundant systems cost money, so there is a business decision to make. What does it cost you, when your systems are down? Do you run with many disparate data centers or one centralized one? What are the environmental risks to your data centers (earthquakes, hurricanes, tornadoes, etc.)? Backup generators can be quite expensive and require quite a bit of maintenance. Some redundant systems impose a performance penalty while you are running normally; is it worth living with this penalty compared to how often you need the redundancy?

Hardware Solutions

At the hardware level, first there are all sorts of redundant drive solutions. There are lots of ways to mirror hard drives so if one fails, the other will be used. These can either be through multiple drives in a server made redundant through RAID (http://en.wikipedia.org/wiki/RAID) or via various NAS (http://en.wikipedia.org/wiki/Network-attached_storage) and SAN (http://en.wikipedia.org/wiki/Storage_Area_Network) solutions. The thing to remember here is that although the disk drives are redundant, the enclosure holding the drives isn’t. This means if something in the enclosure fails, then the drives are inaccessible until they are placed in a new enclosure (server, NAS, etc.). There is also the problem that being in the same box, they could all be damaged by a flood (bathroom above overflows), a power surge (ESCOM), a fire, or some other local event.

Most companies have uninterruptable power supplies (http://en.wikipedia.org/wiki/Uninterruptible_power_supply) for individual computers, the data center or the whole building. Most servers, these days, have dual redundant power supplies (http://en.wikipedia.org/wiki/Blade_server), this is since the power supply can be damaged by power surges, or generally power supplies seem to fail as often as disk drives. Redundant fans help, along with temperature monitoring software. Check your fans to ensure they are clean and working. Many BIOS’s will do a diagnostic on the fans at startup and let you know if one has failed.

Most companies like to have their data in multiple geographic locations. At a minimum these means off-site backups where backups are regularly moved to and stored at a separate location. This means if your local data center is damaged, once you get your hardware up and running again, then you can restore your backups and continue business. For many businesses this isn’t sufficient. They can’t be down while the data center is restored. Generally businesses would like a hot standby at an alternate location that can be switched to take over, if not automatically then very quickly.

Database Features

Most modern databases including SQL Server provide quite a bit of functionality to help solve these problems. This can include mirroring or replicating databases to multiple locations to being able to backup the database server without shutting it down. There are quite an array of options and these are to allow tradeoffs to be made between redundancy and performance. For instance if you completely mirror a database to a different geographic region, then a user will need to wait for a transaction to be committed to both databases before proceeding, this can be slow since the link to the remote connection will be slower than the link to a local server. So the database vendors introduced ideas like lazy replication (http://en.wikipedia.org/wiki/Optimistic_replication). In this case the user proceeds as soon as the transaction is saved on the primary server and then this server lazily performs the updates to the backup server. This then means that if the primary server fails, whatever is in the lazy queue at the time will be lost. However the tradeoff is that hopefully the gained productivity while the primary server is working out weighs the few lost transaction in the rare instances when it fails.

Accpac

Everything that has been discussed so far can be used by Accpac, but doesn’t really have much to do with Accpac. These are all database and server features and not Accpac features. But Accpac is a very flexible product that allows many configurations. So let’s spend a little time discussing Accpac configurations that fit nicely into these scenarios. Remember that to get up and running again, you need to have working Accpac programs, registry settings, site configuration database as well as the company database. You want to ensure that none of these is a single point of failure, that each has a way to either be restored or a way to switch to a hot standby. Generally the program files, registry settings and site configuration don’t change often whereas the company databases are being written to continuously. Ideally you would have hot standbys to switch in when anything fails, but practically this presents problems, like not being able to have two computers on your network with the same name.

If you are using SQL Server and configure Database Setup with the server name, then since this is shared by everyone, you only need to change the server name in one place in Database Setup to get everyone using a new server.

Workstation Setup

In this configuration, the Accpac programs are stored on a central file server and the database server is either this file server or more usually a separate database server. Each workstation is configured to point to this file server and the orgs.ism files on the file server points to the database server. If an individual workstation fails, then that one person can’t do any work until that workstation is replaced. This isn’t so serious because only 1 person is idle rather than the entire Enterprise. Usually the IS department will have a standard “ghost” image that they can restore on a new workstation and get that person going again in a few hours.

The database server contains the most valuable data. It needs to be backed up frequently. Using mirrored drives (RAID 5) or using a mirrored server is a big help.

The file server can fail. Usually you would want this running on mirrored RAID drives. You want a good backup and ghost image, so you can restore things quickly in case of a catastrophic failure. Windows Servers has a feature called DFS (Distributed File System) (http://www.informit.com/articles/article.aspx?p=174367&seqNum=2) (http://en.wikipedia.org/wiki/Distributed_File_System_(Microsoft)); I haven’t tried DFS, but it looks helpful. Another option is to have a restoration contract with your vendor, for instance Dell offers a service where they will restore your server to running in less than 4 hours (quite a long time, but often acceptable).

Terminal Server

In this configuration, all the users access Accpac by logging into a Terminal Server session. All the Accpac programs and configuration files are installed on this Terminal Server and then access a separate Database Server. This configuration has some of the same issues as the previous one, in that the site directory will be only on one server and needs to be made redundant, or easily restored. For terminal server usually you would set up at least two terminal servers, then since you have them have your users split between them (either via load balancing or just assign them). This way you get maximum performance when everything is working, but if one fails you switch those users to the other and away they go. Just make sure that you have enough memory that you can run with all the users on the one server (perhaps a bit slower) for the day or two until you get the second one fixed.

Accpac 6

Once we have migrated to a true web based application, then where ever in this article we mention Terminal Server would become Web Server. Then all the concepts would be the same.

Reporting Server

In either the workstation setup or terminal server case, you probably now have a mirrored SQL Server that isn’t doing anything when things are working well. One thing many customers do is identify users that only do reporting and point them at the replicated server. Then this moves some of the reporting burden from the primary server to give better performance all around.

Conclusion

The company would setup a configuration like that below.

They would have a well documented business continuity plan. All the machines would be ghosted (http://en.wikipedia.org/wiki/Ghost_(software)) so that any failed machine can be restored using new hardware quickly. Redundant components will be used where ever possible, such as using RAID 5 mirrored disks within each unit, in addition to having redundant hardware. The backup terminal server and secondary SQL Server would be located in a different geographic location than the primary equipment. The company would test switching over from one computer to another to ensure the documentation in the business continuity plan is correct and that they can actually do it. They would purchase the equipment from a vendor that can quickly replace any failed component (perhaps with a less than one day service level agreement (SLA)).

International Support in Accpac

smist08 — Sat, 17 Jul 2010 19:53:00 GMT

Introduction

Sage ERP Accpac has installations in hundreds of countries. Accpac has traditionally had strong sales in Canada, the US, the Caribbean, South-East Asia, Africa, the Middle East and Australia/New Zealand. Additionally we have scattered sales in many other regions. How does Accpac address the varied needs of so many legislative jurisdictions, locations and cultures?

Everything in this article applies to both Accpac 5.6A as well as the forthcoming Accpac 6.0A. However as we move to becoming a web based application, we inherit all the good work performed by the WWW standards bodies, which are very good at ensuring the Web is accessible to all. One such standards body is: http://www.w3.org/International/. For instance in http://smist08.wordpress.com/2010/05/28/how-to-layout-forms-in-sage-erp-accpac-6/ I talk about how our new screens will automatically adjust to string lengths as well as how they can automatically lay themselves out in right-to-left format.

Multi-Lingual

First off Accpac is multi-lingual. Accpac supports installing more than one language at a time and languages are associated with users. There is not a French version of Accpac nor a Spanish version; instead you can add French or Spanish as installed languages. Then you can have some users running French, some Spanish and some English. This is especially important for International companies with branches or divisions in several countries. Of course the data in the database only has one version of each string, so if a French user enters say a batch description in French then a Spanish user will see this description in French. But all the forms, reports and messages will be in the correct language for each user. Out of the box we support English, French, Spanish and Chinese (Simplified and Traditional).

Regional Settings

At the next level we have to take care to honor regional settings. These include things like date formats (mm/dd/yy versus dd/mm/yy, etc.), number formatting characters (whether a comma or period is a thousands separator or decimal point), money symbols and such. Generally we just have to be careful to use the values set in the host Browser or Operating System and use them; along with a number of settings we configure in Common Services.

Multi-Currency

Accpac has always had multi-currency ability. Generally you set your company with a “home” currency, and then all incoming/outgoing monetary amounts are in a “source” currency. We always report amounts in source and home currencies. You have to be careful in reports that you add up amounts in the same currency or the totals won’t make any sense. Most reports provide a table of the totals of each source currency and then a grand total in home currency. We support currencies with 0, 1, 2 or 3 decimal places. We also support money amounts with 18 significant digits (15 before the decimal and 3 after). This is more than the 16 digits that double precision floating supports (a method used by many competing Accounting Packages). When converting currencies we support either multiplying or dividing the exchange rates. We maintain centralized tables of all exchange rates, but you can override these at any point.

We also support some additional amounts in third currencies. For instance in Singapore, by law if a company does over 50% of their revenue in a given currency, say USD, then they have to use that currency as their home currency. However they still need to report all sales tax in Singapore Dollars. So maybe they mostly sell to the US, but make a sale to Thailand. So they are paid in Thai Baht, convert the amount to USD for their sales totals and report the sales tax in Singapore Dollars. This is the reason for our “tax reporting currency” feature that allows you to report your sales taxes in a different currency than your home currency.

In version 5.5A we overhauled the currency revaluation process. This was to comply with the new international standard FRS 21. Although this won’t be required in North America for some time, it was required much sooner in other jurisdictions, such as South-East Asia where multi-currency transactions are very prevalent. This new method is quite a bit more complicated than what Accpac was previously using, but this shows our commitment to addressing International concerns and maintaining International standards.

Localization

We don’t produce localized versions of Accpac, there is only one version and we add general support for all jurisdictions there. Rather than shipping an Australian version that supports Australian sales tax and an American version that supports US sales tax, we ship one version with a powerful enough sales tax engine that can support all the sales taxes we have encountered. This then easily allows International companies to use the same Accpac programs and databases for all their transactions.

The reason we added surtaxes to our sales tax module a few versions ago was to support sales taxes in India. In India there are 5 sales taxes and two of them are surtaxes (taxes on tax). So rather than just adding a customization for India we added a general feature, so if any other regions have surtaxes like India, then they are already set. Our hope being that we have a strong enough general support to handle whatever is thrown at us.

Fortunately GAAP (Generally Accepted Accounting Practices) are applied fairly universally. As more countries compete globally and look for global investment, more and more countries are insisting on GAAP. Generally all countries are adopting standards that are agreed on internationally, just that the adoption times can be quite different.

This isn’t to say we wouldn’t produce localized modules. The nice thing about Accpac is that you can add program modules without requiring changes to the base product. Hence we can offer an XBRL Financial Reporting module in Singapore to meet Singapore government reporting requirements. In a similar way Sage regions can develop custom modules for their market, perhaps such as an import/export tax module for India.

Market Differences

A big difference in each geographic market is also the definition of an SMB (Small to Medium Sized Business, http://en.wikipedia.org/wiki/Small_and_medium_enterprises). Our typical sales in Africa and Asia are to much larger companies than we usually sell to in North America. This leads to quite a difference in the feature requests our Product Management team receives from around the world. For instance we might receive requests from Africa for much more sophisticated Inventory processing, whereas from North America they are often looking for product simplification and ease of use.

This is a bigger cause of regional differences than local regulatory requirements. Often the requests we get from one region for one type of industry would be valuable all over the world for that sized company and that particular industry. It’s just that we seem to sell to different industries and different sized companies in different regions.

Summary

Certainly being an International product has its challenges; but, there are lots of rewards as well. I really enjoy traveling to the International Sage Insights and Visions conferences in Australia, Africa and Asia. I find the challenges and accomplishments of the partners in each region have a lot in common. I’m always amazed at the way Accpac is customized and deployed to handle very diverse needs.

What is Localization?

JanD — Thu, 15 Jul 2010 23:21:00 GMT

By Christelle Cornen

Translator, Sage Accpac

Localization - also abbreviated as L10n - is the process of adapting and often translating a product, application or document content to suit the language, cultural and other requirements of a specific market. The goal of localization is to make the material or product transparent to that market and allow the users to interact with the product in their own language in a setting that feels natural to them.

The process of localizing is most frequently done for regions or countries where a different language is spoken and in this case it also involves translation. Sometimes though, localization is also needed for regions or countries where people speak the same language. For example, a product developed for the U.S. market may have to be localized for the Canadian market. This could involve adapting the spelling (e.g. Check versus Cheque), the units of measure or any attribute that is not appropriate for the Canadian market.

When localizing, the localization professional adapts not only the language, but also the graphics, the currencies, the symbols and icons, the keyboard usage, the units of measure, the formats for times dates, addresses and phone numbers, varying legal requirements, and many other details. It may even include a rethinking of the structure, logic, visual design of the product.

After the localization, testing and quality assurance processes are complete, the objective is to have a product that works both linguistically and functionally for the other market, appears to have been developed for that local culture and is appropriate for the market while not changing the original meaning.

The Team Behind Sage ERP Accpac 6.0

kcerny — Tue, 13 Jul 2010 19:53:00 GMT

Sage ERP Accpac is a global product being constructed by a global team. Our team members hail from all corners of the world and have come together to collaborate and work toward a common product vision. Walk the hallways in our Richmond, Canada office and you will hear conversations in multiple languages and see team members leveraging their diverse backgrounds to innovate and share unique perspectives. We believe strongly that a software product ultimately reflects the team that built it and our diversity and world-wise team will show through in our 6.0 release later this year.

Diversity for us does not mean inconsistency though. Our team works hard to ensure we follow standards and we share a common language of usability and quality throughout. We follow Agile development practices but we also have teams focused on architecture, Development and QA standards, design guidelines, documentation standards and consistent processes across teams. We also work across time and space collaborating with remote teammates to help augment our engineering capability.

I am immensely proud of the team behind Sage ERP Accpac. I have been with Sage since November 2009 so my journey here has only just started, but already I have been witness to a positive transformation in process and perspective. Command and control has been replaced with care and commitment and face to face communication has emerged from a long cold email winter. Common tools have also given us shared visibility into progress and risks which has helped us make faster and better decisions.

Ultimately our global team and new processes will benefit our partners and customers with a better product and more collaboration. Our Product Management team regularly channels feedback from our business partners and customers into R&D and our User-Centered Design team reaches out to customers to field test our new product designs.

We are not perfect but we have chosen a culture of continuous improvement. Daily stand-up meetings for individual teams culminate in the entire team getting together once every three weeks to review what we have built, discuss our challenges, and plan improvements in product and process. The intent is to adapt and learn as we go, stay nimble and preserve and enhance what is working and leave behind what is not working. I am excited to see Sage ERP Accpac 6.0 released later this year and I continue to enjoy participating in the positive evolution of our great team.

For a preview of what is coming in Sage ERP Accpac 6.0 here is a link to a recent video overview:

http://www.youtube.com/watch?v=HYUErIxYiPg

Keith Cerny

Director, R&D

Accpac and It’s Databases

smist08 — Sat, 10 Jul 2010 16:26:00 GMT

Introduction

Sage ERP Accpac supports running using either Pervasive.SQL (http://www.pervasivedb.com/Database/Pages/home.aspx), Microsoft SQL Server (http://www.microsoft.com/sqlserver/2008/en/us/) or Oracle (http://www.oracle.com/us/products/database/index.html) Database Servers. This blog posting is going to talk a bit about how Accpac Accounting applications access the database, give some rationale for the way we do things and look at where we may go in the future.

Accpac Plus, the original character based product for first CP/M (http://en.wikipedia.org/wiki/CP/M) and then MS-DOS (http://en.wikipedia.org/wiki/MS-DOS) used a proprietary built-in database. When this product was developed there weren’t standard off the shelf packages to use. This was a simple ISAM based package that was fast and easy to install and configure. The limitations were that:

It didn’t support database transactions so if something failed the database was left corrupted.
It used record locking as its multi-user model, so once too many people were doing things, too many records would be locked and things would grind to a halt.
It didn’t have a server version, so relied on file system locks which were hard to support for all the different network operating system.
It didn’t support SQL and hence didn’t fully support ODBC or any other standard interface mechanism making it hard to use external tools like for reporting.

So when Accpac for Windows was developed, we made the decision to use standard off-the-shelf databases. We decided to base our new database support on transactions and to define a set of requirements that a database would need to support to work with Accpac.

CA-Accpac/2000 originally shipped supporting Btrieve 6.15, and then with version 2.5A we added support for Microsoft SQL Server 6. Later we added support for IBM DB2 and Oracle Databases. We can easily move Accpac data from one database to another via our Database Dump and Load utilities.

Along the way we’ve evolved our API and easily moved from database version to database version. Many third party tools integrate with Accpac through open database interfaces. We currently have a very robust and scalable solution.

Accpac Database API

Accpac is a three tier application with the business logic separated from the database. Our business logic objects (the real core of our Accounting Applications) communicate with the database via a common database API. Then we have database drivers that translate this API into calls to the database server currently being used. How the database driver accomplishes this is entirely up to the driver. Our SQL Server driver uses ODBC to communicate with the SQL Sever Native driver. Oracle uses the Oracle ODBC driver. For Pervasive we communicate with both the relational layer using SQL calls, and with the transactional layer making direct Pervasive API calls. The key point is that all this detail is invisible to the Business Logic. The application programmer creating our business logic objects, just needs to know our Accpac database API to do his job. Then all the differences in the databases are isolated to the Accpac database driver. If a new version of a database server is released and Accpac needs to be updated to support it, then its just a matter of providing a new database driver, without requiring any other application changes.

The Accpac database API includes a number of record oriented API calls, a number of meta-data calls and a number of set based operations. The record based APIs operate on one record at a time, such as read a record based on a supplied key, update that record. The meta-data calls return information about the structure of the database, things like get a list of all the tables in the database, get information on all the fields in a table. The set oriented API calls operate on a set of records at a time. Such as read all records where the description starts with A, or update all records changing any with customer number 1200 to 1400, or delete all records with status “deleted”.

Accpac Architecture Diagram

Reporting

Reporting works a bit differently. We use Crystal Reports as our reporting engine and then Crystal provides the mechanisms to have database independent reports. From Accpac the same Crystal Report is used for Pervasive.SQL, Oracle and SQL Server. We just point Crystal at the correct database and provide the necessary connection information and then let Crystal do the rest. We provide an Accpac reporting API which then handles the details of talking to Crystal. Originally this API was used to talk to CA-RET the Computer Associates Reporting tools, then it was converted to talk to Crystal’s crpe32.dll interface, later to Crystal COM’s interface. With Accpac 6.0A we are upgrading to Crystal Reports 2008 and will use the .Net interface for the VB UIs and the Java interface when running Crystal from the Web. Even though the way we do reporting has changed quite a bit over the years, this has been largely invisible to the applications which just talk to Accpac System Manager’s API and then let System Manager take care of the details.

The Accpac Financial Reporter accesses the database through the Accpac Business Logic and hence the regular Accpac Database API. Accpac Intelligence and Accpac Insight work in a similar manner to Crystal and provide their own database independent mechanisms to access the various databases. Most other reporting solutions also work this way.

Database Requirements

Accpac does have a number of minimum requirements for any database it supports. This includes things like number of fields per table, number of keys supported, record length and such. One key requirement is that the database supports database transactions. As long as a database meets these minimum requirements then it is possible to write a database driver for Accpac to use that database. Note that supporting SQL is not a requirement.

Database Transactions

At the core of Accpac’s database support are database transactions. Any changes made to the database have to be done inside a transaction. A transaction is a group of database operations that are either all applied to the database, or none are applied to the database. So the application never has to worry about half of them being committed to the database. A key differentiator of database servers is how well they handle transactions. The application rule for Accpac is that when the database is changed, operations must be grouped together into a transaction so that the database always has full application integrity. This includes things like the General Ledger never being out of balance. When G/L entries are posted, each entry is a transaction and the database goes from one in balance state to another. This means that if something crashes on either the server or workstation, or an unexpected error occurs then the database will not end up in a corrupt state. Database integrity is always maintained via database transactions.

Multi-User Model

Then our multi-user model builds on the transactioning foundation. Within Accpac we use optimistic concurrency (http://en.wikipedia.org/wiki/Optimistic_concurrency_control) as our multi-user model. If two users go to edit the same record, both users bring the record up on their screen at the same time, then they make some changes to various fields, then both hit save. Now both users started with the same record and made independent changes and both want to save, what should the system do? In the most basic optimistic concurrency model, the first person to save wins, their changes are committed to the database and the second user gets an error message the “Record was modified by another user”. With optimistic concurrency we can be a little more clever than this, and for the second user, we could re-read the record and see what the first user changed and if they are unrelated fields then combine our changes and re-save the record. We do this in a number of cases, but it requires a bit of application support to define what “un-related fields” means. The alternative to optimistic concurrency is to lock the record when the first user reads it, and not let the second user read the record at all. The second user would get a message saying that someone is editing the record and they can’t access it. This saves the second user possibly losing some work. However it imposes a huge multiuser penalty since if a lot of records are locked, then it is highly likely people are going to trip over the locked records all over the place and be blocked from doing work. They won’t be able to load the record just to view it. Accpac Plus used this model of multi-user processing and it restricted Accpac Plus to pretty much only having 25 users, since after that, the number of locks would just cause too many problems. Most systems with high numbers of users use optimistic concurrency.

So what does optimistic concurrency have to do with database transactions? Most document entry in Accpac involves updating and inserting many different database tables. What happens if you are save an O/E Order and you have updated 5 tables and then on the sixth you get an error? You can either re-read the record and reconcile the two sets of changes or you have to abort saving the order. If you have to abort saving the order, you need to do a transaction rollback to undo the changes to the first 5 tables. Fortunately with database transactions this is easy, and you never have to worry about corrupting the database if you encounter an error (multiuser or otherwise). So for complicated header detail entry, you can only really do optimistic concurrency if you are based on a transactioning model.

Stored Procedures and Database Scale-out

A common question is why we don’t use stored procedures? Besides the problems with portability across databases and the attendant lock-in to one solution, there are definite problems with scalability. Since Accpac is aimed at the mid-market, like most such products we don’t support arbitrary database scale-out. This means if you have trouble with the performance of the SQL Server, you just can’t add another SQL Server to solve the bottleneck. We of course do support many configurations with multiple database servers, but there are restrictions. However you can have as many application servers as you like. If you run workstation setup type client server then every workstation is an application server and takes a share in running the business logic. If you deploy via Terminal Server or Citrix, they you can add as many of these as you like (either dedicating users to specific servers or adding a load balancer). So we want to put as little load on the database server as possible, to allow users to use one database server for as long as possible. If we ran stored procedures on the database server then this puts considerable load on the server and makes the database server become a bottleneck quite quickly. People often point out that they are getting slow, but SQL Server is only say 15% utilized, and we say add another applications server. They say why not shift more work to the database server. The problem being that once the database server is full, you’re done. Adding application servers is easy.

We support a couple of configurations with multiple database servers. One is to split a big company into multiple divisional companies within Accpac then run each divisional company on a separate database server. You then combine the data for financial reporting using G/L Consolidations. Another configuration is to add a second database server that is a replicated copy of the primary. Then if you have a number of users that only do reporting then you point them to the replicated server (and you can have as many of these as you like). However you can only have one primary server where database updates occur. But often reporting is quite a load on the database server and moving this to another server can really help.

True database scale-out means you can add database servers at will, but this requires the application be structured quite differently. For instance for Order Entry you might have one server designated the primary, it would contain a small Order header table with the order number and a link to the database that actually contains the order. So as people perform Order Entry, each order only requires one simple insert on the primary server and the actual order is placed on one of the other servers in a round robin fashion. This then allows scale-out of the Order Entry process, since you can add as many of these slave database servers as you like. The complexity comes in for reporting, since now to generate a report on a range of orders, you have to consolidate the data from a number of database servers, greatly adding to the complexity.

The Future

Originally our next database target was going to be MySQL. One reason being to reduce the TCO for our customers, as Sage as a very good deal for distributing MySQL with our applications. Another reason is to implement SaaS down the road, where to be economical, we have to ensure that we don’t have to pay all the subscription revenue to other software companies. However since Oracle bought MySQL, it’s become very questionable whether MySQL will maintain its TCO advantages or whether Oracle will invest in it sufficiently to keep it competitive. So right now we are taking a wait and see approach to MySQL.

Another approach that is becoming popular are the so-called “NoSQL” databases (http://en.wikipedia.org/wiki/NoSQL) which stands for ‘Not only SQL’. These databases are all about database scale-out, but since they think about large scale scalability from the start, they tend to be very different from relational (SQL) databases. Often they load the entire database into memory, and will naturally partition the database over multiple servers in a redundant manner. Often these databases remove the “hard” parts of SQL to make data access faster and more scalable. One example of these is Google Fusion (http://googleresearch.blogspot.com/2009/06/google-fusion-tables.html).

Along these lines the Apache foundation has been developing the Hadoop platform (http://hadoop.apache.org/) along with the HBase database (http://hbase.apache.org/). This is a very ambitious project to provide the entire infrastructure necessary to build fully scalable large scale web based applications like a Facebook or Twitter.

Some of the ideas from the Web have a place in Accounting Applications. For instance quick natural language Google-like searches. If the database is partitioned over 5 servers then these server can each produce their part of the results in parallel and then they can be combined and returned. The key point being the parallelism employed to return possibly difficult queries quickly.

Some ideas are perhaps not going to be so popular. For instance when you refresh Facebook you often get different results each time you do it. Facebook sends out the query to a bunch of servers and then combines all the results returned in a short fixed amount of time and displays your page, but any results coming late from a slow server are ignored. Will accounting users appreciate getting most of the results quicker rather than all the results slower?

Database technology is the foundation for Accounting Applications like Sage ERP Accpac. Most Accounting Applications started life using flat file ISAM packages or home grown solutions. Then at some point made the jump to standard SQL databases. Certainly we’ve gotten a lot of mileage out of SQL, but as the amount of information in data warehouses and other corporate databases grows, where we join to data out on the internet, are we at the point where we are going to see the start of a transition to the next big thing in databases?

Migrating Customizations to Sage ERP Accpac 6

smist08 — Sat, 03 Jul 2010 17:17:00 GMT

As Accpac makes the transformation from being a Windows Desktop Application to becoming a true Web Based Application, people often ask: how will we migrate our current customizations to the new platform? I talked about how to customize the new Web Based screens in: http://smist08.wordpress.com/2010/02/05/sage-erp-accpac-6-customization/; but, didn’t really discuss a strategy for migrating existing customization to this new model.

Ultimately, the new customization model is far more powerful than the current model and this will allow far more complex customizations. It will also be much easier to perform some customizations that currently require quite a bit of code. However it took the channel quite a bit of time to master the current model. It took quite a while for everyone to learn the complexities of VBA programming and our UI customization model. Now we will be starting over.

Care will have to be taken when scheduling and estimating conversion projects. Perhaps starting with easier ones first, to learn from and become experienced in the new technologies before tackling the bigger more complex jobs.

Customization that Remain Largely Unchanged

For report customizations we are still using Crystal Reports and so all current Crystal customizations can still be used. There are fewer database changes for version 6 than we have had in the last few versions, so most customized Crystal Reports should work with no changes whatsoever. We will be moving to the newer Crystal 2008 reporting engine which means you can now use any features introduced in Crystal 2008. In the same way your Accpac Financial Reports will continue to work fine as will anything you have created with Accpac Intelligence or Accpac Insights. Additionally we now have the new “Accpac Inquiry” (http://smist08.wordpress.com/2009/12/11/sage-erp-accpac-6-0-adhoc-query/) which you might want to construct some custom queries for.

Any customizations that talk to the Accpac COM API or the Accpac .Net interface will also remain mostly unchanged and should continue to work. Basically just following the usual upgrade procedures that you follow for any new version.

Any customizations that use View subclassing will continue to work. The View layer is largely unchanged. You should re-instantiate your Views with the View template in the Accpac 6.0 SDK, but this is the same procedure you should do with any new version.

For these API type customizations, it usually involves running a VB EXE program or VBA macro. These can be run from any workstation that has workstation setup installed, but as we run workstation setup on fewer and fewer workstations, you might want to think about running these on the Web Server.

Customizations of the new Web Based UI model

First a couple of caveats:

We will continue to ship the current VB based UI forms for many versions. This means you can continue to use your current screen customizations for quite some time.
We will continue to ship the current Accpac Desktop to allow you to run things exactly as you do today.
Version 6.0A will only have new screens running inside SageCRM. Most other screens will move to the new Web Based technology for 6.1A.

However you will want to consider a strategy for moving your customer’s VB based customization to the new Web Based forms.

If the customizations only involved changes to the form design or changing text, then the customization in 6.x will be very simple and won’t require any coding. All the screen definitions and strings are stored in simple text XML files that can be edited either with special purpose tools (like a visual screen designer) or with a simple text editor like Notepad.

If the customization requires quite a bit of extra processing logic, then this code will need to be written in Java. The programming model is similar to that for the VB UIs. You subclass the UI and add your logic. There are collections of data sources and controls that you can tap into to get events to trigger your logic and allow your logic to manipulate the forms.

Expect a learning curve, to learn the new technologies. To become familiar with Java programming. To understand the programming and customization model used in our new Web Based UIs and how to deploy your updated UI controls on the server.

Although you can customize the current UIs just like you could customize the VB UIs, there is also a new alternative that lets you extend/customize the Accpac SData feeds. With this you can customize the code on the server that generates the data for the UIs. Sometimes this will be a better place to apply customizations. And is a new option you have available.

Like when we introduced VBA, it was a while before people were using it to extensively customize our screens. It took time to learn it and it took time to figure out what could be done. Like then, we will need to provide quite a bit of training, either live at our various conferences or via on-line training on Sage University. The good news is that the new customization model is more powerful than the current model and once you do master it, you will be able to more easily customize the look and feel for the product than ever before.

If the customization involves communications with programs running on the client’s workstation or involves saving files on the client’s workstation, these sort of operations may not be allowed from a Browser based application. There are definite restrictions on what you can do, in order to maintain workstation security. There are some things that you might do right now on the workstation that you might consider moving to the server or might need to find another approach, perhaps by communicating with a cloud service using web service calls. These will be dependent on what exactly you are trying to do and what is allowed.

The main points of customization are:

Various text files such as XML Screen definition files, XML string resource files and configuration files.
Cascading Style Sheets (CSS) that control the overall look and feel of the web pages.
Using Java to programmatically enhance the UIs.
Using Java on the server to programmatically enhance the Accpac SData feeds.

Older Web Technologies are Being Dropped

The Accpac SOAP Web Services are being removed in version 6.0A, so you will need to move any customizations using these to either the Accpac COM API, the Accpac .Net interface or to the new REST based SData Web Services being introduced in version 6.0A.

The Web Desktop is being removed. We will only be including CAB files for screens used by the SageCRM integration. All other Web Based VB screens are being removed. Any customizations that require the old Web Desktop will need to be moved to the new Web Desktop/Portal.

Summary

Any customizations that involve only the database, reporting or the business logic will continue to work or only require minor tweaks similar to the past few versions.

For screen customizations, you can continue to use your current VB customization for quite some time. However you should start to plan how to move these customizations over to the new Web base model.

Any customizations based on our older Web Technologies need to be moved over the new sooner than later.

Accpac on the iPhone and Android

smist08 — Fri, 25 Jun 2010 23:41:00 GMT

In a previous posting (http://smist08.wordpress.com/2010/03/19/sage-erp-accpac-6-x-mobility/), I talked about our mobile strategy of using HTML and JavaScript screens to run on mobile devices. But how exactly will that work? Mobile phones like the iPhone allow you to run web sites in the same manner you run native applications. So you can add an icon to the phone’s home page and then run your web based application. But does it look like a true iPhone or Android native application? Well it does with a little help. There are now several JavaScript libraries that reproduce the look and feel of native phone applications. In the screens below, I used the JQTouch (http://www.jqtouch.com/) library to easily construct some menus to act as an “Accpac Mobile Desktop” to launch our various data portlets (http://smist08.wordpress.com/2009/12/24/sage-erp-accpac-6-0-data-portlets/). This lets a user see various dashboards in real time from their phone.

In this picture we have a basic iPhone with some home page icons, including one for Accpac.

When you select the Accpac icon you then go to a set of menus for Accpac. Notice that even though this is a web page, it looks just like any other iPhone application. There isn’t any sign of the Safari browser, everything is nicely hidden.

The menus have nice animated transitions, so when you select them they slide to the side. Clicking on Payables yields:

Then if we click on Aged Payables we get to our regular Aged Payables snapshot that we normally see in the new portal/desktop. At this point our portlet isn’t styled to fit into the iPhone and there are a few style sheet bugs. But otherwise it does nicely display your aged payables.

Actually it looks a little better in landscape:

To use Apple’s iPhone SDK and iPhone emulator you need a Mac computer. I don’t own an iPhone, an Android phone nor a Mac. To test for the iPhone I used the MobiOne Test Center (http://www.genuitec.com/mobile/). For Android, I used the emulator in the Android SDK (http://developer.android.com/sdk/index.html).

Same sort of thing in Android:

Since we have made all our screens with standard HTML and JavaScript we will work on all modern devices. Plus as HTML5 is rolled out over the next few years we will pick up all the advances there as well.

Sage ERP Accpac 6 User Assistance

smist08 — Sat, 19 Jun 2010 19:03:00 GMT

As Sage ERP Accpac makes its transition from being a traditional Windows desktop application to becoming a more modern Web based application, we are looking to greatly improve the User Assistance within the product. We are applying the principles of User Centered Design (http://en.wikipedia.org/wiki/User-centered_design) to make the product far easier to use. As part of this we are adding all sorts of help and tips integrated right into the screens. For instance in the screen shot below of our Accounts Receivables - Days Receivables Outstanding snapshot, when the user hovers the mouse over the 181 days label, we popup a tooltip help box that describes what this number is and how it is calculated. This way the program is actively assisting you rather than passively waiting for you to hit the F1 key or go to the Help menu. The goal of the User Centered Design combined with this active User Assistance is that you should be able to do your work and understand what is going on without being required to go to a separate help page.

However, if you do need more detailed help or want to study the product in more detail, we have the new Accpac Learning Center. This is similar in concept to our existing Windows Help.

The new Learning Center improves on the existing help in a number of key manors. The new Learning Center is much more focused around helping you accomplish tasks rather provide an encyclopedic reference of every small field in the product. It still describes all the key fields in detail, but is more focused on providing information on the steps to accomplish tasks and providing troubleshooting tips.

The new Learning Center is fully web based like the rest of the product. The core content is installed on the customer’s web server along with the rest of the product. However the Learning Center seamlessly links to Accpac on-line content including the Knowledge Base, the Technical Support Center, User Community Forums, Blogs (like this one), Training Courses and Training Videos.

The Learning Center is available from the Help link at the top of the new Portal/Desktop. There are numerous “?” icons through the product that link you to the appropriate help pages. There is a new “Getting Started” snapshot that is included by default in each user’s home page as shown below.

The goal to the new help system isn’t just to help new users get up and running, but also to help experienced users be more productive by providing active tips and helping to assist users use some of the more advanced or infrequently used features in the system. Then to further connect people to the Accpac online community for a richer more social user experience.

Running Classic Forms in Sage ERP Accpac 6

smist08 — Sat, 12 Jun 2010 16:34:00 GMT

One of the main features of Sage ERP Accpac 6.0A is a fancy new Web based desktop/portal to act as a new home page for Accpac users. This new desktop/portal includes new dashboards (http://smist08.wordpress.com/2009/12/) and reporting capabilities (http://smist08.wordpress.com/2009/12/11/sage-erp-accpac-6-0-adhoc-query/). This is the first step in a transition for Accpac from a traditional Windows desktop application to a true Web based application. For this first release the only accounting screen that have been “webified” are the various quote and order related screens that are run inside SageCRM (http://smist08.wordpress.com/2009/12/17/sage-erp-accpac-6-0-quote-to-orders/). In the meantime we still want the new desktop/portal to be used by all users, so we have provided the ability to run the classic VB forms from the new desktop/portal.

The Tasks and Reports menu give you access to all the screens that are available in Accpac today.

Then when you click on “Order Entry”, the regular screen runs:

You can add any of these screens to the shortcuts bar for easy access:

When running these classic screens, they aren’t using the older Accpac Web Deployed mode, they are using Workstation Setup. This means the screens will run exactly the same as they run from the classic Accpac Desktop. The setup and configuration requirements for these screens are exactly the same as for the classic Desktop.

The classic Desktop is still included with the product and you can still use this, if you wish. Even once we have Web versions of our screens, we will still provide the classic VB screens as an option. This way you can move to the web at your own pace. You can move your customizations at your own pace and you can learn the new screens and transition over at your own pace. The new web based Accpac is still Accpac. All the business logic is exactly the same. The screens will still provide the same functionality as the classic product, but with usability improvements for learn-ability and productivity.

Of course, normally, a web page cannot run a VB ActiveX object. We also don’t want to work like the current Web Desktop where each VB screen is embedded in a web page and downloaded separately taking time and throwing up all sorts of security messages. So what we do is embed one small ActiveX control in our main web page whose responsibility is to run any of the Accpac OCX screens when requested. This way you only need to download and install one ActiveX control from IE. If you want to use this functionality you must use IE as your browser (it will simply be ignored in other browsers like Firefox). But hopefully this provides a simpler less painful mechanism to run our VB forms from IE than the current Web desktop.

Any third party ISV product written in the SDK and using VB based classic screens can provide XML configuration files, so they can run from the new desktop/portal as well. However we will only be running classic VB screens, we will not be running older CA-Realizer based screens nor general EXE programs. For these you will need to use the classic Desktop or run them directly from the Windows Start Menu. Since we are running these programs from an ActiveX control, we simply can’t open it up to run everything since this would be a huge security hole. We certainly can’t run arbitrary EXEs as this would be very exploitable by hackers.

This mechanism should provide an easy way for customers to transition from the VB world of Windows desktop applications to the newer more popular Web Based approach. Again, customers can move at their own pace. They can freely use both the new screens and old screens at once. They can keep using the VB screens and their current customizations for a long time (probably as long as Microsoft supports running them on Windows). And if some users prefer the Web screens and some prefer the VB screens, they can both happily co-exist.

Drilling Down from Crystal Reports in Sage ERP Accpac 6

smist08 — Fri, 04 Jun 2010 22:49:00 GMT

Sage ERP Accpac 6 brings the ability to drill down from data in a Crystal Report. This ability is only available in the new Web version and not from the VB version. Report designers have the ability to add hyper-links to reports, which will trigger a user interface form to be run when the link is clicked on. The Crystal Report can pass data to the UI form to enable drill down. In the screen shot below the cursor is over the first money amount in the report, so an underline appears indicating you can click on this number (the underline appears when the cursor is over the link).

When you click on the link, the Crystal Viewer sends a JavaScript notification to our Portal which then takes that and runs the web URL indicated. This runs another Accpac UI form, which doesn’t display, but instead immediately runs another report to show the drill down information.

Having a look at the Balance Sheet Report in Crystal:

You can select a field and select Format – Hyperlink.

Then set the hyperlink to a formula (the formula button is red in the previous screen shot) that you edit in the formula editor:

Basically with this you are setting the data that will be sent to the Accpac portal when you click on the hyperlink. Here you specify what you want done, including the URL of the UI form to run and build its parameters. You build the parameters using the Crystal formula language to build a string or URL parameters, which the UI form can interpret when it runs. To the end user it then appears that from reports you can drill down to both other reports and to UI data entry forms.

As we develop the new Web Based version of Accpac we are putting hyperlinks everywhere. This allows you to drill down from almost anything. This functionality will be far more pervasive than we’ve had in the past. Adding drill down to the current product has always been very popular, since customers want to know how various numbers are calculated, what was added up to give them a total or summary. Hopefully a popular feature.

How to Layout Forms in Sage ERP Accpac 6

smist08 — Fri, 28 May 2010 22:29:00 GMT

Laying out data entry forms on the web is quite different than laying out forms in Visual Basic. In Visual Basic you place each control on the form with absolute (X,Y) co-ordinates. This leads to problems when you translate text strings, if they overflow the space then you have to manually re-arrange the controls. Resizing windows is difficult since the program needs to figure out how to move and size controls as the form’s size changes.

If you’ve done data entry in a Browser, you’ve probably seen that all forms seem to adapt to the size of the browser and to the length of strings. If a string gets longer things just move over. Originally the browser didn’t have the luxury of programs running behind the scenes to move and size controls as conditions changed, so HTML was originally designed to display forms correctly in windows of different sizes and resolutions. The Browser even supports features like in IE where you can choose Page – Encoding – Right to Left and it will automatically re-arrange the page for right to left reading. HTML was originally designed to not depend on the presence of fonts and to handle running with any font size that the user has set. Below is a bitmap of our sign on page in right-to-left mode. It shows a bug in our decorator panel for doing the rounded corners on the “Sign In” button. It also shows the danger of putting things in bitmaps since the right hand pane and text is all a bitmap and as a consequence isn’t adjusted.

The Browser accomplishes this by representing pages as a tree called the Document Object Model (DOM) (http://en.wikipedia.org/wiki/Document_Object_Model). In this tree there are nodes for layout and there are nodes that contain data like text or images. Typically each layout node will have multiple children.

Inside the Sage Web Toolkit (SWT), layout nodes are represented by layout panels. This way you place the panel on the form, then any controls you place in the panel become its children. You can place layout controls inside other layout controls to build up more complicated layouts. You never specify (X,Y) co-ordinates when building a SWT form. You simply build it by putting controls inside other controls. This makes customization easier, since it won’t easily overlap controls, so if you add a control to a form, all the other controls will move out of the way to make room.

For instance to layout a simple label with an edit box and a button might be done as follows. First a vertical panel is added to be the root object, then a horizontal panel is placed inside the vertical panel. Inside the horizontal panel is placed a label and a text box. So now the label and text box will always be horizontally aligned. The text box will always be to the right of the label and will move over if the text in the label gets longer. Then the button is added to the vertical panel under the horizontal panel. So this form is built without specifying co-ordinates and the text in the label can be any length and the form will adjust to it. If another control is added to the vertical panel between the horizontal panel and button then the button will move down to make room. This makes translation and maintenance of the form very easy since you don’t need to re-arrange everything when you add something or a string’s length changes.

In tree view you can more clearly see how objects are placed inside other objects, or how the tree that makes up the DOM is built up.

These screens shots are of the Accpac SDK’s UI Designer which runs as an Eclipse plug-in that you use when developing forms. The forms themselves are stored as XML files and you can edit this with any XML or text editor as well as the visual screen designer.

The layout panels that are part of Accpac are:

Caption Panel – like a group box, has a caption and puts a box around its contents.
Decorator Panel – wraps a control in 9 images to allow effects like rounded corners.
Disclosure Panel – a panel that can be opened and closed to hide or show the contents.
Dock Panel – Allows you to place controls in the N, S, E, W corners or center.
Flow Panel – Controls placed here flow one after the other as space permits.
Grid Panel – Constructs a grid of a set size with a control in each grid cell.
Horizontal Panel – Controls placed here are arranged horizontally.
Vertical Panel – Controls placed here are arranged vertically.

This isn’t very many layout panels, but by nesting and combining these you can develop very sophisticated layouts, that will adapt to changing text lengths, font sizes, screen resolutions and other factors. Typically we make heavy use of the Grid, Horizontal and Vertical Panels.

For a more complicated example here is part of A/R Invoice Entry:

And in tree view:

If you’ve worked previously either with Java program user interfaces or with web designers, you will probably be familiar with these concepts. In Java the layouts aren’t part of the form, like they are here. Similarly some web tools abstract the layout controls quite differently. But if you are familiar either with Java UIs or web page design, then these concepts should be easy to understand. If you are coming from a Microsoft development tools background then these concepts will take a bit of getting used to. However once you do get used to them, you won’t really want to go back. Generally you have to think a bit more when initially laying out a form. But once a form is done, maintenance becomes very easy going forwards.

Insights 2010

smist08 — Mon, 24 May 2010 17:21:00 GMT

I just returned from the Sage Insights 2010 conference in Denver, Colorado. The conference was really great and Denver was an excellent city to have a conference in. The good thing about Denver is that the conference center is in the center of the city and most of the downtown attractions are within walking distance. This meant we weren’t trapped in an isolated convention center at the mercy of those facilities.

The keynote addresses outlined Sage’s strategies going forwards. The main emphasis was to balance efforts between three columns:

Providing value for the installed base.
Providing connected “cloud” services.
Developing strategic products for new customer acquisition.

These pillars aren’t just to do with R&D, they affect all aspects of our business, including marketing, programs, sales, support and services.

There tends to be a lot of confusion about what the difference is between developing for the installed base versus developing for new customers. After all both will be customers and their businesses will be very similar. Just one uses our products currently and one (hopefully) will shortly. These tend to be confused with an age old argument with-in our products of how many resources to put into developing “accounting” type application features versus “technology” features. Unfortunately anything that isn’t an accounting feature tends to be bucketed as a technology feature, even though many useful non-accounting features like better installation programs aren’t involving any new technology, just refining what we already use.

So to be clear, providing value for the installed base does not mean just adding some “accounting” type features and developing for new customer acquisition does not mean just adding “technology” type features. For instance three top requested features from the installed base are:

Better reporting
Better performance
Lower Total Cost of Ownership (TCO)

None of these are “accounting” features. All may or may not involve new technologies to solve them. Number 2 may just be a matter of re-factoring existing code in the existing technologies. Number 1 may just mean providing more reports in the current tools, or improving the current reports. Sage ERP Accpac 6.0A is addressing numbers 1 and 3 with web based technologies. It includes a new Accpac Inquiry feature and real time dashboards to address number 1. It moves towards a true web based zero-client model to avoid having to install anything on all the client workstations to reduce TCO. Better performance is address by being able to use more advanced web based testing tools to guide code re-factoring for better performance.

By the same token, from the Win-Loss analysis it appears that we lose sales at the upper end because our operations modules don’t provide all the sophisticated features that larger companies require. So adding functionality like sophisticated re-stocking strategies is an “accounting” feature that will allow us to expand the reach of Accpac, but probably won’t benefit existing customers.

Then there is the question of meeting the competition head on. When going head to head with the competition we want our product to be the best looking and most exciting. Right now that tends to mean being the richest web based application. Many times this is categorized as a new customer acquisition strategy, since we need to impress the clients to make a new sale. But it’s also an existing customer strategy, since if we don’t do this then the competition will attack our install base and if they can convince them that our products aren’t moving forwards, then they can start converting them. So we need the new technologies to both make new sales and to protect our installed base from our competitors. We are lucky that accounting applications are very “sticky” and its hard to convert our customers, but they still can be converted and our install base does need to be tended to.

So we can see that for the existing customer and new customer pillars, each will be addressed by a combination of refining existing functionality, embracing newer technologies and adding application functionality.

The middle column above of connected “cloud” services is all about providing new services to all our customers. This includes a number of existing services like Avalara sales tax calculator and Sage Payment Services’ Exchange gateway. It also envisions many new services perhaps like a hosted software as a service (SaaS) model self service modules. For instance a web site where employees of a customer can go to enter expense reports, which are then entered into the on-premise accounting application (using Sage’s SData web services protocol). The goal of these is that they can be developed once and provide a standard interface and then be connected to every Sage accounting application. Some of these will be add-on modules, some may be complimentary.

After the keynotes, the conference was quite exciting as Sage ERP Accpac 6.0 was released to “alpha”, meaning an official installable image was released to all third party developers. The “beta” release that will be available to all business partners is scheduled to be about 7 weeks away. Accpac 6 was installed on many computers for partners to play with, there were many product demonstrations, which were very well received.

At the end of the conference we offered two days of developer training in the Accpac 6.0 SDK which went quite well.

The next year should be quite exciting as we see all these new initiatives and products rolled out.

CCD – What is it?

debir — Wed, 19 May 2010 15:24:00 GMT

You may have heard the acronym CCD thrown around and thought to yourself, just another acronym. CCD stands for Customer-Connected Design. I’m Debi Richkas, Director of Customer-Connected Design for the MAS and Accpac products. The CCD teams within Sage have been around for a few years, but our approach has been around much longer.

In CCD, we see ourselves as the voice of our customers. Unlike surveys which are sent to thousands of customers with very little or no direct customer contact, the CCD team conducts usability studies with our actual customers to get feedback on our designs. We conduct our studies either face-to-face (e.g. customer locations, Summit, or Insights) or on a video conference call. We use recording software that tracks the mouse and cursor actions of our customers during the studies. We then analyze the recordings and present the facts, not our personal preference, to groups within the R&D and Product Management teams. This allows our organization to make an educated decision about the direction of our products.

Some of you may have experienced the benefit of our studies first hand if you work with the MAS 90 or 200 products. The design to keep the report window open after printing was a result of usability studies with actual customers. This wasn’t our initial plan, but after the overwhelming feedback this was a must.

Next week at Insights we will be conducting usability studies on the installation process for MAS 200 SQL and Accpac. We will also be conducting research on what our customers want from a Financial Reporter. If you would like to participate in a study, feel free to contact me as we are constantly soliciting for participants. Keep an eye out for an e-mail from my team; we may be coming to a city near you.

Sage ERP Accpac Macro Tips and Tricks

smist08 — Fri, 14 May 2010 02:38:00 GMT

Sage ERP Accpac uses Visual Basic for Applications (VBA) (http://en.wikipedia.org/wiki/Visual_Basic_for_Applications) as its macro language. This is the same macro language used by Microsoft Office and many other Windows desktop applications. With VBA macros you can access the Accpac Business Logic layer and enter documents, perform processing like posting batches or print reports. Since every screen in Accpac is an ActiveX control, you can embed any screen in a VBA macro and then manipulate/customize it in a standard uniform manner. Writing VBA macros is a huge topic; there are many books available on the topic along with courses at Sage University on VBA specifically for Accpac. This blog posting just lists a few useful tips and tricks that people find helpful when programming Accpac macros. These assume a general knowledge of writing basic Accpac macros. These aren’t in any particular order and tend to jump all over the place.

Upgrading to a New Version of Accpac

If your macro doesn’t customize an Accpac User Interface Screen then often your macro will just run without any changes. The exception tends to be if you are using a set of Views where their required compositions change. When you run you might get an error saying a required View composition is missing. In this case you should re-macro record the Accpac screen that uses these Views and add the missing View compositions. This tends to especially effect macros that are entering Order Entry documents. The main view compositions added for 5.6A are all to do with Serialized Inventory and Lot Tracking which were fully integrated into the core accounting modules.

The rest of this section applies to macros that customize Accpac User Interface Screens. It would also apply to you receiving a new version of some third party ActiveX control. When you insert an ActiveX control into a VBA macro, VBA reads all the information from the control and caches it in an EXD file. Unfortunately VBA doesn’t check when a new version of the control is installed and keeps using the out of date information. Full VB does something similar with OCA files, but is smart enough to update these when a control is updated. Worse the EXD file is strictly based on the base file name of the control, not its class id in the registry and not on the directory where it’s installed.

When we install a new version of Accpac we install our ActiveX controls with the same control names, but with new class ids. This way macros and other users of these controls can still use either version. When we install Product Updates we overwrite the current control, but have compiled the control with “binary compatibility” so all its interfaces are still compatible. For product updates, it is usually worth deleting the EXD files as we have seen problems with these in spite of binary compatibility.

When we upgrade to a new version, we need to delete the existing control from the macro and then insert the new control, along with deleting all the EXD files. The procedure for this is as follows:

Delete screen control from form
Delete icon from toolbar
Remove reference from Tools – References
Save macro and exit VBA
Go to c:\documents and settings\username\Application Data\Microsoft\Forms
Delete the *.exd files
Load the macro
Add the new control to the palette
Make sure the version is correct (from the path)
Insert the control back onto the UserForm
Make sure the name matches what you used before
Save the macro

References

References that you add to your macro project must match exactly the references on your client’s computers. For instance if you add a reference for an Office 2007 object like an Excel worksheet and your client has Office 2003, then the macro won’t run and will have to be corrected by removing the Office 2007 reference and adding one for Office 2003. Most COM/ActiveX library references are version specific. Also note the previous section on deleting EXD files. Similarly your version of Accpac must match the client’s to the Service Pack level.

Also beware of components you might have that a client doesn’t. For instance you might have full Visual Basic installed, which provides a large variety of useful COM libraries to use. However your client probably won’t have VB installed, so you must ensure the library use are using is freely distributable and install it on your client’s computer.

Windows has a number of optional components like Windows Scripting Host. If you require this, you will need to provide instructions on how to add this to Windows.

Debugging

Use Debug.Print to print things out to the intermediates window to trace things (the intermediates window is closed by defaults, so you need to open it to see the output).

The built-in VBA debugger is very powerful and a great way to step through your program to see what is going on. Set a breakpoint just before the problem area and step through your code. Takes a bit of practice to know when to “step in” versus “step over”. Run to cursor is a very useful function.

Always include an error handler in your macro. Any View error will trigger an exception and there will be a text error message that will tell you why the exception happened. Even if you don’t get errors, you still want this error handler in case your clients get errors. For instance if your client makes an optional field required and your macro doesn’t provide it, is very hard to debug if you don’t have an error handler, but an error handler will instantly tell you what is wrong.

If you are running a long process like importing hundreds of order and you just want it to go through and sort out the errors later, then write all the errors to a file with code such as:

ACCPACErrorHandler:

Dim lCount As Long

Dim lIndex As Long

If Errors Is Nothing Then

WriteToErrorLog Err.Description

Else

lCount = Errors.Count

If lCount = 0 Then

WriteToErrorLog Err.Description

Else

For lIndex = 0 To lCount - 1

WriteToErrorLog Errors.Item(lIndex)

Errors.Clear

End If

Resume Next

End If

…

Public lErrorFileHandle As Long

Public Sub WriteToErrorLog(sMessage As String)

If lErrorFileHandle = 0 Then

Dim sFileName As String

sFileName = "c:\popjctest.txt"

lErrorFileHandle = FreeFile(1)

Open sFileName For Output Access Write As #lErrorFileHandle

End If

If Trim(sMessage) = "" Then

Close #lErrorFileHandle

lErrorFileHandle = 0

Else

Print #lErrorFileHandle, sMessage

End If

End Sub

Getting Information From Accpac

Use macro recording to get a starting point. This is especially useful to get the view.open and view.compose calls correct. Often a lot of noise and hard coded values are recorded that need to be cleaned up. The main source of errors we see in macros are due to bad view compositions, so this is really quite important.

Use RVSpy (from the Accpac – Tools start menu). This tool spies on every View call made. Use it to see what Accpac does and use it to see what your macro is doing. Often easier to use if you turn off recording of view functions you aren’t interested in like attribs or presents.

Use the Application Object Model that is posted on our web site. This contains very useful information on the Views and underlying database tables (useful for reporting).

Use the Accpac U.I. Info tools (from the Accpac – Tools start menu). This gives info on UI programs as well as views.

If your macro isn’t working, try doing the same thing in the Accpac UI to ensure you aren’t being blocked because of some sort of settings or something.

Read the macro chapters in the System Manager User’s Guide, these have some good information on how the Accpac Views and macros work.

Keep a Backup of Your Macros

Always keep a backup of your macros, or use a source control system like SubVersion (http://subversion.apache.org/) or Git (http://git-scm.com/). Sometime a macro file might become corrupted, or you might accidentally delete some important code. It’s always nice to go back to a previous version that isn’t too old.

Global Variables

Set global variables to Nothing when the macro completes. This is useful when debugging. If you are running the VBA Editor, then global variables are not cleared when the macro end automatically (they are cleared when VBA exits). So if you re-run the macro it will run with the global variables still having their values from the last run which can cause confusion. Worse even if you assign each variable as you start, this means the old value will be cleared at this point sometimes causing errors if they are objects.

Objects like datasources must be set to Nothing before the macro terminates or VBA may GPF as it shutsdown.

Only One UI

You can only embed one UI in a VBA project at a time. All the Accpac screens use the same variable names and due to bugs in VBA this causes problems. You should be able to refer to them by object name like AccpacCS2000.datasource versus AccpacAP1000.datasource, but VBA gets confused and won’t work properly.

Don’t Use Init

We have replaced Init with RecordGenerate, RecordCreate and RecordClear to make it clear what we are trying to do. Init did all of these and sometimes did the wrong thing. Newer Accpac Views will only accept the Record* functions and will reject Init.

VBA Versus VB

VBA has a number of limitations imposed by Microsoft, so you are motivated to buy full VB. These limitation include:

Having a menu
Having an icon (for when minimized and in the task bar)
Receive events from popup forms
Resizable windows

VBControlExtender

In VB you have to define the controls of type VBControlExtender because unlike VBA, VB can't do the conversion for you. Here's an example getting the Click event of a notebook in full VB:

Dim WithEvents tabctl As VBControlExtender

Private Sub AccpacBK1600UICtrl1_OnUIAppOpened()
Set tabctl = AccpacBK1600UICtrl1.UIAppControls("tbOptions").GetControl

End Sub

Private Sub tabctl_ObjectEvent(Info As EventInfo)
    If Info.Name = "Click" Then
        MsgBox ("tab clicked")
    End If

End Sub

(In VBA we could just access the tabctl directly without using a VBControlExtender).

Well that wraps up my blog posting for this week. Next week I’m attending Sage Insights 2010 in Denver Colorado. If any readers are also attending, make sure you say “Hi”.