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 , , ,