Dynamics 365 Finance & Operations: Data Model Architecture & Key Tables Reference

Table Architecture & Layering

F&O tables are organized into three logical tiers:

1. Base Transaction Tables

Core operational tables that store the fundamental business data. These are created when you perform transactions (post invoices, create sales orders, record journal entries). Examples:

• CustInvoiceJour – Customer invoice headers; one row per invoice.
• CustInvoiceTrans – Customer invoice lines; one row per line item.
• SalesTable – Sales order headers.
• SalesLine – Sales order lines.
• LedgerJournalTable – GL journal headers.
• LedgerJournalTrans – GL journal transaction lines.

Base tables are normalized (one logical entity per row), indexed for quick lookup, and include audit fields (created by, created date, modified by, modified date).

2. Summary & Aggregate Tables

Pre-computed aggregations of base table data, used for reporting and analysis. Examples:

• CustTrans – Customer transaction summary; one row per posted transaction (invoice, payment, adjustment).
• VendTrans – Vendor transaction summary.
• InventTransOrigin – Inventory movement history.

Summary tables are denormalized for speed but may lag behind base tables if not refreshed timely. Most reports query summary tables rather than base tables.

3. Staging & Integration Tables

Temporary tables used during data imports, integrations, and ETL processes. Examples:

• DMFStagingTable – Data Management Framework staging area; holds raw import data before mapping and transformation.
• SalesTableStaging – External sales orders awaiting import.

Staging tables are often cleared after successful import. They don’t contribute to operational data but are critical for integration workflows.

Key Finance Tables Deep Dive

General Ledger

Table	Purpose	Key Fields
LedgerJournalTable	GL journal headers (batch)	JournalNum, Name, Approved, Posted, Approver
LedgerJournalTrans	GL journal line items	JournalNum, LineNum, AccountNum, Debit, Credit, Dimension values
GeneralJournalEntry	Accounting source (newer, entity-based)	AccountingSource, LineNumber, MainAccount, Debit, Credit
LedgerTable	Ledger master (accounting currency)	LedgerID, LedgerType, CompanyID, Currency

Accounts Payable

Table	Purpose	Key Fields
VendTable	Vendor master	AccountNum, Name, Group, PayTerms, Currency, Status
VendInvoiceJour	Vendor invoice header	ParmID, InvoiceAccount, InvoiceDate, DueDate, InvoiceAmount
VendInvoiceTrans	Vendor invoice line items	ParmID, LineNum, ItemNum, Quantity, Price, LineAmount
VendTrans	Vendor transaction summary	AccountNum, TransType, Amount, SettledAmount, DueDate

Accounts Receivable

Table	Purpose	Key Fields
CustTable	Customer master	AccountNum, Name, CustGroup, PayTerms, Currency, Status
CustInvoiceJour	Customer invoice header (posted)	SalesId, InvoiceAccount, InvoiceDate, DueDate, InvoiceAmount
CustInvoiceTrans	Customer invoice line items	SalesId, LineNum, ItemNum, Quantity, Price, LineAmount
CustTrans	Customer transaction summary	AccountNum, TransType, Amount, SettledAmount, DueDate

Key Supply Chain & Manufacturing Tables

Inventory & Procurement

Table	Purpose	Key Fields
InventTable	Item/product master	ItemId, ItemName, ItemGroup, UnitId, Status
InventSum	Inventory quantity on-hand (summary)	ItemId, WarehouseId, Qty, Available, Reserved
InventTransOrigin	Inventory transaction history	ItemId, RefType, RefId, TrnType, Qty, Cost
PurchTable	Purchase order header	PurchId, VendAccount, PurchStatus, PurchDate
PurchLine	Purchase order line items	PurchId, LineNum, ItemId, Qty, Price, DelivDate

Sales & Orders

Table	Purpose	Key Fields
SalesTable	Sales order header	SalesId, CustAccount, SalesStatus, OrderDate, DelivDate
SalesLine	Sales order line items	SalesId, LineNum, ItemId, SalesQty, SalesPrice, DelivDate
SalesDeliveryDateCriteria	ATP (Available-to-Promise) logic	ItemId, WarehouseId, SafetyStock, LeadTime

Manufacturing & Production

Table	Purpose	Key Fields
ProdTable	Production order header	ProdId, ItemId, Qty, Status, StartDate, EndDate
ProdBOM	Bill of Materials	BOMId, ItemId, BOMQty, BOMType, EffectivDate
Route	Manufacturing route (work steps)	RouteId, OperNum, ResourceId, SetupTime, RunTime

Data Entities & OData Exposure

A data entity is a logical abstraction of physical tables, exposed as a REST API endpoint. Data entities simplify integration by hiding table complexity and providing a clean, consistent interface.

Example: Customer Data Entity

Physical table: CustTable (AccountNum, Name, CustGroup, PayTerms, Status, ...30+ columns)

Data entity: CustomersV3 (CustomerNumber, CustomerName, CustomerGroup, PaymentTerms, Status, ...)

The entity maps logical field names to table columns, filters out internal fields, and handles lookups and relationships. When you call the OData endpoint /data/Customers, the system queries the entity, which joins underlying tables as needed.

Key Features:

• CRUD Operations: Create, Read, Update, Delete via REST (POST, GET, PATCH, DELETE).
• Filtering: $filter=Name eq 'Acme'
• Sorting: $orderby=Name
• Expansion: $expand=VendorInvoices to join related entities.
• Pagination: $top=100&$skip=200 for large datasets.

Finding Data Entities:

F&O includes 1000+ predefined data entities. Use the Data Entities workspace to search and explore. Common finance entities:

• CompaniesV2 – Legal entities
• CustomersV3 – Customers
• VendorsV2 – Vendors
• SalesOrdersV3 – Sales orders
• PurchaseOrdersV2 – Purchase orders
• GeneralJournalEntries – GL journals
• ProjectInvoices – Project invoices

Most entities are now V2 or V3, indicating they’ve been updated and extended. Newer versions are recommended for new integrations.

Data Management Framework (DMF)

The Data Management Framework is F&O’s native bulk import tool. Use DMF for large data migrations (10k–1M+ rows) when OData is too slow or doesn’t support the entity.

DMF Workflow:

1. Source Data: Prepare CSV, Excel, or data package with source records.
2. Staging: Upload to DMF staging table; raw data sits here untouched.
3. Map & Transform: Define field mappings (source column > target entity field). Add transformations (lookups, conversions, defaults).
4. Validate: DMF checks data quality: required fields, data types, foreign key lookups.
5. Execute: Import runs; data is loaded into target tables. DMF logs errors; rerun for failed records.
6. Post-Process: Run any batch jobs needed (recalculate summaries, re-index, run validations).

DMF Advantages Over OData:

• Faster for large datasets (staging + bulk insert vs. row-by-row REST calls).
• Better error handling and rollback capability.
• Supports source systems that lack OData entities.

DMF Limitations:

• Not real-time; batches run on a schedule.
• Requires DMF entities to be set up; not all F&O tables have DMF entities.
• Transformations are limited; complex logic requires preprocessing.

Table Relationships & Foreign Keys

F&O tables are linked via foreign key relationships. Understanding these relationships is critical for data migrations and custom reporting.

Example: Sales Order Hierarchy

SalesTable (header) links to SalesLine (details) via SalesId:

SalesTable
  SalesId (PK)
  CustAccount
  OrderDate
  Status
  ——> References CustTable.AccountNum

SalesLine
  SalesId (FK) ——> SalesTable.SalesId
  LineNum (PK composite with SalesId)
  ItemId (FK) ——> InventTable.ItemId
  Qty
  Price

Common Table Relationships:

• Header/Detail: SalesTable > SalesLine, PurchTable > PurchLine, CustInvoiceJour > CustInvoiceTrans.
• Lookups: SalesLine.ItemId > InventTable.ItemId, VendInvoiceJour.VendAccount > VendTable.AccountNum.
• Financial Dimensions: Most transaction tables include DimensionAttribute fields; dimension values are stored separately in DimensionAttributeValue table.

Cascade Delete Rules:

Most F&O relationships use “Restricted” delete: if you try to delete a parent record (e.g., customer) that has child records (e.g., invoices), the delete fails. This protects data integrity. Some relationships use “Cascade”, which deletes children automatically (rare, risky).

Custom Fields & Table Extensions

F&O supports customization via table extensions. You can add custom fields to existing tables without modifying base product code.

Example: Add a “Customer Priority” Field to CustTable

[ExtensionOf(tableStr(CustTable))]
public final class CustTable_Extension
{
    public Priority custPriority;
}

Once extended, the new field is automatically included in the table, forms, OData entities (if configured), and reports.

Best Practices for Extensions:

• Use meaningful field names prefixed with your org code (e.g., TDP_PriorityLevel).
• Add fields to OData entities if they’re needed for integration.
• Index custom fields if you query on them frequently.
• Document custom fields in data dictionary or wiki.

Query Performance & Indexing

Common Performance Issues:

• Unindexed Queries: Querying on a non-indexed column (e.g., where Description like '%widget%') scans the entire table. Add an index if this query runs frequently.
• Joins Without Relationships: Joining two large tables (e.g., InventTrans and InventTable) without a defined foreign key is slow. Use the foreign key relationship.
• Large Batch Imports: DMF can time out if batches are too large. Split into 50k-row chunks.

Optimization Tips:

• Use summary tables (InventSum, CustTrans) for reporting; they’re pre-aggregated and faster.
• Add indexes for frequently queried columns (but not too many; indexes slow down writes).
• Partition large tables by date (e.g., InventTransOrigin) to speed up historical queries.
• Use pagination ($top=1000, $skip=1000) when pulling data via OData to avoid memory overload.

Frequently Asked Questions

Q: What’s the difference between a base table and a data entity?
A: A base table is physical (stored in the database). A data entity is logical (a query, view, or wrapper over one or more tables). Data entities provide a REST API interface and hide table complexity. Use data entities for integration; use base tables for custom reports or advanced queries.

Q: Can I create my own data entities?
A: Yes. Use the Data Entity builder in F&O to create a new entity from one or more tables. You can add it to OData, DMF, or keep it internal for custom reporting.

Q: How do I know which table to query for a specific business question?
A: Use the Data Dictionary or Table Browser in F&O to search for tables and their fields. Start with summary tables if available (CustTrans instead of CustInvoiceJour + CustInvoiceTrans). Ask your DBA or consultant if unsure.

Q: What’s the maximum batch size for DMF imports?
A: Depends on available memory, but 50k–100k rows is safe. If importing 1M records, split into 10 batches of 100k. DMF will log progress and retry failed batches.

Q: Can I delete data directly via OData?
A: Yes, but be careful. DELETE via OData removes rows from the target table. Most entities require a child-record check first (you can’t delete a customer if invoices exist). Test in a sandbox first.

Q: How do I handle financial dimensions in queries?
A: Dimension values are stored in a separate table (DimensionAttributeValue) and linked to transactions via a JSONB or XML field. Use the DefaultDimension or similar field to access dimensions in queries. Most reports use BI tools (Power BI) to denormalize dimensions for easier reporting.

Q: What if I need to join two tables that don’t have a predefined foreign key?
A: Define a custom relationship in your report or OData query. If you need this often, ask your DBA to add a foreign key in the database (requires a hotfix or extension). Avoid ad-hoc joins; they slow down as data grows.

Q: Can I modify base F&O tables directly?
A: No. Never modify base product tables. Use table extensions to add fields or create new tables for custom data. Direct modifications break support and break on future updates.