Other ERPsFree referencePDF, Excel, Word exportSage 100

Sage 100 Table and Module Prefix Reference

Sage 100 tables are named with a module prefix, an underscore and a descriptive name, which makes them easy to read through ODBC. This lookup lists the prefixes and the tables people search for most.

Download:

Table or prefix	Module	What it stores
AR_	Accounts Receivable	Prefix for receivables tables
AP_	Accounts Payable	Prefix for payables tables
GL_	General Ledger	Prefix for general ledger tables
CI_	Common Information	Prefix for shared item, code and master tables
IM_	Inventory Management	Prefix for warehouse level inventory tables
SO_	Sales Order	Prefix for sales order tables
PO_	Purchase Order	Prefix for purchase order tables
BM_	Bill of Materials	Prefix for bill of materials tables
SY_	System	Prefix for system level tables such as company and user
CI_Item	Common Information	Item master
AR_Customer	Accounts Receivable	Customer master
AP_Vendor	Accounts Payable	Vendor master
GL_Account	General Ledger	Chart of accounts
IM_ItemWarehouse	Inventory Management	Item quantities and costs by warehouse
SO_SalesOrderHeader	Sales Order	Sales order headers
SO_SalesOrderDetail	Sales Order	Sales order lines
PO_PurchaseOrderHeader	Purchase Order	Purchase order headers
PO_PurchaseOrderDetail	Purchase Order	Purchase order lines
BM_BillHeader	Bill of Materials	Bill headers
BM_BillDetail	Bill of Materials	Bill component lines

Sage 100 data is normally read through the Sage 100 ODBC driver, which exposes these names. Table availability depends on your edition and installed modules, and names can vary slightly by version. Confirm in your own dictionary before building reports, and do not write through ODBC.

Sage 100 reporting and migration review

Optional: a short expert review of your Sage 100 reporting setup.

Optional. The tool and every export work without this. No spam.

How to use the lookup

Filter by module or by a word like order or bill to find the table you need, then use that name in Crystal Reports, Excel or an ODBC query. Exporting to Excel is useful when you build a mapping for a migration. The first rows show the module prefixes themselves, which helps when you meet an unfamiliar table in a report written years ago.

Reading the structure

Headers and details are separate tables, joined by the document number. Item data is split between the shared CI_Item master and warehouse level tables in Inventory Management. Because Sage 100 data can be exposed through ODBC, reporting tools connect easily, but the driver is a read channel for reports rather than a safe way to update data.

Join header and detail on the document number
CI_Item is the shared item master
Use business objects for any updates

Common mistakes

Frequent errors are reporting on warehouse quantities without a warehouse filter, ignoring edition differences when a module is not installed, and relying on a table name that was renamed in a newer version. Test every query against a copy and keep the ODBC login read only.

Frequently Asked Questions

How do I connect to Sage 100 data?

Most reporting tools connect through the Sage 100 ODBC driver, set up as a data source on the server or workstation. Use a dedicated read-only user.

Where are item quantities stored?

Item definitions are in CI_Item, and quantities and costs by warehouse are in the Inventory Management warehouse table. Always filter by warehouse in reports.

Are these table names guaranteed?

They are widely used, but modules, editions and versions differ. Verify in your own installation before relying on a name in production reports.

Can I write to these tables?

Not safely. Use Sage 100 business objects, Visual Integrator or supported import tools so validation and related updates run.

Many Sage 100 users reach a point where reports and integrations need rethinking. We can help assess the options.

Talk to an Expert Explore More Free Tools

Related Tools

Other ERPs

More Other ERPs tools

See all

Back to All Tools