Microsoft DynamicsFree referencePDF, Excel, Word exportMicrosoft Dynamics GP

Dynamics GP Work, Open and History Table Map

Dynamics GP stores the same business document in different tables depending on where it is in its life. This map shows which table to query for master data, unposted work, open items and history, so reports and migration extracts stop missing records.

Download:

Table	Module	Lifecycle stage	What it holds
GL00100	GL	Master	Account master (ACTINDX, ACTDESCR, account type)
GL00105	GL	Master	Account index master (ACTINDX to account number string ACTNUMST)
GL10000	GL	Work	Unposted GL transaction header (batch entry)
GL10001	GL	Work	Unposted GL transaction lines
GL10110	GL	Summary	Account period summary for the current open year
GL10111	GL	Summary	Account period summary for historical years
GL20000	GL	Open	Posted GL transactions for the open year
GL30000	GL	History	Posted GL transactions for closed years
GL40000	GL	Setup	General Ledger setup
RM00101	RM	Master	Customer master
RM00102	RM	Master	Customer address master
RM10301	RM	Work	Sales work and open transactions entered in Receivables
RM20101	RM	Open	Receivables open transactions (invoices, credits, payments with balance)
RM20201	RM	Open	Receivables apply open (applications between documents)
RM30101	RM	History	Receivables history transactions after removal
RM30201	RM	History	Receivables apply history
PM00200	PM	Master	Vendor master
PM00300	PM	Master	Vendor address master
PM10000	PM	Work	Payables transaction work
PM20000	PM	Open	Payables open transactions
PM30200	PM	History	Payables paid transaction history
PM30300	PM	History	Payables apply to history
SOP10100	SOP	Work	Sales transaction header, not yet moved to history
SOP10200	SOP	Work	Sales transaction line items, not yet moved to history
SOP30200	SOP	History	Sales transaction header history
SOP30300	SOP	History	Sales transaction line amounts history
POP10100	POP	Work	Purchase order header, open
POP10110	POP	Work	Purchase order line items, open
POP30100	POP	History	Purchase order header history
POP30110	POP	History	Purchase order line history
POP10300	POP	Work	Purchasing receipt header, unposted
POP10310	POP	Work	Purchasing receipt lines, unposted
POP30300	POP	History	Purchasing receipt header history
POP30310	POP	History	Purchasing receipt line history
IV00101	IV	Master	Item master
IV00102	IV	Master	Item quantity master, by item and site (RCRDTYPE 2 is site level)
IV00103	IV	Master	Item vendor master
IV10000	IV	Work	Inventory transaction work header
IV10200	IV	Open	Stock purchase receipts (cost layers)
IV30200	IV	History	Inventory transaction history header
IV30300	IV	History	Inventory transaction amounts history
SY01400	SY	System	User master (DYNAMICS database)
SY01500	SY	System	Company master (DYNAMICS database)
SY40100	SY	Setup	Fiscal periods setup, period dates by year

Table names are consistent across GP 2013 through 2018 and later, but ISV products and some versions add columns. Confirm against your own database before writing production queries. Company tables live in each company database; SY01400 and SY01500 live in DYNAMICS.

Want a GP data extraction review?

An optional expert review of your GP extract and reconciliation approach before a migration.

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

How to use the table map

Search by table number, module prefix or keyword such as 'history' or 'receipt'. The lifecycle column tells you where a document sits. A posted receivables invoice lives in RM20101 while it has a balance and moves to RM30101 only after the Remove Receivables Transactions utility runs. If you query only one table you will either miss paid items or double count them.

Filter by module prefix (GL, RM, PM, SOP, POP, IV) to see one subledger
Use the Work and Open rows to find unposted data before a cutover
Export to Excel and share it as a data dictionary for the migration team

The numbering convention behind the names

GP table numbers follow a pattern: 00xxx are masters and setup, 10xxx are work or open-entry tables, 20xxx are open posted records, and 30xxx are history. That pattern is not a strict contract, since GL10110 is a summary and IV10200 holds cost layers, but it is a reliable first guess when you meet an unfamiliar table. The prefix before the digits is the module: RM receivables, PM payables, SOP sales order processing, POP purchase order processing, IV inventory, GL general ledger.

Worked example: where is invoice INV-1041?

A sales invoice touches several tables. Before posting it sits in SOP10100 and SOP10200. After posting it is in SOP10100 until the Sales history utility moves it to SOP30200. Its receivable lives in RM20101 until paid and removed, then RM30101. The GL impact is in GL20000 for the open year or GL30000 for a closed year. Joining across all of these with UNION is how you build a complete document trace.

Common mistakes

The most frequent error is reading balances from the wrong lifecycle stage. Customer balances should come from RM20101 CURTRXAM, not from summing history. Another is joining GL transactions to accounts by account string instead of ACTINDX through GL00105. Finally, remember that ISV add-ons write their own tables, so this map covers standard GP only.

Do not assume a posted document has left the work table
Void documents still exist, check VOIDSTTS or the module's void flag
Test on a copy of the company database, never on production

Frequently Asked Questions

Does this list every Dynamics GP table?

No. A full GP company database has well over a thousand tables. This map covers the core financial, receivables, payables, sales, purchasing and inventory tables most reports and migrations touch. Use the Table and Field reference in GP Support Debugging Tool or the SDK documentation for the rest.

Why are posted documents still in work tables?

GP keeps posted sales and purchasing documents in the 10xxx tables until you run history utilities such as Sales Transaction history transfer. Posted does not mean moved. Check the document status columns rather than assuming the table tells you.

Which database holds SY01500?

SY01500 is in the system database, named DYNAMICS by default. It lists every company and its database name (INTERID). Queries that loop across companies usually start by reading SY01500, then switch databases.

Can I write directly to these tables?

It is strongly discouraged. Direct inserts bypass GP business logic, numbering and posting. Use eConnect, Web Services or integration tools for writes, and treat SQL as read-only for reporting and extracts.

If your GP data needs to be extracted, reconciled and loaded into a successor ERP, we can help build read-only extraction queries and reconciliation reports against your own database.

Talk to an Expert Explore More Free Tools

Related Tools

Microsoft Dynamics

More Microsoft Dynamics tools

See all

Back to All Tools