Stephen Smith's Blog

Musings on Machine Learning…

Customizing Sage ERP Accpac Inquiry

with 6 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”
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 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 item EMPTY>
<!ELEMENT relationships (relationship+)>
<!ELEMENT relationship (join+)>
<!ELEMENT join EMPTY>

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

 <!ATTLIST table name CDATA #REQUIRED>
<!ATTLIST table desc CDATA #REQUIRED>

 <!ATTLIST field name CDATA #REQUIRED>
<!ATTLIST field desc CDATA #IMPLIED>
<!ATTLIST field mask CDATA #IMPLIED>

 <!ATTLIST item value CDATA #REQUIRED>
<!ATTLIST item string 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.

Attributes

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

<table>

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:

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

<releationship>

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” />
</releationship>

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 item EMPTY>
<!ELEMENT relationships (relationship+)>
<!ELEMENT relationship (join+)>
<!ELEMENT join EMPTY>

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

<!ATTLIST table name CDATA #REQUIRED>
<!ATTLIST table desc CDATA #REQUIRED>

<!ATTLIST field name CDATA #REQUIRED>
<!ATTLIST field desc CDATA #IMPLIED>

<!ATTLIST item value CDATA #REQUIRED>
<!ATTLIST item string 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″>
<tables>
<table name=”ARCUS” desc=”Customers”>
<field name=”IDCUST” desc=”Customer Number” mask=”%12C”/>
<field name=”TEXTSNAM” desc=”Short Name” />
<field name=”SWACTV” desc=”Status”  >
<presentation>
<item value=”0″ string=”inactive” />
<item value=”1″ string=”active” />
</presentation>
</field>
<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>
<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” >
<presentation>
<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” />
</presentation>
</field>
<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” >
<presentation>
<item value=”0″ string=”Yes” />
<item value=”1″ string=”No” />
</presentation>
</field>
</table>
<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” >
<presentation>
<item value=”0″ string=”Yes” />
<item value=”1″ string=”No” />
</presentation>
</field>
<field name=”AMTDUETC” desc=”Original Amount” />
<field name=”AMTDISCTC” desc=”Original Discount” />
<field name=”AMTDSCRMTC” desc=”Remaining Discount” />
</table>
<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” />
</table>
</tables>
<relationships>
<relationship  join-type=”left-outer-join” from-table=”ARCUS” to-table=”AROBL”>
<join from-field=”IDCUST” to-field=”IDCUST” />
</relationship>
<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>
<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” />
</relationship>
</relationships>
</domain>

Advertisements

Written by smist08

June 4, 2011 at 9:33 pm

Posted in sage 300

Tagged with , , ,

6 Responses

Subscribe to comments with RSS.

  1. Hi,

    i am working on ACCPAC Inquiry. it was working fine. but problem is, after creating a view from two accpac tables and create a ddf file.when i execute ddf file a message is shows “table is not found”.

    is ddf support user define table or view ?

    Yakub

    July 5, 2011 at 8:03 am

    • It has to be a table that Accpac knows about. Ie createed by our API such as dbCreateTable (which is what an Accpac View would call).

      smist08

      July 5, 2011 at 3:17 pm

  2. […] queries (data domains) you can get a developer to create these for you. I blogged on how to do this here. Any of these that you created previously will continue to work. Just beware that you will need to […]

  3. Hi,
    Can this work for the normal version of Accpac, not web portal? Can we add any fields in AR customer inquiry in normal Accpac?

    Rebeccac

    April 24, 2014 at 7:39 pm

    • This article only applies to the web portal. To do something like this to the desktop one, you will need to use a VBA macro.

      smist08

      April 25, 2014 at 2:20 am

      • Thanks Smist08.

        Is there anywhere I can find a article talk about that or a sample? I want to add a field in the AR customer inquiry document table.

        Rebeccac

        April 25, 2014 at 1:21 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: