Dynamics 365 F&O Reference Database
A searchable reference for Dynamics 365 Finance & Operations tables, data entities, fields, and relationships. Built for supply chain and analytics practitioners landing F&O data into Databricks via Synapse Link or Fabric Link — with a quirks guide for the DataAreaId partitioning, RecId surrogate keys, InventDim dimensions, enums, and UTC/sentinel dates that trip up every first F&O extract.
How this is scoped
- Scope: the supply-chain core — product, inventory, sales, and procurement — plus the shared party and financial-dimension foundations every table points back to. The catalog spans 59 tables and 26 data entities today and is still growing; it leads with the objects an analyst reaches for most, not a full metadata dump.
- Two axes:F&O exposes data both as raw tables and as export-shaped data entities. This reference documents both — the tables you land, and the entities that expose them — because some core objects (InventTrans, InventSum, DirPartyTable) have no standard export entity and must be landed raw.
- Verification: field names, keys, and structural facts are curated against the Microsoft CDM schema pages; enum integer values ship only when verified, and every description is original wording. Last verified July 2026.
- Built for analytics: relationships focus on the joins you'll actually write — header/line pairs, shared-EDT foreign keys, RecId surrogate references, and the InventDim dimension hop that trips up every first on-hand query.
Frequently Referenced Tables
The workhorses of F&O supply-chain analytics — the inventory ledger and on-hand, sales orders, the item master, and the customer master.
- InventTableReleased product master — one row per item within a company, carrying the RecId link to its shared product; item group and model group are assigned in the InventItemGroupItem / InventModelGroupItem link tables, not here
- InventTransThe inventory transaction ledger — every physical and financial stock movement, with issue/receipt status, quantities, dates, and links back to its source document
- InventSumAggregated on-hand quantities by item and inventory dimension — physical, available, and posted balances
- SalesLineSales order line — one row per order line; the core sell-side transaction, joined to its header by SalesId
- SalesTableSales order header — one row per order carrying customer, status, site, and requested dates
- PurchLinePurchase order line — one row per order line; the core buy-side transaction, joined to its header by PurchId
- InventDimThe inventory dimension hub — one row per unique combination of site, warehouse, location, batch, and serial that every stock quantity hangs off by InventDimId
- CustTableCustomer master — one row per customer account, with the RecId links to its global-address-book party and its default financial dimensions
- ProdTableProduction order header — one row per order carrying the item being built, order status and type, scheduled and actual dates, and the BOM and route it was created from
- VendTableVendor master — one row per vendor account, with the RecId link to its global-address-book party
Frequently Used Data Entities
The export-shaped surfaces behind the order-to-cash and procure-to-pay data — what a Synapse Link or OData export gives you.
- EcoResReleasedProductV2EntityReleased products — the item master joined to its shared product, the standard export for item attributes
- SalesOrderHeaderV2EntitySales order headers — one record per order, the header-level export for order-to-cash
- SalesOrderLineV2EntitySales order lines — one record per order line, the line-level export joined to its header
- CustCustomerV3EntityCustomers — the customer master joined to its address-book party, the standard customer export
- VendVendorV2EntityVendors — the vendor master joined to its address-book party, the standard vendor export
- PurchPurchaseOrderHeaderV2EntityPurchase order headers — one record per order, the header-level export for procure-to-pay
- PurchPurchaseOrderLineV2EntityPurchase order lines — one record per order line, the line-level export joined to its header
- ProdProductionOrderHeaderEntityProduction orders — one record per production order header, the standard export for order status, quantities, and schedule dates
The F&O quirks guide
The encoding and modeling traps that make a first F&O extract wrong in ways that look right. Each links to a worked Databricks SQL pattern.
Every application table is partitioned by company — the WHERE filter, and why you must join on it too.
The universal 64-bit key; RecVersion and Partition; the InventTrans → InventTransOrigin join.
Integers with labels in metadata — verified CASE decodes and the GlobalOptionsetMetadata join.
The hub every stock quantity hangs off by InventDimId. Join, then group by the dimension you need.
Decode DefaultDimension into columns via DimensionAttributeValueSet — a two-hop worked example.
ValidFrom/ValidTo valid-time tables; the current-row filter and as-of joins.
Datetimes are stored in UTC — from_utc_timestamp and the date-boundary trap.
1900-01-01 means 'never'. NULL-wrap it so MIN/AVG over dates don't lie.
X++ writes '' and 0, not NULL. NULLIF patterns, and 0-as-valid-enum vs 0-as-not-set.
TableId + RecId reference pairs, and fanning out with a CASE on the reference category.
Getting F&O into your lakehouse
Export to Data Lake is retired. The extraction guide covers the supported paths — Synapse Link for Dataverse and Fabric Link — the metadata columns they add, soft deletes and incremental MERGE patterns, and when to land raw tables vs entity-shaped exports.
Read the extraction guide →Browse by Module
Organized by functional area. Click a module to see its tables and data entities.
- Product InformationThe shared product master and its released-product records, dimensions groups, and units of measure that every inventory and order table points back to11 tables · 7 entitiesSupply chain
- InventoryOn-hand balances, the inventory transaction ledger, dimension records, and warehouse/location master data11 tables · 3 entitiesSupply chain
- Sales & MarketingSales orders from header through line, plus the customer master on the sell side of order-to-cash7 tables · 6 entitiesSupply chain
- Procurement & SourcingPurchase orders and the vendor master across the procure-to-pay cycle7 tables · 3 entitiesSupply chain
- Production ControlProduction orders, BOMs, and routes that define how items are manufactured8 tables · 2 entitiesSupply chain
- Master PlanningPlanned orders, requirement transactions, and forecasts that translate demand into planned supply2 tables · 0 entitiesSupply chain
- Warehouse ManagementAdvanced warehouse work, loads, shipments, and directed put-away and picking inside the four walls5 tables · 3 entitiesSupply chain
- FinanceThe financial-dimension framework and ledger records the supply-chain tables reference for accounting and reporting4 tables · 1 entities
- Organization AdministrationLegal entities (companies), sites, and the shared organizational setup that defines DataAreaId partitioning2 tables · 1 entities
- Global Address BookThe shared party registry (DirPartyTable) behind every customer, vendor, and organization record2 tables · 0 entities
Sources & further reading
Table structure, entity names, and extraction behavior are authoritative only at source. Use these references to verify before building on any claim in this reference.
- Microsoft CDM schema — Operations tables — the version-tagged field lists per F&O table (the primary source this catalog is curated against).
- Dynamics 365 data entities documentation — the authoritative reference for entity names, public collection names, and OData surfaces.
- Synapse Link / Fabric Link documentation — how F&O tables land in ADLS Gen2 / OneLake, the metadata columns, and the lowercase-column behavior.