Customizing Sage ERP Accpac Inquiry

Sage ERP Accpac Inquiry was introduced in Version 6.0 to provide faster access to information for everyone in your organization. Inquiry allows even inexperienced users to create personalized ad-hoc query lists in just a few minutes without knowledge of databases, programming or web technologies. In this post we take a closer look at using this useful new tool.

Decision Support

Inquiry helps uncover opportunities and highlight possible issues, enabling your workforce to execute appropriate actions in response to ever-changing business situations. The highly intuitive interface puts custom information access within easy reach of everyday non-technical users. It is quick and easy to create database connections, add fields and customize data. The query lists you create become part of your user interface in the new Sage ERP Accpac Web Portal.

Predefined Templates Included

The Inquiry tool comes with a number of predefined templates for accessing General Ledger, Accounts Receivable, and Accounts Payable data. In many instances you may find this tool can replace customized Crystal Reports. Upon opening a template, you will find the data arranged in a columnar grid, with the ability to select by up to three different criteria at the top. These capabilities alone will fulfill the needs of many of your staff members, but you can create new inquiries based on different data sets if you wish.

Customizing Inquiry

Here we give an overview of how you can customize Inquiry.

Where Inquiry Definitions Are Stored
When you work in Inquiry and customize the totals, the fields displayed, or the sort field, in the 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 Inquiry. You can take any of the included templates, edit them, and save them as your own with a new name. The templates are stored in the folder:

C:/Program Files/(x86)Common/FilesSageSage AccpacTomcat6/portal/sageERPinquiry60a/template

Under the template directory there are language directories to hold translated strings. Then under each language directory are directories AP001, AR001, GL001 and GL002 for the four categories of included templates.

Adding Additional Data To Inquiry
It is possible to add to the built-in templates to include additional tables or even to add additional data from other applications such as Inventory Control. Because these are XML files, if you have experience working with XML you will be able to modify them and create new ones, or we can assist you.

The XML template files can be opened in a text editor, allowing you to see the query definition. For example:

<QueryDefinition desc=xxFind customer documents
textID=txtARTemplateQryCustomer
domain-definition-file=arddf.xml>

The domain-definition-file (in this case arddf.xml), is the file that defines the data domain definition. This file serves two purposes. It defines all the fields with good descriptions, so it is easy for users to select the data they need. And it defines the database structure to the query engine so the data can be returned properly.

If you want to add some fields or tables to an existing data domain, you just need to edit the existing files to include 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).

Create A New Data Domain
If you want to create a new data domain, then you can take a copy of an existing ddf file and modify it. Next you need to take a copy of one of the template files and modify it to point to your ddf file. Finally, you will need to modify anything else in the template file to match what is in the ddf. One limitation of this process is that you cannot customize the categories in the Inquiry Menu from the Portal. Even if you create a data domain for Inventory Control, you will still need to place your template in one of the existing categories such as AR Customers and Transactions. Sage plans to move this into an XML file in a future version, so you will be able to create your own categories.

Give us a call if you would like us to create some custom templates for Sage ERP Accpac Inquiry that your team can use to make better business decisions.