Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘customization

Sage 300 ERP – Only Activate What You Need

with 12 comments


As we continue to move Sage 300 ERP to the Azure Cloud, one question that gets asked is whether someone just running G/L, A/P and A/R (the Glapar which rhymes with clapper) is going to be negatively affected by the presence of say I/C, O/E and P/O? Fortunately, Sage 300 ERP activates each module independently and unless an accounting module is activated in the database, you don’t see it at all, it’s just as if you hadn’t installed it.

With per user pricing we’ve tended to bundle quite large number of modules under our various pricing schemes. However if you get such a bundle and then activate everything you have, you could enable quite a few fields and icons that clutter things up, which is a nuisance if you never use them. Generally business flows better if you only see icons and fields that you actually use. Why keep seeing currency rate fields when you never select a different currency? Why see selections for things like lots and serial numbers when you don’t use these? Why see project and job costing icons when you don’t use this module?

Using security and the built in form customization abilities can be used to hide complexity as well. However if the feature is enabled, it usually implies that someone in your organization is going to have to deal with it. So consider these in addition to setting up security and setting up customizations for your users.

In this article, I’m going to go through the process of activating applications and provide some behind the scenes info on the various processes around these issues. A slightly related article is my posting on Sage 300’s multi-version support.


To access a module, it first has to be installed. Generally from the installation DVD image, you can select (or de-select) most modules. There are some dependencies, so if you install Purchase Orders then that implies you need a number of other accounting modules to be installed as well. Each accounting module gets its own folder under the Sage 300 installation folders. These are formed by a two character prefix like GL or PO followed by a three letter version like 61A (not the year based display version). Generally all accounting applications are created equally and the Sage 300 System Manager becomes aware of them by the presence of these folders and then gathers information on the application by looking for standard files stored in these folders (like the roto and group files).


When you create a new company in Sage 300, the only applications that are there by default are Administrative Services and Common Services. Below is the data activation screen:


This program lets you choose which applications to activate into the database from the list of all installed accounting modules. When you select a given module, you may need to specify a few extra parameters in the next screen. The program will also tell you about any dependencies that are required and select these for you. Then when it goes to activate the programs it call the activation object in each selected application to let the application do whatever is required to add it to the system. This usually involves creating all the database tables for the application along with seeding any data that is setup automatically (like perhaps a default options record). If you are upgrading to a new version it will do whatever is required to convert the data from the old version to the new.

You can run this program as many times as you like, so if you don’t activate something, you can always come back later and activate it then. Just keep in mind that after you activate something, you can’t de-activate it. We do put up a fairly strong message to ensure you back up your database before running data activation. Not all database conversions can be transactioned, so if data activation does fail, you may need to start over from a backup, though often you can fix the problem and run activation again to finish.

For our hosted versions, you don’t need to install anything and you don’t actually see the data activation screen, you select what you want from a web site and then the database is provisioned for you. For the on-premise version, installation and activation is usually performed by the business partner.

If you just activate General Ledger, then you will only see General Ledger on the desktop and won’t see icons from anything else that is installed.


Also notice that “Create Revaluation Batch” isn’t shown because I haven’t enabled multi-currency for this database.

Other Separate Features

Some modules like multi-currency, serialized inventory, lot tracking and optional fields aren’t installed via data activation. The database support for these modules is always present. To be able to use these you need to install the license for the module and then you can enable the functionality within the other applications. For instance to turn on multicurrency you need to enable this in the Company Profile screen in Common Services.

Until you do this, all the fields, functions and icons for these will be hidden and won’t clutter up your desktop or entry forms. So if you don’t really need these, then don’t turn them on. Also keep in mind that once you enable these features, you can’t turn them off again, they are turned on permanently.

Sample Data

In one regard Sample Data is a bad example, since it has everything possible activated and enabled. Since it comes this way, applications will be activated even if they aren’t installed. This sometimes causes funny problems because some functions that communicate between modules won’t work in this case.

Sample data is a great way to show any feature in Sage 300 ERP, but in one regard it’s rather misleading. It tends to always be run as the ADMIN user and hence always shows all possible icons, fields, and functions. This tends to make the product look much more complicated that it really is in real world usage. In the real world, you wouldn’t activate things you don’t need and in the real world the user wouldn’t have security access to everything so again many things would be hidden and their workspace simplified.


We don’t normally allow deactivating an application or turning off a feature like multi-currency. The reason is that data integrity problems could theoretically occur if you do, since for instance if you have processed payroll checks, then bank would need payroll present to reconcile those checks and if you deactivate payroll while there are un-cleared checks in bank, then you will never be able to reconcile these checks. There are many cases like this so as a general good practice protection we prevent de-activation.

But the developers in the audience will know there is a back door. In the Sage 300 ERP SDK there is a “Deactivate” program that will deactivate an activated application. It does this by dropping all the tables for the application from the database and removing its entry from CSAPP. It does not do any cleanup of data that might be in any other accounting application’s tables. This is a great tool while developing a vertical accounting module for Sage 300, but if you use this on an production system, really be confident that the offending application hasn’t been used and you aren’t going to leave corrupted data in all the other modules as a result of removing this one. Again backup before proceeding. Similarly turning off things like multi-currency by editing the CSCOM table in SQL Enterprise Manager has the same caveats.


Generally you want to keep your accounting system as simple as possible. Modular ERP systems like Sage 300 ERP have a great breadth of functionality, but most companies only need a subset of that, which is relevant for their industry. So be careful to only select what you need and keep your system a little simpler and easier to use.


Written by smist08

March 29, 2013 at 6:35 pm

Customization: A Two-Edged Sword

with 2 comments


When implementing a mid-market ERP system, it’s often suggested that the base ERP should provide 80% of the needed functionality and then the other 20% is provided by customization. The rationale behind this is that although GAAP (Generally Accepted Accounting Principles) is standard, each business is unique and has unique requirements in addition to GAAP, especially in modules outside of the core financials.

All Sage ERP products offer quite high levels of customization ability. Generally we use this as a competitive advantage to make sales, since we can more closely match a customer’s requirements. However some customizations can lead to quite high costs and problems down the road (perhaps a version or two later).

As more and more customers are looking to move to the Cloud, we need to rethink customization and what we are trying to achieve. This blog post looks at some of the problems with Customization and some things to consider when recommending these. These apply to both hosting current ERP’s in the Cloud as well as to how we have to think about Connected Services and Features as a Service (FaaS) in the Cloud.


This is a rather unfair list of many of the problems we typically see with customizations:

  1. Cannot upgrade to a new version of the software because it means doing the customizations all over again.
  2. An initial product implementation fails because the costs and delivery of the customization go over budget and behind schedule.
  3. Some customizations cause system stability issues. For instance adding SQL triggers to the database. Then if the trigger fails, the transaction that invoked it fails with a really hard to diagnose error.
  4. Some customizations cause performance load problems. Perhaps a custom report or inquiry causes a SQL query to run that takes hours to finish, slowing everyone else in the meantime.
  5. In a hosted version everyone runs the same programs, so some customizations can prevent you moving to the cloud.
  6. Extra testing is required for every product update or hotfix to ensure they don’t interfere with customization. This restricts the use of things like automatic updates.
  7. Getting support and diagnosing problems. Customer support has a hard time diagnosing problem in and around customizations because they don’t know what the customizations are meant to do and what they might affect.

Moving to the Cloud

As we look to move more customers to the cloud, we have to deal with all these customization issues. Some level of customization is necessary, so the question to some degree becomes how much? In the cloud we want everyone sharing the same set of programs, so no customized DLLs or EXEs. In the cloud we want everyone to immediately receive hotfixes and product updates, so customizations must be upgrade safe. Further when a new major version comes out, we want everyone in the cloud moved to this new version in a quick, transparent and automated fashion. Hence any customizations allowed in the cloud can’t prevent data activations and can’t require a lot of special testing for each new version.


So what causes many of the problems above? A lot of customization can be a sign of an initial incorrect product choice resulting in trying to fit a square peg into a round hole. It can also indicate an overly eager to please sales team promising the product will do many things that it normally doesn’t. Plus many companies main line of business is developing customizations, so providing these is a major source of revenue.

A big cause of problems with upgrading customization is the result of database schema changes in the core ERP. This is usually the result of adding new features or streamlining functionality for performance and scalability reasons.

Often business logic changes can have unforeseen effects on customizations, even though the database schema doesn’t change, perhaps a customization relies on something in the way the business logic used to work.


As solutions move to the cloud the nature and scope of customizations is changing. This affects both vendors like Sage, in how we need to make sure all customizations can be automatically updated and it affects customization consultants now that you can’t install EXEs or DLLs into the ERP or CRM system itself. So what do we do, since there are still vital business needs that need to be addressed?

First off, we as a vendor have to be much more disciplined in how we change our applications from version to version. Long gone are the days when we could just make any change we wanted and then throw it over the fence to the Business Partners to figure out how to accommodate in the whole eco-system. This made the cost of upgrading quite expensive and detracted from the overall customer experience because generally upgrades introduced all sorts of problems that then needed to be stamped out. As we begin to automatically update people, both in the cloud and on-premise we have to ensure the whole process is automatic and seamless. We have to:

  • Maintain application API compatibility so customizations and ISVs continue to work.
  • Limit schema changes so that we don’t break customizations or ISVs. Mostly just add fields or tables and even if fields are no longer really used, leave them in for compatibility.
  • Provide tools to automatically convert anything beyond the database that is affected like Crystal Reports or VBA macros, so no additional work is required.
  • Ensure that we can upgrade independent of database state, i.e. don’t require that all batches be posted or such things.
  • Work closely with ISVs and Business Partners through alpha, beta and early adopter programs to ensure the entire ecosystem won’t be disrupted by new features and versions.
  • More tightly control the customizations that are allowed in the cloud. Even for custom reports and VBA macros, have rules and monitoring procedures so that badly behaved ones can be found and removed (or fixed).

When we are running our application in the cloud, how do we perform common integration type customizations? A great many of our clients create programs to feed data into and out of an ERP to integrate to things like perhaps a custom subscription billing service, a company shopping web site or one of any number of things. Since you can’t install EXEs or DLLs and even if you could do this via a VBA macros, chances are external communications would be blocked by a firewall, so what do you do? For these cases you have to switch to using Web Services. Which in the Sage world means SData. With SData you will be able to create Cloud to On-Premise integrations or Cloud to Cloud integrations. Basically you are removing the requirement that the two applications being integrated are on the same LAN.


Changing the thinking on customization will take time, but the industry and customer expectations are changing. Sage and its Business Partners need to be more disciplined in how we all customize to ensure we don’t create longer term problems for customers. In the Cloud world these problems show up much more quickly than in the on-premise world. We have to re-evaluate the technologies we are using and re-evaluate how our common customization design patterns are changing.

I tend to think once we get through some transitional pain, that we will come out the other side with a more powerful and more sustainable customization model that will lead to a better customer experience.

Written by smist08

September 29, 2012 at 6:20 pm

Sage Visual Process Flows

with 7 comments


Sage ERP X3 has had a feature called “Visual Process Flows” for some time now. These process flows help users navigate the product to perform their daily tasks. They are fully customizable and you can create these for all the roles in your organization. Below is a picture of a process flow in the Sage ERP X3 desktop.

However, this visual process flow tool wasn’t just built into the Sage X3 desktop. It was actually developed as a standalone component that could be integrated into any product. Now we are in the process of integrating this into a number of other Sage ERP products starting with Sage 300 ERP 2012.

Notice that there is now a “Sage Visual Processes” top level menu item on the Sage 300 Desktop, which contains the list of process flows that have been assigned to a user. To assign flows to users there is an Administrative Services program for this purpose. The shown example isn’t complete yet, but when Sage 300 ERP 2012 ships it will include a number of stock process flows. We can then augment this collection as we go along using Sage Advisor Update. If you have the proper security rights, you can right click on the item in the tree and select edit from the context menu to edit the process flow. The Process Flows we ship with go in the program files area as templates, and then any customized one go in a separate area in shared data. This way we can freely update the templates without overwriting customized flows.

Process Flows

The primary goal of these Process Flows is to allow users to quickly find their tasks and launch screens without searching through the ERP’s giant tree of icons. Within the Process Flows, it is easy to include much more descriptive text of what needs to get done. Most small business products like Sage 50 ERP present the user with this model of navigation. However for mid-market ERP, having a number of static hard coded process flows is inadequate. With this tool you can customize Process Flows for all roles that a mid-market customer requires. You have the ability to add your own graphics and to be quite creative in the creation and look of these flows.

You could argue that Process Flows doesn’t give you anything new since you are still running the same old screens and after all you can customize the tree of icons to limit what a user sees to run. However from all our usability studies, we find that even with this, users still spend quite a lot of time searching for the correct screen to run, especially ones that they run very infrequently. Plus, I think the Process Flows are quite attractive and more appealing than just displaying a list of icons.

You can run one process flow from another, so using these you can construct wizards to step people through longer processes. A great use is to create Process Flow’s for our old setup checklists. Another thing you can do is have a master process flow that drills down into more detailed process flows, creating a more graphical representation of our tree of icons.

If you are familiar with Sage ERP X3 and its Process Flows, then you should feel right at home with the tool in Sage 300 ERP. You can even export a process flow from one product and import it into the other, then you just need to change the Sage ERP X3 screen codes to the Sage 300 ERP screen codes (rotoids like OE1100). The actual Process Flows are stored in JSON format, which is a text file format which is easy to deal with. Along the same lines if you have taken the Sage University course on working with Sage ERP X3 Process Flows then you already know how to work with Sage 300 ERP process flows.

Sage 100/500 ERP

These are being fitted into the Sage 300 ERP Desktop first, and then later they will be fitted into the Sage 100 ERP and Sage 500 ERP Desktops. Later we will also consider running these from our Sage 300 ERP Web Portal. For that matter we can look at other places to run the Process Flow tool, perhaps as a starting page for tablet applications for instance.

Sharing and Collaboration

I know partners like to charge for what they do, but it would be nice if there was a central place where Process Flows can be freely shared. I wonder if Sage started such a site; would many partners contribute to it? Would customers contribute to it? If there was a lot of contribution it could eventually provide a much larger library of Process Flows than anyone could develop individually. The stone soup approach has worked quite well in other areas, but I know when we tried this with Crystal Reports, it failed quite badly. Still crowd sourcing and open source techniques are a very effective manner to really build critical mass. Sage X3 ERP has over 150 process flows, the other Sage ERPs are starting from scratch but will leverage the X3 work to catch up. Hopefully with community participation we can exceed this by quite a bit.


Adding Visual Process Flows to all our products, not only makes them easier to learn and use, but it complements the various branding initiatives to start making all the various Sage ERPs become a family of related products. Plus this feature gives all our desktops a nice visual refresh to make them much more appealing and modern.


Customizing Sage ERP Accpac Inquiry

with 8 comments

The Inquiry function is a new feature of Sage ERP Accpac 6.0A that adds easy to use Inquiry/Reporting functionality to the new Sage ERP Accpac Web Portal. The Inquiry tool, out of the box, comes with a number of predefined Inquiry templates for G/L, A/R and A/P. A common question is whether you can add to these templates to do Inquiries on additional tables or even additional applications. This blog posting is going to endeavor to show you how to do this. Just a note that this posting is really aimed at developers or partners with knowledge of XML and the database structure of Accpac.

When you work in Accpac Inquiry and customize the totals, the fields displayed or the sorting using the nice easy to use Web Screens, you are editing a template file. This is an XML file that stores all the information about what you are doing in the Accpac Inquiry UI. There are a number of starting templates that come with the system which you edit and then save as your own. You can see these templates under the folder: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\inquiry60a\template. Under template there are language directories since these hold translated strings. Then under each language directory are directories AP001, AR001, GL001 and GL002 for the four categories of templates (see the menu in the picture below).

In the picture the “From Templates” are the templates that come with the system and then Saved are the ones that you have modified.

If you open a template file in a text editor, you will see the query definition:

<QueryDefinition desc=”xxFind customer documents”

The domain-definition-file (in this case arddf.xml), is the file that defines the data domain definition. This file serves a couple of purposes. It defines all the fields with good descriptions to the Inquiry UI, so users can choose what to select based on good descriptions. It also defines the database structure to the query engine so the data can be returned properly.  Some documentation for this file is provided below. Again these are XML files and are stored in the folder: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\inquiry60a\ddf. Since these are XML files, you can open these in a text editor to have a look at them.

If you want to add some fields or tables to an existing data domain, you just need to edit the existing files to get what you are looking for. If you do this, beware that they may be overwritten on the next product update or version, so make sure you have a backup in a safe location (preferably a version control system).

If you want to create a new data domain, then take a copy an existing ddf file and modify it to what you need. Next you need to take a copy of one of the template files and modify it to point to your ddf file. Plus you need to modify anything else in the template file to match what is in the DDF.

One limitation of this process is that you can’t customize the categories in the Inquiry Menu from the Portal. Even if you create a data domain for say Inventory Control, you will still need to place your template in one of the existing categories like “A/R Customers and Transactions”. Hopefully we’ll move this into an XML file in a future version, so it can be customized.

Format of Domain Definition File (DDF)

The Data Domain Definitions files are XML files that define the data universe to Accpac Inquiry.

To facilitate the translation of a DDF file contents and support mixing multiple languages, a DDF file will use the UTF-8 encoding as this format fully supports Unicode. Therefore, the first line of a DDF file should be always defined as follows:

<?xml  version=”1.0″ encoding=”UTF-8″?>

XML Document Type Definitions of DDF File

<!DOCTYPE domain[
<!ELEMENT domain (tables, relationships?)>
<!ELEMENT tables (table+)>
<!ELEMENT table (field+)>
<!ELEMENT field (presentation*)>
<!ELEMENT presentation (item+)>
<!ELEMENT relationships (relationship+)>
<!ELEMENT relationship (join+)>

<!ATTLIST domain accpac-database-version  CDATA #REQUIRED>




 <!ATTLIST relationship join-type (equal-join|left-outer-join) #REQUIRED>
<!ATTLIST relationship from-table CDATA #REQUIRED>
<!ATTLIST relationship to-table CDATA #REQUIRED>

 <!ATTLIST join from-field CDATA #REQUIRED>
<!ATTLIST join to-field CDATA #REQUIRED>

Root Element: <domain>

A DDF’s root element is <domain>. This element has the following attributes and child elements. Every attribute or element is mandatory unless otherwise specified.


Name: defines a descriptive name of the domain. This could be displayed to an end user.

accpac-database-version: defines the ERP database version where this DDF is based upon.

For example:

<domain name=”A/R Customer Transactions” accpac-database-version=”6.0″></domain>

Child Elements


The <table> element has two attributes: name and desc. The name attribute is mandatory, and the desc attribute is optional. If no desc attribute is defined, the physical table name will be displayed to an end user. The only child element of the <table> element is <field>, which defines the fields to be exposed to end user. the <field> element also has two attributes: name, and desc. name and desc define the physical field name in the database and the descriptive name respectively. The desc attribute is also optional.

If a field has a presentation list, it can be defined using the presentation element to map a field’s internal value normally encoded in an integer to a more descriptive string.

For example:

<table name=”ARCUS” desc=”Customers”>
<field name=”IDCUST” desc=”Customer Number”/>
<field name=”SWACTV” desc=”Status” >
<item value=”0″ string=”inactive” />
<item value=”1″ string=”active” />


This element defines the relationship between the tables defined in the <table> section, and it should only be defined if there are more than one table in a domain.

Also, there should be only one tree formed when joining all the tables together in a left-to-right fashion. In other words, there should be only one root table.

We currently support two types of join: equal join and left outer join.

The <releationship> element has three attributes: join-type, from-table, and to-table. It has at least one empty child element <join> which defines the matching fields in the from-table and the to-table. The <join> has two attributes: from-field and to-field.

Below is an example of defining the left outer join between ARCUS and AROBL table:

<releationship  join-type=”left outer join” from-table=”ARCUS” to-table=”AROBL”>
<join from-field=”IDCUST” to-field=”IDCUST” />

A Complete Example

Here is an example of A/R Customer Transaction domain. There are 4 tables involved in this domain: ARCUS (customers), AROBL (documents), AROBS (scheduled payments), and AROBP (payments). For simplicity and clarity, not every field in those tables are listed here.

<?xml version=”1.0″ encoding=”UTF-8″?>
<!DOCTYPE domain[
<!ELEMENT domain (tables, relationships?)>
<!ELEMENT tables (table*)>
<!ELEMENT table (field*)>
<!ELEMENT field (presentation*)>
<!ELEMENT presentation (item+)>
<!ELEMENT relationships (relationship+)>
<!ELEMENT relationship (join+)>

<!ATTLIST domain accpac-database-version  CDATA #REQUIRED>




<!ATTLIST relationship join-type (equal-join|left-outer-join) #REQUIRED>
<!ATTLIST relationship from-table CDATA #REQUIRED>
<!ATTLIST relationship to-table CDATA #REQUIRED>

<!ATTLIST join from-field CDATA #REQUIRED>
<!ATTLIST join to-field CDATA #REQUIRED>

<domain name=”A/R Customer Transactions” accpac-database-version=”6.0″>
<table name=”ARCUS” desc=”Customers”>
<field name=”IDCUST” desc=”Customer Number” mask=”%12C”/>
<field name=”TEXTSNAM” desc=”Short Name” />
<field name=”SWACTV” desc=”Status”  >
<item value=”0″ string=”inactive” />
<item value=”1″ string=”active” />
<field name=”NAMECUST” desc=”Customer Name” />
<field name=”TEXTSTRE1″ desc=”Address Line 1″ />
<field name=”NAMECITY” desc=”City” />
<field name=”CODESTTE” desc=”State/Prov.” />
<field name=”CODEPSTL” desc=”Zip/Postal Code” />
<field name=”CODECTRY” desc=”Country” />
<field name=”NAMECTAC” desc=”Contact Name” />
<field name=”TEXTPHON1″ desc=”Phone Number” />
<field name=”TEXTPHON2″ desc=”Fax Number” />
<table name=”AROBL” desc=”documents”>
<field name=”IDCUST” desc=”Customer Number”/>
<field name=”IDINVC” desc=”Document Number”/>
<field name=”DATEDUE” desc=”Due Date”/>
<field name=”DATEINVC” desc=”Document Date”/>
<field name=”TRXTYPETXT” desc=”Document Type” >
<item value=”1″ string=”Invoice” />
<item value=”2″ string=”Debit” />
<item value=”3″ string=”Credit” />
<item value=”4″ string=”Interest” />
<item value=”5″ string=”Unapplied Cash” />
<item value=”10″ string=”Prepayment” />
<item value=”11″ string=”Receipt” />
<item value=”11″ string=”Refund” />
<field name=”DATEDISC” desc=”Discount Date” />
<field name=”AMTINVCTC” desc=”Customer Currency Invoice Amount” />
<field name=”AMTDUETC” desc=”Customer Currency Amount Due” />
<field name=”AMTDISCTC” desc=”Customer Currency Discount Amount” />
<field name=”SWPAID” desc=”Fully Paid Switch” >
<item value=”0″ string=”Yes” />
<item value=”1″ string=”No” />
<table name=”AROBS” desc=”Scheduled Payments”>
<field name=”IDCUST” desc=”Customer Number” />
<field name=”IDINVC” desc=”Document Number” />
<field name=”CNTPAYM” desc=”Payment Number” />
<field name=”DATEDUE” desc=”Due Date” />
<field name=”DATEDISC” desc=”Discount Date” />
<field name=”SWPAID” desc=”Fully Paid Switch” >
<item value=”0″ string=”Yes” />
<item value=”1″ string=”No” />
<field name=”AMTDUETC” desc=”Original Amount” />
<field name=”AMTDISCTC” desc=”Original Discount” />
<field name=”AMTDSCRMTC” desc=”Remaining Discount” />
<table name=”AROBP” desc=”Document Payments”>
<field name=”IDCUST” desc=”Customer Number” />
<field name=”IDINVC” desc=”Document Number” />
<field name=”CNTPAYMNBR” desc=”Payment Number” />
<field name=”IDRMIT” desc=”Check/Receipt No.” />
<field name=”DATEBUS” desc=”Posting Date” />
<field name=”AMTPAYMTC” desc=”Cust. Receipt Amount” />
<field name=”IDBANK” desc=”Bank Code” />
<field name=”TRXTYPE” desc=”Transaction Type” />
<field name=”IDCUSTRMIT” desc=”Remitting Customer No.” />
<field name=”DATERMIT” desc=”Receipt Date” />
<relationship  join-type=”left-outer-join” from-table=”ARCUS” to-table=”AROBL”>
<join from-field=”IDCUST” to-field=”IDCUST” />
<relationship join-type=”left-outer-join” from-table=”AROBL” to-table=”AROBS”>
<join from-field=”IDCUST” to-field=”IDCUST” />
<join from-field=”IDINVC” to-field=”IDINVC” />
<relationship join-type=”left-outer-join” from-table=”AROBS” to-table=”AROBP” >
<join from-field=”IDCUST” to-field=”IDCUST” />
<join from-field=”IDINVC” to-field=”IDINVC” />
<join from-field=”CNTPAYM” to-field=”CNTPAYMNBR” />

Written by smist08

June 4, 2011 at 9:33 pm

Posted in sage 300

Tagged with , , ,

Customizing Crystal Reports for Sage ERP Accpac

with 20 comments

The main reporting engine used by Sage ERP Accpac is Crystal Reports. All transaction listing reports, setup reports, forms and checks are produced by Crystal Reports. Financial Reports and Business Intelligence reports are produced by Excel based reporting tools like Accpac Intelligence, Accpac Insights and the built in G/L Financial Reporter. Customizing the main Accpac reports is a matter of loading the report into the Crystal Reports designer, editing the report and saving it. This sounds easy, but Crystal is a very sophisticated reporting engine and some of the reports in Accpac are quite complicated. This blog posting looks at various topics in customizing reports.

Customization Directories

When you customize a report you can just save it over the existing report in Accpac. However if you do so, then this report could be overwritten by the next product update or will be overwritten if you un-install and re-install the product. Plus you may want different reports for different users or for different companies. The Accpac Customization Directories feature is the solution to these problems.

Here you set the user id and company name and then the directory for where you want the customizations for these combinations stored. You can use the wildcard “*” to indicate all users or all companies. Under the directory you specify here you need to store the reports in a subdirectory structure similar to how they are stored under Accpac. For instance if you customize and A/R 6.0A English report then in the above example you would store it in c:\myreports\ar60a\eng. This way it keeps reports separated by version, application and language to avoid conflicts.

Complicated Reports

Accpac has a lot of options and configurations. Reports have to handle all these possible combinations like multi-currency versus single-currency, G/L activated versus G/L not activated, National Accounts used versus no National Accounts, etc. As a result many sections in Accpac reports are visible based on formulae as are many columns and such. Sometimes if things get too complicated there will be two versions of a report, perhaps one report for single currency and a separate report for multi-currency. This is often if one is portrait while the other is landscape. Generally we try to keep the number of reports down, since this simplifies the long term maintenance of the reports. So if we add a new column we only need to add it to one report rather than five. Below is a screen grab from one of the most complicated reports in Accpac, the PJC Adjustment Posting Journal.

In this report there are many sections all with formulae that indicate when to show them. If you scroll down this report, you would see quite a few sub-total and total sections as well. Customizing this report is quite a challenge. To figure out how it works and then to carefully edit in the middle of this report can be quite daunting. Fortunately this shows the worst case and most reports aren’t this bad.

To approach editing this report you need to find out the section you want to customize and then just concentrate your attention on that one section ignoring all the others. In the screen shot below we brought up the section expert on a G/L account section. Then the X-2 button is red indicating there is a formula that controls the suppression and then if you press that button you get the formula that controls things.

With-in Accpac many things in reports are controlled by formulae. So if you are having trouble finding what is controlling things, look for the X-2 buttons being red indicating there is a formula present to control what is going on.

User Function Libraries

Crystal Reports has many built in functions that can be used in its formula language. However it also gives the ability for applications to add their own functions to the Crystal formula language. Then these functions can be used like any other function inside Crystal Reports. Accpac adds quite a few functions to Crystal. Generally these functions are used to format Accpac data in the same manner the UI forms format them, as well as get useful data out of Accpac that isn’t stored in the database. Below is a list of the functions Accpac adds with a description of what each does.

IsTrue (String)

This function will take in a string, and return its equivalent boolean value. Will return True if the string is TRUE, T, or 1 (any case).

pwFormatDate (Number)

Formats a date in the same way the Accpac does in UI programs. The number should be of the form yyyymmdd.

pwFormatTime (Number)

Formats a time value in the same way Accpac does in UI programs. The number should be of the form hhmmsshh.

TrimZeros (String)

Trims trailing zeroes off a string value. Ie changes “xyz0000” into “xyz”.

LookUpString(String, String, String)

First parameter is a language code (numeric code). Second parameter is a list of language codes. Third parameter is a list of language names. Used to decode language fields in bank services.

SaveNumber(String, String, String, String)

Saves a number into a file. First parameter is the file name. Second is the number to be stored. Third is the file mode flag (“TRUE” to create new file else appends). The forth parameter is the number ID as defined in GetBankTotal below. This function is used in conjunction with GetBankTotal below.


Function reads number from a file and calculates bank total. Parameter is the file name to read. Returns the total. This function is used in conjunction with save number. File contains records of the form:

struct BankTotal
char ID;
char Space;
char Total[30];

ID                                 Total Value
‘1’               Entries
‘2’               NSFs
‘3’               Deposits
‘4’               Checks


Print one space if the blank spaces or null string is parsed. Print a parameter passed.


Translate number to an account type. Parameter is the type. Does the following converstion:

Account Type Number (input)                             Account Type String (returned)
1                                 “IN”
2                                 “DB”
3                                 “CR”
4                                 “IT”
5                                 “UC”
6                                 “DA”
7                                 “AD”
8                                 “CA”
9                                 “AC”
10                                “PI”
11                                “PY”
12                                “ED”
13                                “UD”
14                                “AD”
15                                “CB”
16                                “GL”


Removes leading zeroes from a string.


Returns the System Manager product edition (1=Enterprise, 4=Corporate, 6=Small Business).


Checks if a license is valid. First parameter is the two letter prefix (such as CS), second parameter is the version (such as 53A). Returns 0 if ok, -1 if not found, -2 if expired.


Returns “CS”.


Returns “OB”.


Returns “NA”.


Returns “OB”.


Switches numeric strings to numbers, where the following are hardcoded:

   * Negative sign is “-” and precedes the number, with no space after the sign
* Decimal sign is “.”
* There’s no group (“thousands”) separator

Such strings (i.e. opt fld VALUE data) would have been created with bcdToStr.

(Since those strings come from DB string fields, we use locale-independent representations.)  Using Crystal’s ToNumber on such strings would fail when the locale changes (i.e. negatives become (1,5) instead of -1.5).

pwFormatString(String, String, Number, String)

Formats a standard database field for display. First parameter is a language code. Second parameter is the string to format. Third parameter is a database field type:

Number             Type
1                      String
2                      Binary
3                      Date
4                      Time
5                      Float
6                      BCD (fixed precision numeric)
7                      Short Integer
8                      Long Integer
9                      Boolean
100                   Money

The forth parameter is not used (but you must provide one, empty string is ok).

pwGetString(String, String)

Loads a string from pwuflLLL.dll where LLL is a language code like eng. (I.e. pwufleng.dll). First parameter is the language code. Second parameter is string to translate.


So what do you need to do when you upgrade versions of Accpac? Usually you would do the following:

  1. Copy the customized reports to a new application version under your customization directory, for instance copy c:\myreports\ar60a\eng to c:\myreports\ar61a\eng.
  2. After you have activated the database to the new version, then re-verify the reports against the database. Verify is a Crystal function that checks that the report is in sync with the database.

Strictly speaking the report will still work as long as the database doesn’t change dramatically. If we just added some fields to a table, the report will still work without needing anything to be done. In the early days of Accpac before our Crystal Reports were based on the ODBC driver, you had to verify the reports no matter what, since any change in the database, no matter how small would result in a report not running without running the Crystal verification function. Now a days the reports are more robust and the reports will continue to work as a long as the tables it uses are still there (and we very rarely remove tables). But it doesn’t hurt to re-verify the reports and it is a quick operation.

Datapipe Reports

Within Accpac we have what we call “Datapipe Reports”. These are reports that are built on an Accpac supplied Crystal database driver. We use these reports to supply data that isn’t readily available through ODBC. This includes filtering data for security purposes or performing complicated calculations on the data. Also if ODBC doesn’t retrieve the data with good enough performance then we can write a tailored datapipe report to retrieve the data quickly.

One complaint about datapipe reports is that the columns returned are fixed. So if the datapipe doesn’t return the data you need then what do you do? The solution is to add a sub-report that is based on ODBC to retrieve the additional data that you need.


This blog post just covered a few topics in customizing reports. Crystal Reports is a very large and sophisticated program, with it, there is great flexibility in the types of reports and forms that you can produce. There is great power in what you can accomplish. But with this power comes a certain amount of complexity that it takes a bit of learning and practice to overcome.

Written by smist08

May 21, 2011 at 10:24 pm

Posted in sage 300

Tagged with , ,

Customizing the Order Details in Quote to Orders

with 4 comments

The new Order and Quote Entry screens in the Sage ERP Accpac 6 Quote to Order feature are the first real accounting document entry web based screens produced in the new “Orion” technology. As these go to final release there is a lot of interest in how to customize these screens. In the previous blog posting we started to talk about how to customize these screens. We discussed the declarative layout XML file that describes the layout and performed some simple edits to these screens. In this blog posting we are going to continue customizing these screens, this time customizing the Order Detail Line Table/Grid controls. In this blog posting, we will look at the XML that defines this table along with a couple of customizations.

For more background on Accpac’s new customization model and the XML Declarative layout files see: and

Definition of a SwtTable

The XML file is: C:\”Program Files (x86)\Common Files\Sage\Sage Accpac”\Tomcat6\portal\swtServices\uiDefinitions\oe60a\eng\sagecrmorderui\SageCRMOrderUIUIDefinition.xml. Below is part of the definition of the table control (a widget of type SwtTable) from the declarative layout (many columns omitted):

 <widget type=”swt:SwtTable” id=”orders_DETAILS” datasourceID=”oeorderdetails” preferencestoreID=”DetailTableSettings” height=”250″ width=”980″ autoLoad=”false” showRecordNumber=”true” showCustomFields=”true”>
       <item propertyBinding=”LINETYPE” dataType=”Int” width=”100″>
               <transText text=”xType” textID=”oe60a_sagecrmorderui_colType”/>
      <item propertyBinding=”CALC_ITEMNO_MISCCHARGE” dataType=”Char” width=”160″ formatPattern=”[:printupper:]{0,24}”>
              <transText text=”xItem No./Misc. Charge” textID=”oe60a_sagecrmorderui_colItemMisc”/>
   <item propertyBinding=”DESC” dataType=”Char” width=”200″>
                <transText text=”xDescription” textID=”oe60a_sagecrmorderui_colDesc”/>


The “<widget type=”swt:SwtTable” …” tag defines the SwtTable, sets its initial size along with some other global attributes. You can change these, but might not want to, for instance setting autoLoad to true would cause the table to load when it’s initialized, but the program will load it programmatically so the end effect will be to load it twice.  The main thing you will want to customize is the collection of columnHeaders which define all the columns that are displayed in the SwtTable.

Between the <columnHeaders> and </columnHeaders> tags are a number of <item> tags which each define a column in the table. Each item has a number of attributes, the possible attributes are:

  • propertyBinding: field in the SData feed that the column is bound to.
  • dataType: type of the column possible values are: string, Bool, Byte, Char, Date, Decimal, Int, Long, Real, Time and Object.
  • Width: default width of the column.
  • readOnly: set to true or false whether the column is read only (versus editable).
  • hyperlink: set to true or false whether the column is a hyperlink.
  • Sorting: set to true or false whether you can sort of this column.
  • formatPattern: format pattern for the column.
  • fractionDigits: number of decimals.
  • totalDigits: total number of decimal digits.

Some attributes are only valid for certain data types, for instance fractionDigits only makes sense for Decimal and Real types.

Then there is a <text> sub-element inside the <item> tag where you can specify either the text for the column heading or the textID of a string identifier in the uiContent table on the server. The textID will get the appropriate text for the language the user signed in on as. Make sure you set the textID to “” (empty string) if you want to hard code the string in the declarative layout. Editing the strings in the uiContent table in the Portal database is also a good way to customize strings, or to even specify new strings.

Adding a Column

To add a column to the SwtTable we need to add another <item> to the <columnHeaders> collection. For instance if we wanted to add the “Non-stock Clearing Account” to the table then we would add the following to the collection of <columnHeaders> just before the </columnHeaders> tag:

 <item propertyBinding=”GLNONSTKCR” dataType=”Char” width=”80″ readOnly=”true” visible=”true”>
               <transText text=”Non-stock Clearing Account” textID=””/>

Even though we’ve now added the field GLNONSTKCR to the SwtTable it wouldn’t display since it isn’t in the O/E Orders SData feed. So we need to add it to the SData feed. To do this we need to edit another XML file, namely: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\oe60a\resourceMap\OEOrderViewMapping.xml. This file among other things defines which Order fields to include in the SData feed. In the <includedFields> section for the detail, add the line:

    <resourceViewField viewFieldName=” GLNONSTKCR”       />

This will add the GLNONSTKCR feed to the detail. Make sure you add this to the includeFields for the detail and not one of the header. The field names used in the SData feeds is exactly the same as the field names specified in the Accpac Object Model (AOM) located at:

Whenever you change one of the SData definition files, you need to restart Tomcat to have it take effect.  To do this: run the Service applet from the Administrative Tools icon in the Control Panel and restart the “Sage Accpac Tomcat 6” service. Or do it from the “Configure Tomcat Service” icon under “Sage Accpac” under the start menu.

Note that is you are using sample data then there are a lot of optional fields defined for the Order details. With this new screen all the optional fields appear in columns of the table rather than from a popup form. This means that when we added our new field it won’t appear as the last column in the table, but somewhere in the middle since all the optional fields will come after it.

When the user customizes the column widths, order and visibility of table columns, these customizations are stored in the PreferenceStore table in the Portal database. If you have played with the columns in the table previously and the new column doesn’t show up, you might want to delete the SageCRMOrderUIUIDefinition-DetailTableSettings row from this table to clear the preferences. This table stores what we used to store in the *_p.ism files from our VB UIs.

Also make sure you have a backup of your customized declarative XML file, since you wouldn’t want future product updates to overwrite your one copy.

Below we see a screen shot of the table with our new column added between the Instructions and the Backorder optional field:


Hopefully this gives an idea of a few things you can do with the SwtTable that displays and edits the Order/Quote Detail Lines in the new Quotes to Orders. This article showed how to do this by editing the XML directly; but, if you have the SDK then you can use the SwtUIDesigner to do this visually.

Written by smist08

December 11, 2010 at 6:41 pm

Customizing Quotes to Orders

with 9 comments

With Sage ERP Accpac 6.0A we have incorporated Web based versions of the Accpac Order and Quote entry screens into SageCRM. These are the first accounting document entry screens in the new Accpac Sage Web Toolkit (SWT)  technology. A common question I get asked is how to customize these screens. Certainly the regular current Accpac Order Entry screen is one of the most customized screens in the Accpac system. However it is very hard to customize the current screen when it’s run from SageCRM because of the way it is packaged and run from a CAB file. The new screens are regular Accpac screens and run inside SageCRM just like they would run from Accpac. This means all the regular customization techniques for the new web based technology can be used on them.

As a starting point the screen definitions are now stored in a separate XML screen definition files. You can accomplish a lot just by editing these files. In this blog posting we will look at a collection of simple but powerful customizations you can perform here. In this blog posting we will just be looking at the XML for the controls and manipulating those. Actually in addition to that you can add JavaScript processing code to the events that get fired, this is a bit more sophisticated and we will look at that in a following blog posting.

The XML file is located in the folder: C:\”Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\swtServices\uiDefinitions\oe60a\eng\sagecrmorderui”. The file we will look at is SageCRMOrderUIUIDefinition.xml. The XML files for a couple of popup forms are here also and you can customize these also. You can edit these files in any text or XML editor. If you have the SDK you can edit these files in a visual SWT UI designer, however for the purposes of this blog posting we will assume you don’t have the SDK and edit this file directly as a text file. One nice thing about the new web based technologies is that they are entirely configured by XML files, which being text files can easily be edited for customization purposes.

Introduction to XML

First just a few notes about XML. It’s worth your while to check out a site like to learn the basics of XML. But for what we’re doing here you really don’t need to know very much, just how to find things, cut and paste things, and make minor changes.

The key point is that all data is contained between a starting tag and a closing tag like:

<caption>This is a caption</caption>

Where the closing tag is the opening tag with a / in front of it.

Sometimes you can express attributes for a tag like:

<caption font=”Sans Serif”>This is a caption</caption>

Also if there is no general data then you can end the tag with a /> like:

<caption font=”Sans Serif” text=”This is a caption”/>

Generally most of the other stuff you find in the XML file, you would leave alone, like the version and encoding and such.

Form Layout

We talked a bit on how to layout forms in So we won’t talk too much about how to control the layout here, but we may look at this more in depth in a future posting. But beware that there are two types of widgets in a screen definition file, there are layout widgets that control how things look and then data widgets for displaying/editing data.

Widget Elements

Here a technical description of widgets in the layout XML files taken from the reference XSD file. An XSD file defines what is correct in a corresponding XML file

A widget element must contain a “type” attribute (corresponding to the namespace-qualified class name of the widget).  It may contain other attributes such as an ID as well as other attributes corresponding to simple properties (such as “enabled”).

A widget element may also contain other elements that correspond to compound properties. These compound properties include action (for predefined actions associated with button-like widgets), translatable display string properties such as caption, column headers (for tables), images (which include the URLs to the normal, normal, mouseover, and disabled images for a widget), search fields (for finders) and selectable items (for list boxes).

A widget element may also contain a “handlers” element for JavaScript and default actions widget event handling. That “handlers” element in turn contains a list of “handler” elements.  Each “handler” element has an event  and action attribute that refers to the name of the event and the name of the action that will be executed when the event is triggered.  The “handler” element may also contain a “params” element, which in turn contains a list of “param” elements.  Each “param” element has an id and value attribute that refers to the name and value of a parameter that will be passed in as an additional parameter to the action that is executed when the event is triggered.  Parameters will be passed to the action in the same order in which they are specified in the XML (which is important if it is a java script function being executed by the action).

A “regular” container (“HasWidgets”) widget’s element contains a “children” element.  That “children” element in turn contains a list of widget elements for child widgets.

A grid-type container (“HasWidgetsInGrid”) widget’s element contains a “rows” element.  That “rows” element in turn contains a list of “row” elements, each of which contains a list of “cell” elements.  Each cell is either empty or contains one (child) widget element.

A dock panel widget’s element contains a “dockedWidgets” element.  That “dockedWidgets” element in turn contains a set of docking position elements (e.g. “northWidget” element) for north, south, east, west, and center (in that order).  Each docking position element is either empty or contains one (child) widget element.

A menu bar or menu button widget’s element contains a “menuItemList” element. That “menuItemList” element in turn contains a list of “menuitem” elements, which in turn may contain other “menuItemList” elements (representing submenus).


Below is a screen shot of the Order Entry screen in CRM as it ships un-modified in the product:

Editing the Layout

Within the layout XML files are widget definitions of the form:

 <widget type=”swt:SwtTextBox” id=”orders_ORDNUMBER” enabled=”true” datasourceID=”oeorders” propertyBinding=”ORDNUMBER” width=”170″ style=”swt-TextBox-nohint-noBackground”/>

This is the contents for the base structure of a widget element.  These are the elements we customize.

Changing a Caption

Suppose we want to change the Document Number caption in this form. We can find the XML tag for this widget in the XML file:

<widget type=”swt:SwtLabel” style=”swt-Label documentDetails-LeftRow”>
          <transText text=”xDocument Number:” textID=”oe60a_sagecrmorderui_lblDocNumber”/>

This references a nice translatable string that exists in the Portal database, but we want to change this to a hard coded string specific for our customer. If we blank out the textID field, then it will use the text specified in the text field, so we can change the above to:

<widget type=”swt:SwtLabel” style=”swt-Label documentDetails-LeftRow”>
          <transText text=”Customer Specific Reference:” textID=””/>

Once we save this change, then running the Q2O Order Entry screen will show this caption instead of the one from the Portal database.

Adding a Field

The Quote to Order screen is optimized to make it easy for salespeople to enter orders quickly. However suppose the form is too simple and you require your sales people to enter another field that isn’t already on this screen. In the Q2O screen there is the order description field, but no order reference field. So let’s add the reference field to the Document Details tab.

To do this, find the SwtSingleTabPanel with transText: oe60a_sagecrmorderui_pnlDocDetails. A couple of lines below this is a SwtGridPanel with a rowCount of 4, change this rowCount to 6 (since we are adding one row with the label and one with the textbox.

Then add a new <row> at the end of the grid, this will be just before the </rows> tag:

                <widget type=”swt:SwtLabel”>
                          <text>   <transText text=”Reference:” textID=””/>    </text>
                   <widget type=”swt:SwtTextBox” id=”orders_REFERENCE” datasourceID=”oeorders”
                           propertyBinding=”REFERENCE” width=”400″/>

This will add the controls to the layout. However it won’t work quite yet. This is because the REFERENCE field for the Order Header isn’t provided in the SData feed. However we can add it. To do this we need to edit another XML file, namely: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\oe60a\resourceMap\OEOrderViewMapping.xml. This file among other things defines which Order fields to include in the SData feed. In the <includedFields> section for the header, add the line:

    <resourceViewField viewFieldName=”REFERENCE”       />

This will add the REFERENCE feed to the header. Make sure you add this to the includeFields for the header and not one of the details.

Whenever you change one of the SData definition files, you need to restart Tomcat to have it take effect. After you do this you should see the new field on the form and be able to set and edit it.


Here is the screen shot with these two customizations:


Hopefully this starts to give some idea of how to customize the new screens. If you have the SDK then you can use the SwtUIDesigner to edit these forms; but, hopefully in future versions the visual screen designer will be included in the main product making this process much easier for non-SDK partners to customize.

But at least for now, once you get used to XML you can do a lot of customizations to the current Web based version 6.0A screens.

Written by smist08

November 28, 2010 at 3:00 am

Posted in sage 300

Tagged with , , ,

Migrating Customizations to Sage ERP Accpac 6

with 12 comments

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:; 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” ( 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:

  1. 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.
  2. We will continue to ship the current Accpac Desktop to allow you to run things exactly as you do today.
  3. 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.


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.

Written by smist08

July 3, 2010 at 5:15 pm

Posted in sage 300

Tagged with , , , ,

How to Layout Forms in Sage ERP Accpac 6

with 7 comments

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) ( 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:

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

Update 2011/11/05: Have a look at this posting on using CSS to layout forms.

Written by smist08

May 28, 2010 at 10:27 pm

Posted in sage 300

Tagged with , , , ,

Sage ERP Accpac 6 Customization

with 14 comments

With the next generation of Accpac’s Web based screens, we wanted to greatly improve the customizability of Accpac. We have a User Centered Design team that is working hard to improve the general usability of the product with the hope that fewer customizations are required. But as a mid market product we know that in most cases the 80 – 20 rule still applies, to ensure we deliver at least 80% of the customer’s required functionality, but be flexible enough that the remaining 20% can be filled by ISV partners and customizations.

With-in Accpac 6 there are three categories of screen customization:

  • In product. Basically screens and dialogs that allow users to customize their own screens and workspace. This includes things like adding shortcuts to the shortcuts bar and choosing their Data Portlets for their Portal home page. Configuring the Data Portlets to display what the customer requires.
  • Codeless customization. These are customization that can be performed exclusively inside the screen/form designer. These customizations don’t require any Java programming.
  • Coded customization. Here you are writing Java code to extend and modify the shipped forms.

For any customization we want to ensure that the user can easily upgrade to the next version without having to re-create the customizations from scratch. Ideally this should be handled transparently via the upgrade process. But once coding is involved then there is quite a bit of room to break this, we will publish best practices, so your code will just need to be re-compiled on the new version, but it’s up to the partner performing the customizations to follow these guidelines.

For codeless customizations, all the User Interface screens are saved on the server as XML files separate from the UI program. This means you can edit/customize these XML files without needing to use programming code or development tools. However we do provide a screen designer to edit these XML files in a friendlier manner. In Sage ERP Accpac 6.0, this tool will be available for developers via the SDK, and in 6.1 will be part of the standard product for use by anyone with sufficient security rights. Below is a screen shot of the UI designer editing the A/R Billing Cycles form. You can see some of the controls you can select as well as some of the control properties that can be set.

Some typical customization you can do here include:

  • Changing text. The form will automatically resize as you change text lengths.
  • Adding controls. Controls are controlled via layout, hence you can’t put a control on top of another control. The controls will move out of the way to allow you to insert an new control. You can attach the control to fields in the SData feeds.
  • Adding data sources. You can add SData feeds to the form.
  • Re-arrange all the control. Change the layout.
  • Remove unwanted elements from the form.
  • Change the tab order.
  • Change existing control properties and CSS styles.
  • Change the columns in list controls.
  • Assign actions (like button presses) to standard actions provided by the system.
  • Include JavaScript code to be run when various events happen (ok, so it’s almost codeless).

For coded customizations, there are two places you can extend the UI logic. As a Web based application, some of the code resides on the server and some in the browser on the client. Both of these can be extended via extending their Java classes. Doing either of these requires writing Java code, in the server case you create a number of Java classes and store the .class files in the correct place on the server, for the browser classes, you need to feed your code through the GWT compiler to generate JavaScript code to run in the Browser.

On the server we have classes that receive the SData requests from the clients, and translate these requests into Accpac View (Business Logic) calls. There are a number of classes that do this in a standard manner. However applications can extend the default class for this to do things like add extra fields to the feed. These extra fields may be to provide additional database fields from other places or to perform calculations on the server rather than the client. These classes can also change things like the read-only status of a field. ISV’s or sophisticated customizers can also added classes that extend the existing classes to add additional fields, say for their customizations or change the behavior/values of the existing fields. Since Java fully supports object oriented class inheritance, creating these classes is far easier than creating View subclasses (which you can still do).

On the client, you can extend the classes that comprise the User Interface program that runs in the browser. This is very much like how you wrap our current ActiveX VB UIs. You basically extend the main UI class and get access to a collection of all the controls and SData feeds. You can also add listeners to receive any event you are interested in. Then programming is very similar to how you customize the current UIs. The difference being that you now program in Java rather than VB or VBA, and you have to feed your program through the GWT Compiler to generate a new extended UI.

Hopefully this gives an overview of how customizations work in the new Sage ERP Accpac 6 Web-based world. Generally we are looking to continuously improve our customization model to make it easier to accomplish the customizations that customers require.

Written by smist08

February 5, 2010 at 9:08 pm

Posted in Uncategorized

Tagged with , ,